Examples with DSLContext org.jooq.DSLContext used on opensource projects

Example 1 with DSLContext

use of org.jooq.DSLContext in project OpenAttestation by OpenAttestation.

the class KvAttributeRepository method search.

@Override
public //    @RequiresPermissions("tag_kv_attributes:search")     
KvAttributeCollection search(KvAttributeFilterCriteria criteria) {
    log.debug("KvAttribute:Search - Got request to search for the KvAttributes.");
    KvAttributeCollection objCollection = new KvAttributeCollection();
    try (JooqContainer jc = TagJdbi.jooq()) {
        DSLContext jooq = jc.getDslContext();
        SelectQuery sql = jooq.select().from(MW_TAG_KVATTRIBUTE).getQuery();
        // all the data.
        if (criteria.filter) {
            if (criteria.id != null) {
                // when uuid is stored in database as the standard UUID string format (36 chars)
                sql.addConditions(MW_TAG_KVATTRIBUTE.ID.equalIgnoreCase(criteria.id.toString()));
            }
            if (criteria.nameEqualTo != null && criteria.nameEqualTo.length() > 0) {
                sql.addConditions(MW_TAG_KVATTRIBUTE.NAME.equalIgnoreCase(criteria.nameEqualTo));
            }
            if (criteria.nameContains != null && criteria.nameContains.length() > 0) {
                sql.addConditions(MW_TAG_KVATTRIBUTE.NAME.lower().contains(criteria.nameContains.toLowerCase()));
            }
            if (criteria.valueEqualTo != null && criteria.valueEqualTo.length() > 0) {
                sql.addConditions(MW_TAG_KVATTRIBUTE.VALUE.equalIgnoreCase(criteria.valueEqualTo));
            }
            if (criteria.valueContains != null && criteria.valueContains.length() > 0) {
                sql.addConditions(MW_TAG_KVATTRIBUTE.VALUE.lower().contains(criteria.valueContains.toLowerCase()));
            }
        }
        sql.addOrderBy(MW_TAG_KVATTRIBUTE.NAME, MW_TAG_KVATTRIBUTE.VALUE);
        log.debug("Opening tag-value dao");
        log.debug("Fetching records using JOOQ");
        Result<Record> result = sql.fetch();
        for (Record r : result) {
            KvAttribute obj = new KvAttribute();
            obj.setId(UUID.valueOf(r.getValue(MW_TAG_KVATTRIBUTE.ID)));
            //                obj.setId(r.getValue(MW_TAG_KVATTRIBUTE.ID.coerce(byte[].class), uuidConverter));
            //                obj.setId(r.getValue(MW_TAG_KVATTRIBUTE.ID, uuidConverter));
            //                obj.setId(r.getValue(MW_TAG_KVATTRIBUTE.ID));
            obj.setName(r.getValue(MW_TAG_KVATTRIBUTE.NAME));
            obj.setValue(r.getValue(MW_TAG_KVATTRIBUTE.VALUE));
            objCollection.getKvAttributes().add(obj);
        }
        sql.close();
    } catch (Exception ex) {
        log.error("KvAttribute:Search - Error during attribute search.", ex);
        throw new RepositorySearchException(ex, criteria);
    }
    log.debug("KvAttribute:Search - Returning back {} of results.", objCollection.getKvAttributes().size());
    return objCollection;
}

Example 2 with DSLContext

use of org.jooq.DSLContext in project OpenAttestation by OpenAttestation.

the class CertificateRepository method search.

@Override
public //    @RequiresPermissions("tag_certificates:search") 
CertificateCollection search(CertificateFilterCriteria criteria) {
    log.debug("Certificate:Search - Got request to search for the Certificates.");
    CertificateCollection objCollection = new CertificateCollection();
    try (JooqContainer jc = TagJdbi.jooq()) {
        DSLContext jooq = jc.getDslContext();
        SelectQuery sql = jooq.select().from(MW_TAG_CERTIFICATE).getQuery();
        if (criteria.filter) {
            if (criteria.id != null) {
                // when uuid is stored in database as the standard UUID string format (36 chars)
                sql.addConditions(MW_TAG_CERTIFICATE.ID.equalIgnoreCase(criteria.id.toString()));
            }
            if (criteria.subjectEqualTo != null && criteria.subjectEqualTo.length() > 0) {
                sql.addConditions(MW_TAG_CERTIFICATE.SUBJECT.equalIgnoreCase(criteria.subjectEqualTo));
            }
            if (criteria.subjectContains != null && criteria.subjectContains.length() > 0) {
                sql.addConditions(MW_TAG_CERTIFICATE.SUBJECT.lower().contains(criteria.subjectContains.toLowerCase()));
            }
            if (criteria.issuerEqualTo != null && criteria.issuerEqualTo.length() > 0) {
                sql.addConditions(MW_TAG_CERTIFICATE.ISSUER.equalIgnoreCase(criteria.issuerEqualTo));
            }
            if (criteria.issuerContains != null && criteria.issuerContains.length() > 0) {
                sql.addConditions(MW_TAG_CERTIFICATE.ISSUER.lower().contains(criteria.issuerContains.toLowerCase()));
            }
            if (criteria.sha1 != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.SHA1.equalIgnoreCase(criteria.sha1.toHexString()));
            }
            if (criteria.sha256 != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.SHA256.equalIgnoreCase(criteria.sha256.toHexString()));
            }
            if (criteria.validOn != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.NOTBEFORE.lessOrEqual(new Timestamp(criteria.validOn.getTime())));
                sql.addConditions(MW_TAG_CERTIFICATE.NOTAFTER.greaterOrEqual(new Timestamp(criteria.validOn.getTime())));
            }
            if (criteria.validBefore != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.NOTAFTER.greaterOrEqual(new Timestamp(criteria.validBefore.getTime())));
            }
            if (criteria.validAfter != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.NOTBEFORE.lessOrEqual(new Timestamp(criteria.validAfter.getTime())));
            }
            if (criteria.revoked != null) {
                sql.addConditions(MW_TAG_CERTIFICATE.REVOKED.equal(criteria.revoked));
            }
        }
        sql.addOrderBy(MW_TAG_CERTIFICATE.SUBJECT);
        Result<Record> result = sql.fetch();
        log.debug("Got {} records", result.size());
        for (Record r : result) {
            Certificate certObj = new Certificate();
            try {
                certObj.setId(UUID.valueOf(r.getValue(MW_TAG_CERTIFICATE.ID)));
                // unlike other table queries, here we can get all the info from the certificate itself... except for the revoked flag
                certObj.setCertificate((byte[]) r.getValue(MW_TAG_CERTIFICATE.CERTIFICATE));
                certObj.setIssuer(r.getValue(MW_TAG_CERTIFICATE.ISSUER));
                certObj.setSubject(r.getValue(MW_TAG_CERTIFICATE.SUBJECT));
                certObj.setNotBefore(r.getValue(MW_TAG_CERTIFICATE.NOTBEFORE));
                certObj.setNotAfter(r.getValue(MW_TAG_CERTIFICATE.NOTAFTER));
                certObj.setSha1(Sha1Digest.valueOf(r.getValue(MW_TAG_CERTIFICATE.SHA1)));
                certObj.setSha256(Sha256Digest.valueOf(r.getValue(MW_TAG_CERTIFICATE.SHA256)));
                certObj.setRevoked(r.getValue(MW_TAG_CERTIFICATE.REVOKED));
                log.debug("Certificate:Search - Created certificate record in search result {}", certObj.getId().toString());
                objCollection.getCertificates().add(certObj);
            } catch (Exception e) {
                log.error("Certificate:Search - Cannot load certificate #{}", r.getValue(MW_TAG_CERTIFICATE.ID), e);
            }
        }
        sql.close();
    } catch (Exception ex) {
        log.error("Certificate:Search - Error during certificate search.", ex);
        throw new RepositorySearchException(ex, criteria);
    }
    log.debug("Certificate:Search - Returning back {} of results.", objCollection.getCertificates().size());
    return objCollection;
}

Example 3 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class AbstractResultQuery method execute.

@Override
protected final int execute(ExecuteContext ctx, ExecuteListener listener) throws SQLException {
    listener.executeStart(ctx);
    // [#4511] [#4753] PostgreSQL doesn't like fetchSize with autoCommit == true
    int f = SettingsTools.getFetchSize(fetchSize, ctx.settings());
    if (ctx.family() == POSTGRES && f != 0 && ctx.connection().getAutoCommit())
        log.info("Fetch Size", "A fetch size of " + f + " was set on a auto-commit PostgreSQL connection, which is not recommended. See http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor");
    executeStatementAndGetFirstResultSet(ctx);
    listener.executeEnd(ctx);
    // Fetch a single result set
    if (!many) {
        //         not a result set.
        if (ctx.resultSet() == null) {
            DSLContext dsl = DSL.using(ctx.configuration());
            Field<Integer> c = field(name("UPDATE_COUNT"), int.class);
            Result<Record1<Integer>> r = dsl.newResult(c);
            r.add(dsl.newRecord(c).values(ctx.rows()));
            ctx.resultSet(new MockResultSet(r));
        }
        Field<?>[] fields = getFields(ctx.resultSet().getMetaData());
        cursor = new CursorImpl<R>(ctx, listener, fields, intern.internIndexes(fields), keepStatement(), keepResultSet(), getRecordType(), SettingsTools.getMaxRows(maxRows, ctx.settings()));
        if (!lazy) {
            result = cursor.fetch();
            cursor = null;
        }
    } else // Fetch several result sets
    {
        results = new ResultsImpl(ctx.configuration());
        consumeResultSets(ctx, listener, results, intern);
    }
    return result != null ? result.size() : 0;
}

Example 4 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class TableRecordImpl method storeInsert0.

final int storeInsert0(Field<?>[] storeFields) {
    DSLContext create = create();
    InsertQuery<R> insert = create.insertQuery(getTable());
    addChangedValues(storeFields, insert);
    // Don't store records if no value was set by client code
    if (!insert.isExecutable()) {
        if (log.isDebugEnabled())
            log.debug("Query is not executable", insert);
        return 0;
    }
    // [#1596] Set timestamp and/or version columns to appropriate values
    BigInteger version = addRecordVersion(insert);
    Timestamp timestamp = addRecordTimestamp(insert);
    // [#814] Refresh identity and/or main unique key values
    // [#1002] Consider also identity columns of non-updatable records
    // [#1537] Avoid refreshing identity columns on batch inserts
    Collection<Field<?>> key = setReturningIfNeeded(insert);
    int result = insert.execute();
    if (result > 0) {
        for (Field<?> storeField : storeFields) changed(storeField, false);
        // [#1596] If insert was successful, update timestamp and/or version columns
        setRecordVersionAndTimestamp(version, timestamp);
        // [#1859] If an insert was successful try fetching the generated values.
        getReturningIfNeeded(insert, key);
        fetched = true;
    }
    return result;
}

Example 5 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class ResultImpl method formatInsert.

@Override
public final void formatInsert(Writer writer, Table<?> table, Field<?>... f) {
    DSLContext ctx = DSL.using(configuration());
    try {
        for (R record : this) {
            writer.append(ctx.renderInlined(insertInto(table, f).values(record.intoArray())));
            writer.append(";\n");
        }
        writer.flush();
    } catch (java.io.IOException e) {
        throw new IOException("Exception while writing INSERTs", e);
    }
}