SpringBoot:JPA統合PostgreSQL
3930 ワード
1.jarパッケージの導入(pgsql)
2.プロファイルの書き込み
3.テストクラス
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)
}