{"id":10008367,"date":"2024-02-19T18:58:59","date_gmt":"2024-02-19T10:58:59","guid":{"rendered":"https:\/\/blog.itmax.cn\/?p=10008367"},"modified":"2024-02-19T18:58:59","modified_gmt":"2024-02-19T10:58:59","slug":"spring-boot%e6%95%b4%e5%90%88postgres%e5%ae%9e%e7%8e%b0%e8%bd%bb%e9%87%8f%e7%ba%a7%e5%85%a8%e6%96%87%e6%90%9c%e7%b4%a2","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10008367","title":{"rendered":"Spring Boot\u6574\u5408Postgres\u5b9e\u73b0\u8f7b\u91cf\u7ea7\u5168\u6587\u641c\u7d22"},"content":{"rendered":"<p>\u6709\u8fd9\u6837\u4e00\u4e2a\u5e26\u6709\u641c\u7d22\u529f\u80fd\u7684\u7528\u6237\u754c\u9762\u9700\u6c42\uff1a<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"457\" height=\"624\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdbsQZ\" alt=\"\" title=\"\"><\/p>\n<p>\u641c\u7d22\u6d41\u7a0b\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<p><img decoding=\"async\" width=\"720\" height=\"279\" referrerpolicy=\"no-referrer\" src=\"https:\/\/segmentfault.com\/img\/bVdbsQ0\" alt=\"\" title=\"\" loading=\"lazy\"><\/p>\n<p>\u8fd9\u4e2a\u9700\u6c42\u6d89\u53ca\u4e24\u4e2a\u5b9e\u4f53\uff1a<\/p>\n<ul>\n<li>\u201c\u8bc4\u5206\uff08Rating\uff09\u3001\u7528\u6237\u540d\uff08Username\uff09\u201d\u6570\u636e\u4e0e<code>User<\/code>\u5b9e\u4f53\u76f8\u5173<\/li>\n<li>\u201c\u521b\u5efa\u65e5\u671f\uff08create date\uff09\u3001\u89c2\u770b\u6b21\u6570\uff08number of views\uff09\u3001\u6807\u9898\uff08title\uff09\u3001\u6b63\u6587\uff08body\uff09\u201d\u4e0e<code>Story<\/code>\u5b9e\u4f53\u76f8\u5173<\/li>\n<\/ul>\n<p>\u9700\u8981\u652f\u6301\u7684\u529f\u80fd\u5bf9<code>User<\/code>\u5b9e\u4f53\u4e2d\u7684\u8bc4\u5206\uff08Rating\uff09\u7684\u9891\u7e41\u4fee\u6539\u4ee5\u53ca\u4e0b\u5217\u641c\u7d22\u529f\u80fd\uff1a<\/p>\n<ul>\n<li>\u6309User\u8bc4\u5206\u8fdb\u884c\u8303\u56f4\u641c\u7d22<\/li>\n<li>\u6309Story\u521b\u5efa\u65e5\u671f\u8fdb\u884c\u8303\u56f4\u641c\u7d22<\/li>\n<li>\u6309Story\u6d4f\u89c8\u91cf\u8fdb\u884c\u8303\u56f4\u641c\u7d22<\/li>\n<li>\u6309Story\u6807\u9898\u8fdb\u884c\u5168\u6587\u641c\u7d22<\/li>\n<li>\u6309Story\u6b63\u6587\u8fdb\u884c\u5168\u6587\u641c\u7d22<\/li>\n<\/ul>\n<h2>Postgres\u4e2d\u521b\u5efa\u8868\u7ed3\u6784\u548c\u7d22\u5f15<\/h2>\n<p>\u521b\u5efa<code>users<\/code>\u8868\u548c<code>stories<\/code>\u8868\u4ee5\u53ca\u5bf9\u5e94\u641c\u7d22\u9700\u6c42\u76f8\u5173\u7684\u7d22\u5f15\uff0c\u5305\u62ec\uff1a<\/p>\n<ul>\n<li>\u4f7f\u7528 btree \u7d22\u5f15\u6765\u652f\u6301\u6309User\u8bc4\u5206\u641c\u7d22<\/li>\n<li>\u4f7f\u7528 btree \u7d22\u5f15\u6765\u652f\u6301\u6309Story\u521b\u5efa\u65e5\u671f\u3001\u67e5\u770b\u6b21\u6570\u7684\u641c\u7d22<\/li>\n<li>\u4f7f\u7528 gin \u7d22\u5f15\u6765\u652f\u6301\u5168\u6587\u641c\u7d22\u5185\u5bb9\uff08\u540c\u65f6\u521b\u5efa\u5168\u6587\u641c\u7d22\u5217<code>fulltext<\/code>\uff0c\u7c7b\u578b\u4f7f\u7528<code>tsvector<\/code>\u4ee5\u652f\u6301\u5168\u6587\u641c\u7d22\uff09<\/li>\n<\/ul>\n<p>\u5177\u4f53\u521b\u5efa\u811a\u672c\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"sql\">--Create Users table\nCREATE TABLE IF NOT EXISTS users\n(\n  id bigserial NOT NULL,\n  name character varying(100) NOT NULL,\nrating integer,\nPRIMARY KEY (id)\n)\n;\nCREATE INDEX usr_rating_idx\nON users USING btree\n(rating ASC NULLS LAST)\nTABLESPACE pg_default\n;\n\n--Create Stories table\nCREATE TABLE  IF NOT EXISTS stories\n(\n    id bigserial NOT NULL,\n    create_date timestamp without time zone NOT NULL,\n    num_views bigint NOT NULL,\n    title text NOT NULL,\n    body text NOT NULL,\n    fulltext tsvector,\n    user_id bigint,\n    PRIMARY KEY (id),\nCONSTRAINT user_id_fk FOREIGN KEY (user_id)\nREFERENCES users (id) MATCH SIMPLE\nON UPDATE NO ACTION\nON DELETE NO ACTION\nNOT VALID\n)\n;\nCREATE INDEX str_bt_idx\nON stories USING btree\n(create_date ASC NULLS LAST,\nnum_views ASC NULLS LAST, user_id ASC NULLS LAST)\n;\n\nCREATE INDEX fulltext_search_idx\nON stories USING gin\n(fulltext)\n;<\/code><\/pre>\n<h2>\u521b\u5efaSpring Boot\u5e94\u7528<\/h2>\n<ol>\n<li>\u9879\u76ee\u4f9d\u8d56\u5173\u7cfb\uff08\u8fd9\u91cc\u4f7f\u7528Gradle\u6784\u5efa\uff09\uff1a<\/li>\n<\/ol>\n<pre><code class=\"json\">plugins {\n   id 'java'\n   id 'org.springframework.boot' version '3.1.3'\n   id 'io.spring.dependency-management' version '1.1.3'\n}\n\ngroup = 'com.example'\nversion = '0.0.1-SNAPSHOT'\n\njava {\n   sourceCompatibility = '17'\n}\n\nrepositories {\n   mavenCentral()\n}\n\ndependencies {\n   implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'\n   implementation 'org.springframework.boot:spring-boot-starter-web'\n   runtimeOnly 'org.postgresql:postgresql'\n   testImplementation 'org.springframework.boot:spring-boot-starter-test'\n}\n\ntasks.named('test') {\n   useJUnitPlatform()\n}<\/code><\/pre>\n<ol start=\"2\">\n<li><code>application.yaml<\/code>\u4e2d\u914d\u7f6e\u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f<\/li>\n<\/ol>\n<pre><code class=\"yaml\">spring:\n  datasource: \n    url: jdbc:postgresql:\/\/localhost:5432\/postgres\n    username: postgres\n    password: postgres<\/code><\/pre>\n<ol start=\"3\">\n<li>\u6570\u636e\u6a21\u578b<\/li>\n<\/ol>\n<p>\u5b9a\u4e49\u9700\u8981\u7528\u5230\u7684\u5404\u79cd\u6570\u636e\u6a21\u578b\uff1a<\/p>\n<pre><code class=\"java\">public record Period(String fieldName, LocalDateTime min, LocalDateTime max) {\n}\n\npublic record Range(String fieldName, long min, long max) {\n}\n\npublic record Search(List&lt;Period&gt; periods, List&lt;Range&gt; ranges, String fullText, long offset, long limit) {\n}\n\npublic record UserStory(Long id, LocalDateTime createDate, Long numberOfViews,\n                        String title, String body, Long userRating, String userName, Long userId) {\n}<\/code><\/pre>\n<p>\u8fd9\u91cc\u4f7f\u7528<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=ZRq%2F7KSXzcKgAdGQZUxLMA%3D%3D.G45LtmjYi37PjR3U4mZQFn6K526LuP9mSWG1SW4fJkb0poXsFovdCnCXbYYTPzzLgXB3W2IdgKu378dZrqjQ8AaxQVU42uypweX1ryyunuk%3D\" rel=\"noopener noreferrer\">Java 16\u63a8\u51fa\u7684\u65b0\u7279\u6027record<\/a>\u5b9e\u73b0\uff0c\u6240\u4ee5\u4ee3\u7801\u975e\u5e38\u7b80\u6d01\u3002\u5982\u679c\u60a8\u8fd8\u4e0d\u4e86\u89e3\u7684\u8bdd\uff0c\u53ef\u4ee5\u524d\u5f80<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=BLHTbRDFaeyTHHZlE96NfQ%3D%3D.Qvp91vHWXWdgUp%2FRIy%2FojnyryrV%2BvVV6A0E2PQx84CQt7qU8rqps2UkEWgOvc15Cac5BSw%2BY7NbUPghZPTgFsfDhU6XNw5UaQuz9yOAmEMU%3D\" rel=\"noopener noreferrer\">\u7a0b\u5e8f\u733fDD\u7684Java\u65b0\u7279\u6027\u4e13\u680f<\/a>\u8865\u5168\u4e00\u4e0b\u77e5\u8bc6\u70b9\u3002<\/p>\n<ol start=\"4\">\n<li>\u6570\u636e\u8bbf\u95ee\uff08Repository\uff09<\/li>\n<\/ol>\n<pre><code class=\"java\">@Repository\npublic class UserStoryRepository {\n\n    private final JdbcTemplate jdbcTemplate;\n\n\n    @Autowired\n    public UserStoryRepository(JdbcTemplate jdbcTemplate) {\n        this.jdbcTemplate = jdbcTemplate;\n    }\n\n    public List&lt;UserStory&gt; findByFilters(Search search) {\n        return jdbcTemplate.query(\n                &quot;&quot;&quot;\n                  SELECT s.id id, create_date, num_views, \n                         title, body, user_id, name user_name, \n                         rating user_rating \n                  FROM stories s INNER JOIN users u \n                      ON s.user_id = u.id\n                  WHERE true\n                &quot;&quot;&quot; + buildDynamicFiltersText(search)\n                        + &quot; order by create_date desc offset ? limit ?&quot;,\n                (rs, rowNum) -&gt; new UserStory(\n                        rs.getLong(&quot;id&quot;),\n                        rs.getTimestamp(&quot;create_date&quot;).toLocalDateTime(),\n                        rs.getLong(&quot;num_views&quot;),\n                        rs.getString(&quot;title&quot;),\n                        rs.getString(&quot;body&quot;),\n                        rs.getLong(&quot;user_rating&quot;),\n                        rs.getString(&quot;user_name&quot;),\n                        rs.getLong(&quot;user_id&quot;)\n                ),\n                buildDynamicFilters(search)\n        );\n    }\n\n    public void save(UserStory userStory) {\n        var keyHolder = new GeneratedKeyHolder();\n\n        jdbcTemplate.update(connection -&gt; {\n            PreparedStatement ps = connection\n                .prepareStatement(\n                    &quot;&quot;&quot;\n                      INSERT INTO stories (create_date, num_views, title, body, user_id)\n                          VALUES (?, ?, ?, ?, ?)\n                    &quot;&quot;&quot;,\n                    Statement.RETURN_GENERATED_KEYS\n            );\n            ps.setTimestamp(1, Timestamp.valueOf(userStory.createDate()));\n            ps.setLong(2, userStory.numberOfViews());\n            ps.setString(3, userStory.title());\n            ps.setString(4, userStory.body());\n            ps.setLong(5, userStory.userId());\n\n            return ps;\n        }, keyHolder);\n\n        var generatedId = (Long) keyHolder.getKeys().get(&quot;id&quot;);\n\n        if (generatedId != null) {\n            updateFullTextField(generatedId);\n        }\n    }\n\n    private void updateFullTextField(Long generatedId) {\n        jdbcTemplate.update(\n            &quot;&quot;&quot;\n              UPDATE stories SET fulltext = to_tsvector(title || ' ' || body)\n              where id = ?\n            &quot;&quot;&quot;,\n            generatedId\n        );\n    }\n\n    private Object[] buildDynamicFilters(Search search) {\n        var filtersStream = search.ranges().stream()\n                .flatMap(\n                    range -&gt; Stream.of((Object) range.min(), range.max())\n                );\n\n        var periodsStream = search.periods().stream()\n                .flatMap(\n                    range -&gt; Stream.of((Object) Timestamp.valueOf(range.min()), Timestamp.valueOf(range.max()))\n                );\n\n        filtersStream = Stream.concat(filtersStream, periodsStream);\n\n        if (!search.fullText().isBlank()) {\n            filtersStream = Stream.concat(filtersStream, Stream.of(search.fullText()));\n        }\n\n        filtersStream = Stream.concat(filtersStream, Stream.of(search.offset(), search.limit()));\n\n        return filtersStream.toArray();\n    }\n\n    private String buildDynamicFiltersText(Search search) {\n        var rangesFilterString =\n                Stream.concat(\n                  search.ranges()\n                        .stream()\n                        .map(\n                            range -&gt; String.format(&quot; and %s between ? and ? &quot;, range.fieldName())\n                        ),\n                  search.periods()\n                        .stream()\n                        .map(\n                            range -&gt; String.format(&quot; and %s between ? and ? &quot;, range.fieldName())\n                        )\n                  )\n                  .collect(Collectors.joining(&quot; &quot;));\n\n        return rangesFilterString + buildFulltextFilterText(search.fullText());\n    }\n\n    private String buildFulltextFilterText(String fullText) {\n        return fullText.isBlank() ? &quot;&quot; : &quot; and fulltext @@ plainto_tsquery(?) &quot;;\n    }\n}<\/code><\/pre>\n<ol start=\"5\">\n<li>Controller\u5b9e\u73b0<\/li>\n<\/ol>\n<pre><code class=\"java\">@RestController\n@RequestMapping(&quot;\/user-stories&quot;)\npublic class UserStoryController {\n    private final UserStoryRepository userStoryRepository;\n\n    @Autowired\n    public UserStoryController(UserStoryRepository userStoryRepository) {\n        this.userStoryRepository = userStoryRepository;\n    }\n\n    @PostMapping\n    public void save(@RequestBody UserStory userStory) {\n        userStoryRepository.save(userStory);\n    }\n\n    @PostMapping(&quot;\/search&quot;)\n    public List&lt;UserStory&gt; search(@RequestBody Search search) {\n        return userStoryRepository.findByFilters(search);\n    }\n}<\/code><\/pre>\n<h2>\u5c0f\u7ed3<\/h2>\n<p>\u672c\u6587\u4ecb\u7ecd\u4e86\u5982\u4f55\u5728Spring Boot\u4e2d\u7ed3\u5408Postgres\u6570\u636e\u5e93\u5b9e\u73b0\u5168\u6587\u641c\u7d22\u7684\u529f\u80fd\uff0c\u8be5\u65b9\u6cd5\u6bd4\u8d77\u4f7f\u7528Elasticsearch\u66f4\u4e3a\u8f7b\u91cf\u7ea7\uff0c\u975e\u5e38\u9002\u5408\u4e00\u4e9b\u5c0f\u9879\u76ee\u573a\u666f\u4f7f\u7528\u3002\u5e0c\u671b\u672c\u6587\u5185\u5bb9\u5bf9\u60a8\u6709\u6240\u5e2e\u52a9\u3002\u5982\u679c\u60a8\u5b66\u4e60\u8fc7\u7a0b\u4e2d\u5982\u9047\u56f0\u96be\uff1f\u53ef\u4ee5\u52a0\u5165\u6211\u4eec\u8d85\u9ad8\u8d28\u91cf\u7684<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=9NYX3eu%2F1QSCAECN8c9UMQ%3D%3D.ougTacqi1PGCk7xddx6tUbbQFqsmjRlx%2BjtA3FCLUyJlvSy9nREQ8Tbw4knRvC8u\" rel=\"noopener noreferrer\">Spring\u6280\u672f\u4ea4\u6d41\u7fa4<\/a>\uff0c\u53c2\u4e0e\u4ea4\u6d41\u4e0e\u8ba8\u8bba\uff0c\u66f4\u597d\u7684\u5b66\u4e60\u4e0e\u8fdb\u6b65\uff01\u66f4\u591a<a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=vUHieaJLwRbGztH7BAzSCg%3D%3D.3im9f1ecTiCsgAYsK3PZtdx9uwtIWKKMtf4aeINatGc9rZZmEmlXTzq797%2FNRI3S\" rel=\"noopener noreferrer\">Spring Boot\u6559\u7a0b\u53ef\u4ee5\u70b9\u51fb\u76f4\u8fbe\uff01<\/a>\uff0c\u6b22\u8fce\u6536\u85cf\u4e0e\u8f6c\u53d1\u652f\u6301\uff01<\/p>\n<h2>\u53c2\u8003\u8d44\u6599<\/h2>\n<ul>\n<li><a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=0Rpy7L%2BlfQwoNXzht2aleg%3D%3D.SnqwjIqK5E9Du4GrCzeysbOubvpbBMYHpIbhR7qTA3mamepCtOzadB2CUz2WF1kWJKdrh8q8L6sy1sUR9SsQNhqp2O1acfxtqaLNLWH0GZpeDQarjPdLTrlvmdXMuUZh\" rel=\"noopener noreferrer\">Postgres full-text search Spring boot integration<\/a><\/li>\n<li><a target=\"_blank\" href=\"https:\/\/link.segmentfault.com\/?enc=CZwNDWmenxgzcxzcltomSQ%3D%3D.xWeJaAqwGQZTAJe9cMpOwuqca7%2BjuLlxv%2F0aBM2aSLWQOf%2FzkAj3PCTq%2BtXu5CMGoyDXUfA%2F64auOiaJahaN4pUmTC%2Bg4njH2PEMwpenazI%3D\" rel=\"noopener noreferrer\">Java 16\u65b0\u7279\u6027\uff1arecord<\/a><\/li>\n<\/ul>\n<blockquote><p>\u6b22\u8fce\u5173\u6ce8\u6211\u7684\u516c\u4f17\u53f7\uff1a\u7a0b\u5e8f\u733fDD\u3002\u7b2c\u4e00\u65f6\u95f4\u4e86\u89e3\u524d\u6cbf\u884c\u4e1a\u6d88\u606f\u3001\u5206\u4eab\u6df1\u5ea6\u6280\u672f\u5e72\u8d27\u3001\u83b7\u53d6\u4f18\u8d28\u5b66\u4e60\u8d44\u6e90<\/p><\/blockquote>\n<p><a href=\"https:\/\/segmentfault.com\/a\/1190000044639185\">https:\/\/segmentfault.com\/a\/1190000044639185<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6709\u8fd9\u6837\u4e00\u4e2a\u5e26\u6709\u641c\u7d22\u529f\u80fd\u7684\u7528\u6237\u754c\u9762\u9700\u6c42\uff1a \u641c\u7d22\u6d41\u7a0b\u5982\u4e0b\u6240\u793a\uff1a \u8fd9\u4e2a\u9700\u6c42\u6d89\u53ca\u4e24\u4e2a\u5b9e\u4f53\uff1a \u201c\u8bc4\u5206\uff08Rating\uff09\u3001\u7528 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-10008367","post","type-post","status-publish","format-standard","hentry","category-share"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008367","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10008367"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008367\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10008367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10008367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10008367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}