I am trying to insert Geo location into MySql database using spring data JPA.
MySql Version : 5.7.17
For spring boot I have used 2.5.6 boot version and below dependency I have used
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
Below is the Entity
@Entity
@Table(name = "USER_DETAILS")
@Getter
@Setter
@Data
public class UserDetails implements Serializable {
private static final long serialVersionUID = 3937414011943770889L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private Integer id;
@Column(name = "USER_NAME")
private String userName;
@Column(name = "MOBILE_NO")
private String mobileNo;
@Column(name = "EMAIL_ID")
private String emailId;
@Column(name = "COORDINATE")
private Point point;
From java side I have used below code
public Geometry wktToGeometry(String wellKnownText) throws ParseException {
return new WKTReader().read(wellKnownText);
}
public void saveUser(String lat, String longitude) {
try {
final User user = getUserDetails();
UserDetails userDetails = new UserDetails();
userDetails.setUserName(user.getName());
userDetails.setEmailId(user.getEmail());
userDetails.setPoint( (Point) wktToGeometry("POINT (2 5)"));
userDetailsRepository.save(userDetails);
} catch (Exception e) {
System.out.println("Something went wrong!n" + e.getMessage());
}
}
But after executing above code getting below issue
Hibernate: insert into user_details (email_id, mobile_no, coordinate, user_name) values (?, ?, ?, ?)
[2m2023-02-10 11:15:19.905[0;39m [33m WARN[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m SQL Error: 1416, SQLState: 22001
[2m2023-02-10 11:15:19.906[0;39m [31mERROR[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
Table Structure
CREATE TABLE USER_DETAILS (
ID INT(10) NOT NULL AUTO_INCREMENT,
USER_NAME VARCHAR(100) DEFAULT NULL,
MOBILE_NO VARCHAR(10) DEFAULT NULL,
EMAIL_ID VARCHAR(100) DEFAULT NULL,
COORDINATE POINT NOT NULL,
PRIMARY KEY (ID),
SPATIAL INDEX (COORDINATE)
);
2
Answers
I forgot mentioned one properties in my code. Actually instead of simple MySql dialet we need to use below spatial dialet
After updating dialet it will work fine.
As it’s stated here
I think something is wrong about how you construct
Point
object, namely instead ofwktToGeometry
you should usewktToPoint
: