Select all records whose specific field is not NULL

Question

I have a MySQL table named stars and one of its fields is id_num with default value NULL. I want to select all the records where id_num is not NULL through a PreparedStatement in java.

Right now I am trying this :

private final String idStarsSQL = "select * from `stars` where id_num is not ?";
...

preparedStatement = (PreparedStatement) connection.prepareStatement(idStarsSQL);
preparedStatement.setString(1, NULL);
set = preparedStatement.executeQuery();

but it is not working.

Answer 1

private final String idStarsSQL = "select * from `stars` where id_num is not NULL";

you dont need a PreparedStatement for this.

From my point of view PreparedStatement should be used for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

Statement statement = con.createStatement();
    ResultSet result = statement.executeQuery("select username, age, nickname from user where nickname is not NULL");
    List<User> allUserNullNickname = new ArrayList<>();
    while(result.next()){
        User user = new User();
        user.setUsername(result.getString("username"));
        user.setAge(result.getInt("age"));
        allUserNullNickname.add(user);
    }
    System.out.println("All user without nickname:");
    allUserNullNickname.stream().forEach(user -> System.out.println("username: "+user.getUsername()+" Age: "+user.getAge()));