SpringBoot:JPA統合PostgreSQL

3930 ワード

1.jarパッケージの導入(pgsql)
         
            org.springframework.boot
            spring-boot-starter-data-jpa
        
        
            org.springframework.data
            spring-data-rest-hal-browser
        
        
            com.vividsolutions
            jts
            1.13
        
        
            org.hibernate
            hibernate-spatial
            5.3.0.Beta1
        
        
            org.hibernate
            hibernate-java8
            5.3.0.Beta1
        
        
            com.bedatadriven
            jackson-datatype-jts
            2.3
        
        
        
            org.postgresql
            postgresql
            42.2.5.jre7
        

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 {

    /**
     *       
     * @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) 
}