CODEWITHSUNDEEP
javamysqljsptomcatnetbeans
tuzzo
tuzzo

Reputation: 77

JSP: problems with multiple queries and generated keys

I have this java method:

public boolean insertAuthor(String userid, String password){
    try{
        String query1 = "INSERT INTO user (id, firstName, lastName, belonging, country) VALUES(?,?,?,?,?)";
        PreparedStatement stmt = this.dbConn.prepareStatement(query1);
        stmt.setInt(1,0);
        stmt.setString(2,"default"); //Yes, it's correct with "default"
        stmt.setString(3,"default");
        stmt.setString(4,"default");
        stmt.setString(5,"default");
        //stmt.executeUpdate();
        stmt.executeUpdate(query1, PreparedStatement.RETURN_GENERATED_KEYS);
        ResultSet rs = stmt.getGeneratedKeys();
        int key=0;
        if ( rs.next() ) {
            key = rs.getInt(1);
        }
        String query2 = "INSERT INTO authentication (id, address, password, user_id, login_id) VALUES(?,?,?,?,?)";
        stmt = this.dbConn.prepareStatement(query2);
        stmt.setInt(1,0);
        stmt.setString(2,"default");
        stmt.setString(2,password);
        stmt.setInt(2,key);
        stmt.setString(2,userid);
        stmt.executeUpdate();
        return true;
    }catch(Exception e){
        System.err.println(e.getMessage());
    }
    return false;
}

Let me explain: I would like to execute two queries and the second one need the key that is generated in the first query (I need the primary key of the table "user" because user-authentication is a 1:1 relationship).

So:

  1. Is this the correct way to execute more than one query?
  2. Am I missing something with the returning key? Because if I run ONLY executeUpdate() and I comment every row below it the method works fine, but when I run the code in the example (with the first executeUpdate() commented) I get false (only false, no exception). Do I have to check something in my database?

Thanks in advance.

EDIT: I found a solution. It was an error in columns and not in the method for getting the generated key itself. I will choose Joop Eggen's answer for the improvements that he showed me. Thanks!

Upvotes: 0

Views: 61

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109593

There were a couple of improvements needed.

String query1 = "INSERT INTO user (firstName, lastName, belonging, country)"
        + " VALUES(?,?,?,?)";
String query2 = "INSERT INTO authentication (address, password, user_id, login_id)"
        + " VALUES(?,?,?,?)";
try (PreparedStatement stmt1 = this.dbConn.prepareStatement(query1,
                PreparedStatement.RETURN_GENERATED_KEYS);
        stmt2 = this.dbConn.prepareStatement(query2)) {
    stmt1.setString(1, "default");
    stmt1.setString(2, "default");
    stmt1.setString(3, "default");
    stmt1.setString(4, "default");
    stmt1.executeUpdate();
    try (ResultSet rs = stmt1.getGeneratedKeys())  {
        if (rs.next()) {
            int userid = rs.getInt(1);
            stmt2.setString(1, "default");
            stmt2.setString(2, password);
            stmt2.setInt(3, key);
            stmt2.setString(4, userid);
            stmt2.executeUpdate();
            return true;
        }
    }
} catch (SQLException e) {
    System.err.println(e.getMessage());
}
return false;
  1. Try-with-resources close automatically, also on exception and return.
  2. You have two prepared statements to close.
  3. The executeUpdate with the SQL is for the parents class Statement, and does disrespect the parameter settings. You chose that for the generated keys parameter, but that goes into Connection.prepareStatement.
  4. (SQL) The generated keys should not be listed/quasi-inserted.
  5. It is debatable whether one should catch the SQLException here. throws SQLException is what works for me.

Upvotes: 1

Ayo K
Ayo K

Reputation: 1774

I'll advise you have a username field in your user table so after inserting you can simply do a Select id from user Where username...

Upvotes: 0

Related Questions