@Test public void testBasicJooqSample() { CompanyDao companyDao = new CompanyDao(configuration()); companyDao.findAll().stream().map(String::valueOf).forEach(log::info); Result<Record2<String, String>> results = dslContext() .select(COMPANY.ORGANISATIONNAME, USERPROFILE.EMAIL) .from(COMPANY) .join(USERPROFILE) .on(COMPANY.ID.eq(USERPROFILE.COMPANY_ID)) .join(REGULARUSER) .on(REGULARUSER.ID.eq(USERPROFILE.ID)) .fetch(); // Loop and process results }
public List<User> findAllUsers() { Result<Record2<String, String>> records = dsl.select(USER.USER_NAME, USER_ROLE.ROLE) .from(USER) .leftOuterJoin(USER_ROLE) .on(USER.USER_NAME.eq(USER_ROLE.USER_NAME)) .fetch(); Map<String, List<Record2<String, String>>> byUserName = records.stream() .collect(groupingBy(r -> r.getValue(USER.USER_NAME))); return byUserName.entrySet().stream() .map( entry -> ImmutableUser.builder() .userName(entry.getKey()) .roles(entry.getValue() .stream() .map(record -> record.getValue(USER_ROLE.ROLE)) .filter(roleName -> roleName != null) .map(roleName -> Role.valueOf(roleName)) .collect(Collectors.toList())) .build()) .collect(toList()); }
@Override public Map<Long, Long> findStoragePoolHostsByDriver(long clusterId, Long storageDriverId) { final Map<Long, Long> result = new HashMap<>(); create().select(HOST.ID, STORAGE_POOL.ID) .from(HOST) .leftOuterJoin(STORAGE_POOL_HOST_MAP) .on(STORAGE_POOL_HOST_MAP.HOST_ID.eq(HOST.ID)) .leftOuterJoin(STORAGE_POOL) .on(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID.eq(STORAGE_POOL.ID) .and(STORAGE_POOL.STORAGE_DRIVER_ID.eq(storageDriverId))) .where(STORAGE_POOL.REMOVED.isNull() .and(STORAGE_POOL_HOST_MAP.REMOVED.isNull()) .and(HOST.CLUSTER_ID.eq(clusterId)) .and(HOST.REMOVED.isNull())) .fetchInto((RecordHandler<Record2<Long, Long>>) record -> { Long hostId = record.getValue(HOST.ID); Long storagePoolId = record.getValue(STORAGE_POOL.ID); if (!result.containsKey(hostId) || storagePoolId != null) { result.put(hostId, storagePoolId); } }); return result; }
@Override public Map<Long, List<Object>> findHostsForPools(List<Long> ids, final IdFormatter idFormatter) { final Map<Long, List<Object>> result = new HashMap<>(); create().select(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID, STORAGE_POOL_HOST_MAP.HOST_ID) .from(STORAGE_POOL_HOST_MAP) .where(STORAGE_POOL_HOST_MAP.REMOVED.isNull() .and(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID.in(ids))) .fetchInto(new RecordHandler<Record2<Long, Long>>() { @Override public void next(Record2<Long, Long> record) { Long hostId = record.getValue(STORAGE_POOL_HOST_MAP.HOST_ID); Long storagePoolId = record.getValue(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID); List<Object> pools = result.get(storagePoolId); if (pools == null) { pools = new ArrayList<>(); result.put(storagePoolId, pools); } pools.add(idFormatter.formatId(HostConstants.TYPE, hostId)); } }); return result; }
@Override public Map<Long, List<Object>> findVolumesForPools(List<Long> ids, final IdFormatter idFormatter) { final Map<Long, List<Object>> result = new HashMap<>(); create().select(VOLUME.STORAGE_POOL_ID, VOLUME.ID) .from(VOLUME) .join(STORAGE_POOL) .on(STORAGE_POOL.ID.eq(VOLUME.STORAGE_POOL_ID)) .where(STORAGE_POOL.KIND.ne("docker") .and(VOLUME.STORAGE_POOL_ID.in(ids))) .fetchInto(new RecordHandler<Record2<Long, Long>>() { @Override public void next(Record2<Long, Long> record) { Long volumeId = record.getValue(VOLUME.ID); Long storagePoolId = record.getValue(VOLUME.STORAGE_POOL_ID); List<Object> pools = result.get(storagePoolId); if (pools == null) { pools = new ArrayList<>(); result.put(storagePoolId, pools); } pools.add(idFormatter.formatId(VolumeConstants.TYPE, volumeId)); } }); return result; }
@Override public Map<Long, List<Object>> getInstancesPerHost(List<Long> hosts, final IdFormatter idFormatter) { final Map<Long, List<Object>> result = new HashMap<>(); create().select(INSTANCE.ID, INSTANCE.HOST_ID) .from(INSTANCE) .where(INSTANCE.HOST_ID.in(hosts) .and(INSTANCE.REMOVED.isNull())) .fetchInto((RecordHandler<Record2<Long, Long>>) record -> { Long hostId = record.getValue(INSTANCE.HOST_ID); Long instanceId = record.getValue(INSTANCE.ID); List<Object> list = result.get(hostId); if (list == null) { list = new ArrayList<>(); result.put(hostId, list); } list.add(idFormatter.formatId(InstanceConstants.TYPE, instanceId)); }); return result; }
@Override public Map<Long, List<Object>> getServicesForStack(List<Long> ids, final IdFormatter idFormatter) { final Map<Long, List<Object>> result = new HashMap<>(); create().select(SERVICE.ID, SERVICE.STACK_ID) .from(SERVICE) .where(SERVICE.STACK_ID.in(ids) .and(SERVICE.REMOVED.isNull())) .fetchInto((RecordHandler<Record2<Long, Long>>) record -> { Long id = record.getValue(SERVICE.ID); Long stackId = record.getValue(SERVICE.STACK_ID); List<Object> list = result.get(stackId); if (list == null) { list = new ArrayList<>(); result.put(stackId, list); } list.add(idFormatter.formatId(ServiceConstants.KIND_SERVICE, id)); }); return result; }
protected Iterator<AllocationCandidate> iteratorHosts(List<Long> volumes, QueryOptions options, boolean hosts, AllocationCandidateCallback callback) { final Cursor<Record2<Long,Long>> cursor = create() .select(HOST.ID, STORAGE_POOL.ID) .from(HOST) .leftOuterJoin(STORAGE_POOL_HOST_MAP) .on(STORAGE_POOL_HOST_MAP.HOST_ID.eq(HOST.ID) .and(STORAGE_POOL_HOST_MAP.REMOVED.isNull())) .join(STORAGE_POOL) .on(STORAGE_POOL.ID.eq(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID)) .leftOuterJoin(AGENT) .on(AGENT.ID.eq(HOST.AGENT_ID)) .where( AGENT.ID.isNull().or(AGENT.STATE.eq(CommonStatesConstants.ACTIVE)) .and(HOST.STATE.eq(CommonStatesConstants.ACTIVE)) .and(STORAGE_POOL.STATE.eq(CommonStatesConstants.ACTIVE)) .and(getQueryOptionCondition(options))) .orderBy(SPREAD.get() ? HOST.COMPUTE_FREE.desc() : HOST.COMPUTE_FREE.asc()) .fetchLazy(); return new AllocationCandidateIterator(objectManager, cursor, volumes, hosts, callback); }
@Override public String[] getLastestActiveApiKeys(Agent agent) { Record2<String, String> result = create() .select(CREDENTIAL.PUBLIC_VALUE, CREDENTIAL.SECRET_VALUE) .from(CREDENTIAL) .join(ACCOUNT) .on(ACCOUNT.ID.eq(CREDENTIAL.ACCOUNT_ID)) .join(AGENT) .on(AGENT.ACCOUNT_ID.eq(ACCOUNT.ID)) .where( AGENT.ID.eq(agent.getId()) .and(CREDENTIAL.KIND.eq(CredentialConstants.KIND_AGENT_API_KEY)) .and(CREDENTIAL.STATE.eq(CommonStatesConstants.ACTIVE))) .orderBy(CREDENTIAL.CREATED.desc()) .fetchAny(); return result == null ? null : new String[] { result.value1(), result.value2() }; }
protected Iterator<AllocationCandidate> iteratorHosts(List<Long> volumes, QueryOptions options, boolean hosts) { final Cursor<Record2<Long,Long>> cursor = create() .select(HOST.ID, STORAGE_POOL.ID) .from(HOST) .leftOuterJoin(STORAGE_POOL_HOST_MAP) .on(STORAGE_POOL_HOST_MAP.HOST_ID.eq(HOST.ID) .and(STORAGE_POOL_HOST_MAP.REMOVED.isNull())) .join(STORAGE_POOL) .on(STORAGE_POOL.ID.eq(STORAGE_POOL_HOST_MAP.STORAGE_POOL_ID)) .leftOuterJoin(AGENT) .on(AGENT.ID.eq(HOST.AGENT_ID)) .where( AGENT.ID.isNull().or(AGENT.STATE.eq(CommonStatesConstants.ACTIVE)) .and(HOST.STATE.eq(CommonStatesConstants.ACTIVE)) .and(STORAGE_POOL.STATE.eq(CommonStatesConstants.ACTIVE)) .and(getQueryOptionCondition(options))) .orderBy(SPREAD.get() ? HOST.COMPUTE_FREE.asc() : HOST.COMPUTE_FREE.desc()) .fetchLazy(); return new AllocationCandidateIterator(objectManager, cursor, volumes, hosts); }
@Override public String[] getLastestActiveApiKeys(Agent agent) { Record2<String, String> result = create() .select(CREDENTIAL.PUBLIC_VALUE, CREDENTIAL.SECRET_VALUE) .from(CREDENTIAL) .join(ACCOUNT) .on(ACCOUNT.ID.eq(CREDENTIAL.ACCOUNT_ID)) .join(AGENT) .on(AGENT.ACCOUNT_ID.eq(ACCOUNT.ID)) .where( AGENT.ID.eq(agent.getId()) .and(CREDENTIAL.STATE.eq(CommonStatesConstants.ACTIVE))) .orderBy(CREDENTIAL.CREATED.desc()) .fetchAny(); return result == null ? null : new String[] { result.value1(), result.value2() }; }
@Override public DbVersion getDBVersion() { Record2<String, DateTime> record = ctx.select(SCHEMA_VERSION.VERSION, SCHEMA_VERSION.INSTALLED_ON) .from(SCHEMA_VERSION) .where(SCHEMA_VERSION.INSTALLED_RANK.eq( select(max(SCHEMA_VERSION.INSTALLED_RANK)).from(SCHEMA_VERSION))) .fetchOne(); String ts = DateTimeUtils.humanize(record.value2()); return DbVersion.builder() .version(record.value1()) .updateTimestamp(ts) .build(); }
public static List<Clause<Optional<GameProfile>, Integer>> getTop(ScoreType scoreType, int count) { try (Connection con = SQLHandle.getConnection()) { DSLContext create = DSL.using(con); Result<Record2<String, Integer>> results = create.select(PLAYERS.UUID, HIGH_SCORES.VALUE).from(HIGH_SCORES).join(PLAYERS).on(PLAYERS.ID.equal(HIGH_SCORES.PLAYER_ID)).where( HIGH_SCORES.SCORE_TYPE_ID.equal(scoreType.getId()) ).orderBy(scoreType.getOrder() == ScoreType.Order.ASC ? HIGH_SCORES.VALUE.asc() : HIGH_SCORES.VALUE.desc()).limit(count).fetch(); return results.stream().map( record -> new Clause<>(getProfile(record.getValue(PLAYERS.UUID)), record.getValue(HIGH_SCORES.VALUE)) ).collect(Collectors.toList()); } catch (SQLException e) { e.printStackTrace(); } return Lists.newArrayList(); }
@Test public void testService() { assertNotNull(authorService); authorService.save(1, "testFirstaName", "testLastName"); Record2<String, String> record2 = authorService.get(); assertEquals("testFirstaName", record2.value1()); assertEquals("testLastName", record2.value2()); }
@Override public Map<Long, String> getInstanceIdToInstanceName(long accountId) { final Map<Long, String> toReturn = new HashMap<>(); create().select(INSTANCE.ID, INSTANCE.NAME) .from(INSTANCE) .where(INSTANCE.ACCOUNT_ID.eq(accountId)) .and(INSTANCE.REMOVED.isNull()) .fetchInto(new RecordHandler<Record2<Long, String>>() { @Override public void next(Record2<Long, String> record) { toReturn.put(record.getValue(INSTANCE.ID), record.getValue(INSTANCE.NAME)); } }); return toReturn; }
@Override public Map<Long, String> getCertificateIdToCertificate(long accountId) { final Map<Long, String> toReturn = new HashMap<>(); create().select(CERTIFICATE.ID, CERTIFICATE.NAME) .from(CERTIFICATE) .where(CERTIFICATE.ACCOUNT_ID.eq(accountId)) .and(CERTIFICATE.REMOVED.isNull()) .fetchInto(new RecordHandler<Record2<Long, String>>() { @Override public void next(Record2<Long, String> record) { toReturn.put(record.getValue(CERTIFICATE.ID), record.getValue(CERTIFICATE.NAME)); } }); return toReturn; }
@Override public ItemVersion getRequestedItemVersion(Client client, String itemName) { Record2<Long,String> result = create() .select(CONFIG_ITEM_STATUS.REQUESTED_VERSION, CONFIG_ITEM.SOURCE_VERSION) .from(CONFIG_ITEM_STATUS) .join(CONFIG_ITEM) .on(CONFIG_ITEM.NAME.eq(CONFIG_ITEM_STATUS.NAME)) .where( CONFIG_ITEM_STATUS.NAME.eq(itemName) .and(targetObjectCondition(client))) .fetchOne(); return result == null ? null : new DefaultItemVersion(result.value1(), result.value2()); }
public AllocationCandidateIterator(ObjectManager objectManager, Cursor<Record2<Long,Long>> cursor, List<Long> volumeIds, boolean hosts, AllocationCandidateCallback callback) { super(); this.objectManager = objectManager; this.volumeIds = volumeIds; this.cursor = cursor; this.hosts = hosts; this.callback = callback; }
protected Map<Long,String[]> readKeys() { final Map<Long,String[]> keys = new LinkedHashMap<Long, String[]>(); create() .select(DATA.NAME, DATA.VALUE) .from(DATA) .where( DATA.NAME.like("ssh-client-key%")) .orderBy(DATA.NAME.asc()) .fetchInto(new RecordHandler<Record2<String,String>>() { @Override public void next(Record2<String,String> record) { Matcher m = KEY_NAME_PATTERN.matcher(record.value1()); if ( ! m.matches() ) { return; } Long key = new Long(m.group(1)); String type = m.group(2); String[] value = keys.get(key); if ( value == null ) { value = new String[2]; keys.put(key, value); } int idx = type.equals("public") ? 0 : 1; value[idx] = record.value2(); } }); return keys; }
public AllocationCandidateIterator(ObjectManager objectManager, Cursor<Record2<Long,Long>> cursor, List<Long> volumeIds, boolean hosts) { super(); this.objectManager = objectManager; this.volumeIds = volumeIds; this.cursor = cursor; this.hosts = hosts; }
@SuppressWarnings("unchecked") @Override public <T> T get(String key) { if (key == null) { throw new IllegalArgumentException("Key cannot be null"); } Object cached = cache.get(key); if (cached != null) { return (T) cached; } Record2<UUID, ReferenceType> result = model.create() .select(WORKSPACE_LABEL.REFERENCE, WORKSPACE_LABEL.TYPE) .from(WORKSPACE_LABEL) .where(WORKSPACE_LABEL.WORKSPACE.equal(getDefiningProduct().getId())) .and(WORKSPACE_LABEL.KEY.equal(key)) .fetchOne(); if (result == null) { return null; } T ruleform; switch (result.value2()) { case Existential: ruleform = model.records() .resolve(result.value1()); break; default: throw new IllegalStateException(String.format("Unable to find result type: %s", result.value2())); } if (key != null) { cache.put(key, ruleform); } return ruleform; }
/** * {@inheritDoc} */ @Override public Record2<Long, Long> key() { return (Record2) super.key(); }
/** * {@inheritDoc} */ @Override protected Record2<Integer, Integer> getId(generated.future.async.vertx.tables.pojos.Somethingcomposite object) { return compositeKeyRecord(object.getSomeid(), object.getSomesecondid()); }
/** * {@inheritDoc} */ @Override public Record2<Integer, Integer> key() { return (Record2) super.key(); }
/** * {@inheritDoc} */ @Override protected Record2<Integer, Integer> getId(generated.rx.async.vertx.tables.pojos.Somethingcomposite object) { return compositeKeyRecord(object.getSomeid(), object.getSomesecondid()); }
/** * {@inheritDoc} */ @Override protected Record2<Integer, Integer> getId(generated.classic.async.vertx.tables.pojos.Somethingcomposite object) { return compositeKeyRecord(object.getSomeid(), object.getSomesecondid()); }
@Override public void insertReturningPrimaryAsync(generated.classic.async.vertx.tables.pojos.Somethingcomposite object, Handler<AsyncResult<org.jooq.Record2<java.lang.Integer, java.lang.Integer>>> resultHandler){ throw new UnsupportedOperationException("More than one PK column"); }
/** * {@inheritDoc} */ @Override public Record2<UInteger, String> key() { return (Record2) super.key(); }
/** * {@inheritDoc} */ @Override public Record2<UInteger, UInteger> key() { return (Record2) super.key(); }
/** * {@inheritDoc} */ @Override public Record2<UInteger, UShort> key() { return (Record2) super.key(); }
/** * {@inheritDoc} */ @Override public Record2<String, Long> key() { return (Record2) super.key(); }