實際開發中需要向數據庫發送多條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));
}
}
}
針對JDBC處理事務的操作，在Connection接口中，提供了3個相關的方法，具體如下：
setAutoCommit(boolean autoCommit); // 設置是否自動提交事務 commit(); // 提交事務 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();
}
}
}
使用DBCP數據源需要使用3個jar包，分別是commons-dbcp.jar包(https://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi)、commons-pool.jar(http://commons.apache.org/proper/commons-pool/download_pool.cgi)包和commons-logging.jar(http://commons.apache.org/proper/commons-logging/download_logging.cgi)包。以下程序示例是通過BasicDataSource類直接創建數據源對象。
package server;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
//import com.mysql.jdbc.Statement;
public class DemoDBCP {
public static DataSource ds = null;
static {
// 獲取DBCP數據源實現類
BasicDataSource bds = new BasicDataSource();
// 設置連接池配置信息
bds.setDriverClassName("com.mysql.jdbc.Driver");
bds.setUrl("jdbc:mysql://192.168.1.150/test");
bds.setUsername("teamtalk");
bds.setPassword("123456");
// 設置連接池參數
bds.setInitialSize(5);
bds.setMaxTotal(5);
ds = bds;
}
public static void main(String[] args) throws SQLException {
Connection connection = (Connection) ds.getConnection();
java.sql.DatabaseMetaData metaData = connection.getMetaData();
System.out.println(metaData.getURL());
System.out.println(metaData.getUserName());
System.out.println(metaData.getDriverName());
Statement statement = (Statement) connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * from people");
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
}
}
}
c3p0是目前最流行的開源數據庫連接池之一，它實現了DataSource數據源接口，支持JDBC2和JDB3的標准規范，易於擴展並且性能優越，著名的開源框架Hibernate和Spring使用功能的都是該數據源。c3p0連接數據庫示例（通過配置文件方式），注意：配置文件名稱必須是c3p0-config.xml，該文件必須放在工程bin目錄下。下載地址：https://sourceforge.net/projects/c3p0/?source=typ_redirect
配置文件c3p0-config.xml為：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://192.168.1.150/test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">teamtalk</property>
<property name="password">123456</property>
<property name="checkoutTimeout">3000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<named-config name="demo">
<property name="jdbcUrl">jdbc:mysql://192.168.1.150/test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">teamtalk</property>
<property name="password">123456</property>
<property name="checkoutTimeout">3000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</named-config>
</c3p0-config>
package server;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DemoDBCP {
public static DataSource ds = null;
static {
ComboPooledDataSource cpds = new ComboPooledDataSource();
ds = cpds;
}
public static void main(String[] args) throws SQLException {
Connection connection = (Connection) ds.getConnection();
java.sql.DatabaseMetaData metaData = connection.getMetaData();
System.out.println(metaData.getURL());
System.out.println(metaData.getUserName());
System.out.println(metaData.getDriverName());
}
}
Java JDBC基礎學習小結
