CODEWITHSUNDEEP
javajakarta-eejdbc
Edward
Edward

Reputation: 1377

java.sql.SQLException: error occurred during batching: batch must be either executed or cleared

I am getting

java.sql.SQLException: error occurred during batching: batch must be either executed or cleared

in the below code. I want to execute two queries.

String sql_query="insert into m_status values(20,'test');select * from m_user";
String query1 = sql_query.toUpperCase();

String[] results = query1.split(";");

stmt = conn1.createStatement();
conn1.setAutoCommit(false); 
for (int i = 0; i < results.length; i++) {
    if(results[i].startsWith("SELECT")) {
        rs1 = stmt.executeQuery(results[i]);
    }
    else if(results[i].startsWith("INSERT")){
        stmt.addBatch(results[i]);
    }
}
int [] updateCounts = stmt.executeBatch();
conn1.commit(); 
if (rs1 != null)
    rs1.close();
if (stmt != null)
    stmt.close();
if (conn1 != null)
    conn1.close();

Upvotes: 2

Views: 9669

Answers (3)

tibtof
tibtof

Reputation: 7967

Use two statements, one for select queries and another one for updates. executeQuery checks if there batches for that Statament, and if so throws that error. Also, wrap your queries in a try-catch-finally block and close the resources in the finally block. As it is, your code could lead to connection leaks.

Upvotes: 2

jocelyn
jocelyn

Reputation: 898

You test if your query begins with a uppercase SELECT and it's not the case. So your two queries are added to the batch statement. You could use

com.mysql.jdbc.StringUtils.startsWithIgnoreCase(results[i], "select");

Upvotes: 1

Vipul
Vipul

Reputation: 28103

Make sure to use PreparedStatement#executeBatch() when using batches.

Also make sure you execute

int n = preparedStatement.executeUpdate();

Upvotes: 0

Related Questions