SpringBoot:JPA整合PostgreSQL

1. 引入jar包(pgsql)

         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-rest-hal-browser</artifactId>
        </dependency>
        <dependency>
            <groupId>com.vividsolutions</groupId>
            <artifactId>jts</artifactId>
            <version>1.13</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>5.3.0.Beta1</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-java8</artifactId>
            <version>5.3.0.Beta1</version>
        </dependency>
        <dependency>
            <groupId>com.bedatadriven</groupId>
            <artifactId>jackson-datatype-jts</artifactId>
            <version>2.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.5.jre7</version>
        </dependency>

2. 写配置文件

    jpa:
      database: postgresql
      show-sql: true
      hibernate:
        ddl-auto: update
      properties:
        hibernate:
          dialect: org.hibernate.spatial.dialect.postgis.PostgisDialect

    datasource:
      url: jdbc:postgresql://IP:PORT/库民
      username: 用户名
      password: 密码
      driver-class-name: org.postgresql.Driver

3. 测试类

@Entity(name = "bc_contact")
@Data
public class BcContact {

    //联系人id
    @Id
    @Column(name = "contact_id")
    private Long contactId;

    //位置坐标点
    @Type(type="jts_geometry")
    @Column(name = "point")
    private Point point;

    //圆形围栏，圆心
    @Type(type="jts_geometry")
    @Column(name = "center_point")
    private Point centerPoint;


    //位置面
    @Type(type = "jts_geometry")
    @Column(name = "polygon")
    private Geometry polygon ;

    //状态（0：正常、1：禁用）
    @Column(name = "status")
    private String status;

  
    // 地址类型
    @Column(name = "address_type")
    private String addressType;

    //地址标记颜色
    @Column(name = "mark_color")
    private String markColor;

    // 电子围栏类型 1 圆形、2 多边形
    @Column(name = "elect_fence_type")
    private String electFenceType;

    // 电子围栏数据
    @Column(name = "elect_fence")
    private String electFence;

    // 经度
    @Column(name = "lon")
    private double lon;

    // 纬度
    @Column(name = "lat")
    private double lat;

    // 数据是否完整
    @Column(name = "data_end")
    private String dataEnd;

    // 电子围栏半径，圆形图使用
    @Column(name = "elect_radius")
    private String electRadius;

    // 电子围栏圆心，圆形图使用
    @Column(name = "elect_center")
    private String electCenterStr;

    //距离
    @Column(name = "distance")
    private Double distance ;

}

//sql示例
public interface ContactRepository extends JpaRepository<BcContact,Long> {

    /**
     * 附近地址查询
     * @param lon
     * @param lat
     * @param distance
     * @return
     */
    @Query(value = "select ST_Distance(ST_SetSRID(f.point,4326)\\:\\:geography," +
            " ST_SetSRID(ST_MakePoint(:lon,:lat),4326)\\:\\:geography) as distance ,f.* " +
            " from bc_contact f where f.cms_verify='2' and " +
            " ST_Distance(ST_SetSRID(f.point,4326)\\:\\:geography," +
            " ST_SetSRID(ST_MakePoint(:lon,:lat),4326)\\:\\:geography) < :distance ",nativeQuery = true) 
}