コード例 #1
0
  @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();
  }
コード例 #2
0
ファイル: RowBetweenCondition.java プロジェクト: suhabe/jOOQ
  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();
    }
  }
コード例 #3
0
 @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();
 }
コード例 #4
0
  @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;
  }
コード例 #5
0
ファイル: JooqUtils.java プロジェクト: sergeikh/cattle
  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;
  }
コード例 #6
0
ファイル: PredicateTests.java プロジェクト: Toilal/jOOQ
    @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());
    }
コード例 #7
0
  @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;
  }
コード例 #8
0
ファイル: JooqUtils.java プロジェクト: sergeikh/cattle
  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;
  }
コード例 #9
0
 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);
 }
コード例 #10
0
ファイル: InsertQueryImpl.java プロジェクト: JasonHerr/jOOQ
  @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());
    }
  }
コード例 #11
0
ファイル: InstanceDaoImpl.java プロジェクト: rancher/cattle
  @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;
  }
コード例 #12
0
 @Override
 public final Condition orNot(Condition other) {
   return or(other.not());
 }
コード例 #13
0
 @Override
 public final Condition andNot(Condition other) {
   return and(other.not());
 }
コード例 #14
0
  @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);
  }
コード例 #15
0
ファイル: Pivot.java プロジェクト: sergio13848/jOOQ
    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;
    }