在jdbc中使用预处理语句时可以用作迭代器;这里我想检查客户是否存在

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

我们使用的键是ssnumber;我需要遍历数据库表并使用准备好的语句

Answer 1

您可以使用两种方法中的一种。

• 检查SQL状态码(唯一的约束违反)

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

• 首先触发SELECT以检查客户是否存在

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

SQL状态代码(免责声明:请检查您的数据库文档并验证:)

 DB2        23505
 Derby      23505
 Oracle     23000
 MySQL      23000
 SQL Server 23000