Introduction

What's JDBC

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

JDBC library (includes APIs)的主要用途包括

• Making a connection to a database. 连接数据库

• Creating SQL or MySQL statements. 创建SQL或者MySQL语句

• Executing SQL or MySQL queries in the database. 执行SQL或者MySQL查询

• Viewing & Modifying the resulting records. 查看或者修改结果

JDBC架构

JDBC支持两层或者三层处理逻辑。但是一般，包括两层结构

• JDBC API: This provides the application-to-JDBC Manager connection.

• JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

JDBC drivers实现了JDBC APIs定义的接口。

JDBC连接

连接JDBC通常有四个简单的步骤

• Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.

• Register JDBC Driver: This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.

• Database URL Formulation: This is to create a properly formatted address that points to the database to which you wish to connect.

• Create Connection Object: Finally, code a call to the DriverManagerobject's getConnection( ) method to establish actual database connection.

注册JDBC driver

注册JDBC driver有两种方法：

Approach 1: Class.forName() 自动将driver相关class加载到内存

try {

   Class.forName("oracle.jdbc.driver.OracleDriver");

}

catch(ClassNotFoundException ex) {

   System.out.println("Error: unable to load driver class!");

   System.exit(1);

}

Approach 2: DriverManager.registerDriver() 如果使用的是 non-JDK compliant JVM

try {

   Driver myDriver = new oracle.jdbc.driver.OracleDriver();

   DriverManager.registerDriver( myDriver );

}

catch(ClassNotFoundException ex) {

   System.out.println("Error: unable to load driver class!");

   System.exit(1);

}

Database URL formulation

加载完driver之后，可以用DriverManager.getConnection()建立连接。

下面是常用数据库与connection URL的映射表

JDBC statement，PreparedStatement & CallableStatement

根据不同的需用，可以选择不同的statement接口。

Statement

创建statement

Statement stmt = null;

try {

   stmt = conn.createStatement( );

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   . . .

}

执行statement

创建完statement object之后，可以用来执行SQL语句

• boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.

• int executeUpdate (String SQL): Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.

• ResultSet executeQuery (String SQL): Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

关闭statement

如果close connection节省数据库资源一样，close statement可以确保资源的合理回收。

Statement stmt = null;

try {

   stmt = conn.createStatement( );

   . . .

}

catch (SQLException e) {

   . . .

}

finally {

   stmt.close();

}

JDBC 批处理

批处理(batch processing)允许一次执行多条SQL语句。由于JDBC drivers并没有要求实现这个功能，因此使用前先用 DatabaseMetaData.supportsBatchUpdates() 检测目标数据库是否支持批处理。

Statement对象的批处理

• Create a Statement object using either createStatement() methods.

• Set auto-commit to false using setAutoCommit().

• Add as many as SQL statements you like into batch using addBatch()method on created statement object.

• Execute all the SQL statements using executeBatch() method on created statement object.

• Finally, commit all the changes using commit() method.

// Create statement object

Statement stmt = conn.createStatement();

// Set auto-commit to false

conn.setAutoCommit(false);

// Create SQL statement

String SQL = "INSERT INTO Employees (id, first, last, age) " +

             "VALUES(200,'Zia', 'Ali', 30)";

// Add above SQL statement in the batch.

stmt.addBatch(SQL);

// Create one more SQL statement

String SQL = "INSERT INTO Employees (id, first, last, age) " +

             "VALUES(201,'Raj', 'Kumar', 35)";

// Add above SQL statement in the batch.

stmt.addBatch(SQL);

// Create one more SQL statement

String SQL = "UPDATE Employees SET age = 35 " +

             "WHERE id = 100";

// Add above SQL statement in the batch.

stmt.addBatch(SQL);

// Create an int[] to hold returned values

int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes

conn.commit();

PreparedStatement对象的批处理

// Create SQL statement

String SQL = "INSERT INTO Employees (id, first, last, age) " +

             "VALUES(?, ?, ?, ?)";

// Create PrepareStatement object

PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false

conn.setAutoCommit(false);

// Set the variables

pstmt.setInt( 1, 400 );

pstmt.setString( 2, "Pappu" );

pstmt.setString( 3, "Singh" );

pstmt.setInt( 4, 33 );

// Add it to the batch

pstmt.addBatch();

// Set the variables

pstmt.setInt( 1, 401 );

pstmt.setString( 2, "Pawan" );

pstmt.setString( 3, "Singh" );

pstmt.setInt( 4, 31 );

// Add it to the batch

pstmt.addBatch();

//add more batches

.

.

.

.

//Create an int[] to hold returned values

int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes

conn.commit();

References

http://www.tutorialspoint.com/jdbc/jdbc-where-clause.htm

http://*.com/questions/2839321/connect-java-to-a-mysql-database