java - Using ORACLE Flashback with QueryDSL

Question

We are using QueryDSL to bulk read data from an Oracle database with our SpringBoot application.

final QFps fps = QFps.fps;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB)
                                          .from(fps)
                                          .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

produces the following SQL query which can be succesfully executed:

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B
from FPS FPS  
where FPS.FP_ID = ?

The same works with a left join:

final QFps fps = QFps.fps;
final QHalt halt = QHalt.halt;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB, halt.fieldC)
                                          .from(fps)
                                          .leftJoin(halt).on(fps.fieldA.eq(halt.id))
                                          .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

results in

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B, HALT.FIELD_C
from FPS FPS  
join HALT HALT on FPS.FIELD_A = HALT.ID
where FPS.FP_ID = ?

However, as we need to query this data for certain points in time, we need to make use of Oracle's Flashback feature. Basically, what is needed for this is to add the following SQL between the table name and the alias name:

AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS')

i.e.

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B, HALT.FIELD_C
from FPS AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') FPS  
join HALT AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') HALT on FPS.FIELD_A = HALT.ID
where FPS.FP_ID = ?

I have unsuccesfully tried to get this working with QueryDSL using the .addFlag() and .addJoinFlag() commands.

final QFps fps = QFps.fps;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB)
                                          .from(fps)
                                          .addFlag(Position.BEFORE_FILTER, getFlashbackString())
                                          .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

creates

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B
from FPS FPS AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') 
where FPS.FP_ID = ?

Specifically, the problem ist that the flashback string is added after the alias name instead of between the table name and the alias name.

When working with joins, it becomes even worse:

final QFps fps = QFps.fps;
final QHalt halt = QHalt.halt;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB, halt.fieldC)
                                          .from(fps)
                                          .addFlag(Position.BEFORE_FILTER, getFlashbackString())
                                          .leftJoin(halt).on(fps.fieldA.eq(halt.id))
                                          .addJoinFlag(getFlashbackString())
                                          .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

produces

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B, HALT.FIELD_C
from FPS FPS  
join  AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') HALT HALT on FPS.FIELD_A = HALT.ID AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') 
where FPS.FP_ID = ?

The result of .addJoinFlag() is added before the table name and the .addFlag() content is added - not surprisingly - just before the where.

final QFps fps = QFps.fps;
final QHalt halt = QHalt.halt;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB, halt.fieldC)
                                          .from(fps)
                                          .addFlag(Position.BEFORE_FILTER, getFlashbackString())
                                          .leftJoin(halt).on(fps.fieldA.eq(halt.id))
                                          .addJoinFlag(getFlashbackString(), Position.BEFORE_CONDITION)
                                          .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

changes this to

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B, HALT.FIELD_C
from FPS FPS  
join HALT HALT  AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') on FPS.FIELD_A = HALT.ID AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') 
where FPS.FP_ID = ?

which is not much better.

I have played around with various possible but did not find one that placed the string to be inserted at the correct location.

Am I missing another way to inject SQL in certain positions of the Query?

Failing that, I could use QueryDSL to produce the SQL string and then modify this myself using regular expressions. Is there a way to then pass such a string to QueryDSL in order for the query to be executed?

---

Integrating the accepted answer

The answer given by Jan-Willem Gmellig Meyling works and I just want to integrate it fully into my sample from above:

I created a helper method:

  private static final String ADDITIONAL_FLASHBACK_EXPRESSION = "{0} AS OF TIMESTAMP TO_TIMESTAMP({1}, 'YYYY-MM-DD HH24:MI:SS') {2}";
  private static final DateTimeFormatter FLASHBACK_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

public <T> Expression<String> getFlashbackExpresion(final Timestamp flashbackTimestamp, final RelationalPathBase<T> table) {
    if (flashbackTimestamp == null) {
      return Expressions.stringTemplate(WITHOUT_FLASHBACK_EXPRESSION, table, table);
    }
    
    final LocalDateTime timestamp = LocalDateTime.ofInstant(flashbackTimestamp.toInstant(), ZoneOffset.ofHours(0));
    final String timestampString = timestamp.format(FLASHBACK_FORMATTER);
    
    return Expressions.stringTemplate(ADDITIONAL_FLASHBACK_EXPRESSION, table, timestampString, table);
  }

Using that, I can now create arbitrary flashback paths to be used both with from() and addJoin():

final Timestamp flashbackTimestamp = getFlashbackTimestamp();

final QFps fps = QFps.fps;
final QHalt halt = QHalt.halt;

final fpsFlashback = getFlashbackExpresion(flashbackTimestamp, fps);
final haltFlashback = getFlashbackExpresion(flashbackTimestamp, halt);

final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA, fps.fieldB, halt.fieldC);

query.from(fpsFlashback)
     .getMetadata().addJoin(JointType.LEFTJOIN, haltFlashback);
query.on(fps.fieldA.eq(halt.id))
     .where(fps.fpId.eq(fpId));

final String sql = query.getSQL().getSQL();
log.info("sql={}", sql);

return query.fetch();

which yields

select FPS.FP_ID, FPS.FIELD_A, FPS.FIELD_B, HALT.FIELD_C
from FPS AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') FPS
join HALT AS OF TIMESTAMP TO_TIMESTAMP('2021-01-26 14:48:23', 'YYYY-MM-DD HH24:MI:SS') HALT on FPS.FIELD_A = HALT.ID
where FPS.FP_ID = ?

which is exactly what I wanted.

question from:https://stackoverflow.com/questions/65904950/using-oracle-flashback-with-querydsl

Answer 1

Although JoinFlags are used to achieve similar features, as you have noticed, JoinFlags currently cannot be used to insert flags between a path expression and an alias. This is in fact because the join alias is embedded in the path expression.

Another mechanism to insert snippets of SQL into the produced queries is through TemplateExpressions. Although no API exists to use TemplateExpressions as join target, you can add any expression type as join through the underlying query metadata:

// Even parameterized it for you :)
Expression flashback = Expressions.stringTemplate(
    "{0} AS OF TIMESTAMP TO_TIMESTAMP({1}, 'YYYY-MM-DD HH24:MI:SS') ",
    halt,
    '2021-01-26 14:48:23');


query.from(fps);
query.getMetadata().addJoin(JoinType.LEFTJOIN, flashback);
query.on(fps.fieldA.eq(halt.id));

---

If you don't like the syntax of the above snippet, there are some other ways. You could also look how I've implemented treated paths on top of QueryDSL for JPA: https://github.com/querydsl/querydsl/pull/2530/files . This however requires you to introduce a change in QueryDSL core classes.