Mybatis进阶-输出结果封装resultType和resultMap

2023-10-29 14:14:04

Mybatis 的输出结果封装

1.resultType 配置结果类型

1.1基本类型示例

List<User> findAll();
User findById(Integer userId);
List<User> findByName(String username);
int findTotal();
List<User> findUserByVo(QueryVo vo);

    <select id="findAll" resultType="cn.lj.pojo.User">
        select * from user
    </select>

    <select id="findById" parameterType="int" resultType="cn.lj.pojo.User">
        select * from user where id=#{uid}
    </select>

    <select id="findByName" parameterType="string" resultType="cn.lj.pojo.User">
        select * from user where username like #{name}
    </select>
    <select id="findTotal" resultType="int">
        select count(id) from user
    </select>

    <select id="findUserByVo" parameterType="cn.lj.pojo.QueryVo" resultType="cn.lj.pojo.User">
        select * from user where username like #{user.username}
    </select>

1.2特殊情况示例-当实体类属性和数据库表的列名已经不一致了

public class User implements Serializable {
    private Integer userId;
    private String userName;
    private Date userBirthday;
    private String userSex;
    private String userAddress;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Date getUserBirthday() {
        return userBirthday;
    }

    public void setUserBirthday(Date userBirthday) {
        this.userBirthday = userBirthday;
    }

    public String getUserSex() {
        return userSex;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userBirthday=" + userBirthday +
                ", userSex='" + userSex + '\'' +
                ", userAddress='" + userAddress + '\'' +
                '}';
    }
}

package cn.lj.test;

import cn.lj.dao.UserDao;
import cn.lj.pojo.QueryVo;
import cn.lj.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

/**
 * @author lj
 * @Description:
 * @date 2020-11-24 16:20
 * @QQ 851477108
 */


public class MybatisTest {

    private InputStream in;
    private SqlSession sqlSession;
    private UserDao userDao;

    @Before//用于在测试方法执行之前执行
    public void init() throws IOException {
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //2.创建SqlSessionFactory工厂对象,获得数据源配置信息
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3.工厂对象根据信息创建SqlSession对象
        sqlSession = factory.openSession();
        //4.使用SqlSession创建Dao接口的动态代理对象
        userDao = sqlSession.getMapper(UserDao.class);
    }

    @After    //用于在测试方法执行之后执行
    public void destroy()throws Exception{
        //提交事务,如果不提交,会回滚操作,表数据直接跳一行
        sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }

    @Test
    public void testFindAll() {
        //5.使用代理对象执行方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

    @Test
    public void testSave(){
        User user = new User();
        user.setUserName("lj-selectKey-实体属性名与表列名不同2");
        user.setUserAddress("上海");
        user.setUserSex("男");
        user.setUserBirthday(new Date());
        System.out.println("保存操作之前："+user);
        userDao.saveUser(user);
        System.out.println("保存操作之后："+user);
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setUserId(50);
        user.setUserName("lj-update3");
        user.setUserAddress("上海");
        user.setUserSex("男");
        user.setUserBirthday(new Date());
        userDao.updateUser(user);
        System.out.println("保存操作之后："+user);
    }

    @Test
    public void testDelete(){
        userDao.deleteUser(49);
    }

    @Test
    public void testFindOne(){
        User user = userDao.findById(48);
        System.out.println(user);
    }

    @Test
    public void testFindByName(){
        List<User> users = userDao.findByName("%王%");
//        List<User> users = userDao.findByName("王");
        for (User user : users) {
            System.out.println(user);
        }
    }


    @Test
    public void testFindTotal(){
        int count = userDao.findTotal();
        System.out.println(count);
    }

    @Test
    public void testFindByVo(){
        QueryVo queryVo = new QueryVo();
        User user = new User();
        user.setUserName("%王%");
        queryVo.setUser(user);
        List<User> users = userDao.findUserByVo(queryVo);
        for (User u : users) {
            System.out.println(u);
        }
    }
}

<?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属性必须是dao接口的全限定类名-->
<mapper namespace="cn.lj.dao.UserDao">
<!--映射配置文件的操作配置select,id属性必须dao接口的方法名，resultType是返回结果集类型-实体类的全限定类名-->
    <select id="findAll" resultType="cn.lj.pojo.User">
        select * from user
    </select>
    
    <!--parameterType是参数集类型-实体类的全限定类名-->
    <insert id="saveUser" parameterType="cn.lj.pojo.User">
        <!-- 配置插入操作后，获取插入数据的id -->
        <selectKey keyProperty="userId" keyColumn="id" resultType="int" order="AFTER">
            select last_insert_id()
        </selectKey>
        insert into user(username,address,sex,birthday)values(#{userName},#{userAddress},#{userSex},#{userBirthday});
    </insert>
    
    <update id="updateUser" parameterType="cn.lj.pojo.User">
        update user set username=#{userName},address=#{userAddress},sex=#{userAex},birthday=#{userBirthday} where id=#{userId}
    </update>

    <delete id="deleteUser">
        delete from user where id=#{id}
    </delete>

    <select id="findById" parameterType="int" resultType="cn.lj.pojo.User">
        select * from user where id=#{uid}
    </select>

    <select id="findByName" parameterType="string" resultType="cn.lj.pojo.User">
        select * from user where username like #{name}
    </select>

    <select id="findTotal" resultType="int">
        select count(id) from user
    </select>

    <select id="findUserByVo" parameterType="cn.lj.pojo.QueryVo" resultType="cn.lj.pojo.User">
        select * from user where username like #{user.userName}
    </select>
</mapper>

除了查询，其他操作基本无问题

1.3查询，会出现只有userName有值是因为MySQL在window下不区分大小写

笨方法：as取别名，速度最快，sql解决问题

<select id="findAll" resultType="cn.lj.pojo.User">
    select id as userId,username as userName,address as userAddress,sex as userSex,birthday as userBirthday from user;
</select>

2.resultMap 结果类型

resultMap 标签可以建立查询的列名和实体类的属性名称不一致时建立对应关系。从而实现封装。
在 select 标签中使用 resultMap 属性指定引用即可。
同时 resultMap 可以实现将查询结果映射为复杂类型的 pojo，比如在查询结果映射对象中包括 pojo 和 list 实现一对一查询和一对多查询。

2.1定义 resultMap

id 标签：用于指定主键字段

result 标签：用于指定非主键字段

column 属性：用于指定数据库列名

property 属性：用于指定实体类属性名称

<!-- 配置 查询结果的列名和实体类的属性名的对应关系,建立 User 实体和数据库表的对应关系 -->
<resultMap id="userMap" type="cn.lj.pojo.User">
    <!-- 主键字段的对应,id 标签：用于指定主键字段,property 属性：用于指定实体类属性名称,column 属性：用于指定数据库列名-->
    <id property="userId" column="id"/>
    <!--非主键字段的对应,result 标签：用于指定非主键字段,-->
    <result property="userName" column="username"/>
    <result property="userAddress" column="address"/>
    <result property="userSex" column="sex"/>
    <result property="userBirthday" column="birthday"/>
</resultMap>

2.2映射配置

<select id="findAll" resultMap="userMap">
    select * from user
</select>

查询结果和1.3一致，resultMap避免重复使用别名，大大增加开发效率

码农公寓

Mybatis 的输出结果封装

1.resultType 配置结果类型

1.1基本类型示例

1.2特殊情况示例-当实体类属性和数据库表的列名已经不一致了

1.3查询，会出现只有userName有值是因为MySQL在window下不区分大小写

2.resultMap 结果类型

2.1定义 resultMap

2.2映射配置

相关文章