@Test
public void testFindUsersWithJOOQ() {

    //Query query =     em.createQuery("select u from User u where u.address.country = 'Norway'");
    //Query query = em.createNativeQuery("select * from User where country = 'Norway'");

    DSLContext create = DSL.using(SQLDialect.H2);
    String sql = create
            .select()
            .from(table("User"))
            .where(field("country").eq("Norway"))
            .getSQL(ParamType.INLINED);

    Query query = em.createNativeQuery(sql, User.class);

    List<User> results = query.getResultList();

    assertEquals(3, results.size());

    /*
       JOOQ is a popular, easy to use DSL for writing SQL (not JPQL).
       Besides type-safety and IDE code-completion, one HUGE benefit
       is that the SQL is targeted for the specific dialect of the
       target DB.
     */
}
 

private <R extends Record> Cursor<R> timeQuery(ResultQuery<R> query) {
    if (persistenceSettings.isTimeoutQueries()) {
        query.queryTimeout(persistenceSettings.getQueryTimeout());
    }
    if (!persistenceSettings.isLogSlowQueries()) {
        return query.fetchLazy();
    }
    long start = System.currentTimeMillis();
    Cursor<R> result;
    try {
        result = query.fetchLazy();
    } catch (DataAccessException exc) {
        if (LOGGER.isWarnEnabled()) {
            LOGGER.info("Failed to run query:\n{}", query.getSQL(ParamType.INLINED));
        }
        throw new IllegalStateException("Failed to run query: " + exc.getMessage());
    }
    long end = System.currentTimeMillis();
    long duration = end - start;
    if (LOGGER.isInfoEnabled() && duration > persistenceSettings.getSlowQueryThreshold()) {
        LOGGER.info("Slow Query executed in {} ms:\n{}", duration, query.getSQL(ParamType.INLINED));
    }
    return result;
}
 

/**
 * Build a count query.
 *
 * @return the count query.
 */
public ResultQuery<Record1<Integer>> buildCount() {
    gatherData();

    DSLContext dslContext = pm.getDslContext();
    AggregateFunction<Integer> count;
    if (queryState.isDistinctRequired()) {
        count = DSL.countDistinct(queryState.getSqlMainIdField());
    } else {
        count = DSL.count(queryState.getSqlMainIdField());
    }
    SelectConditionStep<Record1<Integer>> query = dslContext.select(count)
            .from(queryState.getSqlFrom())
            .where(queryState.getSqlWhere());

    if (LOGGER.isTraceEnabled()) {
        LOGGER.trace(GENERATED_SQL, query.getSQL(ParamType.INDEXED));
    }
    return query;
}
 

public Delete buildDelete(PathElementEntitySet set) {
    gatherData();

    DSLContext dslContext = pm.getDslContext();
    final StaMainTable<J> table = tableCollection.getTablesByType().get(set.getEntityType());
    if (table == null) {
        throw new AssertionError("Don't know how to delete" + set.getEntityType().name(), new IllegalArgumentException("Unknown type for delete"));
    }

    SelectConditionStep<Record1<J>> idSelect = DSL.select(queryState.getSqlMainIdField())
            .from(queryState.getSqlFrom())
            .where(queryState.getSqlWhere());

    DeleteConditionStep<? extends Record> delete = dslContext
            .deleteFrom(table)
            .where(
                    table.getId().in(idSelect)
            );

    if (LOGGER.isTraceEnabled()) {
        LOGGER.trace(GENERATED_SQL, delete.getSQL(ParamType.INDEXED));
    }
    return delete;
}
 

@Test
public void testFindUsersWithJOOQ() {

    //Query query =     em.createQuery("select u from User u where u.address.country = 'Norway'");
    //Query query = em.createNativeQuery("select * from User where country = 'Norway'");

    DSLContext create = DSL.using(SQLDialect.H2);
    String sql = create
            .select()
            .from(table("User"))
            .where(field("country").eq("Norway"))
            .getSQL(ParamType.INLINED);

    Query query = em.createNativeQuery(sql, User.class);

    List<User> results = query.getResultList();

    assertEquals(3, results.size());

    /*
       JOOQ is a popular, easy to use DSL for writing SQL (not JPQL).
       Besides type-safety and IDE code-completion, one HUGE benefit
       is that the SQL is targeted for the specific dialect of the
       target DB.
     */
}
 

protected String toPreparedQuery(Query query) {
    if (SQLDialect.POSTGRES.supports(configuration().dialect())) {
        String namedQuery = query.getSQL(ParamType.NAMED);
        return namedQuery.replaceAll(pattern, "\\$");
    }
    // mysql works with the standard string
    return query.getSQL();
}
 

protected JsonArray getBindValues(Query query) {
    ArrayList<Object> bindValues = new ArrayList<>();
    for (Param<?> param : query.getParams().values()) {
        if(!param.getParamType().equals(ParamType.INLINED)) {
            Object value = convertToDatabaseType(param);
            bindValues.add(value);
        }
    }
    return new JsonArray(bindValues);
}
 

/**
 * Helper function returns a range query based on the bounds passed<br>
 */
protected String buildRangeQuery(Object lowerBoundKey, int offset, int limit)
{
  Condition condition = DSL.trueCondition();
  if (getWhereCondition() != null) {
    condition = condition.and(getWhereCondition());
  }

  if (isPollerPartition && lowerBoundKey != null) {
    condition = andLowerBoundKeyCondition(condition, lowerBoundKey);
  }

  String sqlQuery;
  if (getColumnsExpression() != null) {
    Collection<Field<?>> columns = new ArrayList<>();
    for (String column : getColumnsExpression().split(",")) {
      columns.add(field(column));
    }
    sqlQuery = dslContext.select(columns).from(getTableName()).where(condition)
        .orderBy(field(getKey())).limit(limit).offset(offset).getSQL(ParamType.INLINED);
  } else {
    sqlQuery = dslContext.select().from(getTableName()).where(condition).orderBy(field(getKey())).limit(limit)
        .offset(offset).getSQL(ParamType.INLINED);
  }
  LOG.info("DSL Query: " + sqlQuery);
  return sqlQuery;
}
 

@Override
public void executeStart(ExecuteContext ctx) {
  logger.info(ctx.query().getSQL(ParamType.INLINED));
}
 

public ResultQuery<Record> buildSelect() {
    gatherData();

    if (queryState.getSqlSelectFields() == null) {
        queryState.setSqlSelectFields(Collections.emptySet());
    }

    DSLContext dslContext = pm.getDslContext();
    SelectSelectStep<Record> selectStep;
    if (queryState.isDistinctRequired()) {
        addOrderPropertiesToSelected();
        selectStep = dslContext.selectDistinct(queryState.getSqlSelectFields());
    } else {
        selectStep = dslContext.select(queryState.getSqlSelectFields());
    }
    SelectConditionStep<Record> whereStep = selectStep.from(queryState.getSqlFrom())
            .where(queryState.getSqlWhere());

    final List<OrderField> sortFields = queryState.getSqlSortFields().getSqlSortFields();
    SelectSeekStepN<Record> orderByStep = whereStep.orderBy(sortFields.toArray(new OrderField[sortFields.size()]));

    int skip = 0;
    int count;
    if (single) {
        count = 2;
    } else if (staQuery != null) {
        count = staQuery.getTopOrDefault() + 1;
        skip = staQuery.getSkip(0);
    } else {
        count = 1;
    }
    SelectWithTiesAfterOffsetStep<Record> limit = orderByStep.limit(skip, count);

    if (forUpdate) {
        return limit.forUpdate();
    }

    if (LOGGER.isTraceEnabled()) {
        LOGGER.trace(GENERATED_SQL, limit.getSQL(ParamType.INDEXED));
    }
    return limit;
}
 

protected void log(Query query) {
    if (logger.isDebugEnabled()) {
        logger.debug("Executing {}", query.getSQL(ParamType.INLINED));
    }
}
 

protected void log(Query query){
    if(logger.isDebugEnabled()){
        logger.debug("Executing {}", query.getSQL(ParamType.INLINED));
    }
}
 

public ParamType getParamType() {
    return paramType;
}
 

public void setParamType(ParamType paramType) {
    this.paramType = paramType;
}