在此程序中，我必须在where子句中设置ssn的现有值(即:在setString(3，)中) [英] In this program, I have to set already existing value for ssn in where clause (ie: in setString (3, ) )

问题描述

           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);
    }
}

但是我很困惑如何为现有的ssn检索值.我也有一个getCustomers方法来分别检索特定的客户.有帮助吗

解决方案

我建议您重新访问数据库模型.您将SSN用作主键(基于WHERE子句)，但是随后您尝试对其进行更新.更新主键是极端的错误做法(参考: https://stackoverflow.com/a/3838649 /2065845 ).如果引入其他一些键值(也许是自动递增的ID?)，则可以修改更新语句的WHERE子句以使用该ID，并且不再拥有旧SSN的事实就变得不重要了. /p>

除非如此，您要么需要修改方法签名以包括旧的SSN，要么需要执行带有其他值的SELECT以获得旧的SSN(尽管，如果可以的话，我不得不怀疑为什么不仅仅在UPDATE语句的WHERE子句中使用它.

           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

解决方案

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).

这篇关于在此程序中，我必须在where子句中设置ssn的现有值(即:在setString(3，)中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！