How to use Prepared Statement for Transaction?

Question

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 1

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.