org.jooq.Query Java Examples

public boolean updateRoles(String userName, Set<String> newRoles) {
    try {
        dsl.transaction(config -> {
            LOG.info("Removing existing roles for: " + userName);
            DSL.using(config)
                    .delete(USER_ROLE)
                    .where(USER_ROLE.USER_NAME.eq(userName))
                    .execute();

            LOG.info("Inserting roles for " + userName + " / " + newRoles) ;
            DSLContext batcher = DSL.using(config);
            Set<Query> inserts = map(newRoles, r -> batcher
                    .insertInto(USER_ROLE, USER_ROLE.USER_NAME, USER_ROLE.ROLE)
                    .values(userName, r));

            batcher.batch(inserts)
                    .execute();
        });
        return true;
    } catch (Exception e) {
        return false;
    }

}
 

/**
 * Executes batch insert for 'edges' table.
 *
 * @param edges List of edges records to insert
 */
public void batchInsertEdges(List<EdgesRecord> edges) {
    Query batchQuery = context.insertInto(Edges.EDGES,
            Edges.EDGES.SOURCE_ID, Edges.EDGES.TARGET_ID, Edges.EDGES.METADATA)
            .values((Long) null, (Long) null, (JSONB) null)
            .onConflictOnConstraint(Keys.UNIQUE_SOURCE_TARGET).doUpdate()
            .set(Edges.EDGES.METADATA, JsonbDSL.concat(Edges.EDGES.METADATA,
                    Edges.EDGES.as("excluded").METADATA));
    var batchBind = context.batch(batchQuery);
    for (var edge : edges) {
        batchBind = batchBind.bind(edge.getSourceId(), edge.getTargetId(), edge.getMetadata());
    }
    batchBind.execute();
}
 

protected Tuple rxGetBindValues(Query query) {
    ArrayList<Object> bindValues = new ArrayList<>();
    for (Param<?> param : query.getParams().values()) {
        Object value = convertToDatabaseType(param);
        bindValues.add(value);
    }
    Tuple tuple = Tuple.tuple();
    bindValues.forEach(tuple::addValue);
    return tuple;
}
 

protected Tuple getBindValues(Query query) {
    ArrayTuple bindValues = new ArrayTuple(query.getParams().size());
    for (Param<?> param : query.getParams().values()) {
        if (!param.isInline()) {
            Object value = convertToDatabaseType(param);
            bindValues.addValue(value);
        }
    }
    return bindValues;
}
 

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();
}
 

/**
 * Executes the given queryFunction and returns a <code>RowSet</code>
 * @param queryFunction the query to execute
 * @return the results, never null
 */
public Future<RowSet<Row>> executeAny(Function<DSLContext, ? extends Query> queryFunction) {
    Query query = createQuery(queryFunction);
    log(query);
    Promise<RowSet<Row>> rowPromise = Promise.promise();
    delegate.preparedQuery(toPreparedQuery(query)).execute(getBindValues(query),rowPromise);
    return rowPromise.future();
}
 

@Override
public CompletableFuture<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    Query query = createQuery(queryFunction);
    log(query);
    CompletableFuture<RowSet<Row>> rowFuture = new VertxCompletableFuture<>(vertx);
    delegate.preparedQuery(toPreparedQuery(query)).execute(getBindValues(query),createCompletionHandler(rowFuture));
    return rowFuture.thenApply(SqlResult::rowCount);
}
 

/**
 * Executes the given queryFunction and returns a <code>RowSet</code>
 * @param queryFunction the query to execute
 * @return the results, never null
 */
public CompletableFuture<RowSet<Row>> executeAny(Function<DSLContext, ? extends Query> queryFunction) {
    Query query = createQuery(queryFunction);
    log(query);
    CompletableFuture<RowSet<Row>> rowFuture = new VertxCompletableFuture<>(vertx);
    delegate.preparedQuery(toPreparedQuery(query)).execute(getBindValues(query),createCompletionHandler(rowFuture));
    return rowFuture;
}
 

public int[] addApplications(long groupId, List<Long> applicationIds) {
    Query[] queries = applicationIds
            .stream()
            .map(id -> DSL.insertInto(APPLICATION_GROUP_ENTRY)
                    .set(APPLICATION_GROUP_ENTRY.GROUP_ID, groupId)
                    .set(APPLICATION_GROUP_ENTRY.APPLICATION_ID, id)
                    .onDuplicateKeyIgnore())
            .toArray(Query[]::new);
    return dsl.batch(queries).execute();
}
 

protected <T extends Query> T createQuery(Function<DSLContext,T> queryFunction){
    return queryFunction.apply(DSL.using(configuration()));
}
 

@Override
public Single<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    return executeAny(queryFunction).map(SqlResult::rowCount);
}
 

/**
 * Executes the given queryFunction and returns a <code>RowSet</code>
 * @param queryFunction the query to execute
 * @return the results, never null
 */
public Single<RowSet<io.vertx.reactivex.sqlclient.Row>> executeAny(Function<DSLContext, ? extends Query> queryFunction) {
    Query query = createQuery(queryFunction);
    log(query);
    return delegate.preparedQuery(toPreparedQuery(query)).rxExecute(rxGetBindValues(query));
}
 

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

@Override
public Future<Integer> execute(Function<DSLContext, ? extends Query> queryFunction) {
    return executeAny(queryFunction).map(SqlResult::rowCount);
}