Java native SQL query to display all values when an input param is null

Question

I have the following DAO method:

public String getSomeTable(final String param1) {
    String sqlString = "select * from table where name ilike ?";
    Query query = this.getEntityManager().createNativeQuery(sqlString);

    query.setParameter(1, "%param1%");
}

If param1 is null or empty then I want to select all entries from the table. What is the correct way to do this? I am currently using the following:

public String getSomeTable(final String param1) {
    String sqlString = "select * from table where name = ?";
    Query query = this.getEntityManager().createNativeQuery(sqlString);

    if(param1 == null)
        query.setParameter(1, "%%");
    else
        query.setParameter(1, "%param1%");
}

But this is not scalable. I have datatypes like integer, date, etc. I want to know if there is a way to skip checking for that parameter if it is null.

I was planning to use COALESCE(?, CASE WHEN ? = '' THEN '%%' ELSE '%?%') but I think ? can be used only once for a particular parameter. The next one > I write is linked to second param.

Possible duplicate of sql query if parameter is null select all — Julian
– Julian, Commented Aug 18, 2016 at 21:25
Does the table allow null column values? If yes, would the query need to be changed to: select * from table where name is null — Andrew S
– Andrew S, Commented Aug 18, 2016 at 21:26

udog · Accepted Answer · 2016-08-18 21:27:21Z

0

On SQL Server, I use something like this, perhaps you can translate it to postgres:

DECLARE @variable INT = NULL;

SELECT *
FROM sysobjects
WHERE 
(1 = CASE WHEN @variable IS NULL THEN 1 ELSE 2 END)
OR
(id LIKE @variable);

answered Aug 18, 2016 at 21:27

udog

1,5464 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gottfried Lesigang Over a year ago

Why not simpler: WHERE @variable IS NULL OR id = @variable?

Nick Over a year ago

@Shnugo - Or even simpler: WHERE id = COALESCE(@variable,id)?

udog Over a year ago

This was my first idea, clearly there are simpler ones.

Gottfried Lesigang Over a year ago

@Nicarus, this predicat would not use indexes the way you might expect it. Read about sargable...

Nick Over a year ago

@Shnugo - In this scenario is appears safe as there would likely not be an index on this column (see question).

|

Collectives™ on Stack Overflow

Java native SQL query to display all values when an input param is null

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related