/** * Queries all not indexed {@link News} from the database and puts them to * the {@link IndexManager} queue. */ public void indexNotIndexedNews() { final List<News> newsToIndex = DSL.using(jooqConfig). select(). from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN). on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))). where(TABLE_CONTENT.INDEXED.eq(Boolean.FALSE)). fetchInto(News.class); if(newsToIndex.isEmpty()) { LOGGER.info("No unindexed news found."); return; // nothing to index } final SimpleIndexData indexData = new SimpleIndexData(NewsIndexType.getInstance()); for (final News news : newsToIndex) { LOGGER.info("Add unindexed news {} to index queue.", news.getId()); indexData.addElement(new NewsIndexElement(news, EOperation.UPDATE)); // update because it should delete a possible existing news. } IndexManager.getInstance().index(indexData); }
protected void addMappingJoins(SelectQuery<?> query, Table<?> toTable, SchemaFactory schemaFactory, String fromType, Table<?> from, String asName, MapRelationship rel) { Table<?> mappingTable = JooqUtils.getTableFromRecordClass(rel.getMappingType()); /* We don't required the mapping type to be visible external, that's why we use the schemaFactory * from the objectManager, because it is the superset schemaFactory. */ String mappingType = getObjectManager().getSchemaFactory().getSchemaName(rel.getMappingType()); TableField<?, Object> fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD); TableField<?, Object> fieldTo = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getPropertyName()); TableField<?, Object> fieldRemoved = JooqUtils.getTableField(getMetaDataManager(), mappingType, ObjectMetaDataManager.REMOVED_FIELD); org.jooq.Condition cond = fieldFrom.eq(fieldTo.getTable().field(fieldTo.getName())).and(fieldRemoved == null ? DSL.trueCondition() : fieldRemoved.isNull()); query.addJoin(mappingTable, JoinType.LEFT_OUTER_JOIN, cond); fieldFrom = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getOtherRelationship().getPropertyName()); fieldTo = JooqUtils.getTableField(getMetaDataManager(), schemaFactory.getSchemaName(rel.getObjectType()), ObjectMetaDataManager.ID_FIELD); cond = fieldFrom.eq(fieldTo.getTable().asTable(asName).field(fieldTo.getName())); query.addJoin(toTable, JoinType.LEFT_OUTER_JOIN, cond); }
protected void addMappingJoins(SelectQuery<?> query, Table<?> toTable, SchemaFactory schemaFactory, String fromType, Table<?> from, String asName, MapRelationship rel) { Table<?> mappingTable = JooqUtils.getTableFromRecordClass(rel.getMappingType()); String mappingType = schemaFactory.getSchemaName(rel.getMappingType()); TableField<?, Object> fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD); TableField<?, Object> fieldTo = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getPropertyName()); TableField<?, Object> fieldRemoved = JooqUtils.getTableField(getMetaDataManager(), mappingType, ObjectMetaDataManager.REMOVED_FIELD); org.jooq.Condition cond = fieldFrom.eq(fieldTo.getTable().field(fieldTo.getName())).and(fieldRemoved == null ? DSL.trueCondition() : fieldRemoved.isNull()); query.addJoin(mappingTable, JoinType.LEFT_OUTER_JOIN, cond); fieldFrom = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getOtherRelationship().getPropertyName()); fieldTo = JooqUtils.getTableField(getMetaDataManager(), schemaFactory.getSchemaName(rel.getObjectType()), ObjectMetaDataManager.ID_FIELD); cond = fieldFrom.eq(fieldTo.getTable().asTable(asName).field(fieldTo.getName())); query.addJoin(toTable, JoinType.LEFT_OUTER_JOIN, cond); }
/** * Returns the {@link News} with the given id. * @param newsId the it of the {@link News} which will be returned. * @return the found {@link News} or <code>null</code> if no {@link News} was found for the id. */ public News getNews(final long newsId) { return DSL.using(jooqConfig). select(). from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN). on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))). where(TABLE_CONTENT.ID.eq(newsId)). fetchOneInto(News.class); }
/** * Returns the {@link News} from the database. * The result isn't sorted. This method can be used for reindexing. * @param start start index of the results * @param amount the amount of {@link News}s to return after the start index. * @return {@link List} containing {@link News} */ public List<News> getNews(final int start, final int amount) { return DSL.using(jooqConfig). select(). from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN). on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))). limit(start, amount). fetchInto(News.class); }
/** * Returns all ungrouped ({@link News#NEWSGROUPID_NOT_GROUPED_YET}) {@link News} which were already indexed. * Not indexed {@link News} will be grouped after they were indexed. * @return {@link List} containing ungrouped {@link News}. */ public List<News> getNewsToGroup() { return DSL.using(jooqConfig). select(). from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN). on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))). where(TABLE_NEWS.NEWS_GROUP_ID.eq(News.NEWSGROUPID_NOT_GROUPED_YET)). and(TABLE_CONTENT.INDEXED.eq(Boolean.TRUE)). fetchInto(News.class); }
/** * Checks if a {@link News} already exists for the given link. If one exists * then the {@link Defaults} of this are returned. If not then <code>null</code> * will be returned. * * @param news the {@link News} to find a {@link News} with the same link or name. * @return {@link Defaults} or <code>null</code> */ private Defaults getDefaultsForNews(final News news) { final Defaults result = DSL.using(jooqConfig). select(TABLE_CONTENT.ID, TABLE_CONTENT.PUBLISH_DATE, TABLE_NEWS.NEWS_GROUP_ID, TABLE_CONTENT.TITLE, TABLE_CONTENT.TEXT, TABLE_NEWS.IMAGE_URL, TABLE_NEWS.IMAGE_WIDTH, TABLE_NEWS.IMAGE_HEIGHT, TABLE_NEWS.LINK). from(TABLE_CONTENT.join(TABLE_NEWS, JoinType.JOIN). on(TABLE_CONTENT.ID.eq(TABLE_NEWS.CONTENT_ID))). where(TABLE_NEWS.LINK.eq(news.getLink())). or(TABLE_CONTENT.TITLE.eq(news.getTitle()). and(TABLE_CONTENT.PROVIDER_ID.eq(news.getProviderId())). and(TABLE_CONTENT.PUBLISH_DATE.between(getDate(news, PUBLISHDATE_DELTA), getDate(news, -PUBLISHDATE_DELTA)))). fetchOneInto(Defaults.class); // no news found -> exit with null. /* if(result == null) { return null; } final Defaults defaults = new Defaults(); defaults.setId(id); = result.getValue(TABLE_NEWS.CONTENT_ID); defaults.publishDate = result.getValue(TABLE_CONTENT.PUBLISH_DATE); defaults.newsGroupId = result.getValue(TABLE_NEWS.NEWS_GROUP_ID); */ return result; }
@SuppressWarnings("unchecked") private Result<Record7<Integer, Integer, String, String, String, String, String>> getOverviewInternal(UserQueryForm form) { SelectQuery selectQuery = ctx.selectQuery(); selectQuery.addFrom(USER); selectQuery.addJoin(OCPP_TAG, JoinType.LEFT_OUTER_JOIN, USER.OCPP_TAG_PK.eq(OCPP_TAG.OCPP_TAG_PK)); selectQuery.addSelect( USER.USER_PK, USER.OCPP_TAG_PK, OCPP_TAG.ID_TAG, USER.FIRST_NAME, USER.LAST_NAME, USER.PHONE, USER.E_MAIL ); if (form.isSetUserPk()) { selectQuery.addConditions(USER.USER_PK.eq(form.getUserPk())); } if (form.isSetOcppIdTag()) { selectQuery.addConditions(includes(OCPP_TAG.ID_TAG, form.getOcppIdTag())); } if (form.isSetEmail()) { selectQuery.addConditions(includes(USER.E_MAIL, form.getEmail())); } if (form.isSetName()) { // Concatenate the two columns and search within the resulting representation // for flexibility, since the user can search by first or last name, or both. Field<String> joinedField = DSL.concat(USER.FIRST_NAME, USER.LAST_NAME); // Find a matching sequence anywhere within the concatenated representation selectQuery.addConditions(includes(joinedField, form.getName())); } return selectQuery.fetch(); }
private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createPictureQuery() { SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> pictureSelect = create .select(DSL.val("P").as("type"), LOCATION.OFFICIAL_NAME, LOCATION.CITY, USERS.DISPLAYNAME.as("user"), DSL.val("N").as("update_Type"), LOCATION.ID, PICTURES.CREATED_ON, PICTURES.ID.as("picture_Id")) .from(LOCATION).join(PICTURES, JoinType.JOIN).on(LOCATION.ID.equal(PICTURES.FK_LOCATION)) .join(USERS, JoinType.JOIN).on(PICTURES.FK_USER.equal(USERS.ID)) .where(LOCATION.FK_COMMUNITY.equal(fkCommunity).and(LOCATION.ARCHIVED.equal(0))); return pictureSelect; }
private SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> createReviewQuery() { SelectConditionStep<Record8<String, String, String, String, String, Integer, Timestamp, Integer>> reviewsSelect = create .select(DSL.val("R").as("type"), LOCATION.OFFICIAL_NAME, LOCATION.CITY, USERS.DISPLAYNAME.as("user"), DSL.decode().value(REVIEWS.CREATED_ON).when(REVIEWS.LAST_UPDATE, "N").otherwise("U") .as("update_Type"), LOCATION.ID, REVIEWS.LAST_UPDATE, DSL.val(0).as("picture_Id")) .from(LOCATION).join(REVIEWS, JoinType.JOIN).on(LOCATION.ID.equal(REVIEWS.FK_LOCATION)) .join(USERS, JoinType.JOIN).on(REVIEWS.FK_USER.equal(USERS.ID)) .where(LOCATION.FK_COMMUNITY.equal(fkCommunity).and(LOCATION.ARCHIVED.equal(0))); return reviewsSelect; }
protected void addJoins(SelectQuery<?> query, Map<Table<?>, Condition> joins) { if ( joins == null ) { return; } for ( Map.Entry<Table<?>, Condition> entry : joins.entrySet() ) { query.addJoin(entry.getKey(), JoinType.LEFT_OUTER_JOIN, entry.getValue()); } }
@Override @SuppressWarnings("unchecked") public List<Overview> getOverview(OcppTagQueryForm form) { SelectQuery selectQuery = ctx.selectQuery(); selectQuery.addFrom(OCPP_TAG); OcppTag parentTable = OCPP_TAG.as("parent"); selectQuery.addSelect( OCPP_TAG.OCPP_TAG_PK, parentTable.OCPP_TAG_PK, OCPP_TAG.ID_TAG, OCPP_TAG.PARENT_ID_TAG, OCPP_TAG.EXPIRY_DATE, OCPP_TAG.IN_TRANSACTION, OCPP_TAG.BLOCKED ); selectQuery.addJoin(parentTable, JoinType.LEFT_OUTER_JOIN, parentTable.ID_TAG.eq(OCPP_TAG.PARENT_ID_TAG)); if (form.isIdTagSet()) { selectQuery.addConditions(OCPP_TAG.ID_TAG.eq(form.getIdTag())); } if (form.isParentIdTagSet()) { selectQuery.addConditions(OCPP_TAG.PARENT_ID_TAG.eq(form.getParentIdTag())); } switch (form.getExpired()) { case ALL: break; case TRUE: selectQuery.addConditions(OCPP_TAG.EXPIRY_DATE.lessOrEqual(CustomDSL.utcTimestamp())); break; case FALSE: selectQuery.addConditions( OCPP_TAG.EXPIRY_DATE.isNull().or(OCPP_TAG.EXPIRY_DATE.greaterThan(CustomDSL.utcTimestamp())) ); break; default: throw new SteveException("Unknown enum type"); } processBooleanType(selectQuery, OCPP_TAG.IN_TRANSACTION, form.getInTransaction()); processBooleanType(selectQuery, OCPP_TAG.BLOCKED, form.getBlocked()); return selectQuery.fetch().map(new UserMapper()); }
private SelectOnConditionStep<Record> from(SelectSelectStep<Record> select, int userId) { return select.from(LOCATION).join(PICTURES, JoinType.JOIN).on(LOCATION.ID.equal(PICTURES.FK_LOCATION)) .join(USERS, JoinType.JOIN).on(PICTURES.FK_USER.equal(USERS.ID)).leftOuterJoin(USERS_PICTURES_VOTES) .on(USERS_PICTURES_VOTES.FK_USER.equal(userId).and(USERS_PICTURES_VOTES.FK_PICTURE.equal(PICTURES.ID))); }
private SelectQuery<Record> getQuestionSelect(final QuestionsOptions options) { final SelectQuery<Record> query = jooq.selectQuery(); final Field<?> answersCountField = DSL.selectCount() .from(QUESTIONS_ANSWERS) .where(QUESTIONS_ANSWERS.QUESTION_ID.eq(QUESTIONS.QUESTION_ID)) .asField("answersCount"); query.addSelect(USERS.LOGIN, PROFILES.FIRST_NAME, PROFILES.LAST_NAME, PROFILES.NICKNAME, CONTESTS.CODE, TASKS.CODE, QUESTIONS.QUESTION_ID, QUESTIONS.STATUS, QUESTIONS.QUESTION, QUESTIONS.CREATED, QUESTIONS.PRIVATE, answersCountField); query.addFrom(QUESTIONS); query.addJoin(TASKS, JoinType.LEFT_OUTER_JOIN, TASKS.TASK_ID.eq(QUESTIONS.TASK_ID)); query.addJoin(CONTESTS, JoinType.LEFT_OUTER_JOIN, CONTESTS.CONTEST_ID.eq(QUESTIONS.CONTEST_ID)); query.addJoin(USERS, USERS.USER_ID.eq(QUESTIONS.USER_ID)); query.addJoin(PROFILES, USERS.USER_ID.eq(PROFILES.USER_ID)); if (options != null) { final Field<Integer> questionIdField = DSL.field("split_part({0}, '|', 1)::integer", Integer.class, EVENTS.ARGUMENTS); final Field<Object> hasNotificationField = DSL.selectCount() .from(NOTIFICATIONS) .join(EVENTS) .using(NOTIFICATIONS.EVENT_ID) .where(NOTIFICATIONS.USER_ID.eq(JooqDaoUtils.getUserId(options.getCurrentUser()))) .and(NOTIFICATIONS.STATUS.eq(NotifyStatus.unread)) .and(questionIdField.eq(QUESTIONS.QUESTION_ID)) .asField("hasNotification"); query.addSelect(hasNotificationField); query.addOrderBy(hasNotificationField.desc()); if (!Strings.isNullOrEmpty(options.getQuery())) { final String lowerCaseQuery = options.getQuery().toLowerCase(); query.addConditions(QUESTIONS.QUESTION.lower().contains(lowerCaseQuery) .or(USERS.LOGIN.lower().contains(lowerCaseQuery)) .or(PROFILES.NICKNAME.lower().contains(lowerCaseQuery)) .or(TASKS.CODE.lower().contains(lowerCaseQuery)) .or(CONTESTS.CODE.lower().contains(lowerCaseQuery))); } } return query; }