CODEWITHSUNDEEP
javasqlitejdbcnullprepared-statement
TachisAlopex
TachisAlopex

Reputation: 95

Using Java's JDBC PreparedStatement on a SQLite Database to query for NOT NULL

I'm new to PreparedStatement in Java, and I can't seem to figure out an optimal solution to my problem. I want to query for records in a table that are either NULL, or NOT NULL.

When reading PreparedStatement (Java Platform SE 6), I realized that I needed to use the setNull method, but I did not see a way to set NOT NULL?

For example:

SELECT * FROM table WHERE column IS NULL

What I assume I would do is:

public void query(boolean getNull) {
    String querySql = "SELECT * FROM table WHERE column IS ?";
    sqlStatement = connection.prepareStatement(querySql);
    if(getNull)
        sqlStatement.setNull(1, Types.VARCHAR);
    else
        ???;
}

But how would I do the else statement? There is not a setNotNull method. I assume I could do this:

public void query(boolean getNull) {
    String querySql = "SELECT * FROM table WHERE column IS ? NULL";
    sqlStatement = connection.prepareStatement(querySql);
    sqlStatement.setString(1, (getNull ? "" : "NOT"));
}

But that seems very 'hackish.' Is there any better way to do this?

Thank you

Upvotes: 1

Views: 5893

Answers (2)

TAL
TAL

Reputation: 41

? is for passing paramters, not building a SQL. (taken partially from here)

I suggest you to use parameters only for values, not fields or [IS] NULL (anything like that).

Just concatenate the Strings. If you has more than one parameter you should take a look at StringBuilder.

public void query(boolean getNull) {
    String querySql = "SELECT * FROM table WHERE column IS ";
    if(getNull) {
        querySql += "NULL";
    } else {
        querySql += "NOT NULL";
    }      
    sqlStatement = connection.prepareStatement(querySql);
}

also a possible way can be using String.format(...), as example:

public void query(boolean getNull) {
    String querySql = String.format("SELECT * FROM table WHERE column IS %s NULL", (getNull ? "" : "NOT")); 
    sqlStatement = connection.prepareStatement(querySql);
}

Upvotes: 1

Sujay
Sujay

Reputation: 6783

One way to do it would be to look at the resultset that is returned by the query: 

"SELECT * FROM table WHERE column IS ?"

Use your PreparedStatement to set the value to NULL and then execute the query. You can use the resultset.next() to test out what you're looking for.

Upvotes: 0

Related Questions