Using dynamic where clause in Java JDBC

Question

Using Java 11 and JDBC.
Having PostgreSQL table "employee":

I need to have an ability to select data, but I don't know in advance, what WHERE conditions would be used.

It could be:

SELECT * FROM employee WHERE job_name='PRESIDENT';
SELECT * FROM employee WHERE job_name='PRESIDENT' OR salary>6000;
SELECT * FROM employee WHERE salary<5000 AND manager_id=68319;

Writing all possible SQL in the code, would take a lot of time.

Probably, there is some library, that already has implementation for it.

Maybe do something like in [this question](https://stackoverflow.com/a/21807974/5515060)? — Lino, Jun 10 '20 at 08:48
you can consider to use jpa. It help to map tables to java entities and help to reduce native query like this — SoT, Jun 10 '20 at 08:54
@SoT doesn't JPA require Spring to run? That may be a lot of overhead when solely wanting to simplify some SQL queries — Lino, Jun 10 '20 at 09:03
@Artur are you writing a web app or desktop app? If desktop, refer this link https://stackoverflow.com/questions/10582586/how-to-configure-hibernate-in-standalone-swing-application-in-eclipse — SoT, Jun 10 '20 at 09:27
When do you know what whereclause you need? You can build your SQL-Statements as string with dynamic where clauses. But use Parameters for your variables. You don't need Spring for JPA. — TomStroemer, Jun 10 '20 at 09:27

score 1 · Accepted Answer · answered Jun 10 '20 at 10:05

Try sqlBuilder [http://sqlobject.org/SQLBuilder.html]. it has support to build dynamic where clauses based on condition. something like below

  var query = SQL
  .SELECT("*")
  .FROM("employee")
  .WHERE()
  ._If(job_name=="PRESIDENT", "job_name = {0}", job_name)

  .ORDER_BY("job_name");

Using dynamic where clause in Java JDBC

1 Answers1