Java 类org.jooq.conf.ParamType 实例源码

@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.
     */
}

@Override
public <P> void fetchOne(Query query, Function<JsonObject, P> mapper, Handler<AsyncResult<P>> resultHandler) {
    getConnection().setHandler(sqlConnectionResult->{
        if(sqlConnectionResult.succeeded()){
            log("Fetch one", ()-> query.getSQL(ParamType.INLINED));
            sqlConnectionResult.result().queryWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    executeAndClose(rs -> {
                                if(rs.getRows().size() > 1){
                                    throw new TooManyRowsException(String.format("Got more than one row: %d",rs.getRows().size()));
                                }
                                Optional<P> optional = rs.getRows().stream().findFirst().map(mapper);
                                return (optional.orElseGet(() -> null));
                            },
                            sqlConnectionResult.result(),
                            resultHandler)
            );
        }else{
            resultHandler.handle(Future.failedFuture(sqlConnectionResult.cause()));
        }
    });
}

@Override
public void execute(Query query, Handler<AsyncResult<Integer>> resultHandler) {
    getConnection().setHandler(sqlConnectionResult->{
        if(sqlConnectionResult.succeeded()){
            log("Execute", ()-> query.getSQL(ParamType.INLINED));
            sqlConnectionResult.result().updateWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    executeAndClose(UpdateResult::getUpdated,
                            sqlConnectionResult.result(),
                            resultHandler)
            );
        }else{
            resultHandler.handle(Future.failedFuture(sqlConnectionResult.cause()));
        }
    });
}

@Override
public void insertReturning(Query query, Handler<AsyncResult<Long>> resultHandler) {
    getConnection().setHandler(sqlConnectionResult->{
        if(sqlConnectionResult.succeeded()){
            log("Insert Returning", ()-> query.getSQL(ParamType.INLINED));
            sqlConnectionResult.result().update(
                    query.getSQL(ParamType.INLINED),
                    executeAndClose(res -> res.getKeys().getLong(0),
                            sqlConnectionResult.result(),
                            resultHandler)
            );
        }else{
            resultHandler.handle(Future.failedFuture(sqlConnectionResult.cause()));
        }
    });
}

@Override
public <P> void fetch(Query query, Function<JsonObject, P> mapper, Handler<AsyncResult<List<P>>> resultHandler) {
    getConnection().setHandler(sqlConnectionResult->{
        if(sqlConnectionResult.succeeded()){
            log("Fetch", ()-> query.getSQL(ParamType.INLINED));
            sqlConnectionResult.result().queryWithParams(
                    query.getSQL(),
                    getBindValues(query),
                    executeAndClose(rs -> rs.getRows().stream().map(mapper).collect(Collectors.toList()), sqlConnectionResult.result(), resultHandler)
            );
        }else{
            resultHandler.handle(Future.failedFuture(sqlConnectionResult.cause()));
        }
    });
}

@Override
public CompletableFuture<Long> insertReturning(Query query) {
    return getConnection().thenCompose(sqlConnection -> {
        CompletableFuture<Long> cf = new VertxCompletableFuture<>(vertx);
        sqlConnection.update(query.getSQL(ParamType.INLINED), executeAndClose(updateResult->updateResult.getKeys().getLong(0), sqlConnection, cf));
        return cf;
    });
}

/**
 * TODO (tdavis): Named placeholder support is incomplete.
 * It doesn't work with limit/offset (because there are no fields for those) or multi-value params (because they don't use single-parameter conditions)
 */
@Test
public void namedPlaceholderSelect() {
  selectBuilder = parser.newSelectBuilder(query).withParamType(ParamType.NAMED);

  String sql = selectBuilder.build().toString();

  assertThat(sql).isEqualTo(String.format(queryFormat, ":1", ":2", ":3", ":4", ":count", ":full_name", ":7", ":8"));
}

@Test
public void inlineSelect() {
  selectBuilder = parser.newSelectBuilder(query).withParamType(ParamType.INLINED);

  String sql = selectBuilder.build().toString();

  assertThat(sql).isEqualTo(String.format(queryFormat, "'%John%'", "'Jane%'", "1", "2", "100", "'example'", "10", "5"));
}

/**
 * Creates a query like
 * <pre>
 * WITH RECURSIVE cte AS (
 *      SELECT id, content FROM tweets WHERE id = ?
 *      UNION ALL
 *      SELECT t.id, t.content
 *      FROM cte c
 *      JOIN tweets t on t.in_reply_to_status_id = c.id
 * ) SELECT * FROM cte
 * </pre>
 *
 * @param id The id of the tweet starting the hierarchy, i.e. 726762141064286208
 * @return
 */
@Override
public List<TweetEntity> getTweetHierarchy(final long id) {
    final SelectQuery<Record> sqlGenerator = this.create
            .withRecursive("cte").as(
                select()
                    .from(TWEETS)
                    .where(TWEETS.ID.eq(id))
               .unionAll(
                select()
                    .from(name("cte"))
                    .join(TWEETS)
                        .on(TWEETS.IN_REPLY_TO_STATUS_ID .eq(field(name("cte", "id"), Long.class)))
                )
            )
            .select()
            .from(name("cte"))
            .orderBy(field(name("cte", TWEETS.CREATED_AT.getName())))
            .getQuery();

    // Retrieve sql with named parameter
    final String sql = sqlGenerator.getSQL(ParamType.NAMED);
    // and create actual hibernate query
    final Query query = this.entityManager.createNativeQuery(sql, TweetEntity.class);
    // fill in parameter
    sqlGenerator.getParams().forEach((n, v) -> query.setParameter(n, v.getValue()));
    // execute query
    return query.getResultList();
}

/**
 * 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;
}

public static void main(String[] args) {
    System.out.println(DSL.using(new DefaultConfiguration().set(SQLDialect.MYSQL)).insertInto(Tables.SOMETHING).set(new SomethingRecord().setSomeid(123).setSomeregularnumber(1234)).returning().getSQL(ParamType.INLINED));
}

/**
 * Change the parameter type (placeholder/value style) of the resulting SQL string.
 */
public SelectBuilder<T> withParamType(ParamType paramType) {
  this.paramType = paramType;
  return this;
}

public BuiltSelect(SelectFinalStep<?> select, ParamType paramType) {
  this.select = select;
  this.paramType = paramType;
}