Why does my query works only for number<10?-CodePudding

I use this func when entering data. So I need to check is there an employee with this id in db. The same function I use for registration and this method works. But when it comes to integer numbers it checks only when it's under 10 (or maybe it's the first inserted value) This is my code to check unique of 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;
}

And that how I use it in my code:

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

What's wrong with this?

For example this code ask to enter other id when id=2(it's realy on the table), but when I insert id=12(also in the table) it just skips. My table:

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;

CodePudding user response：

Use a prepared statement

Here is some sample code:

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