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

Question

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?

Hi, Any piece of code is highly appreciated & Thanks in Advance. — jasim
– jasim, Commented Dec 18, 2016 at 16:33
Firstly, start using parameterized SQL with prepared statements instead of putting values directly in the SQL. Then use addBatch... — Jon Skeet
– Jon Skeet, Commented Dec 18, 2016 at 16:37
UPDATE statements are not queries. A "query" is defined in the English language as "a question or a request for information". In SQL context, that means a SELECT statement. — Andreas
– Andreas, Commented Dec 18, 2016 at 17:04

Community · Accepted Answer · 2017-05-23 12:08:40Z

2

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();
}

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Dec 18, 2016 at 17:16

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xephi Over a year ago

More secure version :p

jasim Over a year ago

@Andreas Thank you very much :)

Xephi · Accepted Answer · 2016-12-18 17:10:46Z

1

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();

edited Dec 18, 2016 at 17:10

answered Dec 18, 2016 at 16:39

Xephi

4312 silver badges9 bronze badges

2 Comments

jasim Over a year ago

That looks nice. I'll try it. Thank you very much :)

Xephi Over a year ago

You're welcome ! If that works, do not forget to validate answer ;)

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related