QueryDSL 5.0.0 \uc73c\ub85c \ud14c\uc2a4\ud2b8 \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/p>\n
CREATE DATABASE db_test;\n\nUSE db_test;\n\nSHOW TABLES;\n\nDROP TABLE tbl_user;\nDROP TABLE tbl_team;\n\nCREATE TABLE tbl_user (\n id int NOT NULL AUTO_INCREMENT,\n team varchar(32) NULL,\n username varchar(32),\n email varchar(32),\n PRIMARY KEY (id)\n)\nCOLLATE='utf8_general_ci'\nENGINE=INNODB;\n\nCREATE TABLE tbl_team (\n id varchar(32) NULL,\n teamname varchar(32),\n PRIMARY KEY (id)\n)\nCOLLATE='utf8_general_ci'\nENGINE=INNODB;\n\nINSERT INTO tbl_team(id, teamname)\nVALUES('001', '\uac1c\ubc1c\ud300');\n\nINSERT INTO tbl_team(id, teamname)\nVALUES('002', '\uc6b4\uc601\ud300');\n\nINSERT INTO tbl_user(team, username, email)\nVALUES('001', 'Lee', 'skyer9@gmail.com');\n\nINSERT INTO tbl_user(team, username, email)\nVALUES('005', 'Lee2', 'skyer9@gmail.com');<\/code><\/pre>\nQClass \uba85<\/h2>\n
Entity \uba85\uacfc \ud544\ub4dc\uba85\uc774 \uc77c\uce58\ud558\ub294 \uacbd\uc6b0,
\nQClass \uba85\uc774 team1 \uacfc \uac19\uc774 \uc22b\uc790\uac00 \ubd99\ub294\ub2e4.<\/p>\n
@Getter\n@Builder\n@Entity\n@NoArgsConstructor\n@AllArgsConstructor\n@Table(name = "tbl_team", catalog = "db_test")\npublic class Team {\n\n @Id\n private String team;\n\n private String teamname;\n}<\/code><\/pre>\n\uc704\uc5d0\uc11c \ud074\ub798\uc2a4\uba85\uacfc \ud544\ub4dc\uba85\uc774 \ub3d9\uc77c\ud558\ubbc0\ub85c,
\nQClass \ub294 team1 \uc774 \ub41c\ub2e4.<\/p>\n
import static com.example.demo.domain.QTeam.team1;<\/code><\/pre>\njoin, leftJoin, eq<\/h2>\n public List<User> join() {\n\n return jpaQueryFactory\n .selectFrom(user)\n .join(team)\n .on(user.team.eq(team.id))\n .where(team.teamname.eq("\uac1c\ubc1c\ud300"))\n .fetch();\n }\n\n public List<User> leftJoin() {\n\n return jpaQueryFactory\n .selectFrom(user)\n .leftJoin(team)\n .on(user.team.eq(team.id))\n .where(team.teamname.eq("\uac1c\ubc1c\ud300"))\n .fetch();\n }\n\n List<SearchUserResponseDto> users = jpaQueryFactory\n .select(\n Projections.bean(\n SearchUserResponseDto.class,\n user.companyId,\n user.userId,\n company.companyName\n )\n )\n .from(user)\n .leftJoin(company)\n .on(user.companyId.eq(company.companyId))<\/code><\/pre>\njoin subquery<\/h2>\n
\ucc38\uc870<\/a><\/p>\nJPQL \uc774 join subquery \ub97c \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uae30\uc5d0,
\nQueryDsl \ub3c4 \uc11c\ube0c\ucffc\ub9ac\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4.<\/p>\n
\uc544\ub798\uc758 \ubc29\ubc95\uc73c\ub85c hibernate \ub808\ubca8\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \uc11c\ube0c \ucffc\ub9ac\ub97c \uc774\uc6a9\ud560 \uc218 \uc788\ub2e4.
\n(native query \ub97c \uc0ac\uc6a9\ud558\ubbc0\ub85c DB \uad50\uccb4\uc2dc \uace0\ub824\ud574\uc57c \ud55c\ub2e4.)<\/p>\n
@Entity\n@Getter\n@Setter\n@Subselect("select * from tbl_user where username in ('Lee', 'Lee2')") \/\/ native query\npublic class SubUser {\n\n @Id\n private Integer id;\n\n private String team;\n\n private String username;\n\n private String email;\n}<\/code><\/pre>\nimport static com.example.demo.domain.QTeam.team;\nimport static com.example.demo.domain.QUser.user;\nimport static com.example.demo.domain.QSubUser.subUser;\n\n\/\/ ......\n\n public List<User> joinSubQuery() {\n\n return jpaQueryFactory\n .selectFrom(user)\n .join(subUser)\n .on(user.id.eq(subUser.id))\n .where(subUser.username.eq("Lee"))\n .fetch();\n }<\/code><\/pre>\nfrom subquery<\/h2>\n
JPQL \uc774 from subquery \ub97c \uc9c0\uc6d0\ud558\uc9c0 \uc54a\uae30\uc5d0,
\nQueryDsl \ub3c4 \uc11c\ube0c\ucffc\ub9ac\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4.<\/p>\n
native query \ub85c \uc791\uc131\ud558\uac70\ub098,
\n\uc560\ud50c\ub9ac\ucf00\uc774\uc158 \ub808\ubca8\uc5d0\uc11c \ucc98\ub9ac\ud574\uc57c \ud55c\ub2e4.<\/p>\n
>, >=, <, <=, between<\/h2>\n
gt == greater then, goe == greater or equal
\nlt == lower then, loe == lower or equal<\/p>\n
return jpaQueryFactory\n .selectFrom(user)\n\/\/ .where(user.id.gt(1))\n\/\/ .where(user.id.goe(1))\n\/\/ .where(user.id.lt(1))\n\/\/ .where(user.id.loe(1))\n .where(user.id.between(1, 2))\n .fetch();<\/code><\/pre>\nor<\/h2>\n public List<User> or() {\n\n return jpaQueryFactory\n .selectFrom(user)\n .join(team)\n .on(user.id.eq(user.id))\n .where(\n team.teamname.eq("\uac1c\ubc1c\ud300")\n .or\n (team.teamname.eq("\uc6b4\uc601\ud300"))\n )\n .where(user.username.eq("Lee"))\n .fetch();\n }<\/code><\/pre>\n\nor<\/code> \uc640 QueryDelegate<\/code> \ub97c \uc11e\uc5b4\uc4f0\uba74 \uc624\ub958\uac00 \ubc1c\uc0dd\ud55c\ub2e4.
\n\uccab\ubc88\uc9f8 \uc870\uac74\uc774 null \uc774\ub418\uba74 NullPointerException \uc774 \ubc1c\uc0dd\ud55c\ub2e4.<\/p>\n<\/blockquote>\nnot<\/h2>\n
ne == not equal<\/p>\n
return jpaQueryFactory\n .selectFrom(user)\n .join(team)\n .on(user.team.eq(team.id))\n .where(team.teamname.ne("\uc6b4\uc601\ud300"))\n .where(user.username.eq("Lee"))\n .fetch();<\/code><\/pre>\nis null<\/h2>\n return jpaQueryFactory\n .selectFrom(user)\n .leftJoin(team)\n .on(user.team.eq(team.id))\n .where(team.teamname.isNull())\n .where(user.username.eq("Lee2"))\n .fetch();<\/code><\/pre>\nis not null<\/h2>\n return jpaQueryFactory\n .selectFrom(user)\n .leftJoin(team)\n .on(user.team.eq(team.id))\n .where(team.teamname.isNotNull())\n .where(user.username.eq("Lee"))\n .fetch();<\/code><\/pre>\nifnull(isnull)<\/h2>\n
\ub300\uccb4 \ud544\ub4dc\ub3c4 \uac00\ub2a5\ud558\uace0, \ub300\uccb4 \ubcc0\uc218\ub3c4 \uac00\ub2a5\ud558\ub2e4.
\n\ud544\ub4dc \ub610\ub294 \ubcc0\uc218\ub294 \uc5ec\ub7ec\uac1c\ub3c4 \uac00\ub2a5\ud558\ub2e4.
\n\uc5ec\ub7ec\uac1c\uc778 \uacbd\uc6b0 null \uc774 \uc544\ub2cc \uccab\ubc88\uc9f8 \uc544\uc774\ud15c\uc774 \ubc18\ud658\ub41c\ub2e4.
\n\ubaa8\ub450 null \uc774\uba74 null \uc774 \ubc18\ud658\ub41c\ub2e4.<\/p>\n
public List<User> isnull() {\n return jpaQueryFactory\n .selectFrom(user)\n .where(\n \/\/user.username.coalesce(user.email).eq("Lee")\n user.username.coalesce("Lee").eq("Lee")\n )\n .fetch();\n }<\/code><\/pre>\nin<\/h2>\n List<String> usernames = Arrays.asList("Lee", "Lee2");\n return jpaQueryFactory\n .selectFrom(user)\n .where(user.username.in(usernames))\n .fetch();<\/code><\/pre>\nin subquery<\/h2>\n
\uc11c\ube0c\ucffc\ub9ac\ub294 \uc5b8\uc81c\ub098 \uc131\ub2a5\uc774 \ub5a8\uc5b4\uc9c0\ub2c8 \ub2e4\ub978 \ubc29\ubc95\uc744 \ucc3e\uc544\ubcf4\uc790.<\/p>\n
return jpaQueryFactory\n .selectFrom(user)\n .where(user.username.in(\n JPAExpressions\n .select(user.username)\n .from(user)\n .where(user.username.in("Lee", "Lee2"))))\n .fetch();<\/code><\/pre>\nlike<\/h2>\n
like() \ub294 %<\/code> \ub97c \ub123\uc5b4\uc8fc\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n return jpaQueryFactory\n .selectFrom(user)\n \/\/ .where(user.username.startsWith("Lee"))\n .where(user.username.contains("Lee"))\n .fetch();\n\n return jpaQueryFactory\n .selectFrom(user)\n .where(user.username.like("Lee%"))\n .fetch();<\/code><\/pre>\ncase when<\/h2>\n public List<String> caseWhen() {\n\n return jpaQueryFactory\n .select(\n user.username\n .when("Lee").then("A")\n .when("Lee2").then("B")\n .otherwise("C")\n .as("username")\n )\n .from(user)\n .where(user.id.between(1, 2))\n .fetch();\n }<\/code><\/pre>\nsum\/min\/max\/count + case when<\/h2>\nCREATE TABLE `tbl_commute_log` (\n `yyyymmdd` VARCHAR(10) NOT NULL COLLATE 'utf8mb4_general_ci',\n `user_id` VARCHAR(32) NOT NULL COLLATE 'utf8mb4_general_ci',\n `inout_type` TINYINT(4) NOT NULL DEFAULT '0',\n `act_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'\n)\nCOLLATE='utf8mb4_general_ci'\nENGINE=InnoDB\n\nINSERT INTO tbl_commute_log(yyyymmdd, user_id, inout_type, act_time)\nVALUES\n('2022-11-01', 'skyer9', 1, '2022-11-01 09:01:00'),\n('2022-11-01', 'skyer9', 3, '2022-11-01 10:01:00'),\n('2022-11-01', 'skyer9', 4, '2022-11-01 11:01:00'),\n('2022-11-01', 'skyer9', 5, '2022-11-01 12:01:00'),\n('2022-11-01', 'skyer9', 2, '2022-11-01 18:01:00')<\/code><\/pre>\ncase \ubb38\uc744 \uc0ac\uc6a9\ud558\ub294 \ub300\uc2e0 self join \uc744 \uc774\uc6a9\ud574 \ud574\uacb0\ud560 \uc218 \uc788\ub2e4.<\/p>\n
@Override\n public List<CommuteLogStatDto> getStat() {\n QCommuteLog T1 = new QCommuteLog("T1");\n QCommuteLog T2 = new QCommuteLog("T2");\n QCommuteLog T3 = new QCommuteLog("T3");\n return jpaQueryFactory\n .select(\n Projections.bean(\n CommuteLogStatDto.class\n , T1.id.yyyymmdd\n , T1.id.userId\n , T1.id.yyyymmdd.count().as("inoutCount")\n , T2.actTime.min().as("minInTime")\n , T3.actTime.max().as("maxInTime")\n )\n )\n .from(T1)\n .leftJoin(T2)\n .on(\n T1.id.yyyymmdd.eq(T2.id.yyyymmdd)\n .and(T1.id.userId.eq(T2.id.userId)\n .and(T2.id.inoutType.intValue().eq(1)))\n )\n .leftJoin(T3)\n .on(\n T1.id.yyyymmdd.eq(T3.id.yyyymmdd)\n .and(T1.id.userId.eq(T3.id.userId)\n .and(T3.id.inoutType.intValue().eq(2)))\n )\n .groupBy(T1.id.yyyymmdd, T1.id.userId)\n .orderBy(T1.id.yyyymmdd.asc(), T1.id.userId.asc())\n .fetch();\n }<\/code><\/pre>\ndynamic query<\/h2>\n\uac04\ub2e8\ud55c \uc870\uac74\uc778 \uacbd\uc6b0<\/h3>\n public List<User> dynamicQuery(String username, String teamname) {\n\n return jpaQueryFactory\n .selectFrom(user)\n .join(team)\n .on(user.team.eq(team.id))\n .where(\n (username != null) ? user.username.eq(username) : null,\n (teamname != null) ? team.teamname.eq(teamname) : null\n )\n .fetch();\n }<\/code><\/pre>\nQueryDelegate<\/h3>\n
QClass \ub97c \uc7ac\ucef4\ud30c\uc77c\ud574\uc57c \uc778\uc2dd\ud55c\ub2e4.
\nwhere \uc808\uc774 \uc544\uc8fc \uae54\ub054\ud574\uc9c4\ub2e4.<\/p>\n
\uccab\ubc88\uc9f8 \ud30c\ub77c\ubbf8\ud130\ub294 @QueryDelegate(User.class)<\/code> \uac78\ub9b0
\nEntity \uc758 QClass \uc5ec\uc57c \ud55c\ub2e4.<\/p>\n@QueryEntity\npublic class UserExpression {\n\n @QueryDelegate(User.class)\n public static BooleanExpression eqUsernameLee(QUser user) {\n\n return user.username.eq("Lee");\n }\n\n @QueryDelegate(User.class)\n public static BooleanExpression eqUsername(QUser user, String username) {\n\n if (username == null) {\n return null;\n }\n\n return user.username.eq(username);\n }\n}<\/code><\/pre>\n @Override\n public List<User> querydelegate() {\n return jpaQueryFactory\n .selectFrom(user)\n .where(\n user.eqUsernameLee(),\n user.eqUsername("Lee")\n )\n .fetch();\n }<\/code><\/pre>\n\ub450\uac1c \uc774\uc0c1\uc758 Entity \uc5d0 \uc870\uac74\uc744 \uac78\ub824\uba74,
\n\uc544\ubb34 Entity \uc5d0 QueryDelegate<\/code> \ubd99\uc774\uace0,
\n\ub098\uba38\uc9c0 Entity \ub97c \ud30c\ub77c\ubbf8\ud130\ub85c \uc804\ub2ec\ud558\uba74 \ub41c\ub2e4.<\/p>\n@QueryEntity\npublic class UserExpression {\n @QueryDelegate(UserEntity.class)\n public static BooleanExpression departmentEquals(QUserEntity userEntity, QDepartmentEntity departmentEntity, Integer departmentId, String incSubDepartment) {\n\n if (departmentId == null) {\n return null;\n }\n\n if ("N".equals(incSubDepartment)) {\n return userEntity.departmentId.eq(departmentId);\n } else {\n return departmentEntity.cId1.coalesce(-1).eq(departmentId);\n }\n }\n}<\/code><\/pre>\norder by<\/h2>\n public List<User> orderBy() {\n\n return jpaQueryFactory\n .selectFrom(user)\n .where(user.username.contains("Lee"))\n .orderBy(user.username.asc(), user.id.asc())\n \/\/ .orderBy(user.username.desc())\n .fetch();\n }<\/code><\/pre>\ngroup by<\/h2>\n
Projections \uc758 \uac1d\uccb4 \uc0dd\uc131\ubc29\ubc95 3\uac00\uc9c0<\/a> \uc5d0 \ub300\ud574 \uba3c\uc800 \uc77d\uc73c\uc2dc\uba74 \uc88b\uc2b5\ub2c8\ub2e4.<\/p>\n\uc544\ub798 \uc0d8\ud50c\ucf54\ub4dc\ub294 Setter \ub97c \uc774\uc6a9\ud55c \ubc29\uc2dd\uc785\ub2c8\ub2e4.<\/p>\n
@Getter\n@Setter\n@NoArgsConstructor\npublic class UserGroupByDto {\n\n private String username;\n private Long count;\n\n private UserGroupByDto(String username, Long count) {\n throw new RuntimeException("Projections \uc744 \uc774\uc6a9\ud574 \uac1d\uccb4 \uc0dd\uc131\ud560 \uac83");\n }\n}<\/code><\/pre>\n public List<UserGroupByDto> groupBy() {\n\n return jpaQueryFactory\n .select(\n Projections.bean(\n UserGroupByDto.class\n , user.username\n , user.count().as("count")\n )\n )\n .from(user)\n .groupBy(user.username)\n .orderBy(user.username.asc())\n .fetch();\n }<\/code><\/pre>\n @Test\n void groupBy() {\n \/\/ given\n\n \/\/ when\n List<UserGroupByDto> list = repository.groupBy();\n\n \/\/ then\n assertTrue(list.size() > 0);\n\n UserGroupByDto item = list.get(0);\n Assertions.assertEquals(item.getUsername(), "Lee");\n Assertions.assertEquals(item.getCount(), 3);\n }<\/code><\/pre>\nunion, union all<\/h2>\n
JPA \uc5d0\uc11c\ub294 union \uc744 \uc9c0\uc6d0\ud558\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n
"com.querydsl:querydsl-sql:5.0.0" \uc744 \uc774\uc6a9\ud574 Native SQL \ub85c \uc791\uc131\ud558\uac70\ub098,
\nhibernate \ub808\ubca8\uc5d0\uc11c native query \ub97c \uc791\uc131\ud574\uc57c \ud55c\ub2e4.
\n(\uc5b4\ub290 \uacbd\uc6b0\ub4e0 native query \ub97c \uc0ac\uc6a9\ud558\ubbc0\ub85c DB \uad50\uccb4\uc2dc \uace0\ub824\ud574\uc57c \ud55c\ub2e4.)<\/p>\n
paging<\/h2>\n
PageableExecutionUtils \ub97c \uc0ac\uc6a9\ud558\uba74,
\ncount \ucffc\ub9ac\uac00 \ud544\uc694\uc5c6\uc744 \ub54c \ucffc\ub9ac\ub97c \uc0dd\ub7b5\ud55c\ub2e4.<\/p>\n
public Page<User> paging(Pageable pageable) {\n\n \/\/ 'fetchResults()' is deprecated\n\n List<User> list = jpaQueryFactory\n .selectFrom(user)\n .where(user.username.like("Lee%"))\n .offset(pageable.getOffset())\n .limit(pageable.getPageSize())\n .fetch();\n\n JPAQuery<User> count = jpaQueryFactory\n .selectFrom(user)\n .where(user.username.like("Lee%"));\n\n return PageableExecutionUtils.getPage(list, pageable, () -> count.fetch().size());\n }<\/code><\/pre>\n @Test\n void paging() {\n \/\/ given\n Pageable pageable = PageRequest.of(0, 2);\n\n \/\/ when\n Page<User> page = repository.paging(pageable);\n\n \/\/ then\n List<User> list = page.toList();\n\n assertEquals(page.getTotalElements(), 3);\n assertEquals(list.size(), 2);\n }<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"QueryDSL \uc0ac\uc6a9\ud558\uae30 QueryDSL 5.0.0 \uc73c\ub85c \ud14c\uc2a4\ud2b8 \ub418\uc5c8\uc2b5\ub2c8\ub2e4. \ud14c\uc774\ube14 \uc0dd\uc131 CREATE DATABASE db_test; USE db_test; SHOW TABLES; DROP TABLE tbl_user; DROP TABLE tbl_team; CREATE TABLE tbl_user ( id int NOT NULL AUTO_INCREMENT, team varchar(32) NULL, username varchar(32), email varchar(32), PRIMARY KEY (id) ) COLLATE='utf8_general_ci' ENGINE=INNODB; CREATE TABLE tbl_team ( id varchar(32) NULL, teamname varchar(32), PRIMARY KEY\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[],"class_list":["post-5812","post","type-post","status-publish","format-standard","hentry","category-querydsl"],"_links":{"self":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/5812","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5812"}],"version-history":[{"count":52,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/5812\/revisions"}],"predecessor-version":[{"id":7586,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/5812\/revisions\/7586"}],"wp:attachment":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}