CODEWITHSUNDEEP
javasqlpostgresqlpostgresql-9.3
Peter Penzov
Peter Penzov

Reputation: 1626

Testing for null inside SQL query

I want to implement search filter for this table:

CREATE TABLE ACCOUNT(
 ID INTEGER NOT NULL,
 USER_NAME TEXT,
 PASSWD TEXT,
 FIRST_NAME TEXT,
 LAST_NAME TEXT,
 LAST_LOGIN DATE,
 DATE_REGISTERED DATE,
 ROLE INTEGER,
 CAN_LOGIN INTEGER
)
;

-- ADD KEYS FOR TABLE ACCOUNT

ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;


SELECT * FROM ACCOUNT 
WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)
ORDER BY %S %S offset ? limit ?;

But when I have empty search filter I get this error:

org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "null" Position: 30

How can I edit the SQL query in a way that WHERE clause will be skipped if searchString is empty?

Here is the Java method:

public List<AccountsObj> list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException
    {
        String SqlStatement = null;

        if (ds == null)
        {
            throw new SQLException();
        }

        Connection conn = ds.getConnection();
        if (conn == null)
        {
            throw new SQLException();
        }

        String sortDirection = sortAscending ? "ASC" : "DESC";

        SqlStatement = "SELECT * FROM ACCOUNT "
            + " WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)"
            + " ORDER BY %S %S offset ? limit ? ";

        String sql = String.format(SqlStatement, sortField, sortDirection);

        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<AccountsObj> resultList = new ArrayList<>();

        try
        {
            conn.setAutoCommit(false);
            boolean committed = false;

            ps = conn.prepareStatement(sql);
            ps.setInt(1, firstRow);
            ps.setInt(2, rowCount);

            resultSet = ps.executeQuery();
            resultList = ProcessorArrayList(resultSet);

            conn.commit();
            committed = true;

        }
        finally
        {
            ps.close();
            conn.close();
        }

        return resultList;
    }

Upvotes: 0

Views: 120

Answers (2)

nyname00
nyname00

Reputation: 2566

Using SQL to check for a null search string you can do:

SELECT * FROM account WHERE ? IS NULL OR ? IN (user_name, first_name, last_name)

Here the ? IS NULL will short-circuit if the parameter is NULL and the second part will not be evaluated.

Note that, I've used two parameter bindings with the same value (your search string) here and that the ID column is gone - you cannot mix varchar and integer in the IN clause.

Edit For wildcard searches you can use LIKE or ILIKE (for case-insensitive searches) 

SELECT * FROM account WHERE 
     (trim(?) = '') IS NOT FALSE 
    OR user_name like ? 
    OR first_name like ? 
    OR last_name like ?

Using a prepared statement you would call it like this (note that you have to bind the same parameter four times)

try (PreparedStatement ps = conn.prepareStatement(sql)) {

    ps.setString(1, searchString);
    ps.setString(2, searchString);
    ps.setString(3, searchString);
    ps.setString(4, searchString );

    try (ResultSet rs = ps.executeQuery()) {
        // read data 
    }
}

Upvotes: 1

Vic Seedoubleyew
Vic Seedoubleyew

Reputation: 10556

You can replace this line of your java code :

+ (searchString == null || searchString.length == 0 ) ? "" : (" WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)")

It basically checks if searchString is empty, and adds the line only if it is not

Upvotes: 0

Related Questions