Error getting nested result map values for 'company'. Cause: java.sql.SQLException: Invalid value for getInt() - 'NFHK188'
我今天遇到一个我不解的问题，是mybatis多对一关系查询出问题了，但是我自己还是解决了，在网上也查过那个错误，可是找不到我想要的。不知道你们遇到过没有，我接下来分享给大家。希望我这个第一篇博客能帮助到大家！
我定义的两个表information(航班信息表),company(航空公司表)。
CREATE TABLE `aviation`.`information` (
`id` VARCHAR(45) NOT NULL COMMENT '航班编号',
`cid` INT NOT NULL COMMENT '航空公司编号',
`start` VARCHAR(45) NULL COMMENT '出发地',
`end` VARCHAR(45) NULL COMMENT '目的地',
`start_time` TIMESTAMP NULL COMMENT '出发时间',
`seat_count` INT NULL COMMENT '座位总数',
`price` FLOAT NULL COMMENT '票价',
PRIMARY KEY (`id`))COMMENT = '航班信息表';
CREATE TABLE `aviation`.`company` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '公司编号',
`name` VARCHAR(45) NOT NULL COMMENT '公司名称',
`prefix` VARCHAR(45) NOT NULL COMMENT '公司名称前缀',
PRIMARY KEY (`id`))COMMENT = '航空公司表';
ALTER TABLE `aviation`.`information`
ADD INDEX `FK_cid_idx` (`cid` ASC);
ALTER TABLE `aviation`.`information`
ADD CONSTRAINT `FK_cid`
FOREIGN KEY (`cid`)
REFERENCES `aviation`.`company` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
select * from information;
select * from company;
insert into company(name,prefix) values('中国国航','ZGGH');
insert into company(name,prefix) values('东方航空','DFHK');
insert into company(name,prefix) values('南方航空','NFHK');
insert into company(name,prefix) values('海南航空','HNHK');
insert into company(name,prefix) values('奥凯航空','Okey');
insert into company(name,prefix) values('首都航空','SHOU');
insert into information values('ZGGH120',1,'长沙','昆明',now(),280,320);
insert into information values('ZGGH147',1,'长沙','北京',now(),280,799);
insert into information values('Okey177',5,'长沙','三亚',now(),280,589);
insert into information values('SHOU182',6,'北京','长沙',now(),280,466);
insert into information values('DFHK232',2,'上海','昆明',now(),280,556);
insert into information values('NFHK128',3,'长沙','上海',now(),280,560);
insert into information values('NFHK188',1,'长沙','北京',now(),280,799);
insert into information values('HNHK298',4,'海南','长沙',now(),280,800);
insert into information values('SHOU180',6,'北京','昆明',now(),280,490);
insert into information values('DFHK233',2,'上海','杭州',now(),280,500);
上面这个是MySQL数据库里面的一些建表，添加约束，和插入数据的sql语句。
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Information Bean(航班信息实体)
/**
*
*/
package org.hopetech.mybatis.entity;
import java.io.Serializable;
import java.sql.Timestamp;
/**
* @author Administrator 航班信息实体类（实现序列化接口）
*
*/
@SuppressWarnings("serial")
public class Information implements Serializable {
private String id;// 编号
private Integer cid;// 航空公司编号（外键）
private String start;// 出发地
private String end;// 目的地
private Timestamp startTime;// 出发时间
private Integer seatCount;// 座位个数
private Float price;// 票价
private Company company;// 所属航空公司
// 此处省略get,set方法
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Company Bean 航空公司实体
/**
*
*/
package org.hopetech.mybatis.entity;
import java.io.Serializable;
/**
* @author Administrator 航空公司实体类（实现序列化接口）
*
*/
@SuppressWarnings("serial")
public class Company implements Serializable {
private Integer id;// 编号
private String name;// 名称
private String prefix;// 前缀
// 此处省略get,set方法
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Information.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.hopetech.mybatis.dao.InformationDao">
<!-- 航班信息结果映射 -->
<resultMap type="org.hopetech.mybatis.entity.Information" id="resultInformationMap">
<id column="id" property="id"/>
<result column="cid" property="cid"/>
<result column="start" property="start"/>
<result column="end" property="end"/>
<result column="start_time" property="startTime"/>
<result column="seat_count" property="seatCount"/>
<result column="price" property="price"/>
</resultMap>
<!-- 航班信息和航空公司结果映射（多对一关联） -->
<resultMap type="org.hopetech.mybatis.entity.Information" id="resultInformationCompanyMap" extends="resultInformationMap">
<association property="company" javaType="org.hopetech.mybatis.entity.Company">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="prefix" property="prefix"/>
</association>
</resultMap>
<!-- 查询航班信息和航空公司信息（动态查询） -->
<select id="dynamicQueryInformationCompany" resultMap="resultInformationCompanyMap" parameterType="org.hopetech.mybatis.entity.Information">
select i.*,c.* from information i inner join company c on c.id = i.cid
<where>
<if test="cid!=null">and i.cid=#{cid}</if>
<if test="start!=null">and i.start=#{start}</if>
<if test="end!=null">and i.end=#{end}</if>
<if test="startTime!=null">and i.start_time=#{startTime}</if>
</where>
</select>
</mapper>
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Company.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.hopetech.mybatis.dao.ICompanyDao">
</mapper>
上面这个Company.xml映射文件只需要定义好基本格式加上接口，不需要写任何代码。
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0/EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
<typeAliases>
</typeAliases>
<environments default="MySQL5.5.28">
<environment id="MySQL5.5.28">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=UTF-8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!-- mybatis的mapper文件，每个xml配置文件对应一个接口 -->
<mappers>
<mapper resource="org/hopetech/mybatis/entity/Information.xml" />
<mapper resource="org/hopetech/mybatis/entity/Company.xml" />
</mappers>
</configuration>
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
Java代码：接口和实现类
/**
*
*/
package org.hopetech.mybatis.dao;
/**
* @author Administrator 航空公司数据访问层接口
*
*/
public interface ICompanyDao {
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
/**
*
*/
package org.hopetech.mybatis.dao;
import java.util.List;
import org.hopetech.mybatis.entity.Information;
/**
* @author Administrator 航班信息数据访问层接口
*
*/
public interface InformationDao {
/**
* 动态查询航班信息和航空公司信息
* @param Information 航班信息实体
* @return 航班信息和航空公司信息
*/
public List<Information> dynamicQuery(Information information);
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
/**
*
*/
package org.hopetech.mybatis.dao.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.hopetech.mybatis.dao.InformationDao;
import org.hopetech.mybatis.entity.Information;
import org.hopetech.mybatis.util.MyBatisUtil;
/**
* @author Administrator 航班信息数据访问层接口实现类
*
*/
public class InformationDaoImpl implements InformationDao {
/**
* 动态查询航班信息和航空公司信息
* @param Information 航班信息实体
* @return 航班信息和航空公司信息
*/
@Override
public List<Information> dynamicQuery(Information information) {
SqlSession session = MyBatisUtil.getSqlSession();
List<Information> list = session.selectList("org.hopetech.mybatis.dao.InformationDao.dynamicQueryInformationCompany", information);
MyBatisUtil.closeSqlSession();
return list;
}
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
/**
*
*/
package org.hopetech.mybatis.util;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
* @author Administrator MyBatis工具类
*/
public class MyBatisUtil {
private static final String RESOURCE = "mybatis-config.xml";
private static SqlSessionFactory sqlSessionFactory;
private static ThreadLocal<SqlSession> localSession = new ThreadLocal<SqlSession>();
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader(RESOURCE);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
sqlSessionFactory = builder.build(reader);
} catch (Exception e) {
System.err.println("建立SqlSessionFactory错误" + e);
throw new ExceptionInInitializerError(e);
}
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
public static SqlSession getSqlSession() {
SqlSession sqlSession = localSession.get();
if (sqlSession == null) {
sqlSession = (sqlSessionFactory != null) ? sqlSessionFactory.openSession() : null;
localSession.set(sqlSession);
}
return sqlSession;
}
public static void closeSqlSession() {
SqlSession sqlSession = localSession.get();
localSession.set(null);
if (sqlSession != null) {
sqlSession.close();
}
}
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
/**
*
*/
package org.hopetech.mybatis.test;
import java.util.List;
import org.hopetech.mybatis.dao.InformationDao;
import org.hopetech.mybatis.dao.impl.InformationDaoImpl;
import org.hopetech.mybatis.entity.Information;
/**
* @author Administrator 航班信息测试类
*
*/
public class InformationTest {
/**
* @param args
*/
public static void main(String[] args) {
InformationDao idi = new InformationDaoImpl();
Information information = new Information();
information.setCid(1);
List<Information> list = idi.dynamicQuery(information);
System.out.println(list.size());
for (Information info : list) {
System.out.println(info.getId());
}
}
}
<-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
运行以上代码需要加Mybatis.jar包，还有log4j.jar包，以及mysql.jar包等。
打了这么久的代码终于可以运行结果了：
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
DEBUG - Created connection 979294118.
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3a5ed7a6]
DEBUG - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@3a5ed7a6]
DEBUG - ==> Preparing: select i.*,c.* from information i inner join company c on c.id = i.cid WHERE i.cid=?
DEBUG - ==> Parameters: 1(Integer)
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.executor.ExecutorException: Error getting nested result map values for 'company'. Cause: java.sql.SQLException: Invalid value for getInt() - 'NFHK188'
### The error may exist in org/hopetech/mybatis/entity/Information.xml
### The error may involve org.hopetech.mybatis.dao.InformationDao.dynamicQueryInformationCompany-Inline
### The error occurred while setting parameters
### SQL: select i.*,c.* from information i inner join company c on c.id = i.cid WHERE i.cid=?
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting nested result map values for 'company'. Cause: java.sql.SQLException: Invalid value for getInt() - 'NFHK188'
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
at org.hopetech.mybatis.dao.impl.InformationDaoImpl.dynamicQuery(InformationDaoImpl.java:27)
at org.hopetech.mybatis.test.InformationTest.main(InformationTest.java:25)
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting nested result map values for 'company'. Cause: java.sql.SQLException: Invalid value for getInt() - 'NFHK188'
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyNestedResultMappings(DefaultResultSetHandler.java:808)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:763)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:730)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:262)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:234)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:152)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
... 3 more
Caused by: java.sql.SQLException: Invalid value for getInt() - 'NFHK188'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2728)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2816)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:60)
at com.sun.proxy.$Proxy2.getInt(Unknown Source)
at org.apache.ibatis.type.IntegerTypeHandler.getNullableResult(IntegerTypeHandler.java:34)
at org.apache.ibatis.type.IntegerTypeHandler.getNullableResult(IntegerTypeHandler.java:23)
at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:51)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createRowKeyForMappedProperties(DefaultResultSetHandler.java:898)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createRowKey(DefaultResultSetHandler.java:860)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyNestedResultMappings(DefaultResultSetHandler.java:785)
... 16 more
解决如上错误：只需要把数据库的company表id改为cid即可。不要问为什么，我也不知道，有哪位大神可以解说，尽管评论。谢谢大家的支持！
