JDBC Usage Summary _java

1. First of all explain what is JDBC

Before I first saw this noun, I felt it necessary to know what its English full name was, the Java Database connectivity, which is literally interpreted as
The connectivity of the Java database, which is to provide a set of intermediate links, to connect Java code and database, so that it can directly through the operation of Java code can control the database.

The essence of 2.JDBC

It is a Java access to the database solution, hope to be able to use the same way to access different databases, to achieve a specific database-independent Java operation interface.
JDBC is essentially a set of standardized interfaces, different database vendors according to their own database characteristics to implement this set of interfaces, the implementation of the specific methods do not need us to care.

The main interfaces implemented by database vendors in JDBC are as follows:

DriverManager: Drive Management

Connection,
DatabaseMetaData: Connection interface

Statement,
PreparedStatement
CallableStatement: Statement Object interface

ResultSet,
ResultSetMetaData: Result set interface

The working principle of 3.JDBC

The programmer invokes the part of the underlying database vendor implementation,
That is: 1 through the connection interface to achieve the first step of the connection
2) Transfer of SQL statements via statement results
3 database return results saved to resultset result collection connect to the mouth
The main process can be summed up as:

1) Load driver, create connection

2) Creating statement objects

3 Execute SQL statement

4) Send back the result set

5) Close the connection

I'll take my Oracle database with Eclips as an example to illustrate how to implement JDBC

1) Load Driver

The method used is: Class.forName ("Drive");

The way I know how to get to this drive is to expand the JDBC jar package, If I am using Ojdbc6.jar, find the oracle.jdbc.driver inside, find the oracledriver in the inside and then the right mouse button qualitycopy, paste into the quotation mark, such as: Class.forName (" Oracle.jdbc.driver.OracleDriver ");

2) Create a connection

The methods used are: Conn=drivermanager.getconnection ("Jdbc:oracle:thin: @IP Address: 1521:ORCL", "Database Account", "Database Password");

such as: Conn=drivermanager.getconnection ("Jdbc:oracle:thin:@172.16.3.8:1521:orcl", "jsd1601", "jsd1601");

3) Creating statement objects

The method used is: Statement stmt=conn.createstatement ();

Note that you use the connection created above in the second step to invoke the method

Calling the Executeupdate method to pass the SQL statement in to execute the written SQL statement, it should be noted that the Executeupdate method can execute the SQL type Insert,update,delete

such as: Statement stmt=conn.createstatement ();
String sql= "INSERT into Emp_jiawenzhe (Empno,ename,sal,deptno) VALUES (1000, ' Jia Wenji ', 1500,10)";
int i=stmt.executeupdate (SQL);

The return value in this is the number of rows affected, and we can tell if the operation was successful based on the number of rows affected.

4) and for the returned result set mainly refers to the select operation (here is not mentioned)

5) finally close the connection

such as: Conn.close ();

Enumerate a whole code, including comments:

Package jdbc_day01;
Import java.sql.*; 
 /** * Demo JDBC Operation steps * 1, load driver * 2, create connection * 3, create statement Object * 4, send SQL statement * 5, if send is a SELECT statement, processing result set * 6, close connection * @author Jiawenzhe * * */public class JDBCDome01 {public static void main (string[] args) throws SQLException {//requirements: Create an employee, employee number, employee name, work
    Capital, department number//1, Connection conn=null;
      try {class.forname ("oracle.jdbc.driver.OracleDriver");
      SYSTEM.OUT.PRINTLN ("Drive loaded successfully");
      2, conn= drivermanager.getconnection ("Jdbc:oracle:thin:@172.16.3.8:1521:orcl", "jsd1601", "jsd1601");
      System.out.println (Conn.getclass (). GetName ()); 3,//statment statement object.
       Send and Execute SQL statement/* int excuteupdate (String sql);
      * Send insert,update,delete statement * return value int indicates the number of rows that affect the database table/Statement stmt=conn.createstatement ();
      String sql= "INSERT into Emp_jiawenzhe (empno,ename,sal,deptno)" + "VALUES (1000, ' Wangxiao ', 1500,10)";
      int i=stmt.executeupdate (SQL); if (i>0) {System.out.println ("Save success!
      ");
      } catch (ClassNotFoundException e) {e.printstacktrace ();
    1, Log//2, notify the caller throw new RuntimeException ("Load driver error", e);
        }finally{//close connection if (Conn!=null) {try {conn.close ();
        catch (SQLException e) {e.printstacktrace ();

 }
      }
    }

  }
}

Note: The jar package that Oracle database can use: Ojdbc14.jar/ojdbc6.jar (oracle12c with this)
The jar package that the MySQL database can use: Mysql-connector-java-5.0.4-bin.jar

Load Driver class:

Class.forName ("Com.mysql.jdbc.Driver");

1. The Package method leads

After learning Java for a while, the deepest feeling is that when writing a series of code at the moment, if there is the reuse of code, will certainly choose a method to a certain degree of code encapsulation, from the encapsulation of a method to encapsulate a class, before the mention of the use of JDBC database operations (add, delete, change, Because the query involves operations on the result set, separate declarations are divided into four processes:

1) Load Driver

2) Create a connection

3 Create statement object, send SQL

4) Close the connection

In other words, when we do any database operations, we have to do the above steps, this will appear the code redundancy, and then put forward to encapsulate these common steps into a class, so that it can become a tool class, for my use

2. Three versions of the package

1. Version 1
I am in the study of these classes is gradually a gradual package, gradually improve, because if the ultimate version of the package directly, for beginners is difficult to accept, I will elaborate the first is the simplest encapsulation method:

You will find in the front, regardless of how you want to operate the database, load drivers are essential, and load drivers inside the main is the declaration, driver name, IP address, port number, database account name, password and so on, and these are the essence of the string so I define these strings separately, as shown here:

  private static String driverclass= "Oracle.jdbc.driver.OracleDriver";
  private static String url= "JDBC:ORACLE:THIN:LOSTHOST:1521:ORCL";
  private static String user= "System";
  private static String password= "123";

So when I create the connection I can get the name of the variable to replace the long string, while the load-driven method Class.forName, we declare it in the static block, because loading the data while loading the driver

As shown in the following:

  static{
    try {
      class.forname (driverclass);
    } catch (ClassNotFoundException e) {
      e.printstacktrace ();
      throw new RuntimeException ("Load driver error", e);  
    }
  

To create a connection, we call the DriverManager's Getconnection method, and put the corresponding username and password into it, we put this method directly into my own defined method, and then call my method directly to create the connection, and note that The return value of the method is connection this object, which is well understood, because it is the object to which the connection type is to be obtained, as shown here:

   public static  Connection getconnection () throws sqlexception{
    Connection
    conn= Drivermanager.getconnection (Url,user,password);
    return conn;
  }

Then you create the statement object, send SQL, and of course SQL is the only special place in this, because the SQL has to do a variety of things, so this step is not encapsulated.

The last thing to do is close the connection, which is to call the closed () method, as shown below

   public static void Close (Connection conn) {
     if (conn!=null) {
       try {
        conn.close ();
      } catch ( SQLException e) {
        e.printstacktrace ();
        throw new RuntimeException ("Turn Off connection error", e);}}}
   

The first version of the package as a whole is as follows:

Package jbbc_day01;
Import java.sql.Connection;
Import Java.sql.DriverManager;

Import java.sql.SQLException; /** * Used to manage connection * @author Jiawenzhe * */public class Dbutil {private static String driverclass= "Oracle.jdbc.driver.Or
  Acledriver ";
  private static String url= "JDBC:ORACLE:THIN:LOSTHOST:1521:ORCL";
  private static String user= "System";
  private static String password= "123";
    1, load Drive static{try {class.forname (driverclass);
      catch (ClassNotFoundException e) {e.printstacktrace ();  
    throw new RuntimeException ("Load driver error", e); 
   }//2, create a connection/* How to define a method that can create a connection * return value type: whether there is a result of the operation, if so, the type of the result is the return value type * parameter list: * The method function has an indeterminate data participation operation, if there is, is the parameter */public static Connection getconnection () throws sqlexception{Connection conn= Drivermanager.getconnect
    Ion (Url,user,password);
  Return conn;
      }//3, turn off connection public static void Close (Connection conn) {if (conn!=null) {try {conn.close (); catch (SqlexCeption e) {e.printstacktrace ();
      throw new RuntimeException ("Turn Off connection error", E);

 }
     }
   }
   
}

Such a package is good, and then in the SQL operation of the direct call to this package class, and all you need to write is to create your statement object, send your SQL statement can

2. Version 2

In fact, version 2 is very similar to version one, is to make up for version 1 of a deficiency, that is, when you change the database, the different database account password is not the same, this will be the account password and IP address modification, I have these in the form of strings encapsulated in the tool class, That means we have to modify the tool class every time we change the database. This is not appropriate, so the proposed an improvement is to put the connection data into a configuration file, the tool class to read the configuration file, we modify the time directly to modify the configuration file can be

Before introducing this version, I drew a new class that was properties that read the file and read the contents of the configuration file as a stream and returned to the tool class.

First I put the configuration file example out, in fact, do not have to explain, at a glance, as follows:

Jdbc.driverclass=oracle.jdbc.driver.oracledriver
JDBC.URL=JDBC:ORACLE:THIN:LOCALHOST:ORCL
jdbc.user= System
jdbc.password=123

The front is the key, followed by the value, the previous key value is our own definition, just like the definition of variable name, the following value is our database of the actual situation, here we need to pay special attention to IS, The suffix name of this profile must end with. properties, because this class of properties can be read.

The examples in this section are as follows:

  static{
    try {
      //Load Attribute file Data property
      pop=new properties ();
      Pop.load (DBUtil2.class.getClassLoader (). getResourceAsStream ("db.properties"));
       Url=pop.getproperty ("Jdbc.url");
       Driverclass=pop.getproperty ("Jdbc.driverclass");
       User=pop.getproperty ("Jdbc.user");
       Password=pop.getproperty ("Jdbc.password");
      Class.forName (Driverclass);
    } catch (ClassNotFoundException e) {
      e.printstacktrace ();
      throw new RuntimeException ("Load driver error", e);  
    } catch (IOException e) {
      //TODO auto-generated catch block
      e.printstacktrace ();
    }
  }

Load () This method is to read also can be considered to load the configuration file, this whole sentence to remember the line, do not have to delve into its meaning, and getproperty () is through the key to get the corresponding value, very much like the key value to the collection to obtain the form

The overall code is as follows:

Package jbbc_day01;
Import Java.io.FileInputStream;
Import java.io.IOException;
Import Java.io.InputStream;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;

Import java.util.Properties;
  /** * is used to manage connection * Database connection information, save in attribute file * @author Jiawenzhe */public class DBUtil2 {private static String driverclass;
  private static String URL;
  private static String user;
  private static String password;
      1, load driver static{try {//Load Property File Data Properties Pop=new ();
       Pop.load (DBUtil2.class.getClassLoader (). getResourceAsStream ("db.properties"));
       Url=pop.getproperty ("Jdbc.url");
       Driverclass=pop.getproperty ("Jdbc.driverclass");
       User=pop.getproperty ("Jdbc.user");
      Password=pop.getproperty ("Jdbc.password");
    Class.forName (Driverclass);
      catch (ClassNotFoundException e) {e.printstacktrace ();  
    throw new RuntimeException ("Load driver error", e); catch (IOException e) {//TODO AuTo-generated Catch block E.printstacktrace (); 
   }//2, create a connection/* How to define a method that can create a connection * return value type: whether there is a result of the operation, if so, the type of the result is the return value type * parameter list: * The method function has an indeterminate data participation operation, if there is, is the parameter */public static Connection getconnection () throws sqlexception{Connection conn= Drivermanager.getconnect
    Ion (Url,user,password);
  Return conn;
      }//3, turn off connection public static void Close (Connection conn) {if (conn!=null) {try {conn.close ();
        catch (SQLException e) {e.printstacktrace ();
      throw new RuntimeException ("Turn Off connection error", E);
 }
     }
   }
   
}