为什么我的查询只适用于number<10？

Question

我在输入数据时使用这个功能。所以我需要检查一下是否有一个在db中有这个id的雇员。与注册所用的函数相同，此方法可以工作。但对于整数，它只在小于10 (或者可能是第一个插入的值)时进行检查，这是我的代码，用于检查id的惟一性：

private int checkUnique() {
    try {
        Scanner scan = new Scanner(System.in);
        id = scan.nextInt();
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:...", "...", "...");
        Statement st = connection.createStatement();
        ResultSet res = st.executeQuery("select id_emp from employees");
        while (res.next()) {
            if (res.getInt("id_emp")==getId()) {
                res.close();
                st.close();
                connection.close();
                System.out.println("There is employee with this id");
                System.out.println("Enter id");
                checkUnique();
            } else {
                res.close();
                st.close();
                connection.close();
                return id;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return 0;
}

这就是我在代码中使用它的方式：

Statement st = connection.createStatement();
String sql = "INSERT INTO employees (id_emp, first_name, last_name, cnt_kids, cnt_dkids,is_single,added_by) " +
        "VALUES (?, ?, ?, ?, ?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
System.out.println("Enter id");
id = checkUnique();

这是怎么回事？

例如，这段代码要求在id=2(它在表上是真实的)时输入其他id，但是当我插入id=12(也在表中)时，它就跳过了。我的桌子：

CREATE TABLE `employees` (
  `id_emp` int NOT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `cnt_kids` int DEFAULT NULL,
  `cnt_dkids` int DEFAULT NULL,
  `is_single` bit(1) DEFAULT NULL,
  `added_by` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`id_emp`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Answer 1

使用准备好的语句

下面是一些示例代码：

public class SQLMain {
    public static void main(String[] args) throws SQLException {
        System.out.println("ID to check:");
        Scanner scanner = new Scanner(System.in);
        int id = scanner.nextInt();
        
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");
        PreparedStatement preparedStatement = connection.prepareStatement("select id_emp from employees where id_emp = ?");
        preparedStatement.setInt(1, id);
        
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()) {
            // we have a row
            System.out.println("Found employee with id of: " + resultSet.getInt(1));
        } else {
            // no row found - therefore unique 
            System.out.println("not found");
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
        scanner.close();
    }
}