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