传入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);