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);
		}
	}
}