mybatis竟然报”Invalid value for getInt()”
- 2021 年 10 月 31 日
- 笔记
背景
使用mybatis
遇到一个非常奇葩的问题,错误如下:
Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'name' from result set. Cause: java.sql.SQLException: Invalid value for getInt() - 'wo'
场景
还原一下当时的情况:
public interface UserMapper {
@Results(value = {
@Result(property = "id", column = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
@Result(property = "age", column = "age", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
@Result(property = "name", column = "name", javaType = String.class, jdbcType = JdbcType.VARCHAR)
})
@Select("SELECT id, name, age FROM user WHERE id = #{id}")
User selectUser(Long id);
}
@Data
@Builder
public class User {
private Long id;
private Integer age;
private String name;
}
public class MapperMain {
public static void main(String[] args) throws Exception {
MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();
dataSource.setUser("root");
dataSource.setPassword("root");
dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8");
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = new Configuration(environment);
configuration.addMapper(UserMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper userMapper = session.getMapper(UserMapper.class);
System.out.println(userMapper.selectUser(1L));
}
}
}
数据库如下:
上面是一个很简单的例子,就是根据id
选出用户的信息,运行结果如下:
User(id=1, age=2, name=3)
没有任何问题,但是我再往数据库里插入一条数据,如下:
在MapperMain
类中增加一行代码,如下:
System.out.println(userMapper.selectUser(2L));
运行结果如下:
User(id=1, age=2, name=3)
### Error querying database. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'name' from result set. Cause: java.sql.SQLException: Invalid value for getInt() - 'lh'
……
可以看出第一条查询没有问题,第二条查询就报错了
初探
其实我的直觉告诉我,是不是因为User
类里字段顺序和SQL
语句里select字段
的顺序不一致导致的,那就来试一下吧
改一下User
类里字段的顺序:
@Data
@Builder
public class User {
private Long id;
private String name;
private Integer age;
}
结果如下:
User(id=1, name=3, age=2)
User(id=2, name=lh, age=3)
果不其然,直觉还是很6的
或者改一下SQL
语句里select字段
的顺序:
@Data
@Builder
public class User {
private Long id;
private Integer age;
private String name;
}
public interface UserMapper {
@Results(value = {
@Result(property = "id", column = "id", javaType = Long.class, jdbcType = JdbcType.BIGINT),
@Result(property = "age", column = "age", javaType = Integer.class, jdbcType = JdbcType.INTEGER),
@Result(property = "name", column = "name", javaType = String.class, jdbcType = JdbcType.VARCHAR)
})
@Select("SELECT id, age, name FROM user WHERE id = #{id}")
User selectUser(Long id);
}
以我们的直觉,结果肯定也没问题,果不其然,如下:
User(id=1, age=2, name=3)
User(id=2, age=3, name=lh)
再探
其实到上一步,问题已经解决了,可以继续干活了,但是搞不懂为什么,心里总觉得不踏实。
找bug
从debug
开始,从下面的入口开始:
追踪到如下:
可以看出User
这个类是有构造函数的,而且是包含所有字段的构造函数
利用这个构造函数创建实例的时候,参数的顺序就是SQL语句选择字段的顺序,不会根据映射关系去选择
所以就出现了类型不匹配
那我们再来看一下问什么会有一个这样的构造函数产生,直觉告诉我是@Builder
这个注解
一起来看一下User
编译后的结果:
public class User {
private Long id;
private String name;
private Integer age;
User(final Long id, final String name, final Integer age) {
this.id = id;
this.name = name;
this.age = age;
}
public static User.UserBuilder builder() {
return new User.UserBuilder();
}
public static class UserBuilder {
private Long id;
private String name;
private Integer age;
UserBuilder() {
}
public User.UserBuilder id(final Long id) {
this.id = id;
return this;
}
public User.UserBuilder name(final String name) {
this.name = name;
return this;
}
public User.UserBuilder age(final Integer age) {
this.age = age;
return this;
}
public User build() {
return new User(this.id, this.name, this.age);
}
}
}
果然如此,UserBuilder.build()
方法就是利用这个构造函数来生成的。
结局
最终解决方案就是给User
类加上无参的构造函数就OK了,如下:
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer age;
private String name;
private Long id;
}
字段顺序随便放,最后再执行一下:
User(age=2, name=3, id=1)
User(age=3, name=lh, id=2)