Java JDBC高級特性

1、JDBC批處理

實際開發中需要向數據庫發送多條SQL語句，這時，如果逐條執行SQL語句，效率會很低，因此可以使用JDBC提供的批處理機制。Statement和PreparedStatemen都實現了批處理。測試表結構如下：

Statement批處理程序示例

package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class DemoJDBC {
public static void main(String[] args) throws Exception {
// 加載驅動類
Class.forName("com.mysql.jdbc.Driver");

// 通過DriverManager獲取數據庫連接
String url = "jdbc:mysql://192.168.1.150/test";
String user = "teamtalk";
String password = "123456";
Connection connection = (Connection) DriverManager.getConnection(
url, user, password);

String sql1 = "DROP TABLE IF EXISTS people";
String sql2 = "CREATE TABLE people(id int, name varchar(20))";
String sql3 = "INSERT people VALUES(2, 'hdu')";
String sql4 = "UPDATE people SET id = 1";
Statement statement = (Statement) connection.createStatement();
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
statement.addBatch(sql4);
statement.executeBatch();

ResultSet resultSet = statement.executeQuery("SELECT * from people");
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
}
}

PreparedStatement批處理

package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class DemoJDBC {
public static void main(String[] args) throws Exception {
// 加載驅動類
Class.forName("com.mysql.jdbc.Driver");

// 通過DriverManager獲取數據庫連接
String url = "jdbc:mysql://192.168.1.150/test";
String user = "teamtalk";
String password = "123456";
Connection connection = (Connection) DriverManager.getConnection(
url, user, password);

PreparedStatement statement = (PreparedStatement) connection.prepareStatement("INSERT people VALUES(?,?)");
for (int i = 1; i < 4; i++) {
statement.setInt(1, i);
statement.setString(2, "hdu" + i);
statement.addBatch();
}
statement.executeBatch();

ResultSet resultSet = statement.executeQuery("SELECT * from people");
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
}
}

2、JDBC處理事務

針對JDBC處理事務的操作，在Connection接口中，提供了3個相關的方法，具體如下：

1 setAutoCommit(boolean autoCommit); // 設置是否自動提交事務
2 commit(); // 提交事務
3 rollback(); // 撤銷事務

將setAutoCommit()方法參數設置為false後，事務必須使用conn.commit()方法提交，而事務回滾不一定顯式執行conn.rollback()。如果程序最後沒有執行conn.commit()，事務也會回滾，一般是直接拋出異常，終止程序的正常執行。因此，通常情況下，會conn.rollback()語句放在catch語句塊執行。

package demo.jdbc;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class FirstJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
Connection connection = null;
try {
// 加載驅動類
Class.forName("com.mysql.jdbc.Driver");

// 通過DriverManager獲取數據庫連接
String url = "jdbc:mysql://192.168.1.150/test";
String user = "teamtalk";
String password = "123456";
connection = (Connection) DriverManager.getConnection(
url, user, password);
// 關閉事務的自動提交
connection.setAutoCommit(false);

Statement statement = (Statement) connection.createStatement();
PreparedStatement statement1 = (PreparedStatement) connection.prepareStatement("INSERT people VALUES(?, ?)");
PreparedStatement statement2 = (PreparedStatement) connection.prepareStatement("INSERT people VALUES(?, ?)");

statement1.setInt(1, 1);
statement1.setString(2, "hdu1");
statement2.setInt(1, 2);
statement2.setString(2, "hdu2");

statement1.executeUpdate();
statement2.executeUpdate();

ResultSet resultSet = statement.executeQuery("SELECT * from people");
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
}
catch (Exception e) {
// 回滾事務
connection.rollback();
e.printStackTrace();
}
}
}

更多詳情見請繼續閱讀下一頁的精彩內容： http://www.linuxidc.com/Linux/2016-04/129791p2.htm