no operation allowed after statement is closed. Java, Mysql
I have this code:
public void saveGPSReading(GPSReader gpsRd){
String sql="INSERT INTO EventData(" +
"accountID," +
"deviceID," +
"timestamp,"+
"statusCode," +
"latitude," +
"longitude, " +
"gpsAge," +
"speedKPH, " +
"heading," +
"altitude," +
"address," +
"creationTime)" +
"values(" +
"'demo','teltonika001',?,61714,?,?,0,?,?,?,'--not implemented--',?)";
try{
if(!con.isClosed()){
stmt = con.prepareStatement(sql);
for(DeviceEvent de : gpsRd.getEvents()){
GPSElement gps=de.getGpsElement();
IOElementReader ioe= de.getIoElement();
stmt.setLong(1, gps.getTimestamp().getTime());
stmt.setDouble(2, gps.getLatitude());
stmt.setDouble(3, gps.getLongitude());
stmt.setLong(4, gps.getSpeed());
stmt.setLong(5, gps.getAngle());
stmt.setLong(6, gps.getAltitude());
stmt.setLong(7, (new Date()).getTime());
stmt.addBatch();
//String s = stmt.toString();
}
stmt.executeBatch();
}
}
catch(Exception err){
err.printStackTrace();
}
}
which gets called here:
public void readPacketData(){
if(info.isValid()==true){
for( int i =0; i< info.getRecords(); i++){
GPSElement gps= readGPSElement();
IOElementReader io= readIOElement();
DeviceEvent dEvt = new DeviceEvent(gps,io);
gpsReader.addEvent(dEvt);
}
}
dbMan.saveGPSReading(gpsReader);
thats the only place where i call it and am getting that exception at the line
stmt.executeBatch();
java.sql.BatchUpdateException: No operations allowed after statement closed. at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449) at DBManager.saveGPSReading(DBManager.java:64)
any help here?
am not closing anything here. not even the connection, at any point in my code
::EDIT::
so i followed your tips, changed the code to this:
public void saveGPSReading(GPSReader gpsRd){
String sql="INSERT INTO EventData(" +
"accountID," +
"deviceID," +
"timestamp,"+
"statusCode," +
"latitude," +
"longitude, " +
"gpsAge," +
"speedKPH, " +
"heading," +
"altitude," +
"address," +
"creationTime)" +
"values(" +
"'demo','teltonika001',?,61714,?,?,0,?,?,?,'--not implemented--',?)";
try{
Connection con= createConnection();
con.setAutoCommit(false);
stmt = con.prepareStatement(sql);
for(DeviceEvent de : gpsRd.getEvents()){
GPSElement gps=de.getGpsElement();
IOElementReader ioe= de.getIoElement();
stmt.setLong(1, gps.getTimestamp().getTime());
stmt.setDouble(2, gps.getLatitude());
stmt.setDouble(3, g开发者_运维知识库ps.getLongitude());
stmt.setLong(4, gps.getSpeed());
stmt.setLong(5, gps.getAngle());
stmt.setLong(6, gps.getAltitude());
stmt.setLong(7, (new Date()).getTime());
stmt.addBatch();
}
int[] updateCounts = stmt.executeBatch();
checkUpdateCounts(updateCounts);
con.commit();
}
catch (BatchUpdateException e) {
int[] updateCounts = e.getUpdateCounts();
checkUpdateCounts(updateCounts);
try {
con.rollback();
}
catch (Exception e2) {
e.printStackTrace();
}
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
stmt.close();
con.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
with a new method to create the connection :
public Connection createConnection(){
Connection con= null;
if(con ==null){
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
String c=Constants.DB.DB_URL+"?user="+Constants.DB.DB_USER+"&"+"password="+Constants.DB.DB_PASSWORD;
con=DriverManager.getConnection(c);
}
catch (SQLException e) {
e.printStackTrace();
}
}
return con;
}
the same issue still persists
anfy2002us already posted in his comment that your DB-connection may not yet be open (because isClosed only is true if it was opened and then closed).
If that is not the problem here (e.g. if you are really checking that the connection is opened before you enter your readPacketData()
, then do it like in this example:
http://www.java2s.com/Code/Java/Database-SQL-JDBC/DemoPreparedStatementAddBatchMySQL.htm
We should explicitly handle a batch-fail and rollback-retry on fail.
精彩评论