Search code examples
javajdbcderby

Unable to execute update using email address as primary key in Derby

I looked at these questions and my problem is unique to them.

JDBC Update Statement not working in Netbeans but working in SQL

Why is my JDBC prepared statement update not updating the database?

Whenever I use a valid 'email' to update rows in my user_account table, the statement locks up and then I get the following exception:

java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested

For this input it works fine:

EMAIL:chris
NAME:null
PHONE:null
ADDRESS:null

When I use the real input:

EMAIL:validEmailWants@someDomain.com
NAME:null
PHONE:null
ADDRESS:null

The failure occurs.

[EDIT]

However, the input below works fine:

EMAIL:chris@localhost.com
NAME:null
PHONE:null
ADDRESS:null

So it can't be the special characters in email addresses that are causing the problem.

I am using a prepared statement so I am pretty sure I don't need to 'escape' any of the special characters in the email address. But I could be wrong.

The method is below:

public boolean setInfo(InfoBean p){

    ////  NOT USING THE PASSED IN INFOBEAN  //////////
    ////  BECAUSE I HAD TO ISOLATE THE BUG  //////////


    InfoBean proto = new InfoBean();

    //FAIL
    proto.setEmail("validEmailWants@someDomain.com"); 

    //PASS
    //proto.setEmail("chris@localhost.com");


    //PASS
    //proto.setEmail("validEmailHasWanted@someDomain.com");

    // PASS
    //proto.setEmail("validEmailWantsHasWanted@someDomain.com"); 



    System.out.println("EMAIL:"   + proto.getEmail());
    System.out.println("NAME:"    + proto.getName());
    System.out.println("PHONE:"   + proto.getPhoneNumber());
    System.out.println("ADDRESS:" + proto.getAddress());


    Connection c = null;

    try{  
        c = this.dataSource.getConnection();

        String s = "update user_account "
                +  "set name = ?, "
                +  "phone = ?, "
                +  "address = ?, "
                +  "image = ?"
                +  "where email = ?";

        PreparedStatement ps = c.prepareStatement(s);

        if(proto.getName() == null)
            ps.setString(1, ""); 
        else
            ps.setString(1, proto.getName());


        ps.setString(2, proto.getPhoneNumber());
        ps.setString(3, proto.getAddress());

        BufferedImage image = proto.getImage();
        if(image != null){
            ByteArrayOutputStream outStream = new ByteArrayOutputStream();
            ImageIO.write(image, "png", outStream);
            ps.setBlob(4, new ByteArrayInputStream(outStream.toByteArray()));
        }
        else{
            ps.setNull(4, java.sql.Types.BLOB);
        }

        ps.setString(5, proto.getEmail());

        ps.executeUpdate();

        ps.close();
        c.close();
        return true;
}
    catch(Exception e){
        System.out.println("\n\n SOME KIND OF EXCEPTION \n\n");
        e.printStackTrace();            
    }
    finally{
        //System.out.println("\n\n SOME KIND OF EXCEPTION \n\n");
        closeConnection(c);
    }
    return false;

}

The database structure is shown below. The underlined words are the primary keys. The diamonds are relationship sets. Circles are attributes (the column names). The squares are tables.

enter image description here

Below is a printout of the user_account table to compare the passing and failing cases specific attribute values. enter image description here

[UPDATE]

I ran a test case where I set the fields to something that was NOT NULL, then set the back again to NULL. (This was on a different email), and there was no problems. There seems to be something about that particular email which is causing the failure.

Solution

  • I found the problem. I was logging in the user in a separate request (which I forgot about) and not closing the connection properly afterward. enter image description here

    (A simple connection.close() in another method fixed the problem).