|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2019/7/29 13:57 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]JDBC Transaction example
JDBC Transaction exampleJDBC Transaction let you control how and when a transaction should commit into database.
//transaction block start
//SQL insert statement
//SQL update statement
//SQL delete statement
//transaction block end
In simple, JDBC transaction make sure SQL statements within a transaction block are all executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block. See below two examples to understand how JDBC transaction works. 1. Without JDBC TransactionBy default, data will be committed into database when executeUpdate() is called.
String insertTableSQL = "INSERT INTO DBUSER"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
+ "(?,?,?,?)";
String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
+ "WHERE USER_ID = ?";
preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "mkyong101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate(); //data COMMITTED into database.
preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR");
preparedStatementUpdate.setInt(2, 999);
preparedStatementUpdate.executeUpdate(); //Error, value too big, ignore this update statement,
//but user_id=999 is inserted
When this code is executed, the USER_ID = ‘999’ is inserted but the username is not update. 2. With JDBC TransactionTo put this in a transaction, you can use -
dbConnection.setAutoCommit(false); to start a transaction block. -
dbConnection.commit(); to end a transaction block. See code snippets :
dbConnection.setAutoCommit(false); //transaction block start
String insertTableSQL = "INSERT INTO DBUSER"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
+ "(?,?,?,?)";
String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
+ "WHERE USER_ID = ?";
preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "mkyong101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate(); //data IS NOT commit yet
preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR");
preparedStatementUpdate.setInt(2, 999);
preparedStatementUpdate.executeUpdate(); //Error, rollback, including the first insert statement.
dbConnection.commit(); //transaction block end
When this code is executed, update statement is hits error, and make both insert and update statements rollback together. Full JDBC Transaction exampleSee a complete JDBC transaction example.
package com.mkyong.jdbc;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTransactionExample {
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
private static final String DB_USER = "user";
private static final String DB_PASSWORD = "password";
public static void main(String[] argv) throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatementInsert = null;
PreparedStatement preparedStatementUpdate = null;
String insertTableSQL = "INSERT INTO DBUSER"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
+ "(?,?,?,?)";
String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
+ "WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
dbConnection.setAutoCommit(false);
preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "mkyong101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate();
preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
// preparedStatementUpdate.setString(1,
// "A very very long string caused db error");
preparedStatementUpdate.setString(1, "new string");
preparedStatementUpdate.setInt(2, 999);
preparedStatementUpdate.executeUpdate();
dbConnection.commit();
System.out.println("Done!");
} catch (SQLException e) {
System.out.println(e.getMessage());
dbConnection.rollback();
} finally {
if (preparedStatementInsert != null) {
preparedStatementInsert.close();
}
if (preparedStatementUpdate != null) {
preparedStatementUpdate.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
private static java.sql.Timestamp getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return new java.sql.Timestamp(today.getTime());
}
}
原文出處：JDBC Transaction example – Mkyong.com
|
|
|
冷日
(冷日) |
發表時間：2019/7/29 13:59 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]How to use Prepared Statement for Transaction?
- How to use Prepared Statement for Transaction?
I want to insert data in two table with the help of transaction. My query is working properly in my SQL But i don't know how to handle it on java code please help . My code shown Below
private Boolean executeInsertQuery(Connection conn, String schoolID, String branchID,
String studentName, String parentName, String emailId, String password, String className,
String section, int age, String dob, String scholarNo, String address, String contactNo,
int rollType) throws SQLException {
Boolean isSuccess = false;
String statement = "START TRANSACTION;\n" +
"INSERT INTO child_details (SCHOLAR_NUMBER, SCHOOL_ID,BRANCH_ID,CHILD_NAME,ENROLLED_CLASS," +
"CHILD_SECTION,CHILD_AGE,CHILD_DOB) VALUES (?,?,?,?,?,?,?,?);\n" +
"INSERT INTO parents_details (EMAIL_ID, BRANCH_ID,SCHOOL_ID,CHILD_NAME,SCHOLAR_NUMBER,PARENT_CONTACT_NUMBER," +
"PASSWORD,ROLE_TYPE,PARENT_NAME,ADDRESS) VALUES (?,?,?,?,?,?,?,?,?,?);\n" +
"COMMIT";
PreparedStatement stmt = null;
PreparedStatement statement1 = null;
try {
stmt = conn.prepareStatement(statement);
stmt.setString(1, scholarNo);
stmt.setString(2, schoolID);
stmt.setString(3, branchID);
stmt.setString(4, studentName);
stmt.setString(5, className);
stmt.setString(6, section);
stmt.setInt(7, age);
stmt.setString(8, dob);
stmt.setString(9,emailId);
stmt.setString(10, branchID);
stmt.setString(11, schoolID);
stmt.setString(12, studentName);
stmt.setString(13, scholarNo);
stmt.setString(14, contactNo);
stmt.setString(15, password);
stmt.setInt(16, rollType);
stmt.setString(17, parentName);
stmt.setString(18, address);
int count = stmt.executeUpdate();
// int count2 = statement1.executeUpdate();
if (count > 0) {
isSuccess = true;
} else {
isSuccess = false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
stmt.close();
conn.close();
}
}
return isSuccess;
}
private Connection getConnection() {
String url;
Connection conn = null;
try {
if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Production) {
Class.forName("com.mysql.jdbc.GoogleDriver");
url = "jdbc:google:mysql://############?#######";
} else {
Class.forName("com.mysql.jdbc.Driver");
url = "jdbc:mysql://localhost:3306/#######";
}
conn = DriverManager.getConnection(url, "root", "******");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
How to use Prepared statement in this situation?
Here is what I would do. From my past experiences, with multiple insert statements, you can either use batching, multiple prepared call statements, or a callable statement. Using the last idea, here is what your code would look like:
private Boolean executeInsertQuery(Connection conn, String schoolID, String branchID,
String studentName, String parentName, String emailId,
String password, String className, String section,
int age, String dob, String scholarNo, String address,
String contactNo, int rollType) throws SQLException {
boolean isSuccess = false;
String getDBUSERByUserIdSql = "{call insertChildAndParentDetails(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
try (CallableStatement callableStatement = conn.prepareCall(getDBUSERByUserIdSql)) {
callableStatement.setString(1, scholarNo);
callableStatement.setString(2, schoolID);
callableStatement.setString(3, branchID);
callableStatement.setString(4, studentName);
callableStatement.setString(5, className);
callableStatement.setString(6, section);
callableStatement.setInt(7, age);
callableStatement.setString(8, dob);
callableStatement.setString(9, emailId);
callableStatement.setString(10, branchID);
callableStatement.setString(11, schoolID);
callableStatement.setString(12, studentName);
callableStatement.setString(13, scholarNo);
callableStatement.setString(14, contactNo);
callableStatement.setString(15, password);
callableStatement.setInt(16, rollType);
callableStatement.setString(17, parentName);
callableStatement.setString(18, address);
isSuccess = (callableStatement.executeUpdate() > 0);
} catch (SQLException e) {
System.out.println("## we have an exception" + e.getMessage());
}
return isSuccess;
}
In your database scripts, make a stored procedure that is being referenced above in the java code. Here is an example using an oracle DB (NOTE: I don't know your database structure, but add/remove/change the parameters as necessary):
CREATE OR REPLACE PROCEDURE insertChildAndParentDetails(
p_schoolID IN childTable.SCHOOL_ID%TYPE,
p_studentName IN parentTable.STUDENTNAME%TYPE,
p_parentName IN childTable.PARENTNAME%TYPE,
p_emailId IN childTable.EMAILID%TYPE)
IS
BEGIN
INSERT INTO childTable ("SCHOOL_ID", "PARENTNAME", "EMAILID")
VALUES (p_schoolID, p_parentName,p_emailId);
INSERT INTO parentTable ("STUDENTNAME")
VALUES (p_studentName)
COMMIT;
END;
/
If you really want to use multiple prepared statements, separate your method out into two distinctive actions and invoke them from another method, though I personally would stay away from that:
public void insertData() {
insertChildData();
insertParentData();
}
Although, just make sure you do your logic here correctly, to ensure transactions that cause exceptions in one method sync with the other.
Transaction with commit/rollback follow this pattern:
conn.setAutocommit(false); // No commit per statement
try (PreparedStatement stmt1 = conn.prepareSteatement(...)) { // Automatic close.
...
try (PreparedStatement stmt2 = ... )) {
...
conn.commit();
}
} catch (SQLException ex) {
conn.rollback();
}
Try-with-resources is a weird syntax simplifying the otherwise needed closing code.
Auto-generated keys (getGeneratedKeys) might be needed to fetch from the first statement and inserted in the second statement - say if one has an AUTOINCR field. A search will give sample code.
原文出處： java - How to use Prepared Statement for Transaction? - Stack Overflow
|
|
|
|
|
冷日
(冷日) |
發表時間：2019/12/25 8:27 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]JDBC 批量處理
JDBC批量處理 批處理允許將相關的SQL語句組合成一個批處理和一個調用數據庫提交。 當一次發送多個SQL語句到數據庫，可以減少通信開銷的數額，從而提高了性能。 JDBC驅動程序不需要支持此功能。應該使用DatabaseMetaData.supportsBatchUpdates()方法來確定目標數據庫支持批量更新處理。如果你的JDBC驅動程序支持此功能的方法返回true。 聲明addBatch()方法，PreparedStatement和CallableStatement用於各個語句添加到批處理。executeBatch()將用於啟動所有組合在一起的語句的執行。 executeBatch()將返回一個整數數組，數組中的每個元素代表了各自的更新語句的更新計數。 可以添加語句批量處理，可以用theclearBatch()方法刪除它們。此方法刪除所有已添加的addBatch()方法的語句。但是，不能有選擇性地選擇要刪除的語句。 批處理和Statement對象： 下麵是步驟，使用批處理使用說明書對象的典型順序： 使用createStatement()方法創建一個Statement對象。 設置使用自動提交為false，使用 setAutoCommit(). 添加任意多個到批量使用addBatch SQL語句（上創建語句對象）的方法。 執行使用executeBatch()將方法上創建表對象中的所有SQL語句。 最後，提交使用commit()方法的所有更改。 例如： 下麵的代碼段提供了使用Statement對象批量更新中的一個例子：
// Create statement object
Statement stmt = conn.createStatement();
// Set auto-commit to false
conn.setAutoCommit(false);
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
"WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);
// Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
為了更好地理解，建議學習研究 JDBC批處理用Statement對象示例代碼. 批處理使用prepareStatement結果對象： 下麵是步驟，使用批處理用prepareStatement結果對象的典型順序： 創建SQL語句的占位符。 使用任一prepareStatement()方法創建prepareStatement結果對象。 設置使用setAutoCommit()自動提交為false。 添加任意多個批量使用addBatch SQL語句（上創建語句對象）的方法。 執行使用executeBatch()將方法上創建表對象中的所有SQL語句。 最後，提交使用commit()方法的所有更改。 下麵的代碼段提供了使用prepareStatement結果對象批量更新的一個例子：
// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
"VALUES(?, ?, ?, ?)";
// Create PrepareStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);
//Set auto-commit to false
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
.
.
.
.
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
為了更好地理解，建議學習研究 實例代碼.
原文出處：JDBC批量處理 - JDBC基礎教程
|
|
|
冷日
(冷日) |
發表時間：2019/12/25 8:30 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]使用 PreparedStatement 的 Batch 功能
- 使用PreparedStatement的Batch功能
Update大量的數據時, 先Prepare一個INSERT語句再多次的執行, 會導致很多次的網絡連接. 要減少JDBC的調用次數改善性能, 你可以使用PreparedStatement的AddBatch()方法一次性發送多個查詢給數據庫. 例如, 讓我們來比較一下下面的例子.
例 1: 多次執行Prepared Statement
PreparedStatement ps = conn.prepareStatement(
"INSERT into employees values (?, ?, ?)");
for (n = 0; n < 100; n++) {
ps.setString(name[n]);
ps.setLong(id[n]);
ps.setInt(salary[n]);
ps.executeUpdate();
}
例 2: 使用Batch
PreparedStatement ps = conn.prepareStatement(
"INSERT into employees values (?, ?, ?)");
for (n = 0; n < 100; n++) {
ps.setString(name[n]);
ps.setLong(id[n]);
ps.setInt(salary[n]);
ps.addBatch();
}
ps.executeBatch();
在例 1中, PreparedStatement被用來多次執行INSERT語句. 在這裡, 執行了100次INSERT操作, 共有101次網絡往返. 其中,1次往返是預儲statement, 另外100次往返執行每個迭代. 在例2中, 當在100次INSERT操作中使用addBatch()方法時, 只有兩次網絡往返. 1次往返是預儲statement, 另一次是執行batch命令. 雖然Batch命令會用到更多的數據庫的CPU週期, 但是通過減少網絡往返，性能得到提高. 記住, JDBC的性能最大的增進是減少JDBC驅動與數據庫之間的網絡通訊.
————————————————
原文出處：使用PreparedStatement的Batch功能_燕窝-CSDN博客
|
|
|
|
