MyBatis in Action——关联映射和动态SQL(2)

最新推荐文章于 2022-09-22 19:04:35 发布

No_Game_No_Life_

最新推荐文章于 2022-09-22 19:04:35 发布

阅读量430

点赞数 1

本文链接：https://blog.csdn.net/No_Game_No_Life_/article/details/83656653

版权

毕设准备专栏收录该内容

74 篇文章 0 订阅

订阅专栏

关联映射

多对多
首先创建表：

#用户表
create table tb_user(
	id int primary key auto_increment,
	username varchar(18),
	loginname varchar(18),
	password varchar(18),
	phone varchar(18),
	address varchar(18)
);
insert into tb_user(username,loginname,password,phone,address)
values('jack','jack','123456','13920001616','广州');

#商品表
create table tb_article(
	id int primary key auto_increment,
	name varchar(18),
	price double,
	remark varchar(18)
);
insert into tb_article(name,price,remark)
values ('java讲义',108.9,'2018年出版');
insert into tb_article(name,price,remark)
values ('android讲义',99.9,'2017再版');
insert into tb_article(name,price,remark)
values ('ios讲义',89.9,'2016出版');
insert into tb_article(name,price,remark)
values ('mybatis讲义',69.9,'2017再版');

#订单表
create table tb_order(
	id int primary key auto_increment,
	code varchar(32),
	total double,
	user_id int,
	foreign key (user_id) references tb_user(id)
);
insert into tb_order(code,total,user_id)
values('6aaa545sads2da12asaaa',399.6,1);
insert into tb_order(code,total,user_id)
values('6aaa54785sadasasdas45',217.6,1);

#中间表
create table tb_item(
	order_id int,
	article_id int,
	amount int,
	primary key(order_id,article_id),
	foreign key(order_id) references tb_order(id),
	foreign key(article_id) references tb_article(id)
);
insert into tb_item values(1,1,1);
insert into tb_item values(1,2,1);
insert into tb_item values(1,3,2);
insert into tb_item values(2,4,2);
insert into tb_item values(2,1,1);

由表可知，我们需要创建一个User、Article、Order对象分别对应前三张表。

public class User implements Serializable{
	//因为用户和订单是一对多的关系：
    private List<Order> orders;
    ...
}
public class Order implements Serializable {
    //订单和用户是多对一的关系
    private User user;
    //订单和商品是多对多的关系
    private List<Article> articles;
    ...
}
public class Article implements Serializable {
    //商品和订单是多对多的关系
    private List<Order> orders;
    ...
}

下面是XML文件：
UserMapper:

<?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.leesanghyuk.mapper.UserMapper">
    <resultMap id="userResultMap" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="loginname" column="loginname"/>
        <result property="password" column="password"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <collection property="orders" javaType="ArrayList"
                    column="id" ofType="Order"
                    select="com.leesanghyuk.mapper.OrderMapper.selectOrderByUserId"
                    fetchType="lazy">
            <id property="id" column="id"/>
            <result property="code" column="code"/>
            <result property="total" column="total"/>
        </collection>
    </resultMap>

    <select id="selectUserById" parameterType="int" resultMap="userResultMap">
        SELECT * FROM TB_USER WHERE ID=#{id}
    </select>

</mapper>

OrderMapper.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.leesanghyuk.mapper.OrderMapper">
    <resultMap id="orderResultMap" type="Order">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="total" column="total"/>
        <association property="user" javaType="User">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="loginname" column="loginname"/>
            <result property="password" column="password"/>
            <result property="phone" column="phone"/>
            <result property="address" column="address"/>
        </association>
        <!--为什么column是oid接下来的查询语句你会知道-->
        <collection property="articles" javaType="ArrayList"
                    column="oid" ofType="Article"
                    select="com.leesanghyuk.mapper.ArticleMapper.selectArticleInITEMById"
                    fetchType="lazy">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
            <result property="remark" column="remark"/>
        </collection>
    </resultMap>

    <select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
        SELECT u.*,o.id as oid,code,total,user_id FROM tb_user u,tb_order o
        WHERE u.id=o.user_id AND o.id=#{id}
    </select>

    <!--为usermapper的userid查询语句-->
    <select id="selectOrderByUserId" parameterType="int" resultType="Order">
        SELECT * FROM TB_ORDER WHERE user_id=#{id}
    </select>

</mapper>

ArticleMapper.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.leesanghyuk.mapper.ArticleMapper">
    <select id="selectArticleInITEMById" parameterType="int" resultType="Article">
        SELECT * FROM TB_ARTICLE WHERE ID IN(
        SELECT  ARTICLE_ID FROM TB_ITEM WHERE ORDER_ID=#{id}
        )
    </select>

</mapper>

测试代码：

 public static void main(String[] args) {
        SqlSession session=FKSqlSessionFactory.getSqlSession();
        ManyToManyTest test=new ManyToManyTest();
        test.testSelectUserById(session);
        test.testSelectOrderById(session);
        session.commit();
        session.close();
    }

    public void testSelectUserById(SqlSession sqlSession){
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        User user=userMapper.selectUserById(1);
        System.out.println(user.toString());
    }

    public void testSelectOrderById(SqlSession sqlSession){
        OrderMapper orderMapper=sqlSession.getMapper(OrderMapper.class);
        Order order=orderMapper.selectOrderById(1);
        System.out.println(order.toString());
    }

好好体会就行，下面来看动态查询。

###动态SQL
MyBatis还有一个强大的特性，就是它的动态SQL
在实际开发中，经常需要根据不同的条件拼接SQL语句，拼接时还要确保不要忘了必要的空格，有时候还要注意逗号引号等等。这种情况很麻烦。

动态SQL能够简单的处理这一问题，常用的动态SQL语句包括：

if
choose(when、otherwise)
where
set
foreach
bind

下面我们通过一个简单的例子来体会一下：

drop table if exists tb_employee;
create table tb_employee(
	id int(11) primary key auto_increment,
	loginname varchar(18),
	password varchar(18),
	name varchar(18) default null,
	sex char(2) default null,
	age int(11) default null,
	phone varchar(21),
	sal double,
	state varchar(18)
);
insert into tb_employee(loginname,password,name,sex,age,phone,sal,state)
VALUES ('jack','123456','杰克','男',26,'13902019999',9800,'ACTIVE');
insert into tb_employee(loginname,password,name,sex,age,phone,sal,state)
VALUES ('rose','123456','露丝','女',21,'13902018888',6800,'ACTIVE');
insert into tb_employee(loginname,password,name,sex,age,phone,sal,state)
VALUES ('tom','123456','汤姆','男',25,'13902017777',8800,'ACTIVE');
insert into tb_employee(loginname,password,name,sex,age,phone,sal,state)
VALUES ('alice','123456','爱丽丝','女',20,'13902016666',5800,'ACTIVE');

创建employee类

....

if
动态SQL通常会偶的事情就是有条件的包含where子句的一部分，比如:

<mapper namespace="com.leesanghyuk.mapper.EmployeeMapper">
    <select id="selectEmployeeByIdLike"
            resultType="Employee">
        SELECT * FROM tb_employee WHERE state='ACTIVE'
        <!--如果传进来的参数有id属性，则加上id-->
        <if test="id!=null">
            and id=#{id}
        </if>
    </select>
</mapper>

可以看出，这就像一个重载一样。
我们看看接口：

public interface EmployeeMapper {
    List<Employee> selectEmployeeByIdLike(HashMap<String,Object> params);
}

这里接受HashMap做参数，在Mybatis中，#{id}获取参数值的方式有两种，一种是我们之前直接传值，另一种就是使用hashMap了。
下面我们来测试一下：

public static void main(String[] args) {
        SqlSession session=FKSqlSessionFactory.getSqlSession();
        DynamicSQTest test=new DynamicSQTest();
        test.testIFwithID(session);
        test.testIFwithoutID(session);
        session.commit();
        session.close();
    }

    public void testIFwithID(SqlSession sqlSession){
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<>();
        params.put("id",1);
        List<Employee> employees=employeeMapper.selectEmployeeByIdLike(params);
        for (Employee e:employees
             ) {
            System.out.println(e.toString());
        }
    }

    public void testIFwithoutID(SqlSession sqlSession){
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<>();
        List<Employee> employees=employeeMapper.selectEmployeeByIdLike(params);
        for (Employee e:employees
                ) {
            System.out.println(e.toString());
        }
    }

是否在hashmap中传入键为id的键值对，决定了if。
我们看看结果：
在这里插入图片描述

如果需要两个条件或多个条件的的时候，当然我们的if也可以这么写：

<if test="loginname!=null and password!=null">
	and loginname=#{loginname} and password=#{password}
</if>

choose(when、otherwise)
有时候，我们不想用所有的条件语句，只想从中选择一两个，针对这个情况，MyBatis提供了choose元素，它有点像java中的switch语句。
如下：

 <select id="selectEmployeeChoose" resultType="Employee"
            parameterType="hashmap">
        SELECT * FROM tb_employee WHERE state='ACTIVE'
        <choose>
            <when test="id!=null">
                and id=#{id}
            </when>
            <when test="loginname !=null and password!=null">
                and loginname=#{loginname} and password=#{password}
            </when>
            <otherwise>
                and sex='男'
            </otherwise>
        </choose>
    </select>

在<choose>中，分别用了<when>和<otherwise>，这就相当于swich，也相当于if else if。

public void testChoose(SqlSession sqlSession){
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String,Object> params=new HashMap<>();
        params.put("id",1);
        params.put("loginname","rose");
        params.put("password","123456");
        List<Employee> employees=employeeMapper.selectEmployeeChoose(params);
        for (Employee e:employees
                ) {
            System.out.println(e.toString());
        }
    }

我们这里虽然Put了三个键值对，但是实际上只会认定id，如果注释掉id，则会认定loginname和password，再注释掉任意一个，就执行<otherwise>。

where
在前面的if语句，如果这样写：

<select id="selectEmployeeByIdLike"
            resultType="Employee">
        SELECT * FROM tb_employee WHERE 
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="loginname!=null">
        	and loginname="loginname"
        </if>
</select>

假如没有id属性，那么我们的结果就会执行错误。
所以这里引入where:

<select id="selectEmployeeByIdLike"
            resultType="Employee">
        SELECT * FROM tb_employee 
        <where>
        	<if test="id!=null">
            	id=#{id}
        	</if>
        	<if test="loginname!=null">
        		and loginname="loginname"
        	</if>
        </where>
</select>

set
关于动态更新语句还可以使用set。

<update id=" " parameterType=" ">
	update tb_employee
	<set>
		<if test="id!=null">
			id=#{id}
		</id>
		<if test="sal!=null">
			sal#{sal}
		</if>
	</set>
	where state='ACTIVE'
</update>

foreach
有时候需要在一个集合里进行遍历寻找，比如下面这句：

	<select id="selectForEach" resultType="Employee">
        SELECT * FROM TB_EMPLOYEE WHERE ID IN
        <foreach item="item" index="index" collection="list"
            open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

foreach元素功能非常强大，它允许指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许指定开闭匹配的字符串以及在迭代中间放置分隔符。
下面是mapper接口函数：

 List<Employee> selectForEach(List<Integer> ids);

这里传入值不再是HashMap了，而是List，该集合中的每一个Interger元素就是要查询的员工的id。

 public void testFOREACH(SqlSession sqlSession){
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
        List<Integer> ids=new ArrayList<>();
        ids.add(1);
        ids.add(2);
        List<Employee> employees=employeeMapper.selectForEach(ids);
        for (Employee e:employees
                ) {
            System.out.println(e.toString());
        }

    }

我们来看看最后的查询语句和结果：
在这里插入图片描述

bind
有时候需要模糊查询，就需要bind了。

<select id="selectBIND" resultType="Employee">
        <bind name="pattern" value="'%'+_parameter.getName()+'%'"/>
        SELECT * FROM TB_EMPLOYEE WHERE loginname like #{pattern}
    </select>

接口函数这样定义：

List<Employee> selectBIND(Employee employee);

最后测试：

 public void testBIND(SqlSession sqlSession){
        EmployeeMapper employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
        Employee employee=new Employee();
        employee.setName("o");
        List<Employee> employees=employeeMapper.selectBIND(employee);
        for (Employee e:employees
                ) {
            System.out.println(e.toString());
        }
    }