Examples with JooqContainer com.intel.mtwilson.jooq.util.JooqContainer used on opensource projects

Example 1 with JooqContainer

use of com.intel.mtwilson.jooq.util.JooqContainer 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 JooqContainer

use of com.intel.mtwilson.jooq.util.JooqContainer 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 JooqContainer

use of com.intel.mtwilson.jooq.util.JooqContainer in project OpenAttestation by OpenAttestation.

the class TagJdbi method jooq.

/**
     * CODE QUALITY: All usage of this method should be in the following form:
     * <pre>
     * try(JooqContainer jc = TagJdbi.jooq()) {
     * DSLContext jooq = jc.getDslContext();
     * // code
     * }
     * </pre>
     *
     * This ensures the jooq database connection is automatically released
     * at the end of the block (either closed or returned to the pool)
     * 
     * @return
     * @throws SQLException
     * @throws IOException
     */
public static JooqContainer jooq() throws SQLException, IOException {
    // omits the schema name from generated sql ; when we connect to the database we already specify a schema so this settings avoid 
    // redundancy in the sql and allows the administrator to change the database name without breaking the application
    Settings settings = new Settings().withRenderSchema(false).withRenderNameStyle(RenderNameStyle.LOWER);
    SQLDialect dbDialect = getSqlDialect();
    // throws SQLException; Note that the DSLContext doesn't close the connection. We'll have to do that ourselves.
    Connection connection = TagJdbi.getDataSource().getConnection();
    DSLContext jooq = DSL.using(connection, dbDialect, settings);
    return new JooqContainer(jooq, connection);
}