Complete JDBC connection database code and steps in Java development

JDBC connection to database
The & # 8226; Creating a JDBC connection to a database consists of seven steps:
1. Load the JDBC driver:
Before connecting to the database, first load the driver for the database you want to connect to into the JVM,
This is done through the static method forName(String className) of the java.lang.Class Class.
Such as:

try{ 
 //Load the driver class for MySql
 Class.forName("com.mysql.jdbc.Driver") ; 
 }catch(ClassNotFoundException e){ 
 System.out.println(" The driver class could not be found   , load driver failed! "); 
 e.printStackTrace() ; 
 }

After a successful load, an instance of the Driver class is registered with the DriverManager class.
2. Provide the URL for the JDBC connection
The & # 8226; The connection URL defines the protocol, subprotocol, and data source identity for connecting to the database.
The & # 8226; Written form: 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 identity: marks the address and connection port where the database source was found.
For example :(MySql connection URL)
JDBC: mysql:
/ / localhost: 3306 / test & # 63; UseUnicode = true&characterEncoding = GBK;
UseUnicode =true: indicates the use of the Unicode character set. If characterEncoding is set to
For gb2312 or GBK, this parameter must be set to true. CharacterEncoding = GBK: characterEncoding method.

3. Create a connection to the database

The & # 8226; To connect to the database, you need to request java.sql.DriverManager and get the Connection object,
This object represents a connection to a database.
The & # 8226; GetConnectin (String url, String username,
The String password method passes in the path of the database to which you want to connect, the database user name, and
Password to get.
Such as:

//Connect to the MySql database with the username and password as 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 a Statement

The & # 8226; To execute an SQL Statement, you must obtain a java.sql.statement instance, which is divided into the following three
Type:
1. Execute static SQL statements. This is typically implemented with a Statement instance.
2. Execute dynamic SQL statements. It is usually implemented through a PreparedStatement instance.
3. Execute database stored procedures. This is typically implemented through a CallableStatement instance.
Specific implementation method:

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

5. Execute the SQL statement

The Statement interface provides three methods for executing SQL statements: executeQuery and executeUpdate
And the execute
1. ResultSet executeQuery(String sqlString) : execute the SQL statement to query the database
, returns a ResultSet object.
Int executeUpdate(String sqlString) : used to perform INSERT, UPDATE, or
DELETE statements and SQL DDL statements such as CREATE TABLE and DROP TABLE
Execute (sqlString): returns multiple result sets, multiple update counts, or a combination of the two
Statements.
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 a ResultSet object.
The & # 8226; The ResultSet contains all the rows that meet the criteria in the SQL statement, and it provides these pairs through a set of get methods
Access to data in a row.
The & # 8226; Using the access method of a ResultSet object to obtain data:

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

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

Close the JDBC object

When the operation is complete, close all the JDBC objects in use to free the JDBC resources and turn off the sequence harmony
In reverse order:
1. Close the recordset
2. Closing statement
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() ; 
 } 
 }

Example code:

package com.b510; 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
/** 
 * 
 * @author Hongten</br> 
 * @date 2012-7-16 
 * 
 */
public class JDBCTest { 
  public static void main(String[] args) { 
    String driver = "com.mysql.jdbc.Driver"; 
    String dbName = "spring"; 
    String passwrod = "root"; 
    String userName = "root"; 
    String url = "jdbc:mysql://localhost:3308/" + dbName; 
    String sql = "select * from users"; 
 
    try { 
      Class.forName(driver); 
      Connection conn = DriverManager.getConnection(url, userName, 
          passwrod); 
      PreparedStatement ps = conn.prepareStatement(sql); 
      ResultSet rs = ps.executeQuery(); 
      while (rs.next()) { 
        System.out.println("id : " + rs.getInt(1) + " name : "
            + rs.getString(2) + " password : " + rs.getString(3)); 
      } 
 
      //Close recordset
      if (rs != null) { 
        try { 
          rs.close(); 
        } catch (SQLException e) { 
          e.printStackTrace(); 
        } 
      } 
 
      //Closing statement
      if (ps != null) { 
        try { 
          ps.close(); 
        } catch (SQLException e) { 
          e.printStackTrace(); 
        } 
      } 
 
      //Close the link object
      if (conn != null) { 
        try { 
          conn.close(); 
        } catch (SQLException e) { 
          e.printStackTrace(); 
        } 
      } 
 
    } catch (Exception e) { 
      e.printStackTrace(); 
    } 
  } 
 
}

Operation effect:

Id: 3 name: hongten password: 123