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个相关的方法,具体如下:
setAutoCommit(boolean autoCommit); // 设置是否自动提交事务
commit(); // 提交事务
rollback(); // 撤销事务
将setAutoCommit()方法参数设置为false后,事务必须使用conn.commit()方法提交,而事务回滚不一定显式执行conn.rollback()。如果程序最后没有执行conn.commit(),事务也会回滚,一般是直接抛出异常,终止程序的正常执行。因此,通常情况下,会conn.rollback()语句放在catch语句块执行。
将setAutoCommit()方法参数设置为false后,如果没有提交事务,也就是没有调用conn.commit()方法,则数据库中的内容不会更新,修改的只是内存缓冲区中的数据。
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();
}
}
}
3、JDBC连接池
DBCP数据源
使用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数据库连接池
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());
}
}