Jason學習筆記

/*
 * 設一變數 int batchsize=3，使批次作業每3筆即執行一次
 */

import java.sql.*;

public class BatchHomeWork1 {
	public static void main(String[] args) {
		Connection conn = null;

		try {     
			String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
			conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
			
			String qryStmt = "SELECT empno, salary FROM employee";
			PreparedStatement pstmt = conn.prepareStatement(qryStmt);
			ResultSet rs = pstmt.executeQuery();
			
			String updateStmt = "UPDATE employee SET salary = ? WHERE empno = ?";
			pstmt = conn.prepareStatement(updateStmt);
			int count=0; //統計addBatch()內有幾次SQL指令
			int batchsize=3; //當addBatch()內有3次SQL指令就寫入資料庫
			while (rs.next()) {
				pstmt.setDouble(1, rs.getDouble(2) * 1.1);
				pstmt.setInt(2, rs.getInt(1));
				pstmt.addBatch();
				count++; //每執行一筆SQL指令+1
				if((count%batchsize)==0){ 
				pstmt.executeBatch();//當SQL指令筆數為3的倍數時就執行批次處理	
				}
			}
			pstmt.executeBatch();//餘數不為0的資料，等while迴圈執行完後一同寫入資料庫

			qryStmt = "SELECT ename, salary FROM employee";
			pstmt = conn.prepareStatement(qryStmt);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println("name = " + rs.getString("ename"));
				System.out.println("salary = " + rs.getDouble("salary"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch(SQLException e) {
					e.printStackTrace();
				}
		}
	}
}