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

Example 1 with SelectQuery

use of org.jooq.SelectQuery 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 SelectQuery

use of org.jooq.SelectQuery 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 SelectQuery

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

the class LoaderImpl method executeSQL.

private void executeSQL(Iterator<? extends Object[]> iterator) throws SQLException {
    Object[] row = null;
    BatchBindStep bind = null;
    InsertQuery<R> insert = null;
    execution: {
        rows: while (iterator.hasNext() && ((row = iterator.next()) != null)) {
            try {
                // [#5858] Work with non String[] types from here on (e.g. after CSV import)
                if (row.getClass() != Object[].class)
                    row = Arrays.copyOf(row, row.length, Object[].class);
                //         in case LoaderFieldMapper was used.
                if (fields == null)
                    fields0(row);
                // [#2741]         TODO: This logic will be externalised in new SPI
                for (int i = 0; i < row.length; i++) if (StringUtils.equals(nullString, row[i]))
                    row[i] = null;
                else if (i < fields.length && fields[i] != null)
                    if (fields[i].getType() == byte[].class && row[i] instanceof String)
                        row[i] = DatatypeConverter.parseBase64Binary((String) row[i]);
                // TODO: In batch mode, we can probably optimise this by not creating
                // new statements every time, just to convert bind values to their
                // appropriate target types. But beware of SQL dialects that tend to
                // need very explicit casting of bind values (e.g. Firebird)
                processed++;
                // in some dialects
                if (onDuplicate == ON_DUPLICATE_KEY_IGNORE) {
                    SelectQuery<R> select = create.selectQuery(table);
                    for (int i = 0; i < row.length; i++) if (i < fields.length && primaryKey[i])
                        select.addConditions(getCondition(fields[i], row[i]));
                    try {
                        if (create.fetchExists(select)) {
                            ignored++;
                            continue rows;
                        }
                    } catch (DataAccessException e) {
                        errors.add(new LoaderErrorImpl(e, row, processed - 1, select));
                    }
                }
                buffered++;
                if (insert == null)
                    insert = create.insertQuery(table);
                for (int i = 0; i < row.length; i++) if (i < fields.length && fields[i] != null)
                    addValue0(insert, fields[i], row[i]);
                // dialects execute a SELECT and then either an INSERT or UPDATE
                if (onDuplicate == ON_DUPLICATE_KEY_UPDATE) {
                    insert.onDuplicateKeyUpdate(true);
                    for (int i = 0; i < row.length; i++) if (i < fields.length && fields[i] != null && !primaryKey[i])
                        addValueForUpdate0(insert, fields[i], row[i]);
                } else // Don't do anything. Let the execution fail
                if (onDuplicate == ON_DUPLICATE_KEY_ERROR) {
                }
                try {
                    if (bulk != BULK_NONE) {
                        if (bulk == BULK_ALL || processed % bulkAfter != 0) {
                            insert.newRecord();
                            continue rows;
                        }
                    }
                    if (batch != BATCH_NONE) {
                        if (bind == null)
                            bind = create.batch(insert);
                        bind.bind(insert.getBindValues().toArray());
                        insert = null;
                        if (batch == BATCH_ALL || processed % (bulkAfter * batchAfter) != 0)
                            continue rows;
                    }
                    if (bind != null)
                        bind.execute();
                    else if (insert != null)
                        insert.execute();
                    stored += buffered;
                    executed++;
                    buffered = 0;
                    bind = null;
                    insert = null;
                    if (commit == COMMIT_AFTER)
                        if ((processed % batchAfter == 0) && ((processed / batchAfter) % commitAfter == 0))
                            commit();
                } catch (DataAccessException e) {
                    errors.add(new LoaderErrorImpl(e, row, processed - 1, insert));
                    ignored += buffered;
                    buffered = 0;
                    if (onError == ON_ERROR_ABORT)
                        break execution;
                }
            } finally {
                if (listener != null)
                    listener.row(result);
            }
        // rows:
        }
        // Execute remaining batch
        if (buffered != 0) {
            try {
                if (bind != null)
                    bind.execute();
                if (insert != null)
                    insert.execute();
                stored += buffered;
                executed++;
                buffered = 0;
            } catch (DataAccessException e) {
                errors.add(new LoaderErrorImpl(e, row, processed - 1, insert));
                ignored += buffered;
                buffered = 0;
            }
            if (onError == ON_ERROR_ABORT)
                break execution;
        }
    // execution:
    }
    // Rollback on errors in COMMIT_ALL mode
    try {
        if (commit == COMMIT_ALL) {
            if (!errors.isEmpty()) {
                stored = 0;
                rollback();
            } else {
                commit();
            }
        } else // Commit remaining elements in COMMIT_AFTER mode
        if (commit == COMMIT_AFTER) {
            commit();
        }
    } catch (DataAccessException e) {
        errors.add(new LoaderErrorImpl(e, null, processed - 1, null));
    }
}