• Mybatis基础进阶学习2


    Mybatis基础进阶学习2

    1.测试基本结构

    2.三个POJO

    package com.pojo;
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class Orders implements Serializable {
        private static final long serialVersionUID = 1L;
    
        private Integer id;
    
        private Integer userId;
    
        private String number;
    
        private Date createtime;
    
        private String note;
    
        /**
         * 附加对象 用户对象
         */
        private User user;
    
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getUserId() {
            return userId;
        }
    
        public void setUserId(Integer userId) {
            this.userId = userId;
        }
    
        public String getNumber() {
            return number;
        }
    
        public void setNumber(String number) {
            this.number = number == null ? null : number.trim();
        }
    
        public Date getCreatetime() {
            return createtime;
        }
    
        public void setCreatetime(Date createtime) {
            this.createtime = createtime;
        }
    
        public String getNote() {
            return note;
        }
    
        public void setNote(String note) {
            this.note = note == null ? null : note.trim();
        }
    
        /**
         * 这是一个输出对象时拼接的语句
         */
        @Override
        public String toString() {
            return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
                    + ", note=" + note + "]";
        }
    }
    package com.pojo;
    
    import java.io.Serializable;
    import java.util.List;
    
    /**
     * @author: XDZY
     * @date: 2018/8/30 14:23
     * @description: 包装类(将类包装到该类里面)
     * 序列化:当一个对象要从内存中出来并且传输到别人的电脑内存时,他的状态会改变,所以要序列化
     * 反序列化:一个对象的数据传到别人电脑时要经过反序列化才不会改变状态
     */
    public class QueryVo implements Serializable {
        private static final long serialVersionUID = 1L;
    
        private User user;
    
        List<Integer> idsList;
    
        Integer[] ids;
    
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        public List<Integer> getIdsList() {
            return idsList;
        }
    
        public void setIdsList(List<Integer> idsList) {
            this.idsList = idsList;
        }
    
        public Integer[] getIds() {
            return ids;
        }
    
        public void setIds(Integer[] ids) {
            this.ids = ids;
        }
    }
    package com.pojo;
    
    import java.io.Serializable;
    import java.util.Date;
    import java.util.List;
    
    public class User implements Serializable {
        private static final long serialVersionUID = 1L;
    
        private Integer id;
        /**
         * 用户姓名
         */
        private String username;
        /**
         * 性别
         */
        private String sex;
        /**
         * 生日
         */
        private Date birthday;
        /**
         * 地址
         */
        private String address;
    
        /**
         * 附加对象ordersList
         */
        private List<Orders> ordersList;
    
        public List<Orders> getOrdersList() {
            return ordersList;
        }
    
        public void setOrdersList(List<Orders> ordersList) {
            this.ordersList = ordersList;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", sex=" + sex
                    + ", birthday=" + birthday + ", address=" + address + "]";
        }
    }

    3.用户映射器与订单映射器

    package com.mapper;
    
    import com.pojo.Orders;
    import com.pojo.User;
    
    import java.util.List;
    
    /**
     * @author: XDZY
     * @date: 2018/8/30 23:08
     * @description: 实现订单增删改查的接口(映射器)
     */
    public interface OrderMapper {
        /**
         * 查询所有订单
         *
         * @return
         */
        List<Orders> findOrderList();
    
        /**
         * 一对一关联查询(一个订单属于一个用户)
         *
         * @return
         */
        List<Orders> findOrders();
    
        /**
         * 一对多关联查询(一个用户可以有多个订单)
         *
         * @return
         */
        List<User> findUserList();
    }
    <?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">
    <!-- 命名空间:用于区分;如test.findUserById -->
    <mapper namespace="com.mapper.OrderMapper">
        <!-- 查询所有订单 -->
        <!--<select id="findOrderList" resultType="Orders">-->
    
        <!-- 当表字段名pojo属性不同时,需要手动映射 -->
        <resultMap id="orders" type="Orders">
            <!-- 当表的字段与属性一样时,可以省略不写 -->
            <result column="user_id" property="userId"/>
        </resultMap>
        <!-- select中的resultMap要对应resultMap的id -->
        <select id="findOrderList" resultMap="orders">
            select id,user_id,number,createtime,note from orders
        </select>
    
        <!-- 一对一关联查询(一个订单属于一个用户):因为有其他表的字段,所以只能用自定义映射resultMap -->
        <!-- 因为是多表查询,所以不管字段与属性名相同,也不能省略 -->
        <resultMap id="order" type="Orders">
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <!-- 映射到另一张表的字段;如果是一对一,则使用association;javaType表示java泛型 -->
            <association property="user" javaType="User">
                <id column="user_id" property="id"/>
                <result column="username" property="username"/>
            </association>
        </resultMap>
        <select id="findOrders" resultMap="order">
            select o.id,o.user_id,o.number,o.createtime,u.username
            from orders o
            left join user u
            on o.user_id=u.id;
        </select>
    
        <!-- 一对多关联查询(一个用户可以有多个订单) -->
        <resultMap id="user" type="User">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <!-- 映射到另一张表的字段;如果是一对多,则使用collection;ofType表示遍历每个元素类型 -->
            <collection property="ordersList" ofType="Orders">
                <id column="id" property="id"/>
                <result column="number" property="number"/>
            </collection>
        </resultMap>
        <select id="findUserList" resultMap="user">
            select o.id,o.user_id,o.number,o.createtime,u.username
            from user u
            left join orders o
            on o.user_id=u.id;
        </select>
    </mapper>
    package com.mapper;
    
    import com.pojo.QueryVo;
    import com.pojo.User;
    
    import java.util.List;
    
    /**
     * @author: XDZY
     * @date: 2018/8/30 10:02
     * @description: 实现用户增删改查的接口(映射器)
     * 遵循四个原则
     * 1)接口方法名与xml文件标签id一样
     * 2)方法返回值类型与xml文件一样
     * 3)方法的参数与xml文件一样
     * 4)xml文件命名空间与该接口绑定
     */
    public interface UserMapper {
        /**
         * 通过ID查询一个用户
         *
         * @param id
         * @return
         */
        User findUserById(Integer id);
    
        /**
         * 根据用户名模糊查询用户增强版
         *
         * @param vo
         * @return
         */
        List<User> findUserByQueryVo(QueryVo vo);
    
        /**
         * 查询用户数量
         *
         * @return
         */
        Integer findCountUser();
    
        /**
         * 根据性别和名字查询用户
         *
         * @param user
         * @return
         */
        List<User> findUserByNameAndSex(User user);
    
        /**
         * 根据多个ID查询用户(3种写法)
         *
         * @param vo
         * @return
         */
        //public List<User> findUserByIds(Integer[] ids);
        //public List<User> findUserByIds(List<Integer> ids);
        List<User> findUserByIds(QueryVo vo);
    }
    <?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">
    <!-- 命名空间:用于区分;如test.findUserById -->
    <mapper namespace="com.mapper.UserMapper">
        <!-- 通过ID查询一个用户 -->
        <select id="findUserById" parameterType="Integer" resultType="User">
            <!-- #{}:表示占位符 -->
            select * from user where id=#{v}
        </select>
    
        <!-- 根据用户名模糊查询用户 -->
        <select id="findUserByName" parameterType="String" resultType="com.pojo.User">
            <!-- ${}:表示字符串拼接 -->
            -- select * from user where username like '%${value}%'
            <!-- 防sql注入 -->
            select * from user where username like "%"#{value}"%"
        </select>
    
        <!-- 添加用户 -->
        <insert id="addUser" parameterType="com.pojo.User">
            <!-- 获取最新的ID主键 -->
            <selectKey keyProperty="id" resultType="Integer" order="AFTER">
                select LAST_INSERT_ID()
            </selectKey>
            insert into user (username,birthday,address,sex)
            values (#{username},#{birthday},#{address},#{sex})
        </insert>
    
        <!-- 更新用户 -->
        <update id="updateUserById" parameterType="com.pojo.User">
            update user
            set username=#{username},birthday=#{birthday},address=#{address},sex=#{sex}
            where id=#{id}
        </update>
    
        <!-- 删除用户 -->
        <delete id="delUserById" parameterType="Integer">
            delete from user where id=#{id}
        </delete>
    
        <!-- 根据用户名模糊查询用户增强版,当要改变查询条件时,要改动的代码减少 -->
        <select id="findUserByQueryVo" parameterType="QueryVo" resultType="com.pojo.User">
            <!-- 防sql注入 -->
            select * from user where username like "%"#{user.username}"%"
        </select>
    
        <!-- 查询用户数量 -->
        <select id="findCountUser" resultType="Integer">
            select count(1) from user
        </select>
    
        <!-- sql片段的使用:将重复要写的代码放这里 -->
        <sql id="sqlCopy">
            select * from user
        </sql>
    
        <!-- 根据性别和名字查询用户 -->
        <!-- where标签可以去掉前and;因为如果sex为空,则and在前面会报sql语法错误 -->
        <select id="findUserByNameAndSex" parameterType="User" resultType="User">
            <include refid="sqlCopy"/>
            <where>
                <if test="sex!=null and sex!=''">
                    sex=#{sex}
                </if>
                <if test="username!=null and username!=''">
                    and username=#{username}
                </if>
            </where>
        </select>
    
        <!-- 根据多个ID查询用户(1,2,3) -->
        <!-- separator:表示分隔符 -->
        <!-- 坑:Integer[]的collection为array;List<Integer>的collection为list -->
        <select id="findUserByIds" parameterType="QueryVo" resultType="User">
            <include refid="sqlCopy"/>
            <where>
                <foreach collection="idsList" item="id" separator="," open=" id in (" close=")">
                    #{id}
                </foreach>
            </where>
        </select>
    </mapper>

    4.单元测试

    package com.junit;
    
    import com.mapper.OrderMapper;
    import com.mapper.UserMapper;
    import com.pojo.Orders;
    import com.pojo.QueryVo;
    import com.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.Test;
    
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author: XDZY
     * @date: 2018/8/30 10:15
     * @description: mybatis简单查询
     * 动态代理对象会根据mapper接口方法的返回值确定调用selectOne,selectList还是其他方法
     * 动态代理模式:就相当于你要去找人,告诉一个代理,他帮你找到
     */
    public class MapperTest {
        /**
         * 根据用户ID查询用户信息
         *
         * @throws Exception
         */
        @Test
        public void testMapper() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    
            User user = mapper.findUserById(10);
            System.out.println(user);
        }
    
        /**
         * 根据提供的包装类里用户名模糊查询用户信息
         *
         * @throws Exception
         */
        @Test
        public void testMapperQueryVo() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    
            QueryVo vo = new QueryVo();
            User user = new User();
            user.setUsername("五");
            vo.setUser(user);
            List<User> users = mapper.findUserByQueryVo(vo);
            for (User user1 : users) {
                System.out.println(user1);
            }
        }
    
        /**
         * 查询用户数量
         *
         * @throws Exception
         */
        @Test
        public void testMapperUserCount() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    
            int i = mapper.findCountUser();
            System.out.println(i);
        }
    
        /**
         * 查询所有订单
         *
         * @throws Exception
         */
        @Test
        public void testMapperOrderList() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    
            List<Orders> list = mapper.findOrderList();
            for (Orders order : list) {
                System.out.println(order);
            }
        }
    
        /**
         * 根据性别和名字查询用户
         *
         * @throws Exception
         */
        @Test
        public void testMapperFindUserByNameAndSex() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    
            User user = new User();
            user.setUsername("xdzy");
            user.setSex("男");
            List<User> users = mapper.findUserByNameAndSex(user);
            for (User user1 : users) {
                System.out.println(user1);
            }
        }
    
        /**
         * 根据多个ID查询用户(1,2,3)
         *
         * @throws Exception
         */
        @Test
        public void testMapperfindUserByIds() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    
            List<Integer> ids = new ArrayList<Integer>();
            ids.add(16);
            ids.add(22);
            ids.add(27);
            QueryVo vo = new QueryVo();
            vo.setIdsList(ids);
            List<User> users = mapper.findUserByIds(vo);
            for (User user1 : users) {
                System.out.println(user1);
            }
        }
    }
    package com.junit;
    
    import com.mapper.OrderMapper;
    import com.pojo.Orders;
    import com.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.Test;
    
    import java.io.InputStream;
    import java.util.List;
    
    /**
     * @author: XDZY
     * @date: 2018/8/30 10:15
     * @description: mybatis多表查询
     */
    public class MapperTest2 {
        /**
         * 一对一关联查询
         *
         * @throws Exception
         */
        @Test
        public void testMapperOrders() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    
            List<Orders> ordersList = mapper.findOrders();
            for (Orders order : ordersList) {
                System.out.println(order);
            }
        }
    
        /**
         * 一对多关联查询
         *
         * @throws Exception
         */
        @Test
        public void testMapperUserList() throws Exception {
            //加载核心配置文件
            String resource = "sqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            //创建sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //sqlSession帮我们生成一个实现类给接口
            OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    
            List<User> userList = mapper.findUserList();
            for (User user : userList) {
                System.out.println(user);
            }
        }
    }
  • 相关阅读:
    web端
    vue 键盘事件keyup/keydoen
    APiCloud
    APiCloud
    对于HTML和XML的理解
    JS 的三种定义变量 var let const
    jQuery
    NodeJS 阻塞/非阻塞
    NodeJs REPL交互式解析器常用命令
    用NodeJS创建一个聊天服务器
  • 原文地址:https://www.cnblogs.com/xdzy/p/9565950.html
Copyright © 2020-2023  润新知