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

Question

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

tibtof · Accepted Answer · 2012-06-14 09:53:33Z

2

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.

edited Jun 14, 2012 at 9:53

answered Jun 14, 2012 at 9:20

tibtof

7,9971 gold badge35 silver badges50 bronze badges

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

Comments

jocelyn · Accepted Answer · 2012-06-14 09:23:20Z

1

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

answered Jun 14, 2012 at 9:23

jocelyn

8987 silver badges13 bronze badges

Comments

Vipul · Accepted Answer · 2012-06-14 09:21:41Z

0

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

Also make sure you execute

int n = preparedStatement.executeUpdate();

answered Jun 14, 2012 at 9:21

Vipul

28.2k7 gold badges63 silver badges76 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related