Java - Mysql - Multiple query

Question

String qLink = "";

                        qLink = "INSERT INTO trackgps.queclinklogs(Supplier,NtwProtocol,IMEI,log,DBTIME)" +
                                "VALUES"                    +
                                "("                         +
                                "'" + supplier              + "',"+
                                "'" + protocol              + "',"+
                                "'" + failedQIMEI           + "',"+
                                "'" + failedQLog            + "',"+
                                "'" + currentQuecTimestamp  + "'" +
                                "),"                        +
                                "("                         +
                                "'" + supplier              + "'" + "," +
                                "'" + protocol              + "'" + "," +
                                "'" + QuecLinkIMEI          + "'" + "," +
                                "'" + data2                 + "'" + "," +
                                "'" + currentQuecTimestamp  + "'" +
                                ")";    


                        Statement stmtLink = connQ.createStatement();
                        stmtLink.execute(qLink);
                        stmtLink.close();

String bytesconsumption = "";

                    bytesconsumption = "INSERT INTO test_backoffice.carrierDataConsumption(IMEI,beginMonth,endMonth,dataConsumed,month,year) VALUES"    +
                                        "("                                                                                                             +
                                        "'"+ QuecLinkIMEI                                                                                   + "'" + "," +
                                        "NOW()"                                                                                                   + "," +
                                        "NOW()"                                                                                                   + "," +
                                        "'"+ totalBytesConsumed                                                                                  + "'," +
                                        "MONTH(NOW())"                                                                                            + "," +
                                        "YEAR(NOW())"                                                                                                   +
                                        ") ON DUPLICATE KEY UPDATE endMonth = NOW(), dataConsumed = dataConsumed + " + totalBytesConsumed;

                    Statement stmtbytesconsumption;

                    stmtbytesconsumption = connQ.createStatement();
                    stmtbytesconsumption.execute(bytesconsumption);
                    stmtbytesconsumption.close();

String qdebug = "";

    qdebug = "INSERT INTO trackgps.rawdata(Module,SubModule,IMEI,listenerTime,msg)" +
            "VALUES"                    +
            "("                         +
            "'"+ "LISTENER TCP"         + "'" + "," +
            "'"+ SubMod                 + "'" + "," +
            "'"+ identifier             + "'" + "," +
            "'"+ listendatetime         + "'" + "," +
            "'"+ msg                    + "'" +
            ")";    

    Statement stmtqdebug = conn.createStatement();
    stmtqdebug.execute(qdebug);
    stmtqdebug.close();

Is there anyway to execute this three inserts in just one java statement? Instead of creating 3 Statements with 3 executes and 3 closes?

Other question I have, Should I use Statements or PrepareStatements?

agad · Accepted Answer · 2014-02-14 15:34:31Z

1

You can call all 3 queries on one statement:

Statement stmt = conn.createStatement();
stmt.executeUpdate(qLink);
stmt.executeUpdate(bytesconsumption);
stmt.executeUpdate(qdebug);
stmt.close();

Use PreparedSatement instead of Statement when you want:

to execute the same statement many times with different set of parameters
don't take care about parameter formating, eg. if listendatetime is of type Timestamp, you can use just ps.setTimestamp(4, listendatetime) and a driver formats it properly independently on underlaying database.

answered Feb 14, 2014 at 15:34

agad

2,1891 gold badge20 silver badges33 bronze badges

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

Comments

user4018366 · Accepted Answer · 2014-02-14 15:21:54Z

1

You can concatenate the queries with a ";" character to split it and execute all in a unique statement.

The query:

"INSERT INTO table1 ... ;INSERT INTO table2 ...;"

and the execution:

Statement st = conn.createStatement();
st.execute("INSERT INTO table1 ... ;INSERT INTO table2 ...;");
st.close();

Cheers

answered Feb 14, 2014 at 15:21

user4018366

4 Comments

ThelmaJay Over a year ago

tks :). I'm going to try it.

ThelmaJay Over a year ago

it doesn't work :(. It only works when I do separate prepared statement.

user4018366 Over a year ago

You can use @agad solution! :)

ThelmaJay Over a year ago

Tk u @fastebro. I will use it :)

Collectives™ on Stack Overflow

Java - Mysql - Multiple query

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related