In this program, I have to set already existing value for ssn in where clause (ie: in setString (3, ) )

Question

           public void UpdateCustomer(Customer customer) throws CustomerHomeException, ClassNotFoundException{

    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String url = "jdbc:odbc:Mydb";
        String user = "user1";
        String password = "password";
        Connection con = DriverManager.getConnection(url,user,password);
        PreparedStatement smt= con.prepareStatement("update customer SET ssn = ? customer_name = ? where ssn = ?");
        if(getCustomer.equals(customer.getSocialSecurityNumber()))
        smt.setString(1,customer.getSocialSecurityNumber());
        smt.setString(2, customer.getName());
        smt.setString(3, customer.);
        smt.executeUpdate();
        smt.close();
        con.close();
}
    catch (SQLException e){
        throw new CustomerHomeException("Failed to create CustomerHome", e);
    }
}

but I am confused how can i retrieve value for existing ssn. Also I have a method getCustomers to retrieve a particular customer separately. Will that help

Use a `SELECT` SQL query . – Sotirios Delimanolis May 15 '13 at 20:08 — Sotirios Delimanolis, May 15 '13 at 20:08

score 1 · Answer 1 · edited May 23 '17 at 11:55

1

I recommend that you revisit your database model. You are using SSN as a primary-key (based on you WHERE clause), but then you're trying to update it. It is EXTREMELY poor practice to update primary keys (ref: https://stackoverflow.com/a/3838649/2065845). If you introduce some other key value (an auto-incrementing ID perhaps?), then you'll be able to modify your update statement's WHERE clause to use that ID, and the fact that you no longer have the old SSN becomes unimportant.

Barring that, you'll either need to modify your method signature to include the old SSN, or you'll need to execute a SELECT with some other value to get the old SSN (though, if you can do that, I have to wonder why you don't just use that in the WHERE clause of your UPDATE statement).

edited May 23 '17 at 11:55

Community

1
1

answered May 15 '13 at 20:14

GamerJosh

3,419
3
19
28

1

"*you won't be able to [update a primary key]*" that is simply not true. Technically you *can* update a primary key (if e.g. FK constraints are still valid) - But I do agree that it's a bad habit and not recommended. – May 15 '13 at 21:24
@a_horse_with_no_name - I've done some testing, and you are correct--I'll modify my answer. I have used a database in the past that prevented this from happening (and which I, obviously, based that statement on), but perhaps it was a setting that was changed from the default behavior--unfortunately, I don't know the details as I wasn't the DBA. – GamerJosh May 16 '13 at 14:52
1

Do you expect your customers to change their Social Security Numbers? Frankly, it is improper under US Law to use social security numbers as identification at all. Even if you have legal reasons to store SSNs in your databases, you should encrypt those data to avoid data spills. – Eric Jablow May 16 '13 at 16:23

In this program, I have to set already existing value for ssn in where clause (ie: in setString (3, ) )

1 Answers1

Linked