Need Help! I am unable to convert a Point from MySQL using JdbcTemplate. However, when using JPA it worked out!
Don't suggest an answer with SELECT ST_AsWKT() + WKTReader(), I have already tried and it did not work out very well...
Maybe you need to specify the SpatialDialect for JDBC or write a custom converter???
Error:
java.sql.SQLException: Conversion not supported for type org.locationtech.jts.geom.Point
Source
Type in Mysql: POINT NULL SRID 4326
Type in Java: org.locationtech.jts.geom.Point
in Gradle:
implementation 'org.locationtech.jts:jts-core:1.18.0'
NtpJdbcRepository.java:
import org.locationtech.jts.geom.Point;
@Repository
@AllArgsConstructor
public class NtpJdbcRepository implements NtpRepository {
private final JdbcTemplate jdbcTemplate;
@Override
public List<NtpSource> getNtpSourceWithNtpIdandDate(List<Integer> ntp_id, Date startDate, Date endDate) {
String selectQuery = "SELECT "
+ "u.idTable,"
+ "u.ntp_id,"
+ "u.dataDT,"
+ "u.ip,"
+ "u.country,"
+ "u.iso_code,"
+ "u.city,"
+ "u.coordinates,"
+ "SUM(u.counts_requests) as counts_requests "
+ "FROM ntp_source AS u "
+ "WHERE u.ntp_id IN (?) and u.dataDT BETWEEN ? AND ? "
+ "GROUP BY u.ip";
String inSql = ntp_id.stream().map(String::valueOf)
.collect(Collectors.joining(","));
return jdbcTemplate.query(selectQuery,
new Object[]{inSql, startDate, endDate},
(rs, rowNum) -> {
NtpSource ntpSource = new NtpSource();
ntpSource.setId(rs.getInt("idTable"));
ntpSource.setNtpId(rs.getInt("ntp_id"));
ntpSource.setDataDT(rs.getDate("dataDT"));
ntpSource.setIp(rs.getString("ip"));
ntpSource.setCountry(rs.getString("country"));
ntpSource.setIsoCode(rs.getString("iso_code"));
ntpSource.setCity(rs.getString("city"));
ntpSource.setCountsRequests(rs.getLong("counts_requests"));
ntpSource.setCoordinates(rs.getObject("coordinates", Point.class)); // <-- There error: SQLException: Conversion not supported for type org.locationtech.jts.geom.Point
return ntpSource;
});
}
}
NtpSource.java:
import org.locationtech.jts.geom.Point;
@Entity
@Table(name = "ntp_source")
@ToString
@Data
@NoArgsConstructor
@AllArgsConstructor
public class NtpSource implements Serializable {
@Id
@Column(name = "idTable")
private int id;
@Column(name = "ntp_id")
private int ntpId;
@Column(name = "dataDT", columnDefinition="DATETIME")
@Temporal(TemporalType.TIMESTAMP)
private Date dataDT;
private String ip;
private String country;
@Column(name = "iso_code")
private String isoCode;
private String city;
@Column(name = "coordinates", nullable = true, columnDefinition = "POINT SRID 4326")
public Point coordinates;
@Column(name = "counts_requests")
private long countsRequests;
}
Analogical request with JPA (NtpJpaRepository.java):
@Repository
public interface NtpJpaRepository extends JpaRepository<NtpSource, Long> {
@Query(nativeQuery = true, value = "SELECT "
+"u.idTable,"
+"u.ntp_id, "
+"u.dataDT, "
+"u.ip,"
+"u.country,"
+"u.iso_code,"
+"u.city,"
+"u.coordinates,"
+"SUM(u.counts_requests) as counts_requests"
+"FROM ntp_source AS u "
+"WHERE u.ntp_id IN (:ntp_id) and u.dataDT BETWEEN :startDate AND :endDate "
+"GROUP BY u.ip")
List<NtpSource> getNtpSourceWithNtpIdandDate(
@Param("ntp_id") List<Integer> ntp_id,
@Param("startDate") Date startDate,
@Param("endDate") Date endDate);
...
}
application.properties:
spring.datasource.url=jdbc:mysql://localhost/ntp
spring.datasource.username=*****
spring.datasource.password=*****
spring.sql.init.encoding=UTF-8
spring.datasource.connectionProperties=useUnicode=true;
org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect #testing
server.servlet.context-path=/ntp_visits
server.port = 8090
## Hibernate & JPA Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.jdbc.batch_size=3500
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.springframework.jdbc.core = TRACE
#logging.level.org.hibernate.SQL=DEBUG
#logging.level.org.hibernate.type=trace
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.properties.hibernate.use_sql_comments=false
# THYMELEAF (ThymeleafAutoConfiguration)
#spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
spring.thymeleaf.mode=HTML5
spring.thymeleaf.encoding=UTF-8
spring.web.resources.add-mappings=true
spring.thymeleaf.cache=true
build.gradle:
plugins {
id 'org.springframework.boot' version '2.5.3'
id 'io.spring.dependency-management' version '1.0.11.RELEASE'
id 'java'
id 'war'
}
group = 'org.vniiftri'
version = '1.0'
sourceCompatibility = '1.8'
configurations {
compileOnly {
extendsFrom annotationProcessor
}
}
configurations.all {
exclude module: 'slf4j-log4j12'
}
repositories {
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
implementation 'org.springframework.boot:spring-boot-starter-data-rest'
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'com.google.code.gson:gson:2.8.7'
implementation group: 'javax.validation', name: 'validation-api', version: '2.0.1.Final'
//implementation 'org.locationtech.jts:jts:1.18.0'
implementation 'org.locationtech.jts:jts-core:1.18.0'
//implementation 'org.n52.jackson:jackson-datatype-jts:1.2.4'
//implementation group: 'com.bedatadriven', name: 'jackson-datatype-jts', version: '2.4'
implementation 'org.hibernate:hibernate-core:5.5.5.Final'
implementation group: 'org.hibernate', name: 'hibernate-spatial', version: '5.5.5.Final'
//implementation group: 'com.graphhopper.external', name: 'jackson-datatype-jts', version: '0.10-2.5-1'
compileOnly 'org.projectlombok:lombok'
runtimeOnly 'mysql:mysql-connector-java'
annotationProcessor 'org.projectlombok:lombok'
providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
test {
useJUnitPlatform()
}
I have already solved this issue for a long time, I will publish how I did it. Maybe there is a better way.
NtpJdbcRepository.java:
@Repository
@AllArgsConstructor
public class NtpJdbcRepository implements NtpRepository {
private final NamedParameterJdbcTemplate namedJdbcTemplate;
private final PointReader pointReader;
@Override
public List<NtpSource> getNtpSource(List<Integer> ntp_id, Date startDate, Date endDate) {
String selectQuery = "SELECT "
+ "u.ntp_id, "
+ "u.ip,"
+ "u.country,"
+ "u.iso_code,"
+ "u.city,"
+ "ST_AsWKB(u.coordinates) as coordinates," // <- returns a binary result.
+ "SUM(u.counts_requests) as counts_requests "
+ "FROM ntp_source AS u "
+ "WHERE u.ntp_id IN (:ntp_ids) and u.dataDT BETWEEN :startDate AND :endDate "
+ "GROUP BY u.ip, u.ntp_id";
MapSqlParameterSource parameters = new MapSqlParameterSource()
.addValue("ntp_ids", ntp_id)
.addValue("startDate", startDate)
.addValue("endDate", endDate);
return namedJdbcTemplate.query(selectQuery,
parameters, (rs, rowNum) -> {
NtpSource ntpSource = new NtpSource();
ntpSource.setNtpId(rs.getInt("ntp_id"));
ntpSource.setIp(rs.getString("ip"));
ntpSource.setCountry(rs.getString("country"));
ntpSource.setIsoCode(rs.getString("iso_code"));
ntpSource.setCity(rs.getString("city"));
ntpSource.setCountsRequests(rs.getLong("counts_requests"));
ntpSource.setCoordinates(pointReader.read(rs.getBytes("coordinates"))); // <- get byte[]
return ntpSource;
});
}
}
PointReader.java:
@Component
@Slf4j
public class PointReader {
private static final int SRID = 4326;
private final WKTReader reader;
private final WKBReader wkbReader;
private final GeometryFactory gf;
public PointReader() {
PrecisionModel pm = new PrecisionModel(PrecisionModel.FLOATING);
gf = new GeometryFactory(pm, SRID);
wkbReader = new WKBReader(gf);
reader = new WKTReader(gf);
}
public Point read(@NotNull byte[] bytes) {
if (bytes == null) {
return null;
} else {
Geometry geometry;
try {
geometry = wkbReader.read(bytes);
return convert3Dto2D(geometry);
} catch (ParseException e) {
log.error("Ошибка при чтении Point в виде байтов.", e);
return null;
}
}
}
// need the point to have only 2 measurements, but not three
private Point convert3Dto2D(Geometry g3D) {
Coordinate geomCoord = g3D.getCoordinate().copy();
CoordinateSequence seq = new PackedCoordinateSequenceFactory().create(1, 2);
seq.setOrdinate(0, CoordinateSequence.X, geomCoord.x);
seq.setOrdinate(0, CoordinateSequence.Y, geomCoord.y);
return gf.createPoint(seq);
}
}