Querydsl

Querydsl Document

list object, join

해당 관리자의 로그를 10개 조회한다

java

    public List<AdminLog> list(Long adminId) {
        return this.queryFactory
            .select(this.qAdminLog)
            .from(this.qAdminLog)
            .join(this.qAdmin)
            .where(this.qAdmin.adminId.eq(adminId))
            .where(this.qAdminLog.userId.eq(this.qAdmin.userId))
            .limit(10)
            .fetch();
    }

sql

    select
        admin_log.id,
        admin_log.ip,
        admin_log.reg_datetime,
        admin_log.role,
        admin_log.uri,
        admin_log.user_id
    from
        admin_log admin_log
    join
        admin admin
    where
        admin.admin_id = 47
        and admin_log.user_id = admin.user_id limit 10

insert

관리자를 추가한다

java

    @Transactional
    public Admin add(Admin admin) {
        long adminId = this.queryFactory
            .insert(this.qAdmin)
            .populate(admin)
            .executeWithKey(this.qAdmin.adminId);

        return admin.setAdminId(adminId);
    }

sql

    insert
    into
        admin
        (user_id, daum_id, name, role, status, reg_datetime, mod_datetime)
    values
        ('test', 'test', 'test', 'test', 'SERVICE', '2016-01-13 15:12:55', '2016-01-13 15:12:55')

update

관리자의 이름을 변경한다

java

    @Transactional
    public long update(Admin admin) {
        return
            this.queryFactory
                .update(this.qAdmin)
                .set(this.qAdmin.name, admin.getName())
                .where(this.qAdmin.adminId.eq(admin.getAdminId()))
                .execute();
    }

sql

    update
        admin
    set
        name = '32bef302a4ce465d92b1a7330a20b700'
    where
        admin.admin_id = 47

delete

관리자를 삭제한다

java

    @Transactional
    public long remove(Long adminId) {
        return
            this.queryFactory
                .delete(this.qAdmin)
                .where(this.qAdmin.adminId.eq(adminId))
                .execute();
    }

sql

    delete
    from
        admin
    where
        admin.admin_id = 178

list object, like, in

이름에 김이 들어가고 상태가 서비스이거나 대기인 작가를 조회한다

java

    public List<Author> listByName() {
    return
    this.queryFactory
    .select(this.qAuthor)
    .from(this.qAuthor)
    .where(this.qAuthor.name.contains("김"))
    .where(this.qAuthor.status.in(Arrays.asList(Application.Status.SERVICE.name(), Application.Status.WAITING.name())))
    .fetch();
    }

sql

    select
        author.author_id,
        author.name,
        author.cp_name,
        author.image_url
        .....
    from
        author author
    where
        author.name like '%김%'
        and author.status in (
            'SERVICE', 'WAITING'
        )

list object, exists

작가의 상태가 서비스이고 서비스중인 프로젝트를 가지고 있는 작가<Author>를 조회한다

java

    public List<Author> listForExistsServiceProject() {
    QProjectAuthorMap qProjectAuthorMap = QProjectAuthorMap.qProjectAuthorMap;
    QProject qProject = QProject.qProject;

    return
    this.queryFactory
    .select(qAuthor)
    .from(qAuthor)
    .where(qAuthor.status.eq(Application.Status.SERVICE.name()))
    .where(
    SQLExpressions.select(Expressions.constant(1))
    .from(qProjectAuthorMap, qProject)
    .where(qProjectAuthorMap.projectId.eq(qProject.projectId))
    .where(qProjectAuthorMap.authorId.eq(qAuthor.authorId))
    .where(qProject.supportStatus.eq(Application.Status.SERVICE.name()))
    .exists()
    )
    .fetch();
    }

sql

    select
        author.author_id,
        author.name,
        author.cp_name,
        author.image_url,
       .....
    from
        author author
    where
        author.status = 'SERVICE'
        and exists (
            select
                1
            from
                project_author_map project_author_map,
                project project
            where
                project_author_map.project_id = project.project_id
                and project_author_map.author_id = author.author_id
                and project.support_status = 'SERVICE'
        )

tuple, multiple left join, order by

회차의 발행일시 역순으로 해서 회차와 조회수, 좋아요수를 조회한다

java

    public List<Tuple> tupleForContentCount() {
    QContentCount qViewContentCount = new QContentCount("viewContentCount");
    QContentCount qLikeContentCount = new QContentCount("likeContentCount");

    return
    this.queryFactory
    .select(this.qProjectEpisode,
    qViewContentCount.contentCount,
    qLikeContentCount.contentCount
    )
    .from(this.qProjectEpisode)
    .leftJoin(qViewContentCount)
    .on(qViewContentCount.objectType.eq(Application.ObjectType.EPISODE.name()))
    .on(qViewContentCount.objectId.eq(this.qProjectEpisode.episodeId))
    .on(qViewContentCount.countType.eq(Application.CountType.VIEW.name()))
    .leftJoin(qLikeContentCount)
    .on(qLikeContentCount.objectType.eq(Application.ObjectType.EPISODE.name()))
    .on(qLikeContentCount.objectId.eq(this.qProjectEpisode.episodeId))
    .on(qLikeContentCount.countType.eq(Application.CountType.LIKE.name()))
    .where(this.qProjectEpisode.status.eq(Application.Status.SERVICE.name()))
    .orderBy(this.qProjectEpisode.publishDatetime.asc())
    .limit(10)
    .fetch();
    }

sql

    select
        project_episode.episode_id,
        project_episode.project_id,
        project_episode.name,
        project_episode.title,
        project_episode.seq_name,
        project_episode.image_url,
        ....
    from
        project_episode project_episode
    left join
        content_count viewContentCount
            on viewContentCount.object_type = 'EPISODE'
            and viewContentCount.object_id = project_episode.episode_id
            and viewContentCount.count_type = 'VIEW'
    left join
        content_count likeContentCount
            on likeContentCount.object_type = 'EPISODE'
            and likeContentCount.object_id = project_episode.episode_id
            and likeContentCount.count_type = 'LIKE'
    where
        project_episode.status = 'SERVICE'
    order by
        project_episode.publish_datetime asc limit 10

tuple, left join, order by

후원정보와 리워드 정보를 10개 조회한다

java

    public List<Tuple> list() {
    return
    this.queryFactory
    .select(this.qProjectOrder, this.qProjectSupportAmount)
    .from(this.qProjectOrder)
    .leftJoin(qProjectSupportAmount)
    .on(this.qProjectSupportAmount.amountId.eq(this.qProjectOrder.amountId))
    .where(this.qProjectOrder.payStatus.eq(Application.PayStatus.OK.name()))
    .orderBy(this.qProjectOrder.orderId.desc())
    .limit(10)
    .fetch();
    }

sql

    select
        project_order.address,
        project_order.address_detail,
        project_order.amount_id,
        project_order.cancel,
        ....
    from
        project_order project_order
    left join
        project_support_amount project_support_amount
            on project_support_amount.amount_id = project_order.amount_id
    where
        project_order.pay_status = 'OK'
    order by
        project_order.order_id desc limit 10

date, expression

현재일시를 조회한다

java

    public Date getCurrentDatetime() {
        return
            this.queryFactory
                .select(Expressions.currentTimestamp())
                .fetchOne();
    }

sql

    select
        current_timestamp
    from
        dual

tuple, function, group by

시간별로 작성된 서비스중인 프로젝트 개수를 조회한다

java

    public List<Tuple> tupleGroupByHour() {
    StringTemplate groupByHour =
    Expressions.stringTemplate("date_format({0}, {1})", this.qProject.regDatetime, "%Y-%m-%d %H");

    return
    this.queryFactory
    .select(groupByHour, qProject.count())
    .from(this.qProject)
    .where(this.qProject.supportStatus.eq(SupportStatus.SERVICE.name()))
    .groupBy(groupByHour)
    .fetch();
    }

sql

    select
        date_format(project.reg_datetime,
        '%Y-%m-%d %H'),
        count(project.project_id)
    from
        project project
    where
        project.support_status = 'SERVICE'
    group by
        date_format(project.reg_datetime,
        '%Y-%m-%d %H')

tuple, sub query, alias

프로젝트 정보와 프로젝트에 매핑된 회차수를 추출후 회차수 역순으로 조회한다

java

    public List<Tuple> tupleForList() {
    QProject qProjectAlias = new QProject("x");

    return
    this.queryFactory
    .select(qProjectAlias, Expressions.datePath(Long.class, "count"))
    .from(
    SQLExpressions
    .select(this.qProject,
    SQLExpressions
    .select(this.qProjectEpisode.count())
    .from(this.qProjectEpisode)
    .where(this.qProjectEpisode.projectId.eq(this.qProject.projectId))
    .where(this.qProjectEpisode.status.eq(Status.SERVICE.name()))
    .as("count")
    )
    .from(this.qProject)
    .where(this.qProject.supportStatus.eq(SupportStatus.SERVICE.name()))
    .limit(10)
    .as("x")
    )
    .orderBy(Expressions.datePath(Long.class, "count").desc())
    .fetch();
    }

sql

    select
        x.project_id,
        x.name,
        x.title,
        x.seq_name,
        x.project_type,
        x.image_url,
       ....
    from
        (select
            project.project_id,
            project.name,
            project.title,
            project.seq_name,
            project.project_type,
            project.image_url,
            .....
            (select
                count(project_episode.episode_id)
            from
                project_episode project_episode
            where
                project_episode.project_id = project.project_id
                and project_episode.status = 'SERVICE') as count
        from
            project project
        where
            project.support_status = 'SERVICE' limit 10
        ) as x
    order by
        count desc

tuple, case, group by

후원금액 조건별로 그룹핑해서 개수를 조회한다

java

    public List<Tuple> tupleGroupByAmount() {
    Expression amount = new CaseBuilder()
    .when(qProjectSupportAmount.amount.gt(10000))
    .then("만원이상")
    .otherwise("만원미만");

    return
    this.queryFactory
    .select(amount, qProjectSupportAmount.count())
    .from(this.qProjectSupportAmount)
    .where(qProjectSupportAmount.status.eq(Application.Status.SERVICE.name()))
    .groupBy(amount)
    .fetch();
    }

sql

    select
        case
            when project_support_amount.amount > 10000 then '만원이상'
            else '만원미만'
        end,
        count(project_support_amount.amount_id)
    from
        project_support_amount project_support_amount
    where
        project_support_amount.status = 'SERVICE'
    group by
        case
            when project_support_amount.amount > 10000 then '만원이상'
            else '만원미만'
        end

count

세션 개수를 조회한다

java

    public long getCount() {
        return
            this.queryFactory
                .select(this.qSession.count())
                .from(this.qSession)
                .fetchOne();
    }

sql

    select
        count(session.session_id)
    from
        session session

bulk insert

조회된 세션정보 10개를 새롭게 추가한다

java

    @Transactional
    public long addBulk() {
        return this.queryFactory
            .insert(this.qSession)
            .columns(this.qSession.token, this.qSession.ip, this.qSession.userId)
            .select(
                SQLExpressions
                    .select(this.qSession.token, this.qSession.ip, this.qSession.userId)
                    .from(this.qSession)
                    .limit(10)
            )
            .execute();
    }

sql

    insert
    into
        session
        (token, ip, user_id) select
            session.token,
            session.ip,
            session.user_id
        from
            session session limit 10