@Override
public int findByTieIdAndTeachTypeIdAndPageCount(
ClassroomTimetableListVo classroomTimetableListVo, int tieId) {
Condition a =
Tables.CLASSROOM_COURSE_TIMETABLE_INFO
.TIE_ID
.eq((tieId))
.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TEACH_TYPE_ID.eq(
classroomTimetableListVo.getTeachTypeId()));
if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoFileName())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_FILE_NAME.like(
"%" + classroomTimetableListVo.getTimetableInfoFileName() + "%"));
}
if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoTerm())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_TERM.like(
"%" + classroomTimetableListVo.getTimetableInfoTerm() + "%"));
}
if (StringUtils.hasLength(classroomTimetableListVo.getClassroom())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.CLASSROOM.like(
"%" + classroomTimetableListVo.getClassroom() + "%"));
}
Record1<Integer> record1 =
create.selectCount().from(Tables.CLASSROOM_COURSE_TIMETABLE_INFO).where(a).fetchOne();
return record1.value1();
}
protected static org.jooq.Condition listToCondition(TableField<?, Object> field, List<?> list) {
org.jooq.Condition condition = null;
for (Object value : list) {
if (value instanceof Condition) {
org.jooq.Condition newCondition = toCondition(field, (Condition) value);
condition = condition == null ? newCondition : condition.and(newCondition);
} else {
condition = condition == null ? field.eq(value) : condition.and(field.eq(value));
}
}
return condition;
}
@Override
public Result<ClassroomCourseTimetableInfoRecord> findByTieIdAndTeachTypeIdAndPage(
ClassroomTimetableListVo classroomTimetableListVo, int tieId) {
Condition a =
Tables.CLASSROOM_COURSE_TIMETABLE_INFO
.TIE_ID
.eq((tieId))
.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TEACH_TYPE_ID.eq(
classroomTimetableListVo.getTeachTypeId()));
if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoFileName())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_FILE_NAME.like(
"%" + classroomTimetableListVo.getTimetableInfoFileName() + "%"));
}
if (StringUtils.hasLength(classroomTimetableListVo.getTimetableInfoTerm())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_TERM.like(
"%" + classroomTimetableListVo.getTimetableInfoTerm() + "%"));
}
if (StringUtils.hasLength(classroomTimetableListVo.getClassroom())) {
a =
a.and(
Tables.CLASSROOM_COURSE_TIMETABLE_INFO.CLASSROOM.like(
"%" + classroomTimetableListVo.getClassroom() + "%"));
}
int pageNum = classroomTimetableListVo.getPageNum();
int pageSize = classroomTimetableListVo.getPageSize();
if (pageNum <= 0) {
pageNum = 1;
}
Result<ClassroomCourseTimetableInfoRecord> record14s =
create
.selectFrom(Tables.CLASSROOM_COURSE_TIMETABLE_INFO)
.where(a)
.orderBy(Tables.CLASSROOM_COURSE_TIMETABLE_INFO.TIMETABLE_INFO_FILE_DATE.desc())
.limit((pageNum - 1) * pageSize, pageSize)
.fetch();
return record14s;
}
@Override
public Instance getInstanceByUuidOrExternalId(Long accountId, String uuid, String externalId) {
Instance instance = null;
Condition condition =
INSTANCE
.ACCOUNT_ID
.eq(accountId)
.and(INSTANCE.STATE.notIn(CommonStatesConstants.PURGED, CommonStatesConstants.PURGING));
if (StringUtils.isNotEmpty(uuid)) {
instance =
create().selectFrom(INSTANCE).where(condition.and(INSTANCE.UUID.eq(uuid))).fetchAny();
}
if (instance == null && StringUtils.isNotEmpty(externalId)) {
instance =
create()
.selectFrom(INSTANCE)
.where(condition.and(INSTANCE.EXTERNAL_ID.eq(externalId)))
.fetchAny();
}
return instance;
}
@Test
public void testConditionalSelect() throws Exception {
Condition c = trueCondition();
assertEquals(4, create().selectFrom(TBook()).where(c).execute());
c = c.and(TBook_PUBLISHED_IN().greaterThan(1945));
assertEquals(3, create().selectFrom(TBook()).where(c).execute());
c = c.not();
assertEquals(1, create().selectFrom(TBook()).where(c).execute());
c = c.or(TBook_AUTHOR_ID().equal(
select(TAuthor_ID()).from(TAuthor()).where(TAuthor_FIRST_NAME().equal("Paulo"))));
assertEquals(3, create().selectFrom(TBook()).where(c).execute());
}
public static org.jooq.Condition toConditions(
ObjectMetaDataManager metaData, String type, Map<Object, Object> criteria) {
org.jooq.Condition existingCondition = null;
for (Map.Entry<Object, Object> entry : criteria.entrySet()) {
Object value = entry.getValue();
Object key = entry.getKey();
TableField<?, Object> field = null;
if (key == org.jooq.Condition.class) {
if (!(value instanceof org.jooq.Condition)) {
throw new IllegalArgumentException(
"If key is Condition, value must be an instanceof Condition got key ["
+ key
+ "] value ["
+ value
+ "]");
}
} else {
field = getTableField(metaData, type, key);
if (field == null) {
continue;
}
}
org.jooq.Condition newCondition = null;
if (value instanceof org.jooq.Condition) {
newCondition = (org.jooq.Condition) value;
} else if (value instanceof Condition) {
newCondition = toCondition(field, (Condition) value);
} else if (value instanceof List) {
newCondition = listToCondition(field, (List<?>) value);
} else if (value == null) {
newCondition = field.isNull();
} else {
newCondition = field.eq(value);
}
if (existingCondition == null) {
existingCondition = newCondition;
} else {
existingCondition = existingCondition.and(newCondition);
}
}
return existingCondition;
}
@SuppressWarnings("unchecked")
private final Merge<R> toMerge(Configuration configuration) {
Table<R> i = getInto();
if (i.getPrimaryKey() != null) {
Condition condition = null;
List<Field<?>> key = new ArrayList<Field<?>>();
for (Field<?> f : i.getPrimaryKey().getFields()) {
Field<Object> field = (Field<Object>) f;
Field<Object> value = (Field<Object>) insertMaps.getMap().get(field);
key.add(value);
Condition other = field.equal(value);
if (condition == null) {
condition = other;
} else {
condition = condition.and(other);
}
}
MergeOnConditionStep<R> on = create(configuration).mergeInto(i).usingDual().on(condition);
// [#1295] Use UPDATE clause only when with ON DUPLICATE KEY UPDATE,
// not with ON DUPLICATE KEY IGNORE
MergeNotMatchedStep<R> notMatched = on;
if (onDuplicateKeyUpdate) {
notMatched = on.whenMatchedThenUpdate().set(updateMap);
}
return notMatched
.whenNotMatchedThenInsert(insertMaps.getMap().keySet())
.values(insertMaps.getMap().values());
} else {
throw new IllegalStateException(
"The ON DUPLICATE KEY IGNORE/UPDATE clause cannot be simulated when inserting into non-updatable tables : "
+ getInto());
}
}
@Override
public Page<DatasetItem> fetchPage(
Pageable pageable,
boolean excludeEmpty,
String termFilter,
Boolean hasVectorData,
Boolean hasCoverageData,
Boolean hasLayerData,
Boolean hasSensorData) {
// Query filters.
Condition condition = DSL.trueCondition();
if (isNotBlank(termFilter)) {
condition =
condition.and(
DATASET
.IDENTIFIER
.likeIgnoreCase('%' + termFilter + '%')
.or(CSTL_USER.LOGIN.likeIgnoreCase('%' + termFilter + '%'))
.or(DATA.NAME.likeIgnoreCase('%' + termFilter + '%')));
}
if (excludeEmpty) {
condition = condition.and(countData(DATASET.ID).asField().greaterThan(0));
}
if (hasVectorData != null) {
Field<Integer> countVectorData = countDataOfType(DATASET.ID, "VECTOR").asField();
condition =
condition.and(hasVectorData ? countVectorData.greaterThan(0) : countVectorData.eq(0));
}
if (hasCoverageData != null) {
Field<Integer> countCoverageData = countDataOfType(DATASET.ID, "COVERAGE").asField();
condition =
condition.and(
hasCoverageData ? countCoverageData.greaterThan(0) : countCoverageData.eq(0));
}
if (hasLayerData != null) {
Field<Integer> countLayerData = countLayerData(DATASET.ID).asField();
condition =
condition.and(hasLayerData ? countLayerData.greaterThan(0) : countLayerData.eq(0));
}
if (hasSensorData != null) {
Field<Integer> countSensorData = countSensorData(DATASET.ID).asField();
condition =
condition.and(hasSensorData ? countSensorData.greaterThan(0) : countSensorData.eq(0));
}
// Content query.
List<DatasetItem> content =
dsl.selectDistinct(ITEM_FIELDS)
.from(DATASET)
.leftOuterJoin(CSTL_USER)
.on(CSTL_USER.ID.eq(DATASET.OWNER)) // dataset -> cstl_user
.leftOuterJoin(DATA)
.on(DATA.DATASET_ID.eq(DATASET.ID)) // dataset -> data
.where(condition)
.orderBy(JooqUtils.sortFields(pageable, ITEM_FIELDS))
.limit(pageable.getPageSize())
.offset(pageable.getOffset())
.fetchInto(DatasetItem.class);
// Total query.
Long total =
dsl.selectDistinct(DSL.countDistinct(DATASET.ID))
.from(DATASET)
.leftOuterJoin(CSTL_USER)
.on(DATASET.OWNER.eq(CSTL_USER.ID)) // dataset -> cstl_user
.leftOuterJoin(DATA)
.on(DATA.DATASET_ID.eq(DATASET.ID)) // dataset -> data
.where(condition)
.fetchOne(0, Long.class);
return new PageImpl<>(pageable, content, total);
}
private Table<Record> select(Configuration configuration) {
List<Field<?>> groupingFields = new ArrayList<Field<?>>();
List<Field<?>> aliasedGroupingFields = new ArrayList<Field<?>>();
List<Field<?>> aggregatedFields = new ArrayList<Field<?>>();
Table<?> pivot = table.as("pivot_outer");
// Clearly, the API should be improved to make this more object-
// oriented...
// This loop finds all fields that are used in aggregate
// functions. They're excluded from the GROUP BY clause
for (Field<?> field : aggregateFunctions) {
if (field instanceof Function) {
for (QueryPart argument : ((Function<?>) field).getArguments()) {
if (argument instanceof Field) {
aggregatedFields.add((Field<?>) argument);
}
}
}
}
// This loop finds all fields qualify for GROUP BY clauses
for (Field<?> field : table.fields()) {
if (!aggregatedFields.contains(field)) {
if (!on.equals(field)) {
aliasedGroupingFields.add(pivot.field(field));
groupingFields.add(field);
}
}
}
// The product {aggregateFunctions} x {in}
List<Field<?>> aggregationSelects = new ArrayList<Field<?>>();
for (Field<?> inField : in) {
for (Field<?> aggregateFunction : aggregateFunctions) {
Condition join = trueCondition();
for (Field<?> field : groupingFields) {
join = join.and(condition(pivot, field));
}
@SuppressWarnings("unchecked")
Select<?> aggregateSelect =
using(configuration)
.select(aggregateFunction)
.from(table)
.where(on.equal((Field<T>) inField))
.and(join);
aggregationSelects.add(
aggregateSelect.asField(inField.getName() + "_" + aggregateFunction.getName()));
}
}
// This is the complete select
Table<Record> select =
using(configuration)
.select(aliasedGroupingFields)
.select(aggregationSelects)
.from(pivot)
.where(pivot.field(on).in(in.toArray(new Field[0])))
.groupBy(aliasedGroupingFields)
.asTable();
return select;
}