Springboot+mybatis-plus+PostgreSql CURD 实现

• pom.xml ：加载PostgreSql数据包

1.  
   `<dependency>
2.  
   <groupId>org.postgresql</groupId>
3.  
   <artifactId>postgresql</artifactId>
4.  
   <version>42.2.26</version>
5.  
   </dependency>
6.  
   <!-- PostgreSql 驱动包 -->
7.  
   <dependency>
8.  
   <groupId>net.postgis</groupId>
9.  
   <artifactId>postgis-jdbc</artifactId>
10.  
    <version>2.5.0</version>
11.  
    </dependency>`

• application-dev.yml ：连接数据库
  1.  
     spring:
  2.  
     datasource:
  3.  
     type: com.alibaba.druid.pool.DruidDataSource
  4.  
     druid:
  5.  
     driver-class-name: org.postgresql.Driver
  6.  
     url: jdbc:postgresql://localhost:5432/test?currentSchema=public
  7.  
     username: postgres
  8.  
     password: ****
  9.  
     jpa:
  10.  
      properties:
  11.  
      hibernate:
  12.  
      dialect: org.hibernate.dialect.PostgreSQLDialect
  13.  
      hbm2ddl:
  14.  
      auto: update
• 数据库sql文件
  1.  
     -- ----------------------------
  2.  
     -- Table structure for geometries
  3.  
     -- ----------------------------
  4.  
     DROP TABLE IF EXISTS "public"."geometries";
  5.  
     CREATE TABLE "public"."geometries" (
  6.  
       "name" varchar COLLATE "pg_catalog"."default",
  7.  
       "geom" "public"."geometry",
  8.  
       "crttime" timestamp(6)
  9.  
     )
  10.  
      ;
  11.  
      COMMENT ON TABLE "public"."geometries" IS '点、线、面';
  12.  
       
  13.  
      -- ----------------------------
  14.  
      -- Records of geometries
  15.  
      -- ----------------------------
  16.  
      INSERT INTO "public"."geometries" VALUES ('Point', '010100000000000000000000000000000000000000', '2022-10-27 09:25:19');
  17.  
      INSERT INTO "public"."geometries" VALUES ('Linestring', '01020000000400000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040', '2022-10-26 09:25:23');
  18.  
      INSERT INTO "public"."geometries" VALUES ('Polygon', '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-10-28 09:25:27');
  19.  
      INSERT INTO "public"."geometries" VALUES ('PolygonWithHole', '01030000000200000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F', '2022-10-25 09:25:31');
  20.  
      INSERT INTO "public"."geometries" VALUES ('Collection', '0107000000020000000101000000000000000000004000000000000000000103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-11-01 09:25:34');
  
• controller
  1.  
     -- ----------------------------
  2.  
     -- Table structure for geometries
  3.  
     -- ----------------------------
  4.  
     DROP TABLE IF EXISTS "public"."geometries";
  5.  
     CREATE TABLE "public"."geometries" (
  6.  
       "name" varchar COLLATE "pg_catalog"."default",
  7.  
       "geom" "public"."geometry",
  8.  
       "crttime" timestamp(6)
  9.  
     )
  10.  
      ;
  11.  
      COMMENT ON TABLE "public"."geometries" IS '点、线、面';
  12.  
       
  13.  
      -- ----------------------------
  14.  
      -- Records of geometries
  15.  
      -- ----------------------------
  16.  
      INSERT INTO "public"."geometries" VALUES ('Point', '010100000000000000000000000000000000000000', '2022-10-27 09:25:19');
  17.  
      INSERT INTO "public"."geometries" VALUES ('Linestring', '01020000000400000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040', '2022-10-26 09:25:23');
  18.  
      INSERT INTO "public"."geometries" VALUES ('Polygon', '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-10-28 09:25:27');
  19.  
      INSERT INTO "public"."geometries" VALUES ('PolygonWithHole', '01030000000200000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F', '2022-10-25 09:25:31');
  20.  
      INSERT INTO "public"."geometries" VALUES ('Collection', '0107000000020000000101000000000000000000004000000000000000000103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-11-01 09:25:34');
  

• controller
  1.  
     @Slf4j
  2.  
     @RestController
  3.  
     @Api(tags = "GeoController", description = "pgGis测试")
  4.  
     @RequestMapping("/pg")
  5.  
     public class GeoController {
  6.  
     @Autowired
  7.  
     private PgGisService pgGisService;
  8.  
      
  9.  
     /**
  10.  
      * 测试pg数据库
  11.  
      */
  12.  
      @GetMapping("/list")
  13.  
      @ApiOperation("测试pg数据库")
  14.  
      public CommonResult list(String name,String crttime){
  15.  
      List<PgGis> list = pgGisService.list1(name,crttime);
  16.  
      if(CollUtil.isEmpty(list)){
  17.  
      return CommonResult.failed("没有查询到值");
  18.  
      }
  19.  
      return CommonResult.success(list);
  20.  
      }
  21.  
       
  22.  
      @GetMapping("/list2")
  23.  
      @ApiOperation("测试pg数据库2")
  24.  
      public CommonResult list2(PgGis pgGis){
  25.  
      List<PgGis> list = pgGisService.list2(pgGis);
  26.  
      if(CollUtil.isEmpty(list)){
  27.  
      return CommonResult.failed("没有查询到值");
  28.  
      }
  29.  
      return CommonResult.success(list);
  30.  
      }
  31.  
       
  32.  
      }
  
• service
  1.  
     @Slf4j
  2.  
     @RestController
  3.  
     @Api(tags = "GeoController", description = "pgGis测试")
  4.  
     @RequestMapping("/pg")
  5.  
     public class GeoController {
  6.  
     @Autowired
  7.  
     private PgGisService pgGisService;
  8.  
      
  9.  
     /**
  10.  
      * 测试pg数据库
  11.  
      */
  12.  
      @GetMapping("/list")
  13.  
      @ApiOperation("测试pg数据库")
  14.  
      public CommonResult list(String name,String crttime){
  15.  
      List<PgGis> list = pgGisService.list1(name,crttime);
  16.  
      if(CollUtil.isEmpty(list)){
  17.  
      return CommonResult.failed("没有查询到值");
  18.  
      }
  19.  
      return CommonResult.success(list);
  20.  
      }
  21.  
       
  22.  
      @GetMapping("/list2")
  23.  
      @ApiOperation("测试pg数据库2")
  24.  
      public CommonResult list2(PgGis pgGis){
  25.  
      List<PgGis> list = pgGisService.list2(pgGis);
  26.  
      if(CollUtil.isEmpty(list)){
  27.  
      return CommonResult.failed("没有查询到值");
  28.  
      }
  29.  
      return CommonResult.success(list);
  30.  
      }
  31.  
       
  32.  
      }
  
• service-impl
  1.  
     @Slf4j
  2.  
     @Service
  3.  
     @Transactional
  4.  
     public class PgGisServiceImpl extends BaseServiceImpl<geometriesMapper, PgGis> implements PgGisService {
  5.  
      
  6.  
     @Autowired
  7.  
     private geometriesMapper geometriesMapper;
  8.  
      
  9.  
     @Override
  10.  
      public List<PgGis> list1(String name,String crttime) {
  11.  
      return geometriesMapper.list1(name, crttime);
  12.  
      }
  13.  
       
  14.  
      @Override
  15.  
      public List<PgGis> list2(PgGis pgGis) {
  16.  
      LambdaQueryWrapper<PgGis> qw = new QueryWrapper<PgGis>().lambda()
  17.  
      .eq(pgGis.getGeom() != null, PgGis::getGeom, pgGis.getGeom())
  18.  
      .eq(pgGis.getName() != null, PgGis::getName, pgGis.getName())
  19.  
      .eq(pgGis.getCrttime() != null, PgGis::getCrttime, pgGis.getCrttime());
  20.  
      return list(qw);
  21.  
      }
  22.  
       
  23.  
      }
  
• entity
  1.  
     @Data
  2.  
     @TableName(value = "geometries", autoResultMap = true)
  3.  
     @ApiModel(value = "PgGis数据库" )
  4.  
     public class PgGis implements Serializable {
  5.  
     private static final long serialVersionUID = 1L;
  6.  
      
  7.  
     @ApiModelProperty("name信息")
  8.  
     @TableField("name")
  9.  
     private String name;
  10.  
       
  11.  
      @ApiModelProperty("geom信息")
  12.  
      @TableField(typeHandler = MyGeometryTypeHandler.class)
  13.  
      private String geom;
  14.  
       
  15.  
      @ApiModelProperty("crtTime信息")
  16.  
      @TableField("crttime")
  17.  
      private Date crttime;
  18.  
       
  19.  
      }
  
• mapper
  1.  
     @Mapper
  2.  
     public interface geometriesMapper extends BaseMapper<PgGis> {
  3.  
      
  4.  
     static final String FIND_GEOJSON_SQL="<script>"
  5.  
     "SELECT name, st_asgeojson(geom) geom, crttime FROM geometries p "
  6.  
     "where 1 = 1 "
  7.  
     "<if test='null != name'>and p.name like concat('%', #{name}, '%')</if>"
  8.  
     "<if test='null != crttime'>and p.crttime like concat('%', #{crttime}, '%')</if>"
  9.  
     "</script>";
  10.  
      @Select(FIND_GEOJSON_SQL)
  11.  
      List<PgGis> list1(@Param("name")String name, @Param("crttime")String crttime);
  12.  
      }
• config配置
  1.  
     @MappedTypes({String.class})
  2.  
     public class MyGeometryTypeHandler extends BaseTypeHandler<String> {
  3.  
     @Override
  4.  
     public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
  5.  
     PGgeometry pGgeometry = new PGgeometry(parameter);
  6.  
     ps.setObject(i, pGgeometry);
  7.  
     }
  8.  
      
  9.  
     @Override
  10.  
      public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
  11.  
      PGgeometry pGgeometry = new PGgeometry(rs.getString(columnName));
  12.  
      if (pGgeometry == null) {
  13.  
      return null;
  14.  
      }
  15.  
      return pGgeometry.toString();
  16.  
      }
  17.  
       
  18.  
      @Override
  19.  
      public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
  20.  
      PGgeometry pGgeometry = new PGgeometry(rs.getString(columnIndex));
  21.  
      if (pGgeometry == null) {
  22.  
      return null;
  23.  
      }
  24.  
      return pGgeometry.toString();
  25.  
      }
  26.  
       
  27.  
      @Override
  28.  
      public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
  29.  
       
  30.  
      PGgeometry pGgeometry = new PGgeometry(cs.getString(columnIndex));
  31.  
      if (pGgeometry == null) {
  32.  
      return null;
  33.  
      }
  34.  
      return pGgeometry.toString();
  35.  
      }
  36.  
      }
  

这篇好文章是转载于：学新通技术网