CODEWITHSUNDEEP
javajdbcprepared-statement
R H
R H

Reputation: 397

Looking Pattern for UPDATE SQL in Java

I am looking the best coding pattern for the below update SQL table with prepared statement in Java.

SQL Table name is table1 and attr1, att2, att3, att4, attr5 and .... in table1.

Currently my pseudo 

If (want to update att1 only) {
    PreparedStatement pst = Connection.prepareStatement("UPDATE table1 SET attr1 = ?");
} else if (want to update attr1 & attr2 only) {
    PreparedStatement pst = Connection.prepareStatement("UPDATE table1 SET attr1 = ?, attr2 = ?");
} else if (want to update attr1 & attr2 & attr3) {
    PreparedStatement pst = Connection.prepareStatement("UPDATE table1 SET attr1 = ?, attr2 = ?, attr3 = ?");
}  else if (want to udpate attr1 & attr3) {
   PreparedStatement pst = Connection.prepareStatement("UPDATE table1 SET attr1 = ?, attr3 = ?");
} ......

.... else {
    Bad Request
}

This above code will make more complex with WHERE SQL condition. I don't like this if - else if - else pattern here because very hard to maintain.

Yes, I know other options are dynamically generate UPDATE SQL query and use ORM solution. I believe dynamically generate SQL UPDATE Query logic would become complex.

Please provide any other pattern OR solution which can fit best here.

Upvotes: 1

Views: 273

Answers (1)

CodeClimber
CodeClimber

Reputation: 4664

You could just write one update statement to update all non-key fields and call that each time.

public class MyThing {
     private long uniqueID;
     private String attr1;
     private String attr2;
     private String attr3;
     // ++ rest of attriutes, getters and setters  
}

public MyThing getMyThing(long uniqueID) {
    // code to retrieve MyThing from table1, poulating all attributes
}

public void updateMyThing(MyThing myThing) {
    PreparedStatement pst = Connection.prepareStatement
        (" UPDATE table1 SET attr1 = ?, attr2 = ?, attr3 = ?, attr4 = ?, attr5 = ?" +
         " WHERE id = ? );
    pst.SetString(1, myThing.getAttr1());
    pst.SetString(2, myThing.getAttr2());
    pst.SetString(3, myThing.getAttr3());
    pst.SetString(4, myThing.getAttr4());
    pst.SetString(5, myThing.getAttr5());
    pst.SetString(6, myThing.getId());

    // etc.
}

So retrieve a MyThing object from the DB. Update whatever attributes you want then call the update method. All attributes updated whether they have changed or not

Upvotes: 1

Related Questions