1

I was preparing a simple INSERT query with PreparedStatement over MySQL. I understand the usage and difference between execute(), int executeUpdate, and ResultSet executeQuery() methods. I have used executeUpdate() with my INSERT query which should, execution, return the number of affected rows, as follows:

public boolean registerStudent()throws SQLException{
    int i = 0;
    boolean flag = false;
    try{
        String query = "INSERT INTO userinfo (`name`,`email`,`qualification`,`password`,`centre`,`registrationTime`,`registrationNumber`) VALUES (?,?,?,?,?,now(),?)";
         pstmt = cn.prepareStatement(query);
         pstmt.setString(1,getStudentname());
         pstmt.setString(2,getEmail());
         pstmt.setString(3,getQualification());
         pstmt.setString(4,getPassword());
         pstmt.setString(5,getCentre());
         pstmt.setInt(6, getRegistrationCount());
         i = pstmt.executeUpdate();
         if(i==1){
             flag = true;
             return flag;
         }
    }
    catch(Exception e){
        e.printStackTrace();
    }
    return flag;
}

However, I am getting this SQLException :

java.sql.SQLException: Can not issue executeUpdate() for SELECTs

even though I am not using any SELECT query but an INSERT!

Is there any other mistake I am making? because my program seems to be running properly otherwise.

And here is my jdbc connection code written in a separate java class :

public class DBConnection {
static Connection cn;
public static Connection getConnection()throws SQLException, IOException{
    try{
        Class.forName("com.mysql.jdbc.Driver");
        cn = DriverManager.getConnection("jdbc:mysql://localhost/sampleapp", "root", "");
    }
    catch(Exception e){
        e.printStackTrace();
    }
    return cn;
}
Harsh Vardhan
  • 103
  • 1
  • 3
  • 7

2 Answers2

1

The Connection and PreparedStatement (and ResultSet) variables should all be local, not static or instance class members. You're running into a thread-safety issue.

user207421
  • 305,947
  • 44
  • 307
  • 483
  • BTW, I agree with you that the ResultSet and PreparedStatement objects should be local. The connection,however, should be global as it's normally expensive to create a database connection. – dsp_user Feb 02 '16 at 11:26
  • @dsp_user That's what connection pools are for. [JDBC Connections are not thread safe](http://stackoverflow.com/a/1531103/207421): you need one per thread. – user207421 Feb 02 '16 at 11:31
  • Yes, and they're usually implemented as singletons. But the OP isn't using a connection pool. Anyway, let's leave it at that.:) – dsp_user Feb 02 '16 at 11:36
  • @dsp_user Let's leave it that the OP should be using local variables for all three objects, as this answer says. – user207421 Feb 02 '16 at 11:45
  • That's for him/her to decide. I wouldn't. – dsp_user Feb 02 '16 at 11:46
  • Also, your comment about thread-safety may not always be true (e.g. https://jdbc.postgresql.org/documentation/92/thread.HTML) – dsp_user Feb 02 '16 at 14:34
  • @dsp_user It is for me to decide how I will leave a discussion. Your link is broken, but a connection object generally cannot be thread-safe on the wire unless it synchronises itself, which blocks concurrent use, which is highly undesirable. A thread should have its own connection. Period. – user207421 Feb 02 '16 at 22:38
  • Sorry for the link. This one should work ( https://jdbc.postgresql.org/documentation/92/thread.html ). Yes, synchronized access may cause performance issues but it may be fine for some applications. (although not for web applications with high traffic) – dsp_user Feb 03 '16 at 07:41
  • @dsp_user This is just illogical. On the one hand you're warning against JDBC drivers that don't implement the specification, and yet here you are using exactly the same issue as a supporting argument. – user207421 Feb 03 '16 at 09:32
  • I'm not trying to defend myself. My answer is flawed (for the reasons explained) but so is yours (because we don't whether or not the OP has multithread issues). Anyway, let's just drop it cuz we apparently disagree.:) – dsp_user Feb 03 '16 at 09:36
-1

The executeUpdate method cannot be called on either a PreparedStatement or CallableStatement.

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String)

EDIT: I missed that you used the executeUpdate() without parameters, which should work but note that not every JDBC driver complies with the Java JDBC specifications.

dsp_user
  • 2,061
  • 2
  • 16
  • 23
  • Please provide your source for this bizarre assertion. – user207421 Feb 02 '16 at 09:44
  • @Ejp, People are all too quick to downvote here on SO. – dsp_user Feb 02 '16 at 09:56
  • He isn't calling the method you cited. He is calling [executeUpdate()](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate()) without any parameters. Some people are all too quick to answer here on SO. Your claim isn't even remotely plausible. – user207421 Feb 02 '16 at 09:58
  • If you are now suggesting that JDBC drivers exist which don't permit [`PreparedStatement.executeUpdate()`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate()) to be called despite the fact that it is directly declared in that interface, and that give this error message when you do so, again you need to provide some evidence. You seem to be clutching at straws frankly. – user207421 Feb 02 '16 at 11:33
  • You can find examples here on SO, that some JDBC drivers don't throw an sqle even if you use a select statement with updateQuery. Anayway, I said, in my update, that it should work, didn't I. – dsp_user Feb 02 '16 at 11:39
  • If they don't throw an exception when they should, how is this an answer to a question about an exception being thrown when it shouldn't? – user207421 Feb 02 '16 at 11:44