UTF-8 as default server’s character set \ub97c \uccb4\ud06c\ud569\ub2c8\ub2e4.<\/p>\n
\uc124\uce58\ub41c \ud504\ub85c\uadf8\ub7a8\uc911 MySQL Client \ub97c \uc2e4\ud589\ud558\uace0 \ub85c\uadf8\uc778\ud569\ub2c8\ub2e4.<\/p>\n
\uacc4\uc815\uc744 \uc0dd\uc131\ud558\uace0, \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c \uc0dd\uc131 \ud6c4, \uad8c\ud55c\uc744 \ubd80\uc5ec\ud569\ub2c8\ub2e4.<\/p>\n
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';\nCREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';\n\nCREATE DATABASE myboard;\n\nGRANT ALL PRIVILEGES ON myboard.* TO 'myuser'@'localhost';\n\nFLUSH PRIVILEGES;\n\nUSE myboard;\n\nCREATE TABLE `tbl_post` (\n `idx` bigint(20) NOT NULL AUTO_INCREMENT,\n `title` varchar(100) NOT NULL,\n `content` varchar(3000) NOT NULL,\n `writer` varchar(20) NOT NULL,\n `view_cnt` int(11) NOT NULL DEFAULT 1,\n `delete_yn` tinyint(1) NOT NULL DEFAULT 0,\n `created_date` DATETIME NOT NULL DEFAULT current_timestamp(),\n `modified_date` datetime DEFAULT NULL,\n PRIMARY KEY (`idx`)\n);<\/code><\/pre>\n\ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \uc0dd\uc131(Oracle 18c Express Edition)<\/h2>\n\/\/ sqlplus\n\/\/ \uc0ac\uc6a9\uc790\uba85 \uc785\ub825: system\n\/\/ \ube44\ubc00\ubc88\ud638 \uc785\ub825:\n\/\/ conn\/as sysdba\ncreate user c##myuser identified by mypass;\ngrant connect, resource, dba to c##myuser;\n\nCREATE TABLE tbl_post (\n idx NUMBER(20) NOT NULL,\n title VARCHAR2(100) NOT NULL,\n content VARCHAR2(3000) NOT NULL,\n writer VARCHAR2(20) NOT NULL,\n view_cnt NUMBER(4) DEFAULT 1 NOT NULL,\n delete_yn NUMBER(1) DEFAULT 0 NOT NULL,\n created_date DATE DEFAULT SYSDATE NOT NULL,\n modified_date DATE DEFAULT NULL\n);\n\nALTER TABLE tbl_post ADD CONSTRAINT idx_pk PRIMARY KEY (idx);\n\nCREATE SEQUENCE idx_seq START WITH 1;\n\nCOMMIT;\n\n\/\/ SID \ud655\uc778\nSELECT instance FROM v$thread;<\/code><\/pre>\ninsert\/update\/delete\/select<\/h2>\n\ud504\ub85c\uc81d\ud2b8 \uc0dd\uc131<\/h3>\n
\uc5ec\uae30<\/a> \ub97c \ucc38\uc870\ud558\uc5ec \ud504\ub85c\uc81d\ud2b8\ub97c \uc0dd\uc131\ud574 \uc90d\ub2c8\ub2e4.
\nlombok, devtools, spring boot web, jpa \ub97c \uc120\ud0dd\ud574 \uc90d\ub2c8\ub2e4.<\/p>\n\uc5ec\uae30<\/a> \ub97c \ucc38\uc870\ud558\uc5ec mapstruct \ub3c4 \ud504\ub85c\uc81d\ud2b8\uc5d0 \ucd94\uac00\ud569\ub2c8\ub2e4.<\/p>\nbuild.gradle<\/p>\n
dependencies {\n implementation 'org.springframework.boot:spring-boot-starter-data-jpa'\n implementation 'org.springframework.boot:spring-boot-starter-web'\n compileOnly 'org.springframework.data:spring-data-commons'\n implementation 'org.mapstruct:mapstruct:1.4.1.Final'\n compileOnly 'org.projectlombok:lombok'\n compileOnly 'org.projectlombok:lombok-mapstruct-binding:0.2.0'\n developmentOnly 'org.springframework.boot:spring-boot-devtools'\n annotationProcessor 'org.projectlombok:lombok'\n annotationProcessor 'org.mapstruct:mapstruct-processor:1.4.1.Final'\n testImplementation 'org.springframework.boot:spring-boot-starter-test'\n\n runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'\n implementation 'org.hibernate:hibernate-core:6.4.1.Final'\n implementation 'com.oracle.database.jdbc:ojdbc10:19.22.0.0'\n}<\/code><\/pre>\n\uae30\ubcf8\uc801\uc778 \ud750\ub984<\/h3>\n
\uac12\uc758 \uc785\ub825\uacfc \uacb0\uacfc\uac12\uc740 \ubc18\ud658\uc740 json \uc5d0 \uc758\ud574 \uc774\ub8e8\uc5b4\uc9d1\ub2c8\ub2e4.<\/p>\n
\uc0ac\uc6a9\uc790\uc785\ub825(json) -> DTO -> Data Object -> DB
\n\uc0ac\uc6a9\uc790\ubc18\ud658(json) <- DTO <- Data Object <- DB<\/p>\n
Domain \ub808\uc774\uc5b4 \uc0dd\uc131<\/h3>\n
\uc2a4\ud504\ub9c1 \ubd80\ud2b8 JPA \uc5d4\ud130\ud2f0\uc5d0 \ub300\ud574\uc11c\ub294 \ub2e4\ub978 \ubb38\uc11c\ub97c \ucc3e\uc544\ubcf4\uc2dc\uae30 \ubc14\ub78d\ub2c8\ub2e4.<\/p>\n
Post.java<\/p>\n
@Entity\n@Getter\n@Setter\n@Table(name = "tbl_post")\n@NoArgsConstructor\npublic class Post {\n @Id\n @GeneratedValue(strategy = GenerationType.IDENTITY) \/\/ MariaDB\n \/\/ @GeneratedValue(generator="idx_seq") \/\/ Oracle\n \/\/ @SequenceGenerator(name="idx_seq",sequenceName="idx_seq", allocationSize=1) \/\/ Oracle\n private Long idx;\n\n @Column(length = 100, nullable = false)\n private String title;\n\n @Column(length = 3000, nullable = false)\n private String content;\n\n @Column(length = 20, nullable = false)\n private String writer;\n\n @Column(nullable = false)\n private Integer viewCnt = 1;\n\n @Column(nullable = false)\n private Integer deleteYn = 0;\n\n @Column(nullable = false)\n private LocalDateTime createdDate = LocalDateTime.now();\n\n @Column\n private LocalDateTime modifiedDate;\n}<\/code><\/pre>\nPostRepository.java<\/p>\n
public interface PostRepository extends JpaRepository<Post, Long> {\n}<\/code><\/pre>\napplication.yml (MariaDB)<\/p>\n
spring:\n datasource:\n url: jdbc:mariadb:\/\/${MYSQL_HOST:localhost}:3306\/myboard\n username: myuser\n password: mypass\n driver-class-name: org.mariadb.jdbc.Driver\n jpa:\n hibernate:\n ddl-auto: none\n show-sql: false<\/code><\/pre>\napplication.yml (Oracle 18c Express Edition)<\/p>\n
spring:\n jpa:\n hibernate:\n ddl-auto: none\n show-sql: false\n open-in-view: false\n database-platform: org.hibernate.dialect.OracleDialect\n properties:\n hibernate:\n temp:\n use_jdbc_metadata_defaults: false\n datasource:\n hikari:\n connection-timeout: 30000\n idle-timeout: 600000\n max-lifetime: 1800000\n maximum-pool-size: 10\n url: jdbc:oracle:thin:@${MYSQL_HOST:localhost}:1521:xe\n username: c##myuser\n password: mypass\n driver-class-name: oracle.jdbc.OracleDriver\n type: com.zaxxer.hikari.HikariDataSource\n main:\n allow-bean-definition-overriding: true\n lazy-initialization: true<\/code><\/pre>\nService \ub808\uc774\uc5b4 \uc0dd\uc131<\/h3>\n
DTO \ub97c \uc5b4\ub5bb\uac8c \uc0dd\uc131\ud558\uace0 \uc6b4\uc601\ud558\ub294\uc9c0\ub294 \uc120\ud0dd\uc758 \ubb38\uc81c\uc785\ub2c8\ub2e4.
\n\uc5ec\uae30\uc11c\ub294 \ubb38\uc11c\uc758 \ubd84\ub7c9\uc744 \uc904\uc774\uae30 \uc704\ud574 \ud558\ub098\uc758 DTO \ub9cc \uc0dd\uc131\ud569\ub2c8\ub2e4.<\/p>\n
PostDto.java<\/p>\n
@Getter\n@Setter\n@NoArgsConstructor\npublic class PostDto {\n private Long idx;\n private String title;\n private String content;\n private String writer;\n private Integer viewCnt;\n private Integer deleteYn;\n private LocalDateTime createdDate;\n private LocalDateTime modifiedDate;\n}<\/code><\/pre>\nMapStruct \ub97c \uc774\uc6a9\ud558\uba74 \uc18c\uc2a4\ucf54\ub4dc\uac00 \ub9e4\uc6b0 \uac04\ub2e8\ud574\uc9d1\ub2c8\ub2e4.<\/p>\n
GenericMapper.java<\/p>\n
public interface GenericMapper<D, E> {\n D toDto(E e);\n List<D> toDto(List<E> e);\n void updateFromDto(D dto, @MappingTarget E entity);\n}<\/code><\/pre>\nPostMapper.java<\/p>\n
@Mapper(\n componentModel = "spring",\n unmappedTargetPolicy = ReportingPolicy.IGNORE,\n nullValuePropertyMappingStrategy = NullValuePropertyMappingStrategy.IGNORE\n)\npublic interface PostMapper extends GenericMapper<PostDto, Post> {\n}<\/code><\/pre>\nPostService.java<\/p>\n
@RequiredArgsConstructor\n@Service\npublic class PostService {\n private final PostRepository postRepository;\n private final PostMapper mapper = Mappers.getMapper(PostMapper.class);\n\n @Transactional\n public PostDto insert(PostDto dto) {\n \/\/ validate(OperationMode.INSERT, dto);\n Post e = new Post();\n mapper.updateFromDto(dto, e);\n return mapper.toDto(postRepository.save(e));\n }\n\n @Transactional\n public PostDto update(PostDto dto) {\n \/\/ validate(OperationMode.UPDATE, dto);\n Post e = get(dto.getIdx());\n mapper.updateFromDto(dto, e);\n e.setModifiedDate(LocalDateTime.now());\n return mapper.toDto(postRepository.save(e));\n }\n\n @Transactional\n public void delete(Long idx) {\n Post e = get(idx);\n e.setModifiedDate(LocalDateTime.now());\n e.setDeleteYn(1);\n }\n\n @Transactional\n public void undelete(Long idx) {\n Post e = get(idx);\n e.setModifiedDate(LocalDateTime.now());\n e.setDeleteYn(0);\n }\n\n @Transactional\n public PostDto getOne(Long idx) {\n Post e = get(idx);\n e.setViewCnt(e.getViewCnt() + 1);\n return mapper.toDto(get(idx));\n }\n\n private Post get(Long idx) {\n Optional<Post> e = postRepository.findById(idx);\n if (e.isEmpty()) {\n throw new RuntimeException("data not found");\n }\n return e.get();\n }\n}<\/code><\/pre>\nController \ub808\uc774\uc5b4 \uc0dd\uc131<\/h3>\n@RequiredArgsConstructor\n@RestController\npublic class PostController {\n private final PostService service;\n\n @PostMapping("\/api\/v1\/ins")\n public PostDto insert(@RequestBody PostDto dto) {\n return service.insert(dto);\n }\n\n @PostMapping("\/api\/v1\/upd")\n public PostDto update(@RequestBody PostDto dto) {\n return service.update(dto);\n }\n\n @GetMapping("\/api\/v1\/del")\n public void delete(@RequestParam("id") Long id) {\n service.delete(id);\n }\n\n @GetMapping("\/api\/v1\/undel")\n public void undelete(@RequestParam("id") Long id) {\n service.undelete(id);\n }\n\n @GetMapping("\/api\/v1\/")\n public PostDto get(@RequestParam("id") Long id) {\n return service.getOne(id);\n }\n}<\/code><\/pre>\n\ud14c\uc2a4\ud2b8<\/h3>\n
VSCode \uc5d0 Thunder Client \ud50c\ub7ec\uadf8\uc778\uc744 \uc124\uce58\ud558\uba74 API \ub97c \ud638\ucd9c\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
![\"\"](\"https:\/\/www.skyer9.pe.kr\/wordpress\/wp-content\/uploads\/2024\/03\/2024-03-06-01-300x88.png\")
<\/a><\/p>\nsearch<\/h2>\n
\ub2e4\ub978 API \uc640 \ub2e4\ub974\uac8c \uc0dd\uac01\ud574\uc57c \ud560 \ubd80\ubd84\uc774 \ub9ce\uc9c0\ub9cc,
\n\uc774 \ubb38\uc11c\ub294 \uae30\ucd08\uc9c0\uc2dd\uc744 \uc8fc\ubaa9\uc801\uc73c\ub85c \ud558\uae30\uc5d0,
\n\uac00\uc7a5 \uac04\ub2e8\ud55c \ud615\ud0dc\uc758 \uac80\uc0c9\uc744 \uad6c\ud604\ud569\ub2c8\ub2e4.<\/p>\n
\ud398\uc774\uc9d5\uc744 \uad6c\ud604\ud558\uace0, \uac80\uc0c9\uc870\uac74\uc740 \uc5c6\uac70\ub098 1\ub098\uc758 \uac80\uc0c9\uc870\uac74\uc744 \uad6c\ud604\ud569\ub2c8\ub2e4.<\/p>\n
QueryDSL \uc138\ud305<\/h3>\n
\uc5ec\uae30<\/a> \ub97c \ucc38\uc870\ud558\uc5ec QueryDSL \uc744 \uc138\ud305\ud569\ub2c8\ub2e4.<\/p>\n\ucee4\uc2a4\ud140 \ucffc\ub9ac \uc0dd\uc131<\/h3>\npublic interface PostRepositoryCustom {\n Page<Post> search(Integer pageNo, Integer pageSize, String searchStr);\n}<\/code><\/pre>\nimport static com.example.board_api.domain.QPost.post;\n\npublic class PostRepositoryImpl extends QuerydslRepositorySupport implements PostRepositoryCustom {\n private final JPAQueryFactory jpaQueryFactory;\n\n public PostRepositoryImpl(JPAQueryFactory jpaQueryFactory) {\n super(Post.class);\n this.jpaQueryFactory = jpaQueryFactory;\n }\n\n @Override\n public Page<Post> search(Integer pageNo, Integer pageSize, String searchStr) {\n Pageable pageable = PageRequest.of(pageNo, pageSize);\n\n List<Post> list = jpaQueryFactory\n .selectFrom(post)\n .where(\n post.title.contains(searchStr)\n .or\n (post.content.contains(searchStr))\n .or\n (post.writer.contains(searchStr))\n )\n .orderBy(post.idx.desc())\n .offset(pageable.getOffset())\n .limit(pageable.getPageSize())\n .fetch();\n\n JPAQuery<Post> count = jpaQueryFactory\n .selectFrom(post)\n .where(\n post.title.contains(searchStr)\n .or\n (post.content.contains(searchStr))\n .or\n (post.writer.contains(searchStr))\n );\n\n return PageableExecutionUtils.getPage(list, pageable, () -> count.fetch().size());\n }\n}<\/code><\/pre>\npublic interface PostRepository extends JpaRepository<Post, Long>, PostRepositoryCustom {\n}<\/code><\/pre>\n\uc11c\ube44\uc2a4 \ub808\uc774\uc5b4<\/h3>\n@Setter\n@Getter\npublic class SearchRequestDto {\n private Integer pg;\n private Integer sz;\n private String s;\n}<\/code><\/pre>\n@RequiredArgsConstructor\n@Service\npublic class PostService {\n public List<PostDto> search(SearchRequestDto dto) {\n if (dto.getPg() == null || dto.getPg() < 0) {\n dto.setPg(0);\n }\n if (dto.getSz() == null || dto.getSz() < 1 || dto.getSz() > 50) {\n dto.setSz(50);\n }\n Page<Post> list = postRepository.search(dto.getPg(), dto.getSz(), dto.getS());\n return mapper.toDto(list.getContent());\n }\n}<\/code><\/pre>\n\ucee8\ud2b8\ub864\ub7ec \ub808\uc774\uc5b4<\/h3>\n@RequiredArgsConstructor\n@RestController\npublic class PostController {\n @GetMapping("\/api\/v1\/search")\n public List<PostDto> search(SearchRequestDto dto) {\n return service.search(dto);\n }\n}<\/code><\/pre>\nQueryEntity<\/h3>\n
\ub610\ub294 QueryEntity \ub97c \uc774\uc6a9\ud574 \ubcf4\ub2e4 where \uc808\uc744 \uac04\uc18c\ud654\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
@QueryEntity\npublic class PostExpression {\n @QueryDelegate(Post.class)\n public static BooleanExpression matchSearchKeyword(QPost post,String searchStr) {\n if (searchStr == null || searchStr.isEmpty()) {\n return null;\n }\n return post.title.contains(searchStr).or(post.content.contains(searchStr)).or(post.writer.contains(searchStr));\n }\n}<\/code><\/pre>\npublic class PostRepositoryImpl extends QuerydslRepositorySupport implements PostRepositoryCustom {\n @Override\n public Page<Post> search(Integer pageNo, Integer pageSize, String searchStr) {\n Pageable pageable = PageRequest.of(pageNo, pageSize);\n\n List<Post> list = jpaQueryFactory\n .selectFrom(post)\n .where(\n post.matchSearchKeyword(searchStr)\n )\n .orderBy(post.idx.desc())\n .offset(pageable.getOffset())\n .limit(pageable.getPageSize())\n .fetch();\n\n JPAQuery<Post> count = jpaQueryFactory\n .selectFrom(post)\n .where(\n post.matchSearchKeyword(searchStr)\n );\n\n return PageableExecutionUtils.getPage(list, pageable, () -> count.fetch().size());\n }\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Spring Boot \uac8c\uc2dc\ud310 \uc0dd\uc131\ud558\uae30 \uc2a4\ud504\ub9c1 \ubd80\ud2b8\ub97c \uc774\uc6a9\ud574 \uac8c\uc2dc\ud310\uc744 \uc0dd\uc131\ud574 \ubd05\ub2c8\ub2e4. \uc77c\ubc18\uc801\uc778 \ud504\ub85c\uc81d\ud2b8\uac00 API \uc11c\ubc84\uc640 UI \uac00 \ubd84\ub9ac\ub418\uace0, API \ub97c \uc2a4\ud504\ub9c1\uc774 \ub2f4\ub2f9\ud558\uace0, UI \ubd80\ubd84\uc740 \uc790\ubc14\uc2a4\ud06c\ub9bd\ud2b8\uac00 \ub300\uc751\ud558\ub294 \ubc29\uc2dd\uc774\ub77c \uc800\ub294 API \uc11c\ubc84 \uad6c\uc131\ud558\ub294 \ubd80\ubd84\uc744 \uc911\uc810\uc801\uc73c\ub85c \uc124\uba85\ud569\ub2c8\ub2e4. UI \ubd80\ubd84\uc740 \uc81c\uac00 \uc798 \uc548\ud558\ub294 \ubd80\ubd84\uc774\ub77c… \uc124\uba85\uc744 \ud558\uac8c \ub420\uc9c0\ub294 \ubaa8\ub974\uaca0\uc2b5\ub2c8\ub2e4. \ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \uc0dd\uc131(MariaDB) \uc5ec\uae30 \uc5d0\uc11c MariaDB \ub97c \ub2e4\uc6b4\ubc1b\uc544 \uc124\uce58\ud569\ub2c8\ub2e4. UTF-8 as default server’s\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":[50],"tags":[],"class_list":["post-8680","post","type-post","status-publish","format-standard","hentry","category-spring-boot--"],"_links":{"self":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/8680","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=8680"}],"version-history":[{"count":38,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/8680\/revisions"}],"predecessor-version":[{"id":8731,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/8680\/revisions\/8731"}],"wp:attachment":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}