JDBC批处理

JDBC批处理

一.JDBC批处理概述

• 连续执行多条SQL语句，若逐条执行这些SQL语句，效率会很低。为此，JDBC提供了批处理机制，用于执行多条SQL语句。

二.Statement实现批处理

1.批处理方法

方法	方法描述
Statement.addBatch( )	添加SQL语句
Statement.executeBatch( )	执行批处理
Statement.clearBatch( )	清除批处理

2.使用示例

（1）JDBCUtils类

package cn.com.demo;

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

public class JDBCUtils {
	// 加载驱动并建立数据库连接
	public static Connection getConnection() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		String databaseUrl = "jdbc:mysql://localhost:3306/mydb";
		String username = "root";
		String password = "test";
		Connection connection = DriverManager.getConnection(databaseUrl, username, password);
		return connection;
	}

	// 关闭数据库连接释放资源
	public static void release(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			statement = null;
		}
	}
	
	// 关闭数据库连接释放资源
	public static void release(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

	// 关闭数据库连接释放资源
	public static void release(Statement statement, Connection connection) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			statement = null;
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}

	// 关闭数据库连接释放资源
	public static void release(ResultSet resultSet, Statement statement, Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			resultSet = null;
		}
		release(statement, connection);
	}
}

（2）TestStatementBatch测试类

package cn.com.demo;
import java.sql.Connection;
import java.sql.Statement;
public class TestStatementBatch {
	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JDBCUtils.getConnection();
			statement = connection.createStatement();
			String sql1 = "DROP TABLE IF EXISTS student";
			String sql2 = "CREATE TABLE student (studnet_num int,name varchar(20))";
			String sql3 = "INSERT INTO student VALUES(1,'dodo')";
			String sql4 = "INSERT INTO student VALUES(2,'xixi')";
			statement.addBatch(sql1);
			statement.addBatch(sql2);
			statement.addBatch(sql3);
			statement.addBatch(sql4);
			statement.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(null, statement, connection);
		}
	}
}

三.PreparedStatement实现批处理

1.创建表

-- 创建表
DROP TABLE IF EXISTS student;
CREATE TABLE worker(
   studnet_num INT,
   name VARCHAR(20)
);

2.TestPreparedStatement测试类

package cn.com.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class TestPreparedStatement{
	public static void main(String[] args) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtils.getConnection();
			String sql = "INSERT INTO worker VALUES(?,?)";
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < 123; i++) {
				preparedStatement.setInt(1, i);
				preparedStatement.setString(2, "name" + i);
				preparedStatement.addBatch();
				// 为防止内存溢出，每20条执行一次批处理
				if (i % 20 == 0) {
					preparedStatement.executeBatch();
					preparedStatement.clearBatch();
				}
			}
			//将剩下的未处理SQL执行批处理
			preparedStatement.executeBatch();
			preparedStatement.clearBatch();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
            // 关闭资源
			JDBCUtils.release(null, preparedStatement, connection);
		}
	}
}

四.Statement与PreparedStatement批处理比较

• 比较Statement，使用PreparedStatement实现批处理不仅安全而且效率更高。