mybatis笔记02

0. 文章目录
1. Mybatis映射文件
2. 动态SQL
4. 关联查询

0. 文章目录

Mybatis映射文件
动态SQL
关联查询

1. Mybatis映射文件

映射文件中select的两个参数重要参数：parameter和resultType
parameter：输入映射，定义输入到sql中的映射类型，${value}表示使用参数将${value}替换，做字符串拼接。如果是取简单数量类型的参数，括号中的值必须为value。
resultType：输出映射，定义结果映射类型。

1.1 输入映射

输入映射支持的数据类型：
- 基本数据类型：基础类型以及包装类、String
- POJO
- Map
- 包装的POJO：一个pojo中有pojo属性

输入映射参数为map类型举例

案例预览

/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml

<?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="com.itzhouq.mybatis.mapper.UserMapper">
	<!-- statementId -->
	<select id="findById" parameterType="int" resultType="com.itzhouq.mybatis.pojo.User" >
		select * from user where id = #{id}
	</select>
	
	<insert id="insertUser" parameterType="com.itzhouq.mybatis.pojo.User">
		<selectKey resultType="int" keyProperty="id" order="AFTER">
			<!-- 返回当前事务最后产生的id值 -->
			SELECT LAST_INSERT_ID();
		</selectKey>
		insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address});
	</insert>
	<select id="findUserByMap" parameterType="map" resultType="com.itzhouq.mybatis.pojo.User">
		select * from user where username like '%${username}%' and sex = #{sex} and address=#{address}
	</select>
	
</mapper>

/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
	public User findById(int id);
	
	public void insertUser(User user);
	//映射参数为map类型
	public List<User> findUserByMap(Map<String, Object> map);
}

/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java

package com.itzhouq.mybatis.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;

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.Before;
import org.junit.Test;

import com.itzhouq.mybatis.mapper.UserMapper;
import com.itzhouq.mybatis.pojo.User;

public class UserMapperTest {
	SqlSessionFactory sqlSessionFactory = null;
	@Before
	public void init() throws IOException {
		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
		sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
	}
	
	@Test
	public void testFindById() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		User user = mapper.findById(28);
		System.out.println(user);
		sqlSession.close();
	}
	
	@Test
	public void testFindByMap() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		HashMap<String, Object> map = new HashMap<String, Object>();
//		select * from user where username like '%张%' and sex = '1' and address='河南郑州'
//		select * from user where username like '%${username}%' and sex = #{sex} and address=#{address}
		map.put("username", "张");
		map.put("sex", "1");
		map.put("address", "河南郑州");
		
		List<User> users= mapper.findUserByMap(map);
		System.out.println(users);
		sqlSession.close();
	}
	
}

测试testFindByMap得到两个user对象

输入映射参数为包装的POJO类型举例

在pojo中新建一个类QueryVo

/mybatis-day02/src/com/itzhouq/mybatis/pojo/QueryVo.java

package com.itzhouq.mybatis.pojo;

public class QueryVo {
	private User user;

	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
}

接口中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
	public User findById(int id);
	
	public void insertUser(User user);
	//映射参数为map类型
	public List<User> findUserByMap(Map<String, Object> map);
	//映射参数为包装的POJO类型
	public List<User> findUserByQueryVo(QueryVo queryVo);

}

测试文件中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml添加


	<select id="findUserByQueryVo" parameterType="queryVo" resultType="user">
	<!-- queryVo
			user
				username -->
		select * from user where username like '%${user.username}%'
	</select>

测试类/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java

@Test
	public void testFindByQueryVo() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		QueryVo queryVo = new QueryVo();
		User user = new User();
		user.setUsername("张");
		queryVo.setUser(user);
		List<User> users= mapper.findUserByQueryVo(queryVo);
		System.out.println(users);
		sqlSession.close();
	}

1.2 输出映射

输出映射支持的数据类型：
- 基本数据类型：基础类型以及包装类、String
- POJO
- Map
- List

输出映射参数为map类型举例

接口com.itzhouq.mybatis.mapper.UserMapper

package com.itzhouq.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public interface UserMapper {
	public User findById(int id);
	public void insertUser(User user);
	//映射参数为map类型
	public List<User> findUserByMap(Map<String, Object> map);
	//映射参数为包装的POJO类型
	public List<User> findUserByQueryVo(QueryVo queryVo);
	//输出映射类型为int型
	public int findUserCount();
	//输出映射参数为map类型
	public Map findById2(int id);
	
}

映射配置文件/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml中添加

<select id="findUserCount" resultType="int">
		select count(*) from user;
</select>

测试类

@Test
	public void testFindById2() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		Map map = mapper.findById2(28);
		System.out.println(map);
		//{birthday=2019-04-09, address=芭蕉洞, sex=0, id=28, username=玉面狐狸}
		sqlSession.close();
	}

1.3 resultMap

resultMap作用：当实体类的属性名和表的字段名不一致时，需要手动映射。

复制数据库中的user表为user_copy1，更改其中的username和sex字段，这时候这两个字段和实体类User中的属性名就不一致了。

更改后的表和类需要正常映射的话，需要手动配置resultMap

<resultMap id="findById3ResultMap" type="user">
    <id column="id" property="id"/>
    <result column="user_name" property="username"/>
    <result column="gender" property="sex"/>
    <!-- <result column="birthday" property="birthday"/>
  <result column="address" property="address"/> -->
</resultMap>

<select id="findById3" parameterType="int" resultMap="findById3ResultMap" >
    select * from user_copy1 where id = #{id}
</select>

字段相同的列可以省略不用手动配置

2. 动态SQL

2.1 if和where

需求分析：实际开发过程中，会用到组合条件查询，条件的个数是不固定的，这就要求sql语句中的条件根据传入的参数动态改变。为此，mybatis引入了where和if标签产生动态sql。

示例：

UserMapper.java文件中添加一个方法

//动态Sql
public List<User> findListByUser(User user);

xml配置文件

<select id="findListByUser" parameterType="user" resultType="user">
    select * from user
    <where>
        <if test="username!=null and username!=''">
            username like '%${username}%'
        </if>
        <if test="sex!=null and sex!=''">
            and sex = #{sex}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
    </where>
</select>

if作用：判断
where作用：取出第一个and或者or

测试

@Test
public void testFindListByUser() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    //		user.setUsername("张");
    user.setSex("1");
    user.setAddress("河南郑州");
    List<User> list= mapper.findListByUser(user);
    System.out.println(list);
    sqlSession.close();
}

mybatis可以根据user中条件个数动态生成sql语句

比如上面代码中两个条件下，生成的sql语句为：

DEBUG [main] - ==>  Preparing: select * from user WHERE sex = ? and address=?

2.2 foreach循环

需求分析：要查询用户id为1 16 22 的用户信息，相对应的sql语句为 SELECT * FROM user where id in (1, 16, 22)。查询的参数是数组，把这个数组放在包装类中传递。

接口文件中添加

//动态sql，foreach循环
public List<User> findListByArray(QueryVo queryVo);

包装类中添加数组ids

package com.itzhouq.mybatis.pojo;

public class QueryVo {
    private User user;

    int[] ids;

    public int[] getIds() {
        return ids;
    }
    public void setIds(int[] ids) {
        this.ids = ids;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
}

映射配置文件

<select id="findListByArray" parameterType="queryVo" resultType="user">
    <!-- queryVo -->
    <!-- ids = [ 1, 16, 22] -->
    <!-- SELECT * FROM `user` where id in (1, 16, 22) -->
    <!-- select * from user where id in -->
    SELECT * FROM `user` where id in 
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</select>

测试类

@Test
public void testFindListByArray() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    QueryVo queryVo = new QueryVo();
    int[] ids = {1, 16, 22};
    queryVo.setIds(ids);
    List<User> list= mapper.findListByArray(queryVo);
    System.out.println(list);
    sqlSession.close();
}

生成的sql语句

DEBUG [main] - ==>  Preparing: SELECT * FROM user where id in ( ? , ? , ? )

2.3 sql片段 sql+include

sql片段主要是用于xml文件中的代码复用

<select id="findListByUser" parameterType="user" resultType="user">
    select * from user
    <include refid="findUserWhere"></include>
</select>
<sql id="findUserWhere">
    <where>
        <if test="username!=null and username!=''">
            username like '%${username}%'
        </if>
        <if test="sex!=null and sex!=''">
            and sex = #{sex}
        </if>
        <if test="address!=null and address!=''">
            and address=#{address}
        </if>
    </where>
</sql>

2.4 set

需求分析：在更新数据库操作时，有可能只是更改某几个字段，传递的数据类型为user时，如果user的某些属性没有赋值，相当于把这些数据库中的这些字段设为null或者默认值，这样不符合我们的需求。这就需要判断user中的属性是否为空了。但是结合sql语句，修改操作时，每个字段后面又一个逗号。这里需要需要使用set去掉逗号，保证生成sql语句的正确性。

接口代码

//动态sql，set
public void updateUser(User user);

映射文件中添加

<update id="updateUser" parameterType="user">
    <!-- update user set username=#{username},sex=#{sex},
   birthday=#{birthday},address=#{address} where id=#{id} -->
    update user
    <set>
        <if test="username != null and username != ''">
            username=#{username},
        </if>
        <if test="sex != null and sex != ''">
            sex=#{sex},
        </if>
        <if test="birthday != null">
            birthday=#{birthday},
        </if>
        <if test="address != null and address != ''">
            address=#{address},
        </if>
    </set>
    where id=#{id}
</update>

测试类

@Test
public void testUpdateUser() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = new User();
    user.setId(28);
    user.setUsername("铁扇公主");
    mapper.updateUser(user);
    sqlSession.commit();
    sqlSession.close();
}

生成的sql语句

DEBUG [main] - ==>  Preparing: update user SET username=? where id=?

4. 关联查询

4.1 商品订单数据模型

商品订单数据模型

4.2 一对一查询

案例：查询所有订单信息，关联查询下单用户信息
因为一个订单信息只会是一个人下的订单，所以从查询订单信息出发关联用户信息为一对一查询。如果从用户信息出发下的订单则会为一对多查询，因为一个用户可以下多个订单。

4.2.1 方法一：

使用resultType，定义订单信息pojo类，此pojo类中包括了订单信息和用户信息。

sql语句

select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id

新建pojo类，OrdersUser，这个类中应该包括sql查询出来的所有字段

public class OrdersUser {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;

OrdersMapper.xml文件

<?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="com.itzhouq.mybatis.mapper.OrdersMapper">
    <!-- statementId -->
    <select id="findOrdersUserList" resultType="ordersUser" >
        select o.*,u.username,u.address,u.birthday,u.sex from
        orders o left join user u on o.user_id = u.id
    </select>
</mapper>

测试类OrdersMapperTest.java

package com.itzhouq.mybatis.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.Before;
import org.junit.Test;

import com.itzhouq.mybatis.mapper.OrdersMapper;
import com.itzhouq.mybatis.mapper.UserMapper;
import com.itzhouq.mybatis.pojo.OrdersUser;
import com.itzhouq.mybatis.pojo.QueryVo;
import com.itzhouq.mybatis.pojo.User;

public class OrdersMapperTest {
    SqlSessionFactory sqlSessionFactory = null;
    @Before
    public void init() throws IOException {
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
    }

    @Test
    public void testFindOrdersUserList() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<OrdersUser> ordersUserList = mapper.findOrdersUserList();
        System.out.println(ordersUserList);
        sqlSession.close();

    }
}

查询结果与数据库中相同

定义专门的pojo类作为输出类型，其中定了sql查询结果集所有的字段，此方法较为简单。

4.2.2 方法2:

使用resultMap,定义resultMap用于映射一对一的查询结果。

select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id

定义pojo类：早Orders类中加入Users属性，user属性中用于存储关联查询的用户信息，因为订单关联查询用户是一对一的关系，所以这使用单个User对象存储关联查询的用户信息。
```
public class Orders {
    private int id;
    private int user_id;
    private String number;
    private Date createtime;
    private String note;

    private User user;

    public int getId() {
        return id;
    }
```

映射文件OrdersMapper.xml

<resultMap id="ordersResultMap" type="orders" >
    <id column="id" property="id"/>
    <result column="user_id" property="user_id"/>
    <result column="number" property="number"/>
    <result column="createtime" property="createtime"/>
    <result column="note" property="note"/>
    <!-- 构建一对一级联关系 -->
    <association property="user" javaType="com.itzhouq.mybatis.pojo.User">
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
    </association>
</resultMap>

<select id="findOrdersList" resultMap="ordersResultMap" >
    select o.*,u.username,u.address,u.birthday,u.sex from
    orders o left join user u on o.user_id = u.id
</select>

这里resultMap指定ordersResultMap。

:表示进行关联查询的单条记录

property:表示关联查询到结果存储在com.itzhouq.mybatis.pojo.User中

javaType:表示关联查询的结果类型

：查询结果的user_id列对象关联对象的id属性，这里是表示user_id是关联对象的唯一标识。

OrdersMapper接口

public interface OrdersMapper {
    public List<OrdersUser> findOrdersUserList();

    public List<Orders> findOrdersList();
}

测试

@Test
public void testFindOrdersList() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> ordersList = mapper.findOrdersList();
    System.out.println(ordersList);
    sqlSession.close();
}

总结：使用association完成关联查询，将关联查询信息映射到pojo对象中。

4.3 一对多查询

案例：查询所有用户信息及用户相关联的订单信息
用户信息和订单信息为一对多的关系
sql语句

定义pojo类：在User中添加

public class User implements Serializable {
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    private List<Orders> ordersList;

映射文件OrdersMapper.xml

<resultMap type="user" id="UserResultMap">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="address" property="address"/>
    <result column="birthday" property="birthday"/>
    <result column="sex" property="sex"/>
    <!-- public List<User> findUserList(); -->
    <!-- collection：一对多关联，ofType集合中包含的属性 -->
    <collection property="ordersList" ofType="com.itzhouq.mybatis.pojo.Orders">
        <id column="oid" property="id"/>
        <result column="id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    </collection>
</resultMap>
<select id="findUserList" resultMap="UserResultMap">
    select u.*,o.id oid,o.number,o.createtime,o.note from user u 
    inner join orders o on o.user_id = u.id
</select>

collection部分定义了用户关联的订单信息。表示关联查询结果集
property="orders"：关联查询的结果集存储在User对象的上哪个属性。
ofType="orders"：指定关联查询的结果集中的对象类型即List中的对象类型。此处可以使用别名，也可以使用全限定名。
<id />及<result/>的意义同一对一查询。

接口

public interface OrdersMapper {
    public List<OrdersUser> findOrdersUserList();

    public List<Orders> findOrdersList();
    //查询User，User中关联多个订单
    public List<User> findUserList();
}

测试

@Test
public void testFindUserList() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    List<User> userList = mapper.findUserList();
    System.out.println(userList);
    sqlSession.close();
}

结果与数据库中查询的结果一致。

相关阅读:
Scrum为什么不喜欢“来自客户的频繁变更”？
[转]SQL Server计算列
 宽容，是创新的土壤
 【转载】《2010年年度总结》
远程维护需要“千里眼”
TOP语句放到表值函数外，效率异常低下
 “设计应对变化”实例讲解一个数据同步系统
 “批量少次”还是“少量多次”邮件通信系统效率浅谈
 NYOJ117 求逆序数
 NYOJ92 图像有用区域
原文地址：https://www.cnblogs.com/itzhouq/p/mybatis2.html