1

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.

Hexy
  • 828
  • 14
  • 24
  • 1
    Possible duplicate of [sql query if parameter is null select all](http://stackoverflow.com/questions/13474207/sql-query-if-parameter-is-null-select-all) – Julian Aug 18 '16 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 Aug 18 '16 at 21:26
  • Which DB are you using? – Julian Aug 18 '16 at 21:27
  • Yes, the table allows NULL values. – Hexy Aug 18 '16 at 21:27
  • I am using Postgres – Hexy Aug 18 '16 at 21:27
  • I believe you can use one of the answers in that duplicate question then. – Julian Aug 18 '16 at 21:29
  • @Julian It says cannot be used where name is NULL ? – Hexy Aug 18 '16 at 21:30
  • Maybe it's because the keyword for that isn't the same as in MySQL, i think you should take a look at how to check null values in postgres – Julian Aug 18 '16 at 21:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/121296/discussion-between-hexy-and-julian). – Hexy Aug 18 '16 at 21:31
  • Sorry, i can't join chat rooms from where i'm at right now. [Here's something that might help you](https://www.postgresql.org/docs/9.1/static/functions-comparison.html) – Julian Aug 18 '16 at 21:35
  • It is complicated because of using ? in the java string. I used COALESCE(?, CASE WHEN ? = '' THEN '%%' ELSE '%?%') but you cannot have ? more than once for a parameter. – Hexy Aug 18 '16 at 22:30
  • I'm curious as to why you wouldn't just use code to detect that the parameter is null and issue a completely different query. Having two queries should be easier on the database. – Chris Aug 22 '16 at 16:25
  • Also, have you looked at JPA's positional parameters? Does it allow using COALESCE(?1, CASE WHEN ?1 = '' THEN '%%' ELSE '%?%') ? – Chris Aug 22 '16 at 16:32

1 Answers1

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);
udog
  • 1,490
  • 2
  • 17
  • 30
  • Why not simpler: `WHERE @variable IS NULL OR id = @variable`? – Shnugo Aug 18 '16 at 21:35
  • @Shnugo - Or even simpler: `WHERE id = COALESCE(@variable,id)`? – Nick Aug 18 '16 at 21:39
  • This was my first idea, clearly there are simpler ones. – udog Aug 18 '16 at 21:46
  • @Nicarus, this predicat would not use indexes the way you might expect it. Read about *sargable*... – Shnugo Aug 18 '16 at 21:48
  • @Shnugo - In this scenario is appears safe as there would likely not be an index on this column (see question). – Nick Aug 18 '16 at 21:49
  • @Nicarus, Well, don't know... And admittably don't know the specialties of Postgres either... With SQL-Server I'd say, it's just a bad habit to kick, even if it might work in many situations... However, happy coding! – Shnugo Aug 18 '16 at 21:53
  • I think the problem is I am writing java code so if I use more than one ? it is mapped to param2 . So I don't the conditional clause will work. – Hexy Aug 18 '16 at 22:27
  • You can use the above format to create a stored procedure in the database, and pass in a single parameter to get you the data. – Chris Aug 22 '16 at 16:27