关联映射
多对多
首先创建表:
#用户表
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());
}
}
最后的查询语句和结果为: