JDBC database connection process and driver loading and design patterns

First, import the jar package of JDBC;

Next, the code:

Class.forName (xxx.xx.xx) returns a class
Class.forName (xxx.xx.xx) is used to ask JVM to find and load the specified class, which means that JVM executes a static snippet of the class.

JDBC connects to the database

The & # 8226; Create a program that connects to the database with JDBC, including seven steps:

1. Load JDBC driver:

Before connecting to the database, first load the driver of the database you want to connect to to the JVM(Java virtual machine),

This is achieved through the static method forName(String className) of the java.lang.Class class.

Such as:

try{　　
　　// loading MySql The driving class  
　　Class.forName("com.mysql.jdbc.Driver") ; 　　
　　// Note: about Class Of the class forName methods 
　　// Load with this method className Class into the memory space 
　　/* Class Of the class forName methods , As follows: 
　　@CallerSensitive
　　public static Class<?> forName(String className) throws ClassNotFoundException {
　　　　Class<?> caller = Reflection.getCallerClass();// Gets the object that calls this method 
 Let's say it's passed in "com.mysql.jdbc.Driver" . 
　　　　jvm It's going to drive jar The package  com.mysql.jdbc  , using the caller's class loader to invoke the Driver class 
 ( com.mysql.jdbc  In the Driver Class inherited from java.sql In the package Driver interface ,
 Header of the class: public class com.mysql.jdbc.Driver extends com.mysql.jdbc.NonRegisteringDriver implements java.sql.Driver {}
 You can see that this class inherits java.sql.Driver Interface, so you can use a combination of factory mode and singleton mode in your program to call) 
　　　　return forName0(className, true, ClassLoader.getClassLoader(caller), caller);// If there is an exception to the load, an exception is returned 
　　　　//forName0(className, true, ClassLoader.getClassLoader(caller), caller);
　　　　// ClassLoader.getClassLoader(caller) Class loader calls using the class that calls this method driver The class corresponding to the interface 
　　}
　　*/
}catch(ClassNotFoundException e){ 
　　System.out.println(" The driver class could not be found   , the load driver failed! "); 
　　e.printStackTrace() ;
}

After a successful load, an instance of the Driver class is registered with the DriverManager class. DriverManager.getConnection (url, username, password) operations, which we'll talk about later

2. Provide URL for JDBC connection

Connecting to URL defines the protocol, subprotocol, and data source identity for connecting to a database.

Written format: protocol: subprotocol: data source identifier

Protocol: always start with jdbc in JDBC

Subprotocol: is the name of the driver or database management system for the bridge connection.

Data source identification: marks the address and connection port where the database source was found.

For example: (MySql connection URL) : jdbc: mysql: / / localhost: 3306 / test

The full path for jdbc: mysql: / / localhost: 3306 / test & # 63; useUnicode = true & characterEncoding = gbk;

useUnicode=true: indicates whether the Unicode character set is used. If characterEncoding is set to gb2312 or GBK, this parameter must be set to true.

characterEncoding=gbk: character encoding.

3. Create a connection to the database

The & # 8226; To connect to the database, request and obtain the Connection object from xx.sql.DriverManager,

This object represents a connection to a database.

The & # 8226; Using DriverManager getConnectin(String url, String username,

The String password) method passes in the path, username, and password of the database to which you want to connect.

Such as:

// The connection MySql Database, username and password root 
String url = "jdbc:mysql://localhost:3306/test" ; 
String username = "root" ; 
String password = "root" ; 
try{ 
Connection con = DriverManager.getConnection(url , username , password ) ; 
}catch(SQLException se){ 
System.out.println(" Database connection failed! "); 
se.printStackTrace() ; 
}

4. Create an Statement

The & # 8226; To execute the SQL statement, you must obtain an java.sql.Statement instance, which is of the following three types:

1. Execute the static SQL statement. This is usually done through an Statement instance.

2. Execute the dynamic SQL statement. It is usually implemented through an PreparedStatement instance.

3. Execute database stored procedures. It is usually implemented through an CallableStatement instance.

Specific implementation method:

Statement stmt = con.createStatement() ; 
　　PreparedStatement pstmt = con.prepareStatement(sql) ; 
　　CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;

5. Execute SQL

The Statement interface provides three ways to execute SQL statements: executeQuery, executeUpdate, and execute

1. ResultSet executeQuery(String sqlString) : execute the SQL statement of query database and return 1 result set (ResultSet) object.

2. int executeUpdate(String sqlString) : used to execute INSERT, UPDATE or DELETE statements and SQL DDL statements, such as CREATE TABLE and DROP TABLE

3. execute(sqlString): a statement that returns multiple result sets, multiple update counts, or a combination of two.

Specific implementation code:

ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ; 
　　int rows = stmt.executeUpdate("INSERT INTO ...") ; 
　　boolean flag = stmt.execute(String sql) ;

6. Processing results

Two cases:

1. The number of records affected by this operation is returned when the update is performed.

2. The result returned by executing the query is an ResultSet object.

The & # 8226; ResultSet contains all the rows that meet the criteria in the SQL statement, and it provides access to the data in those rows through a set of get methods.

The & # 8226; Get the data using the access method of the result set (ResultSet) object:

while(rs.next()){ 
　　　　String name = rs.getString("name") ; 
　　　　String pass = rs.getString(1) ; //  This method is efficient  
　　}

(columns are numbered from left to right and start at column 1)

7. Close the JDBC object

Close all the JDBC objects in use to free JDBC resources after the operation is completed. Close the JDBC objects in the reverse order of declaration:

1. Close the record set

2. Closing statements

Close the connection object

if(rs != null){ //  Close recordset  
　　try{ 
rs.close() ; 
　　}catch(SQLException e){ 
　　　　e.printStackTrace() ; 
　　} 
} 
if(stmt != null){ //  Closing statement  
　　try{ 
　　　　stmt.close() ; 
　　}catch(SQLException e){ 
　　　　e.printStackTrace() ; 
　　} 
} 
if(conn != null){ //  Close the connection object  
　　try{ 
　　　　conn.close() ; 
　　}catch(SQLException e){ 
　　　　e.printStackTrace() ; 
　　} 
}