Jason學習筆記

/*
 * Transaction交易
 */
import java.sql.*;

public class TransactionDemo {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
			conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
			
			conn.setAutoCommit(false); //隱含交易開始

			String insStmt = "INSERT INTO department VALUES (?, ?)";
			PreparedStatement pstmt = conn.prepareStatement(insStmt);
			pstmt.setInt(1, 401);
			pstmt.setString(2, "Sales");
			pstmt.executeUpdate();
			pstmt.setInt(1, 402);
			pstmt.setString(2, "Service");
			pstmt.executeUpdate();
			pstmt.setInt(1, 403);
			pstmt.setString(2, "Production");
			pstmt.executeUpdate();
			conn.commit();

			pstmt.setInt(1, 404);
			pstmt.setString(2, "Sales1");
			pstmt.executeUpdate();
			pstmt.setInt(1, 405);
			pstmt.setString(2, "Service2");
			pstmt.executeUpdate();
			pstmt.setInt(1, 406);
			pstmt.setString(2, "Production2");
			pstmt.executeUpdate();
			conn.commit(); //結束目前的交易並將所有暫存的資料永久變更至資料庫中

			conn.setAutoCommit(true); //隱含交易結束，回復自動提交模式

			pstmt = conn.prepareStatement("SELECT * FROM department");
			ResultSet rs = pstmt.executeQuery();

			System.out.printf("%10s %20s %n", "deptno", "dname");
			while (rs.next()) {
				System.out.printf("%10s %20s %n", rs.getString("deptno"), rs.getString("dname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback(); //如果有錯誤就結束目前的交易並將暫存的資料遺棄
				System.err.println("Transaction is being rolled back");
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}// end of main()
}// end of class TransactionDemo