- 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
|
[轉貼]JDBC Transaction example (冷日