@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();
}
private final QueryPartInternal delegate(Configuration configuration) {
// These casts are safe for RowImpl
RowN r = (RowN) row;
RowN min = (RowN) minValue;
RowN max = (RowN) maxValue;
// These dialects don't support the SYMMETRIC keyword at all
if (symmetric
&& asList(ASE, CUBRID, DB2, DERBY, FIREBIRD, H2, MYSQL, ORACLE, SQLITE, SQLSERVER, SYBASE)
.contains(configuration.getDialect())) {
if (not) {
return (QueryPartInternal) r.notBetween(min, max).and(r.notBetween(max, min));
} else {
return (QueryPartInternal) r.between(min, max).or(r.between(max, min));
}
}
// These dialects either don't support row value expressions, or they
// Can't handle row value expressions with the BETWEEN predicate
else if (row.size() > 1
&& asList(CUBRID, DERBY, FIREBIRD, MYSQL, ORACLE, SQLITE, SQLSERVER, SYBASE)
.contains(configuration.getDialect())) {
Condition result = r.ge(min).and(r.le(max));
if (not) {
result = result.not();
}
return (QueryPartInternal) result;
} else {
return new Native();
}
}
@Override
public boolean isProjectOwner(
long projectId, Long usingAccount, boolean isAdmin, Set<Identity> identities) {
if (identities == null) {
return false;
}
if (isAdmin) {
return true;
}
if (usingAccount != null && usingAccount.equals(projectId)) {
return false;
}
Set<ProjectMemberRecord> projectMembers = new HashSet<>();
Condition allMembers = DSL.falseCondition();
for (Identity id : identities) {
allMembers =
allMembers.or(
PROJECT_MEMBER
.EXTERNAL_ID
.eq(id.getExternalId())
.and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(id.getExternalIdType()))
.and(PROJECT_MEMBER.ROLE.eq(ProjectConstants.OWNER))
.and(PROJECT_MEMBER.PROJECT_ID.eq(projectId))
.and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE))
.and(PROJECT_MEMBER.REMOVED.isNull()));
}
projectMembers.addAll(create().selectFrom(PROJECT_MEMBER).where(allMembers).fetch());
return !projectMembers.isEmpty();
}
@Override
public List<Account> getAccessibleProjects(
Set<Identity> identities, boolean isAdmin, Long usingAccount) {
List<Account> projects = new ArrayList<>();
if (identities == null) {
return projects;
}
if (isAdmin) {
projects.addAll(
create()
.selectFrom(ACCOUNT)
.where(ACCOUNT.KIND.eq(ProjectConstants.TYPE).and(ACCOUNT.REMOVED.isNull()))
.orderBy(ACCOUNT.ID.asc())
.fetch());
return projects;
}
if (usingAccount != null) {
Account project = getAccountById(usingAccount);
if (project != null && project.getKind().equalsIgnoreCase(ProjectConstants.TYPE)) {
projects.add(project);
return projects;
}
}
// DSL.falseCondition is created so that we can dynamically build a or
// Condition without caring what the external Ids are and still make one
// Database call.
Condition allMembers = DSL.falseCondition();
for (Identity id : identities) {
allMembers =
allMembers.or(
PROJECT_MEMBER
.EXTERNAL_ID
.eq(id.getExternalId())
.and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(id.getExternalIdType()))
.and(PROJECT_MEMBER.REMOVED.isNull())
.and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE)));
}
SelectQuery<Record> query = create().selectQuery();
query.addFrom(ACCOUNT);
query.addJoin(PROJECT_MEMBER, PROJECT_MEMBER.PROJECT_ID.equal(ACCOUNT.ID));
query.addConditions(allMembers);
query.setDistinct(true);
projects.addAll(query.fetchInto(ACCOUNT));
Map<Long, Account> returnProjects = new HashMap<>();
for (Account project : projects) {
returnProjects.put(project.getId(), project);
}
projects = new ArrayList<>();
projects.addAll(returnProjects.values());
return projects;
}
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;
}
@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());
}
@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;
}
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;
}
public List<? extends ProjectMember> getProjectMembersByIdentity(
long projectId, Set<Identity> identities) {
Condition allMembers = DSL.falseCondition();
for (Identity identity : identities) {
allMembers =
allMembers.or(
PROJECT_MEMBER
.EXTERNAL_ID
.eq(identity.getExternalId())
.and(PROJECT_MEMBER.EXTERNAL_ID_TYPE.eq(identity.getExternalIdType()))
.and(PROJECT_MEMBER.REMOVED.isNull())
.and(PROJECT_MEMBER.STATE.eq(CommonStatesConstants.ACTIVE))
.and(PROJECT_MEMBER.PROJECT_ID.eq(projectId)));
}
SelectQuery<Record> query = create().selectQuery();
query.addFrom(PROJECT_MEMBER);
query.addConditions(allMembers);
query.setDistinct(true);
return query.fetchInto(PROJECT_MEMBER);
}
@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 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;
}
@Override
public final Condition orNot(Condition other) {
return or(other.not());
}
@Override
public final Condition andNot(Condition other) {
return and(other.not());
}
@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;
}
