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.

You do not Need a prepared Statement here. use simply `select * from stars where id_num is not null` — Jens, Mar 10 '17 at 08:37
@Jens And how will I connect this string with the database? I mean, now I have `.executeQuery();` . What will I do after I declare this string? — Marievi, Mar 10 '17 at 08:41
`.executeQuery(idStarsSQL); `. Also you can use it with prepared Statement but without parameter — Jens, Mar 10 '17 at 08:42
@Jens aah so you mean that I should just erase the statement `preparedStatement.setString(1, NULL);`, right? And just write `NULL` in my string. — Marievi, Mar 10 '17 at 08:43

nano_nano · Accepted Answer · 2017-03-10T08:59:18.153

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

That's what I am saying... I do need a PreparedStatement, based on your answer. I got it, thanks a lot!! — Marievi, Mar 10 '17 at 08:50

score 1 · Answer 2 · answered Mar 10 '17 at 08:38

1

Because null is a not a value, it more of a keyword in SQL, so you have to hard code it into your sql statement

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

answered Mar 10 '17 at 08:38

Mehdi

582
4
14

Youcef LAIDANI · Answer 3 · 2017-09-14T19:02:42.867

1

You can use :

PreparedStatement preparedStatement = 
            connection.prepareStatement("select * from `stars` where id_num is not NULL");
ResultSet result = preparedStatement.executeQuery();

while (result.next()) {
    result.getString("attribute1");
    result.getString("attribute2");
}

You can learn more here about PreparedStatement and about ResulSet

edited Sep 14 '17 at 19:02

answered Mar 10 '17 at 08:46

Youcef LAIDANI

55,661
15
90
140

score 0 · Answer 4 · answered Mar 10 '17 at 09:29

The preparedStatement's primary feature is to insert parameters into the SQL statement and in your case you dont need to do this because null is not a param so you can clearly do :

PreparedStatement myRequest= 
            connection.prepareStatement("select * from `stars` where id_num is not NULL");
ResultSet myResult= preparedStatement.executeQuery();

Select all records whose specific field is not NULL

4 Answers4