shailendra kushwah shailendra kushwah - 5 months ago 8
MySQL Question

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?

Answer

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.