batch.java

JDK实例宝典代码

package book.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 执行一批SQL语句（批处理）
 */
public class Batch {
	
	/**
	 * 判断数据库是否支持批处理
	 * @param con
	 * @return
	 */
	public static boolean supportBatch(Connection con){
		try {
			// 得到数据库的元数据
			DatabaseMetaData md = con.getMetaData();
			return md.supportsBatchUpdates();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 执行一批SQL语句
	 * @param con	数据库的连接
	 * @param sqls	待执行的SQL数组
	 * @return
	 */
	public static int[] goBatch(Connection con, String[] sqls){
		if (sqls == null){
			return null;
		}
		Statement sm = null;
		try {
			sm = con.createStatement();
			// 将所有的SQL语句添加到Statement中
			for (int i=0; i<sqls.length; i++){
				sm.addBatch(sqls[i]);
			}
			// 一次执行多条SQL语句
			return sm.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OperateDB.closeStatement(sm);
		}
		return null;
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		String dbName = "studentdb";
		String tableName = "student_basic";
		String userName = "test";
		String password = "test";
		String[] sqls = new String[3];
		sqls[0] = "UPDATE student_basic SET score=95 where name='john'";
		sqls[1] = "INSERT INTO student_basic (name, age, score) VALUES ('lisi', 17, 78)";
		sqls[2] = "DELETE FROM student_basic where name='zhangsan'";
		
		Connection con = null;
		try {
			// 获得数据库连接
			con = DBConnector.getMySQLConnection(null, null, null, dbName,
					userName, password);
			// 判断是否支持批处理
			boolean supportBatch = Batch.supportBatch(con);
			System.out.println("支持Batch？ " + supportBatch);
			if (supportBatch){
				// 执行一批SQL语句
				int[] results = Batch.goBatch(con, sqls);
				// 分析执行的结果
				for (int i=0; i<sqls.length; i++){
					if (results[i] >= 0){
						System.out.println("语句: " + sqls[i] + " 执行成功，影响了"
								+ results[i] + "行数据");
					} else if (results[i] == Statement.SUCCESS_NO_INFO){
						System.out.println("语句: " + sqls[i] + " 执行成功，影响的行数未知");
					} else if (results[i] == Statement.EXECUTE_FAILED){
						System.out.println("语句: " + sqls[i] + " 执行失败");
					}
				}
			}
		} catch (ClassNotFoundException e1) {
			throw e1;
		} catch (SQLException e2) {
			throw e2;
		} finally {
			// 关闭数据库连接
			OperateDB.closeConnection(con);
		}
	}
}