CODEWITHSUNDEEP
javamysqljpajdbc
Lev
Lev

Reputation: 100

Search DB entries for a match when table has eight columns

I have to work with a POJO "Order" that 8 fields and each of these fields is a column in the "order" table. The DB schema is denormalized (and worse, deemed final and unchangeable) so now I have to write a search module that can execute a search with any combination of the above 8 fields.

Are there any approaches on how to do this? Right now I get the input in a new POJO and go through eight IF statements looking for values that are not NULL. Each time I find such a value I add it to the WHERE condition in my SELECT statement.

Is this the best I can hope for? Is it arguably better to select on some minimum of criteria and then iterate over the received collection in memory, only keeping the entries that match the remaining criteria? I can provide pseudo code if that would be useful. Working on Java 1.7, JSF 2.2 and MySQL.

Upvotes: 0

Views: 61

Answers (1)

qujck
qujck

Reputation: 14578

Each time I find such a value I add it to the WHERE condition in my SELECT statement.

This is a prime target for Sql Injection attacks!

Would something like the following work with MySql?

SELECT *
FROM SomeTable
WHERE (@param1 IS NULL OR SomeTable.SomeColumn1 = @param1) OR
    (@param2 IS NULL OR SomeTable.SomeColumn2 = @param2) OR
    (@param3 IS NULL OR SomeTable.SomeColumn3 = @param3) OR
    /* .... */

Upvotes: 1

Related Questions