Defozo Defozo - 3 months ago 11
Java Question

jOOQ - How to create a SQL query with WHERE having multiple conditions

I want to create a query which should work like this one:

SELECT
sensor_id,
measurement_time,
measurement_value
FROM
public.measurement_pm2_5
WHERE
(sensor_id = 1 AND measurement_time BETWEEN to_timestamp(123) AND to_timestamp(999999999999))
OR (sensor_id = 49 AND measurement_time BETWEEN to_timestamp(555) AND to_timestamp(556))
OR (sensor_id = 9 AND measurement_time BETWEEN to_timestamp(7654) AND to_timestamp(999999299347))
OR (sensor_id = 44 AND measurement_time BETWEEN to_timestamp(4252) AND to_timestamp(999949999348))
OR (sensor_id = 60 AND measurement_time BETWEEN to_timestamp(63452) AND to_timestamp(999998999349))
;


The number of
OR
s in this query may vary.


Is it even possible to build such a query using jOOQ with its type safe API or I have to create it manually using plain SQL?

I know that if there wouldn't be additional statement about
measurement_time
- different for every
sensor_id
- it would look like this:

Set<Integer> sensorIds = new HashSet<>();
sensorIds.add(1);
sensorIds.add(49);
sensorIds.add(9);
sensorIds.add(44);
sensorIds.add(60);
Timestamp startTime = new Timestamp(123L);
Timestamp endTime = new Timestamp(999999999999L);
try(java.sql.Connection conn = Connection.hikariDataSource.getConnection()) {
System.out.println("SQL = " + DSL.using(conn).select()
.from(MEASUREMENT_PM2_5)
.where(MEASUREMENT_PM2_5.SENSOR_ID.in(sensorIds))
.and(MEASUREMENT_PM2_5.MEASUREMENT_TIME.between(startTime, endTime))
.getSQL());
} catch (SQLException e) {
e.printStackTrace();
}


But unfortunately I have different timestamps for different
sensor_id
s.

fge fge
Answer

It is perfectly doable with JooQ.

You want to create a suitable Condition and plug it into your query.

The question is how to build a condition... And there are many ways to build one.

For instance:

final Condition c1 = someField.eq(someOtherField);
// or
final Condition c1 = someField.lessThan(someValue);

The way to obtain a Field reference depend on your setup.

Possibilites are endless; and then if you have two conditions c1 and c2, to build a condition c, you can:

final Condition c = c1.and(c2); // or c1.or(c2)

and plug that condition c into your final query.

If you use JooQ, chances are that you have generated the metadata code already, and if not, even with only the JDBC URL alone, JooQ can infer table and column names for you.

Comments