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); } } }