【Springboot+mybatis-plus+PostgreSql CURD 实现】

• pom.xml ：加载PostgreSql数据包

`<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.26</version>
</dependency>
<!-- PostgreSql 驱动包 -->
<dependency>
    <groupId>net.postgis</groupId>
    <artifactId>postgis-jdbc</artifactId>
    <version>2.5.0</version>
</dependency>`

• application-dev.yml ：连接数据库

  spring:
      datasource:
          type: com.alibaba.druid.pool.DruidDataSource
          druid:
              driver-class-name: org.postgresql.Driver
              url: jdbc:postgresql://localhost:5432/test?currentSchema=public
              username: postgres
              password: ****
      jpa:
          properties:
              hibernate:
                  dialect: org.hibernate.dialect.PostgreSQLDialect
                  hbm2ddl:
                      auto: update

• 数据库sql文件

  -- ----------------------------
  -- Table structure for geometries
  -- ----------------------------
  DROP TABLE IF EXISTS "public"."geometries";
  CREATE TABLE "public"."geometries" (
    "name" varchar COLLATE "pg_catalog"."default",
    "geom" "public"."geometry",
    "crttime" timestamp(6)
  )
  ;
  COMMENT ON TABLE "public"."geometries" IS '点、线、面';
  
  -- ----------------------------
  -- Records of geometries
  -- ----------------------------
  INSERT INTO "public"."geometries" VALUES ('Point', '010100000000000000000000000000000000000000', '2022-10-27 09:25:19');
  INSERT INTO "public"."geometries" VALUES ('Linestring', '01020000000400000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040', '2022-10-26 09:25:23');
  INSERT INTO "public"."geometries" VALUES ('Polygon', '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-10-28 09:25:27');
  INSERT INTO "public"."geometries" VALUES ('PolygonWithHole', '01030000000200000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F', '2022-10-25 09:25:31');
  INSERT INTO "public"."geometries" VALUES ('Collection', '0107000000020000000101000000000000000000004000000000000000000103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-11-01 09:25:34');

• controller

  -- ----------------------------
  -- Table structure for geometries
  -- ----------------------------
  DROP TABLE IF EXISTS "public"."geometries";
  CREATE TABLE "public"."geometries" (
    "name" varchar COLLATE "pg_catalog"."default",
    "geom" "public"."geometry",
    "crttime" timestamp(6)
  )
  ;
  COMMENT ON TABLE "public"."geometries" IS '点、线、面';
  
  -- ----------------------------
  -- Records of geometries
  -- ----------------------------
  INSERT INTO "public"."geometries" VALUES ('Point', '010100000000000000000000000000000000000000', '2022-10-27 09:25:19');
  INSERT INTO "public"."geometries" VALUES ('Linestring', '01020000000400000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040', '2022-10-26 09:25:23');
  INSERT INTO "public"."geometries" VALUES ('Polygon', '0103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-10-28 09:25:27');
  INSERT INTO "public"."geometries" VALUES ('PolygonWithHole', '01030000000200000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F', '2022-10-25 09:25:31');
  INSERT INTO "public"."geometries" VALUES ('Collection', '0107000000020000000101000000000000000000004000000000000000000103000000010000000500000000000000000000000000000000000000000000000000F03F0000000000000000000000000000F03F000000000000F03F0000000000000000000000000000F03F00000000000000000000000000000000', '2022-11-01 09:25:34');

• controller

  @Slf4j
  @RestController
  @Api(tags = "GeoController", description = "pgGis测试")
  @RequestMapping("/pg")
  public class GeoController {
      @Autowired
      private PgGisService pgGisService;
  
      /**
       * 测试pg数据库
       */
      @GetMapping("/list")
      @ApiOperation("测试pg数据库")
      public CommonResult list(String name,String crttime){
          List<PgGis> list = pgGisService.list1(name,crttime);
          if(CollUtil.isEmpty(list)){
              return CommonResult.failed("没有查询到值");
          }
          return CommonResult.success(list);
      }
  
      @GetMapping("/list2")
      @ApiOperation("测试pg数据库2")
      public CommonResult list2(PgGis pgGis){
          List<PgGis> list  = pgGisService.list2(pgGis);
          if(CollUtil.isEmpty(list)){
              return CommonResult.failed("没有查询到值");
          }
          return CommonResult.success(list);
      }
  
  }

• service

  @Slf4j
  @RestController
  @Api(tags = "GeoController", description = "pgGis测试")
  @RequestMapping("/pg")
  public class GeoController {
      @Autowired
      private PgGisService pgGisService;
  
      /**
       * 测试pg数据库
       */
      @GetMapping("/list")
      @ApiOperation("测试pg数据库")
      public CommonResult list(String name,String crttime){
          List<PgGis> list = pgGisService.list1(name,crttime);
          if(CollUtil.isEmpty(list)){
              return CommonResult.failed("没有查询到值");
          }
          return CommonResult.success(list);
      }
  
      @GetMapping("/list2")
      @ApiOperation("测试pg数据库2")
      public CommonResult list2(PgGis pgGis){
          List<PgGis> list  = pgGisService.list2(pgGis);
          if(CollUtil.isEmpty(list)){
              return CommonResult.failed("没有查询到值");
          }
          return CommonResult.success(list);
      }
  
  }

• service-impl

  @Slf4j
  @Service
  @Transactional
  public class PgGisServiceImpl extends BaseServiceImpl<geometriesMapper, PgGis> implements PgGisService {
  
      @Autowired
      private geometriesMapper geometriesMapper;
  
      @Override
      public List<PgGis> list1(String name,String crttime) {
          return geometriesMapper.list1(name, crttime);
      }
  
      @Override
      public List<PgGis> list2(PgGis pgGis) {
          LambdaQueryWrapper<PgGis> qw = new QueryWrapper<PgGis>().lambda()
                  .eq(pgGis.getGeom() != null, PgGis::getGeom, pgGis.getGeom())
                  .eq(pgGis.getName() != null, PgGis::getName, pgGis.getName())
                  .eq(pgGis.getCrttime() != null, PgGis::getCrttime, pgGis.getCrttime());
          return list(qw);
      }
  
  }

• entity

  @Data
  @TableName(value = "geometries", autoResultMap = true)
  @ApiModel(value = "PgGis数据库" )
  public class PgGis  implements Serializable {
      private static final long serialVersionUID = 1L;
  
      @ApiModelProperty("name信息")
      @TableField("name")
      private String name;
  
      @ApiModelProperty("geom信息")
      @TableField(typeHandler = MyGeometryTypeHandler.class)
      private String geom;
  
      @ApiModelProperty("crtTime信息")
      @TableField("crttime")
      private Date crttime;
  
  }

• mapper

  @Mapper
  public interface geometriesMapper extends BaseMapper<PgGis> {
  
      static final String FIND_GEOJSON_SQL="<script>"
              + "SELECT name, st_asgeojson(geom) geom, crttime FROM geometries p "
              + "where 1 = 1 "
              + "<if test='null != name'>and p.name like concat('%', #{name}, '%')</if>"
              + "<if test='null != crttime'>and p.crttime like concat('%', #{crttime}, '%')</if>"
              + "</script>";
      @Select(FIND_GEOJSON_SQL)
      List<PgGis> list1(@Param("name")String name, @Param("crttime")String crttime);
  }

• config配置

  @MappedTypes({String.class})
  public class MyGeometryTypeHandler extends BaseTypeHandler<String> {
      @Override
      public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
          PGgeometry pGgeometry = new PGgeometry(parameter);
          ps.setObject(i, pGgeometry);
      }
  
      @Override
      public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
          PGgeometry pGgeometry = new PGgeometry(rs.getString(columnName));
          if (pGgeometry == null) {
              return null;
          }
          return pGgeometry.toString();
      }
  
      @Override
      public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
          PGgeometry pGgeometry = new PGgeometry(rs.getString(columnIndex));
          if (pGgeometry == null) {
              return null;
          }
          return pGgeometry.toString();
      }
  
      @Override
      public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
  
          PGgeometry pGgeometry = new PGgeometry(cs.getString(columnIndex));
          if (pGgeometry == null) {
              return null;
          }
          return pGgeometry.toString();
      }
  }