先看下表结构:
mysql> show create table user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`status` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from user \G
*************************** 1. row ***************************
id: 1
name: a
status: 2
mybatis的mapper.xml:
<select id="selectById" resultType="com.mamcharge.techc.action_audit.domain.User">
select * from user
where id=#{id}
</select>
<select id="selectByIdReturnMap" resultType="map">
select * from user
where id=#{id}
</select>
对应的java接口:
/**根据id查询*/
User selectById(@Param("id") Long id);
/**根据id查询*/
Map<String, Object> selectByIdReturnMap(@Param("id") Long id);
这两个方法仅仅是返回值的类型不一样,输出结果:
@Test
public void selectTest(){
User u = userMapper.selectById(1L);
System.out.println(u);
//User(id=1, name=a, status=2)
}
@Test
public void selectByIdReturnMapTest(){
Map<String, Object> map = userMapper.selectByIdReturnMap(1L);
System.out.println(map);
//{name=a, id=1, status=true}
}
可以看到,status字段在返回对象User的情况下能正确取到值,但是在返回Map的情况下,却返回了true!
我们用原始的jdbc查一下看看:
Connection connection = DriverManager.getConnection(url, user, password);
Statement stmt= connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from user where id = 1");
while(rs.next()){
System.out.println(rs.getInt("status") + "," +
rs.getBoolean("status"));
//2,true
}
status字段既可以用getInt读出来,也可以用getBoolean读出来,那么问题可能就出在mybatis在在映射ResultSet的时候!如果返回类型是User对象,mybatis是可以明确的知道status的类型是Integer,那么肯定就会掉用getInt()了,但是,如果返回类型是Map,根本不知道status的类型,那就只能用数据库字段的默认的类型了,那么默认的类型是啥呢?
while(rs.next()){
ResultSetMetaData metaData = rs.getMetaData();
System.out.println(metaData.getColumnClassName(3));//java.lang.Boolean
System.out.println(metaData.getColumnType(3));//-7
System.out.println(metaData.getColumnTypeName(3));//TINYINT
}
mysql默认的tinyint(1)的类型是Boolean,因此mybatis就会把status当成boolean来处理了。
看一下mybatis的ResultSetWrapper的处理:
public TypeHandler<?> getTypeHandler(Class<?> propertyType, String columnName) {
TypeHandler<?> handler = null;
Map<Class<?>, TypeHandler<?>> columnHandlers = typeHandlerMap.get(columnName);
if (columnHandlers == null) {
columnHandlers = new HashMap<Class<?>, TypeHandler<?>>();
typeHandlerMap.put(columnName, columnHandlers);
} else {
handler = columnHandlers.get(propertyType);
}
if (handler == null) {
//这个是bit
JdbcType jdbcType = getJdbcType(columnName);
//propertyType是Integer或者Object,得到的handler是IntegerTypeHandler或者UnknownTypeHandler
handler = typeHandlerRegistry.getTypeHandler(propertyType, jdbcType);
// Replicate logic of UnknownTypeHandler#resolveTypeHandler
// See issue #59 comment 10
// 如果handler是UnknownTypeHandler
if (handler == null || handler instanceof UnknownTypeHandler) {
final int index = columnNames.indexOf(columnName);
//这里会取metaData.getColumnClassName,也就是boolean
final Class<?> javaType = resolveClass(classNames.get(index));
if (javaType != null && jdbcType != null) {
//javaType=boolean jdbcType=bit,得到的handler是BooleanTypeHandler,最终就会调用rs.getBoolean
handler = typeHandlerRegistry.getTypeHandler(javaType, jdbcType);
} else if (javaType != null) {
handler = typeHandlerRegistry.getTypeHandler(javaType);
} else if (jdbcType != null) {
handler = typeHandlerRegistry.getTypeHandler(jdbcType);
}
}
if (handler == null || handler instanceof UnknownTypeHandler) {
handler = new ObjectTypeHandler();
}
columnHandlers.put(propertyType, handler);
}
return handler;
}
因此,千万不要设置数据库的数据类型是tinyint(1)!当我们把数据类型改成tinyint(4)的时候,再查一下看看:
mysql> alter table user modify status tinyint(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
while(rs.next()){
ResultSetMetaData metaData = rs.getMetaData();
System.out.println(metaData.getColumnClassName(3));//java.lang.Integer
System.out.println(metaData.getColumnType(3));//-6
System.out.println(metaData.getColumnTypeName(3));//TINYINT
}
还有一种解决办法是添加url参数tinyInt1isBit=false,同样可以不把tinyint(1)当成是boolean,mysql默认是把它当成boolean的,参考:https://www.jianshu.com/p/6885cad1cb14
结论:
(1)mysql的tinyint(1)和tinyint(4)实际都是占用8个bit位,千万不要设置tinyint(1),除非仅仅用到了0和1.
(2)如果设置了tinyint(1),可以在url中添加tinyInt1isBit=false来获取到实际的值,而非true、false