CODEWITHSUNDEEP
javamysqlsqljoin
jasim
jasim

Reputation: 459

How to merge different MySQL queries with set values into single query in Java?

I want to merge four different SQL queries into single query in order to reduce usage of SQL connections

This is my code:

        long WTPty = 0L; // holds some value from other part of program
        long NTPty = 0L; // holds some value from other part of program
        long ETPty = 0L; // holds some value from other part of program
        long STPty = 0L; // holds some value from other part of program

            Statement stmt = (Statement) conn.createStatement();
//query 1    
    String w_tblpty="update tbl_priority SET total='"+WTPty+"' where priority= 'west'";
    stmt.executeUpdate(w_tblpty);
//query 2     
    String n_tblpty="update tbl_priority SET total='"+NTPty+"' where priority= 'north'";
    stmt.executeUpdate(n_tblpty);
//query 3     
    String s_tblpty="update tbl_priority SET total='"+STPty+"' where priority= 'south'";
    stmt.executeUpdate(s_tblpty);
//query 4     
    String e_tblpty="update tbl_priority SET total='"+ETPty+"' where priority= 'east'";
    stmt.executeUpdate(e_tblpty);

My objective is to reduce SQL connection usage and optimize the code. Is it possible to merge the above four queries into just single one?

Upvotes: 0

Views: 144

Answers (2)

Andreas
Andreas

Reputation: 159106

As Jon Skeet said, use a PreparedStatement, addBatch(), and executeBatch(). Also, use try-with-resources:

String sql = "UPDATE tbl_priority SET total = ? where priority = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setLong  (1, WTPty);
    stmt.setString(2, "west");
    stmt.addBatch();

    stmt.setLong  (1, NTPty);
    stmt.setString(2, "north");
    stmt.addBatch();

    stmt.setLong  (1, STPty);
    stmt.setString(2, "south");
    stmt.addBatch();

    stmt.setLong  (1, ETPty);
    stmt.setString(2, "east");
    stmt.addBatch();

    stmt.executeBatch();
}

Upvotes: 2

Xephi
Xephi

Reputation: 431

You can use addBatch method on the Statement object :

    long WTPty = 0L; // holds some value from other part of program
    long NTPty = 0L; // holds some value from other part of program
    long ETPty = 0L; // holds some value from other part of program
    long STPty = 0L; // holds some value from other part of program

    Statement stmt = (Statement) conn.createStatement();
    //query 1    
    String w_tblpty="update tbl_priority SET total='"+WTPty+"' where priority= 'west'";
    //query 2     
    String n_tblpty="update tbl_priority SET total='"+NTPty+"' where priority= 'north'";
    //query 3     
    String s_tblpty="update tbl_priority SET total='"+STPty+"' where priority= 'south'";
    //query 4     
    String e_tblpty="update tbl_priority SET total='"+ETPty+"' where priority= 'east'";
    stmt.addBatch(w_tblpty);
    stmt.addBatch(n_tblpty);
    stmt.addBatch(s_tblpty);
    stmt.addBatch(e_tblpty);
    stmt.executeBatch();

Upvotes: 1

Related Questions