确认
当前程序版本
3.5.9
问题描述
需要编写一个 TypeHandler 将一个包含经纬度的 GeomPoint 对象插入到 MySQL point 类型的字段中。
public class GeomPoint {
private double longitude = 0;
private double latitude = 0;
}
转换器如下：
public class GeomPointTypeHandler extends BaseTypeHandler<GeomPoint> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, GeomPoint parameter, JdbcType jdbcType)
throws SQLException {
var pointWKT = String.format("ST_GeomFromText('POINT(%f %f)')", parameter.getLongitude(),
parameter.getLatitude());
// 使用 setObject 来避免函数调用被当作字符串传递
ps.setObject(i, pointWKT, Types.OTHER);
}
@Override
public GeomPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
return parsePoint(rs.getString(columnName));
}
@Override
public GeomPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return parsePoint(rs.getString(columnIndex));
}
@Override
public GeomPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return parsePoint(cs.getString(columnIndex));
}
private GeomPoint parsePoint(String point) {
if (StringUtils.hasText(point)) {
var coords = point.replace("POINT(", "").replace(")", "").trim().split("\\s+");
return new GeomPoint(Double.parseDouble(coords[0]), Double.parseDouble(coords[1]));
}
return null;
}
}
使用 IService.save() 方法插入实体对象时报错“Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field”，尝试过 ps.setString() 和 ps.setObject() 都不行。从 p6sy 打印的 SQL 日志来看，转换后的字段值为 'ST_GeomFromText(''POINT(0.000000 0.000000)'')' ，被当作了字符串，导致 ST_GeomFromText 函数调用不生效。
直接执行下面的 SQL 是正常的：
INSERT INTO geom_test (location) VALUE (ST_GeomFromText('POINT(0.000000 0.000000)'));
详细堆栈日志
2024-11-28T15:57:29.740+08:00 ERROR 45912 --- [server] [0.0-8080-exec-2] c.m.a.s.p.spring.GlobalExceptionHandler : org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
### The error may exist in com/muming/app/server/dao/db/UserMapper.java (best guess)
### The error may involve com.muming.app.server.dao.db.UserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO user ( username, password, nickname, location, create_time ) VALUES ( ?, ?, ?, ?, ? )
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
; Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
### The error may exist in com/muming/app/server/dao/db/UserMapper.java (best guess)
### The error may involve com.muming.app.server.dao.db.UserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO user ( username, password, nickname, location, create_time ) VALUES ( ?, ?, ?, ?, ? )
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
; Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:118) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116) ~[spring-jdbc-6.1.14.jar:6.1.14]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93) ~[mybatis-spring-3.0.4.jar:3.0.4]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:347) ~[mybatis-spring-3.0.4.jar:3.0.4]
at jdk.proxy2/jdk.proxy2.$Proxy84.insert(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:224) ~[mybatis-spring-3.0.4.jar:3.0.4]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59) ~[mybatis-plus-core-3.5.9.jar:3.5.9]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:152) ~[mybatis-plus-core-3.5.9.jar:3.5.9]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.5.9.jar:3.5.9]
at jdk.proxy3/jdk.proxy3.$Proxy87.insert(Unknown Source) ~[na:na]
at com.baomidou.mybatisplus.extension.repository.IRepository.save(IRepository.java:37) ~[mybatis-plus-extension-3.5.9.jar:3.5.9]
at com.muming.app.server.service.UserService.registerByUsername(UserService.java:71) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:379) ~[spring-tx-6.1.14.jar:6.1.14]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768) ~[spring-aop-6.1.14.jar:6.1.14]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720) ~[spring-aop-6.1.14.jar:6.1.14]
at com.muming.app.server.service.UserService$$SpringCGLIB$$0.registerByUsername(<generated>) ~[classes/:na]
at com.muming.app.server.controller.UserController.registerByUsername(UserController.java:34) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.1.14.jar:6.1.14]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.1.14.jar:6.1.14]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.1.14.jar:6.1.14]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:547) ~[jakarta.servlet-api-6.0.0.jar:6.0.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.1.14.jar:6.1.14]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614) ~[jakarta.servlet-api-6.0.0.jar:6.0.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.1.14.jar:6.1.14]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar:6.1.14]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at cn.dev33.satoken.filter.SaPathCheckFilterForJakartaServlet.doFilter(SaPathCheckFilterForJakartaServlet.java:55) ~[sa-token-spring-boot3-starter-1.39.0.jar:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.1.14.jar:6.1.14]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar:6.1.14]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.1.14.jar:6.1.14]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.1.14.jar:6.1.14]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:384) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63) ~[tomcat-embed-core-10.1.31.jar:10.1.31]
at java.base/java.lang.Thread.run(Thread.java:840) ~[na:na]
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) ~[mysql-connector-j-8.3.0.jar:8.3.0]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at com.p6spy.engine.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:362) ~[p6spy-3.9.1.jar:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75) ~[mybatis-3.5.16.jar:3.5.16]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.16.jar:3.5.16]
at jdk.proxy2/jdk.proxy2.$Proxy110.update(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.16.jar:3.5.16]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:61) ~[mybatis-3.5.16.jar:3.5.16]
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106) ~[mybatis-plus-extension-3.5.9.jar:3.5.9]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) ~[mybatis-3.5.16.jar:3.5.16]
at jdk.proxy2/jdk.proxy2.$Proxy109.update(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.16.jar:3.5.16]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.16.jar:3.5.16]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:333) ~[mybatis-spring-3.0.4.jar:3.0.4]
... 76 common frames omitted
2024-11-28T15:57:29.758+08:00 WARN 45912 --- [server] [0.0-8080-exec-2] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.DataIntegrityViolationException: <EOL>### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field<EOL>### The error may exist in com/muming/app/server/dao/db/UserMapper.java (best guess)<EOL>### The error may involve com.muming.app.server.dao.db.UserMapper.insert-Inline<EOL>### The error occurred while setting parameters<EOL>### SQL: INSERT INTO user ( username, password, nickname, location, create_time ) VALUES ( ?, ?, ?, ?, ? )<EOL>### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field<EOL>; Data truncation: Cannot get geometry object from data you send to the GEOMETRY field]
使用 p6sy 打印出来的 SQL 日志如下：
2024-11-28T15:57:29.568+08:00 INFO 45912 --- [server] [0.0-8080-exec-2] p6spy : #1732780649568 | took 5ms | statement | connection 0| url jdbc:mysql://localhost:3306/muming?connectionTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true
SELECT id,username,password,mobile,nickname,avatar_file_id,gender,birthday,location,province,city,region,wechat_unionid,settings,last_login_time,create_time,update_time,deleted FROM user WHERE deleted=0 AND (username = ?)
SELECT id,username,password,mobile,nickname,avatar_file_id,gender,birthday,location,province,city,region,wechat_unionid,settings,last_login_time,create_time,update_time,deleted FROM user WHERE deleted=0 AND (username = 'jaggerwang');
2024-11-28T15:57:29.574+08:00 INFO 45912 --- [server] [0.0-8080-exec-2] p6spy : #1732780649574 | took 1ms | statement | connection 0| url jdbc:mysql://localhost:3306/muming?connectionTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true
SELECT id,username,password,mobile,nickname,avatar_file_id,gender,birthday,location,province,city,region,wechat_unionid,settings,last_login_time,create_time,update_time,deleted FROM user WHERE deleted=0 AND (nickname = ?)
SELECT id,username,password,mobile,nickname,avatar_file_id,gender,birthday,location,province,city,region,wechat_unionid,settings,last_login_time,create_time,update_time,deleted FROM user WHERE deleted=0 AND (nickname = '天火');
2024-11-28T15:57:29.683+08:00 INFO 45912 --- [server] [0.0-8080-exec-2] p6spy : #1732780649683 | took 8ms | statement | connection 0| url jdbc:mysql://localhost:3306/muming?connectionTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true
INSERT INTO user ( username, password, nickname, location, create_time ) VALUES ( ?, ?, ?, ?, ? )
INSERT INTO user ( username, password, nickname, location, create_time ) VALUES ( 'jaggerwang', '$2a$10$wi23U287UW663Bn.Ze.oLel69S5sEl3QBna4NdsDBRfhjQDBt0pRy', '天火', 'ST_GeomFromText(''POINT(0.000000 0.000000)'')', '2024-11-28T15:57:29.672337+08:00' );
2024-11-28T15:57:29.738+08:00 INFO 45912 --- [server] [0.0-8080-exec-2] p6spy : #1732780649738 | took 1ms | rollback | connection 0| url jdbc:mysql://localhost:3306/muming?connectionTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true
**Comment From: miemieYaho**
typehandler与mp无关
**Comment From: jaggerwang**
@miemieYaho typehandler与mp无关，但 SQL 是 mp 生成的阿，问题不在 typehandler 这，它只是生成一个 SQL 片段 ST_GeomFromText('POINT(0.000000 0.000000)')，但最终在拼接为 SQL 的时候被当作一个字符串从而加上了引号。拜托在关闭 SQL 之前仔细阅读一下别人用心反馈的问题，而不是只看个 标题就关了，刚提交的问题，十分钟就给关了，那谁还来用心反馈问题。
**Comment From: miemieYaho**
建议你去再学习一遍jdbc吧,而且typehandler也不是生成sql片段的
**Comment From: jaggerwang**
跟 JDBC 有毛关系，这就是 MP 如何使用 TypeHandler 转换后的结果来动态生成 SQL 的问题。TypeHandler 生成的字符串也是 SQL 的一部分，称为 SQL 片段有什么问题，在这里钻什么字眼。
对于 MP BaseMapper 内置的那些查询方法，会使用 TypeHandler 生成的 SQL 片段来动态生成 SQL，如果是自己在 mapper 文件里或者使用注解来编写 SQL，完全可以使用 #{} 来插入参数值，这样就不会让 ST_GeomFromText('POINT(0 0)') 这样的带函数调用的 SQL 表达式被转义，但对于 MP 动态生成的 SQL，能否去控制，TypeHandler 需要做什么特殊配置。
```java
public interface YourMapper {
@Insert("INSERT INTO your_table (your_column) VALUES (#{yourParameter})")
void insertYourData(@Param("yourParameter") YourCustomType yourParameter);
}
Comment From: miemieYaho
那你说说如何在不用A的情况下能把A用错？
Comment From: jaggerwang
@TabField 注解里指定了 typeHandler 阿，这个注解不是 MP 提供的吗，这叫没用？你的意思是凡是 MP 用到的所有底层组件（MyBatis、JDBC）如果出现问题，都跟 MP 无关是吧。即便真的无关，回复的语气能否客气些，提问题的人也是在做贡献，别人花十几二十分钟用心提了一个 Issue，不到十分钟你看个标题回一句“与 MP 无关”就直接关了，谁会觉得舒服。
Comment From: miemieYaho
亲，那你愿意花十几二十分钟在搜索引擎上搜一下typehandler的setNonNullParameter在哪个地方被使用的，再学习一下那个地方的代码如何？
Comment From: jaggerwang
搜过 Google，问过 ChatGPT。从使用者的角度来说，TypeHandler 是通过 MP 的注解来指定的，SQL 也是 MP 自动生成的，那自然会觉得问题首先得从 MP 这里查起。对于大部分使用者来说对 MP 内部的实现原理并不了解，哪怕你简单阐述一下为什么跟 MP 无关都行，而不是简单粗暴的一句“无关”就直接把 Issue 给关了，我也不会有啥意见。
Comment From: miemieYaho
你还是别当程序员了，你连问题原因都找不到还在自以为是，动脑子的工作不适合你，早点改行吧别浪费大好时光了
Comment From: jaggerwang
你的意思是任何人都得靠自己解决所有问题，那提 Issue 的意义在哪？我也不相信你就那么天才，不向任何人提问求助。像你这种素质的人，还是少做开源，免得影响大家对国产开源的印象！
Comment From: miemieYaho
issue不是给你这么菜的人用来浪费别人时间的，请你有点自知之明，你也别再来杠了，像你这种又菜又不自知还嘴硬到底的不配得到好态度对待