解决mybatis实体类和数据库列名不匹配的两种办法
我们在实际开发中，会遇到实体类与数据库类不匹配的情况，在开发中就会产生各种各样的错误，那么我们应该怎么去解决这一类的错误呢？很简单，下面我们介绍两种解决方法：
首先我们看一下数据库和实体类不匹配的情况：
解决办法1
当我们查询的时候我们可以在映射文件mapper.xml中采取取别名的方式：
<select id="findAll" resultMap="cn.com.scitc.domian.User" >
select id as userId,username as userName,birthday as userBirthday,sex as userSex,address as userAddress from user
</select>
<!--配置查询所有-->
<select id="findAll" resultType="cn.com.scitc.domian.User" >
-- select id as userId,username as userName,birthday as userBirthday,sex as userSex,address as userAddress from user
select * from user;
</select>
<!--save-->
<insert id="saveUser" parameterType="cn.com.scitc.domian.User">
<!-- 配置插入操作后，获取插入数据的id keyProperty实体类 keyColum是数据库的值 order什么时候操作-->
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER" >
select last_insert_id()
</selectKey>
insert into user (username,birthday,sex,address) values (#{userName},#{userBirthday},#{userSex},#{userAddress});
</insert>
<!--update-->
<update id="updateUser" parameterType="cn.com.scitc.domian.User">
update user set username = #{userName},birthday=#{userBirthday},sex=#{userSex},address=#{userAddress} where id=#{id}
</update>
<!--delete-->
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
<!--用id 查询一个 交代清楚返回的结果-->
<select id="findById" parameterType="Integer" resultType="cn.com.scitc.domian.User">
select * from user where id = #{userId}
</select>
<!--username模糊查询-->
<select id="fingByName" parameterType="string" resultType="cn.com.scitc.domian.User">
select * from user where username like #{userName}
</select>
我们再看看接口和测试类
接口dao中
public interface UserDao {
// 查询所有
// @Select("select * from user")
List<User> findAll();
// save
void saveUser(User user);
// update
void updateUser(User user);
// delete
void deleteUser(Integer userId);
// 查询一个
User findById(Integer userId);
// 根据名称 模糊查询
List<User> fingByName(String username);
}
实现类：
public class MybatisTest {
// 初始化值
private InputStream in;
private SqlSession sqlSession;
private UserDao userDao;
// 在测试方法执行之前执行
@Before
public void init() throws Exception{
// 读取文件
in = Resources.getResourceAsStream("mybatis-Config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
userDao = sqlSession.getMapper(UserDao.class);
}
@After
public void destory() throws Exception{
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();;
in.close();
}
@Test
public void testFindAll() throws Exception{
List<User> users = userDao.findAll();
for (User user : users){
System.out.println("对象有："+user);
}
}
// saveTest
@Test
public void TestSave() {
User user = new User();
user.setUserName("modify 修改");
user.setUserBirthday(new Date());
System.out.println("保存方法之前"+user);
user.setUserSex("男");
user.setUserAddress("天津");
userDao.saveUser(user);
System.out.println("保存方法之后"+user);
}
// update
@Test
public void TestUpdate() {
User user = new User();
user.setUserId(5);
user.setUserName("aa");
user.setUserBirthday(new Date());
user.setUserSex("男");
user.setUserAddress("德阳");
userDao.updateUser(user);
}
@Test
public void TestDelete(){
userDao.deleteUser(5);
}
// findById
@Test
public void findById(){
User user = userDao.findById(3);
System.out.println(user);
}
//username模糊查询
@Test
public void TestFindByName(){
List<User> users = userDao.fingByName("%李%");
// List<User> users = userDao.fingByName("李四");
for (User user:users){
System.out.println(user);
}
}
//查询总记录数
@Test
public void findTotal(){
int count = userDao.findTotal();
System.out.println(count);
}
//username模糊查询
@Test
public void TestQuery(){
QueryVo queryVo = new QueryVo();
User user = new User();
user.setUserName("%李%");
queryVo.setUser(user);
List<User> userByVo = userDao.findUserByVo(queryVo);
for (User u:userByVo){
System.out.println(u);
}
}
}
解决办法2
配置查询结果的列名和实体类的属性名对应的关系
依然是映射文件
<!--配置查询结果的列名 和实体类的属性名对应的关系-->
<resultMap id="userMap" type="cn.com.scitc.domian.User">
<!--主键字段对应名-->
<id property="userId" column="id"></id>
<!--非主键字段的对应 property是实体类严格区分大小写 column数据库字段严格按照数据库字段 -->
<result property="userName" column="username"></result>
<result property="userBirthday" column="birthday"></result>
<result property="userSex" column="sex"></result>
<result property="userAddress" column="address"></result>
</resultMap>
我们在mapper.xml文件中配置这些配置的作用就是与数据库进行关联：
=注意
主键 ：实体类的主键id一定要和数据库的主键id写对
property：是实体类严格区分大小写
column数据库字段严格按照数据库字段
配置好这些 我们就开始引用。
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace dao权限类名===别名-->
<mapper namespace="cn.com.scitc.dao.UserDao">
<!--配置查询结果的列名 和实体类的属性名对应的关系-->
<resultMap id="userMap" type="cn.com.scitc.domian.User">
<!--主键字段对应名-->
<id property="userId" column="id"></id>
<!--非主键字段的对应 property是实体类严格区分大小写 column数据库字段严格按照数据库字段 -->
<result property="userName" column="username"></result>
<result property="userBirthday" column="birthday"></result>
<result property="userSex" column="sex"></result>
<result property="userAddress" column="address"></result>
</resultMap>
<!--配置查询所有-->
<select id="findAll" resultType="userMap" >
-- select id as userId,username as userName,birthday as userBirthday,sex as userSex,address as userAddress from user
select * from user;
</select>
<!--save-->
<insert id="saveUser" parameterType="userMap">
<!-- 配置插入操作后，获取插入数据的id keyProperty实体类 keyColum是数据库的值 order什么时候操作-->
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER" >
select last_insert_id()
</selectKey>
insert into user (username,birthday,sex,address) values (#{userName},#{userBirthday},#{userSex},#{userAddress});
</insert>
<!--update-->
<update id="updateUser" parameterType="userMap">
update user set username = #{userName},birthday=#{userBirthday},sex=#{userSex},address=#{userAddress} where id=#{id}
</update>
<!--delete-->
<delete id="deleteUser" parameterType="int">
delete from user where id = #{id}
</delete>
<!--用id 查询一个 交代清楚返回的结果-->
<select id="findById" parameterType="Integer" resultType="userMap">
select * from user where id = #{userId}
</select>
<!--username模糊查询-->
<select id="fingByName" parameterType="string" resultType="userMap">
select * from user where username like #{userName}
</select>
<!--username模糊查询 第二种写法-->
<!--<select id="fingByName" parameterType="string" resultType="cn.com.scitc.domian.User">-->
<!--select *from user where username like '%${value}%'-->
<!--</select>-->
<!--获取用户总记录条数-->
<select id="findTotal" resultType="int">
select count(id) from user
</select>
<!--根据queryVo的条件查询用户-->
<select id="findUserByVo" parameterType="cn.com.scitc.domian.QueryVo" resultType="UserMap">
select * from user where username like #{user.userName}
</select>
</mapper>
就是相当于把每一个要执行的sql语句的resultType的参数换成resultMap的值。
最后在进行测试就OK.。
