Mybatis——传入POJO对象，动态SQL，多表关系，与Spring整合
传入POJO对象
QueryVo.java
public class QueryVo implements Serializable{
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
SQL映射文件
<!-- 传入POJO对象，查询用户 -->
<select id="findUserByPOJO" parameterType="QueryVo" resultType="User">
select * from user where id = #{user.id}
</select>
测试类
@Test
public void func9() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 4.SqlSEssion帮我生成一个实现类（给接口的字节码文件）
UserDao mapper = sqlSession.getMapper(UserDao.class);
// 创建一个POJO对象
QueryVo vo = new QueryVo();
User u = new User();
u.setId(2);
vo.setUser(u);
User userByPOJO = mapper.findUserByPOJO(vo);
System.out.println(userByPOJO);
// -------------------------------------------
}
resultMap（手动指定字段）
数据库表
Order实体文件
public class Order {
// 订单id
private int id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
get/set。。。
}
SQL映射文件
由于实体和SQL中的userid字段不同，所以要使用resultMap
<resultMap id="jzy" type="Order">
<result column="user_id" property="userId" />
</resultMap>
<select id="findOrderById" parameterType="int" resultMap="jzy">
select * from `order` where id = #{id}
-- order是关键字，所以要用``包起来
</select>
OrderDao.java
public interface OrderDao {
Order findOrderById(int i);
}
测试类
public void func() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 4.执行相应的SQL语句
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
Order orderById = mapper.findOrderById(1);
System.out.println(orderById);
// -------------------------------------------
}
一.动态SQL
1）if标签&&where标签
<!-- 根据性别和名字查询用户 where 可以去掉第一个前ANd -->
<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
<where>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
</where>
</select>
2）foreach标签
a.直接传入数组
UserDao.java
public interface UserDao {
List<User> selectUserByIds(int[] ids);
}
配置文件
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="int[]" resultType="User">
<include refid="selector"/>
<where>
<!--只要是传入的数组，collection就是"array"-->
<foreach collection="array" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
@Test
public void func3() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
// 方法一：直接传入一个数组
int[] ids = {1,2,3};
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> list = mapper.selectUserByIds(ids);
System.out.println(list);
// -------------------------------------------
}
b.直接传入集合
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="Integer" resultType="User">
<include refid="selector"/>
<where>
<!--只要是传入的集合，collection就是"list"-->
<foreach collection="list" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
// 方法二：直接传入一个集合 List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(2); ids.add(3); UserDao mapper = sqlSession.getMapper(UserDao.class); List<User> list = mapper.selectUserByIds(ids); System.out.println(list);
c.传入一个POJO，里面有集合或者数组
<!-- 多个ID select * from user where id in (1,2,3)-->
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<include refid="selector"/>
<where>
<foreach collection="ids" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>
测试类
// 方法三：传入一个POJO
//List<Integer> ids = new ArrayList<>();
//
//ids.add(1);
//ids.add(2);
//ids.add(3);
int[] ids = {1,2,3};
QueryVo vo = new QueryVo();
vo.setIds(ids);
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> list = mapper.selectUserByIds(vo);
System.out.println(list);
二.关联查询
1、一对一查询
1）在Order中添加一个user字段并提供get，set方法
2）书写接口
List<Order> findOrderList();
3）书写配置文件
<resultMap id="jbb" type="Order"> <id column="id" property="id"/> <result column="user_id" property="userId" /> <result column="number" property="number" /> <!-- 一对一配置 --> <association property="user" javaType="User"> <id column="user_id" property="id" /> <result column="username" property="username"/> </association> </resultMap> <select id="findOrderList" resultMap="jbb"> select o.id,o.user_id,o.number,o.createtime,u.username from `order` o left join user u on o.user_id=u.id </select>
4）测试类
public void func4() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
List<Order> orderList = mapper.findOrderList();
System.out.println(orderList);
// -------------------------------------------
}
2、一对多查询
1）在User中添加一个集合，保存Order对象
2）书写接口
List<User> findUserAllOrder();
3）SQL映射文件
<resultMap id="jjj" type="User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <!-- 一对多 --> <collection property="orderList" ofType="Order"> <id column="id" property="id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="user_id" property="userId"/> </collection> </resultMap> <select id="findUserAllOrder" resultMap="jjj"> select o.id,o.user_id,o.number,o.createtime,u.username from user u left join `order` o on o.user_id=u.id </select>
测试类
@Test
public void func5() throws IOException {
// 1.加载配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
// 2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.获取sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();
// -------------------------------------------
OrderDao mapper = sqlSession.getMapper(OrderDao.class);
List<User> userAllOrderList = mapper.findUserAllOrder();
System.out.println(userAllOrderList);
// -------------------------------------------
}
三.与Spring整合
方式一：原始Dao方式
1）Spring配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd ">
<!-- 读取配置文件 -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- 设置数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="10" />
<property name="maxIdle" value="5" />
</bean>
<!-- 建立Mybatis的SQLSessionFactory工厂 -->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入连接池 -->
<property name="dataSource" ref="dataSource"/>
<!-- 核心配置文件的位置 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
<!-- Dao原始Dao -->
<bean id="userDao" class="cn.x5456.dao.UserDaoImpl">
<property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
</bean>
</beans>
2）UserDao.java
// 继承SqlSessionDaoSupport，调用父类的getSqlSession方法
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
public void main(String[] args) {
super.getSqlSession().selectList("xxx.xxx","参数");
}
}
方式二：mapper代理模式
1）Spring配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd ">
<!-- 读取配置文件 -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- 设置数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="maxActive" value="10" />
<property name="maxIdle" value="5" />
</bean>
<!-- 建立Mybatis的SQLSessionFactory工厂 -->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入连接池 -->
<property name="dataSource" ref="dataSource"/>
<!-- 核心配置文件的位置 -->
<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
</bean>
<!-- Mapper动态代理开发 -->
<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
<property name="mapperInterface" value="cn.x5456.dao.UserMapper"/>
</bean>
</beans>
2）Mybatis主配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 设置别名 --> <typeAliases> <!-- 指定扫描包，会把包内所有的类都设置别名，别名的名称就是类名，大小写不敏感 --> <package name="cn.x5456.domain" /> </typeAliases> <mappers> <package name="cn.x5456.dao"/> </mappers> </configuration>
3）SQL映射关系配置文件
<?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="cn.x5456.dao.UserMapper"> <!-- 将当前接口与接口绑定 -->
<!-- 通过ID查询一个用户 -->
<select id="findUserById" parameterType="Integer" resultType="User">
select * from user where id = #{id}
</select>
</mapper>
4）UserMapper接口
public interface UserMapper {
User findUserById(Integer id);
}
5）测试类
public void func(){
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("spring-config.xml");
UserMapper userMapper = (UserMapper) ac.getBean("userMapper");
User userById = userMapper.findUserById(1);
System.out.println(userById);
}
方式三：动态扫描
上面的会有一个弊端，如果Mapper太多，配置文件就变得很多。所以引入扫描
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <!-- 基本包 --> <property name="basePackage" value="com.itheima.mybatis.mapper"/> </bean>
获取mapper方法
UserMapper userMapper = ac.getBean(UserMapper.class);
