what can be used as an iterator while using prepared statement in jdbc;here I want to check if the customer exists

Question

             public void addCustomer(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("insert into customer (ssn,customer_name) values (?,?)");
        if (this.getCustomers().equals(customer.getSocialSecurityNumber()) == false){
        smt.setString(1, customer.getSocialSecurityNumber());
        smt.setString(2, customer.getName());
        smt.executeUpdate();
        }
        smt.close();
        con.close();
    } catch (SQLException e) {
        throw new CustomerHomeException("Failed to create CustomerHome", e);
    }

the key we use is the ssnumber ; i need to iterate through the database table and check using prepared statement

Possible duplicate http://stackoverflow.com/questions/16574002/in-this-program-i-have-to-set-already-existing-value-for-ssn-in-where-clause-i — Reimeus, May 15 '13 at 22:23
I strongly advise you to follow some JDBC, Java and SQL tutorials instead of spamming SO with each and every question you have. — Mark Rotteveel, May 16 '13 at 07:58

Ravi K Thapliyal · Answer 1 · 2013-05-15T22:59:50.340

1

You can use one of the two approaches.

Check the SQL state code (for unique constraint violation)

} catch(SQLException e) {
    if(e.getSQLState().equals("23505")) // for DB2
        System.err.println("Customer already exists!");
}

Fire a SELECT first to check if the Customer exists

ResultSet rs = con.createStatement().executeQuery(
 "SELECT * FROM customer WHERE ssn = " + customer.getSocialSecurityNumber());
if(rs.next()) {
    System.err.println("Customer already exists!");
    return;
}

SQL State codes (Disclaimer: Please check your DB's documentation and verify :)

 DB2        23505
 Derby      23505
 Oracle     23000
 MySQL      23000
 SQL Server 23000

edited May 15 '13 at 22:59

answered May 15 '13 at 22:27

Ravi K Thapliyal

51,095
9
76
89

@a_horse_with_no_name SQL state codes are standardized I suppose. No harm in verifying though. – Ravi K Thapliyal May 15 '13 at 22:32
I don't think the actual state code is standardized, only the "group" (the first two digits). Oracle for example does not return 2305, nor do SQL Server or MySQL. – May 15 '13 at 22:36
@a_horse_with_no_name You're correct. Thanks for pointing this out. – Ravi K Thapliyal May 15 '13 at 23:00

what can be used as an iterator while using prepared statement in jdbc;here I want to check if the customer exists

1 Answers1