sql片段
sql的配置文件中出现sql冗余
在开发中，SQL的拼接很常见，有很多对拼接的sql具有重复性高的特点，有sql冗余，不仅不美观还导致映射文件配置臃肿，这时最好把重复的sql抽取出来，作为公用的sql片段，尤其在动态sql中应用中更加显著，提高可重用性。
定义sql片段
<!--使用sql标签定义一个sql片段-->
<sql id="baseColumn ">
id,name,gender,age
</sql>
使用sql片段
<select id="getUsers" resultType="cn.offcn.entity.User">
select <include refid= "baseColumn"></include> from user
</select>
动态sql语句
动态sql介绍
动态sql的概念
顾名思义，SQL 是动态拼接成的，根据传入的变量值进行逻辑操作并动态拼接，方便实现多条件下的数据库操作。在业务逻辑复杂，即简单 SQL 无法完成时，需要拼接时就要使用动态SQL。
动态sql解决的问题
动态sql主要解决根据条件判断附加条动态sql主要解决多条件变化查询，实现自动判断记录字段是否需要更新，根据条件判断附加条sql条件，实现批量添加数据、批量修改数据、批量修删除数据等，优化sql语句，提高执行效率。
构建测试环境
创建maven项目
添加依赖
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
创建bean
public class User implements Serializable {
private Integer id;
private String name;
private String gender;
private Integer age;
private Date birthday;
public User() {}
public User(String name, String gender, Integer age, Date birthday) {
this.name = name;
this.gender = gender;
this.age = age;
this.birthday = birthday;
}
//生成getter和setter方法
}
创建接口
public interface UserMapper {
}
编写框架配置文件和sql映射文件
编写框架配置文件
<?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>
<!-- 配置 mybatis的环境 -->
<environments default="development">
<!-- 配置环境 -->
<environment id="development">
<!-- 配置事务的类型 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置连接数据库的信息:用的是数据源【连接池】-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis001"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 注册UserDao接品映射文件位置 -->
<mappers>
<mapper resource="cn/offcn/mapper/UserMapper.xml"/>
</mappers>
</configuration>x
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.offcn.mapper.UserMapper">
</mapper>
where标签
where标签简介
where标签用于代替sql中的where关键字，可以根据条件判断是否附加where关键字。如果where标签中有条件成立就会附加where关键字，如果没有成立的条件就不会附加where关键字. 可以去掉离他最近一个无关的and 或or关键字.where标签的书写格式为<where>添写附加条件</where>
where标签使用
编写接口方法findByUser
/**
* 根据User中的字段进行查询
* @return
*/
public List<User> findByUser(User user);
使用where标签进行sql处理
<select id="findByUser" parameterType="cn.offcn.entity.User" resultType="cn.offcn.entity.User">
select * from user
<where>
and name=#{name} and age=#{age}
</where>
</select>
测试findByUser方法
@Test
public void testFindByUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("张三");
user.setAge(22);
List<User> userList = userMapper.findByUser(user);
//遍历
userList.forEach(System.out::print);
//关闭连接
MyBatisUtils.close(session);
}
if标签标签的应用
if标签简介
if标签表示逻辑条件判断，如果条件成立就附加<if></if>之间的sql语句,如果条件不成立就不附加<if></if>之间的sql语句。书写格式为:<if test="表达式">sql语句</if>
if标签使用
编写接口方法findUsersByCondition
/**
* 根据条件查询
* @param user
* @return
*/
public List<User> findUsersByCondition(User user);
使用if标签进行sql处理
<select id="findUsersByCondition" parameterType="cn.offcn.entity.User" resultType="cn.offcn.entity.User">
select * from user
<where>
<if test="name!=null">
name=#{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
<if test="name!=null">
and gender=#{gender}
</if>
</where>
</select>
测试findUsersByCondition方法
@Test
public void testFindUsersByCondition() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("王五");
user.setAge(20);
user.setGender("男");
List<User> userList = userMapper.findUsersByCondition(user);
//遍历
userList.forEach(System.out::print);
//关闭连接
MyBatisUtils.close(session);
}
通过产生的sql语句可以看出，当if标签中test属性表达式为true时，就会附加if标签之间的条件。
注意：<if>标签的 test 属性中写的是对象的属性名，如果是包装类的对象要使用 OGNL 表达式的写法。
set标签的应用
set标签简介
set标签用于更新语句中，代替set关键字，可以有效对指定字段进行更新，提升sql的执行效率。，当set标签中有条件成立时就会附加set标签，set标签会去除无关的逗号。set标签中一般嵌套if标签进行使用其格式为
<set>
<if test="name">
name=#{name},
</if>
<if test="age">
age=#{age},
</if>
......
</set>
set标签使用
编写接口方法updateUser
/**
* 更新user
* @param user
*/
public void updateUser(User user);
使用set标签进行sql处理
<update id="updateUser" parameterType="cn.offcn.entity.User">
update user
<set>
<if test="name!=null">
name=#{name}
</if>
<if test="gender!=null">
gender=#{gender}
</if>
<if test="age!=null">
age=#{age}
</if>
<if test="birthday!=null">
birthday=#{birthday}
</if>
</set>
where id=#{id}
</update>
测试updateUser方法
@Test
public void testUpdateUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("王五");
user.setAge(20);
//更新user
userMapper.updateUser(user);
//提交事务
MyBatisUtils.close(session);
//关闭连接
MyBatisUtils.close(session);
}
通过产生的sql语句可以看出，当set标签中有条件成立时就会附加set关键字，字段为null时该列不会被更新。set可以忽略与sql无关的逗号。
trim标签
trim标签简介
trim标签为万能标签，可用于set或where等。prefix表示要附加的前缀关键字，suffix表示要附加的后缀关键字,prefixOverrides表示要忽略前置字符，suffixOverrides表示要忽略后置字符。
格式:
<trim prefix="where" prefixOverrides=",">
<if test="name!=null">
name=#{name}
</if>
<if test="age!=null">
age=#{age}
</if>
<if test="name!=null">
gender=#{gender}
</if>
</trim>
trim标签使用
修改where标签和set标签
1)应用于where
<select id="findUsersByCondition" parameterType="cn.offcn.entity.User" resultType="cn.offcn.entity.User">
select * from user
<trim prefix="where" prefixOverrides=",">
<if test="name!=null">
name=#{name}
</if>
<if test="age!=null">
age=#{age}
</if>
<if test="name!=null">
gender=#{gender}
</if>
</trim>
</select>
2)用于set标签
<update id="updateUser" parameterType="cn.offcn.entity.User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="name!=null">
name=#{name},
</if>
<if test="gender!=null">
gender=#{gender},
</if>
<if test="age!=null">
age=#{age},
</if>
<if test="birthday!=null">
birthday=#{birthday}
</if>
</trim>
where id=#{id}
</update>
测试方法测试updateUser和findUsersByCondition方法
@Test
public void testFindUsersByCondition() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("王五");
user.setAge(20);
user.setGender("男");
List<User> userList = userMapper.findUsersByCondition(user);
//遍历
userList.forEach(System.out::print);
//关闭连接
MyBatisUtils.close(session);
}
@Test
public void testUpdateUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("王五");
user.setAge(20);
//更新user
userMapper.updateUser(user);
//提交事务
MyBatisUtils.close(session);
//关闭连接
MyBatisUtils.close(session);
}
choose标签
+ 标签简介
choose标签作用条件判断来拼接指定的条件，它和if不太相同，choose似类于java中的switch语句用法，直要有条件成立，其它判断将得不到执行，如果所有条件都不成立则执行otherwise标签中的内容。
格式:
<choose>
<when test=条件1>
执行的代码；
</when>
<when test=条件2>
执行的代码；
</when>
......
<otherwise>
执行的代码；
</when>
</otherwise>
</choose>
choose标签使用
编写接口方法getInfoByUser
/**
* 查询符合条件的所有user对象
* @param user
* @return
*/
public List<User> getInfoByUser(User user);
使用choose标签进行sql处理
<select id="getInfoByUser" parameterType="cn.offcn.entity.User" resultType="cn.offcn.entity.User">
select * from user
<where>
<choose>
<when test="name!=null">
name=#{name}
</when>
<when test="age!=null">
age=#{age}
</when>
<when test="gender!=null">
gender=#{gender}
</when>
<otherwise>
birthday='1991-10-10'
</otherwise>
</choose>
</where>
</select>
测试接口方法getInfoByUser
@Test
public void testGetInfoByUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
//创建User对象
User user=new User();
user.setName("张三");
user.setAge(22);
List<User> userList = userMapper.getInfoByUser(user);
//遍历
userList.forEach(System.out::print);
//关闭连接
MyBatisUtils.close(session);
}
foreach标签
foreach标签简介
foreach标签表示循环，对sql中有重复的部分可以使用此循环来动态拼接sql语句。可以实现批量添加、批量删除、批量更新操作。foreach标签中有很多的属性，请参考下面的Foreach标签属性表。
| 属性名称 | 含义 |
|---|---|
| collection | 指定你要使用的集合类型 |
| item | 集合中每个元素。 |
| open | 在起始时，需要附加字符串，只附加一次。 |
| close | 在结束时，需要附加字符，只附加一次。 |
| separator | 在每个循环结时需要附加的字符串。 |
| index | 每个循环的索引值。 |
foreach标签使用
在接口中创建 addBatchUser、updateBatchUser、deleteBatchUser
/**
* 批量添加
* @param userList
*/
public void addBatchUser(List<User> userList);
/**
* 批量更新
* @param userList
*/
public void updateBatchUser(List<User> userList);
/**
* 批量删除
* @param ids
*/
public void deleteBatchUser(List<Integer> ids);
使用forcach标签进行sql处理
<!--批量添加-->
<insert id="addBatchUser">
insert into user (name,gender,age,birthday) values
<foreach collection="list" item="user" separator=",">
(#{user.name},#{user.gender},#{user.age},#{user.birthday})
</foreach>
</insert>
<!--批量更新-->
<update id="updateBatchUser">
<foreach collection="list" item="user" separator=";">
update set user name=#{user.name},gender=#{user.gender},age=#{user.age},birthday=#{user.birthday}
where id=#{id}
</foreach>
</update>
<!--批量删除-->
<delete id="deleteBatchUser">
delete from user where id in
<foreach collection="list" item="userId" separator="," open="(" close=")">
#{userId}
</foreach>
</delete>
测试
//批量添加测试
@Test
public void testAddBatchUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> userList=new ArrayList<>();
userList.add(new User("赵丽","女",22,new Date()));
userList.add(new User("李宁","女",25,new Date()));
userList.add(new User("王海涛","男",20,new Date()));
userMapper.addBatchUser(userList);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
//批量更新测试
@Test
public void testUpdateBatchUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
List<User> userList=new ArrayList<>();
userList.add(new User(1,"赵刚","男",24,new Date()));
userList.add(new User(2,"白雪","女",25,new Date()));
userList.add(new User(3,"王海燕","女",20,new Date()));
userMapper.updateBatchUser(userList);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
//批量删除测试
@Test
public void testDeleteBatchUser() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
List<Integer> ids=Arrays.asList(1,2,3);
userMapper.deleteBatchUser(ids);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
注意：mysql本身不支持批量更新，如果需要批量更新时在url中附加allowMultiQueries=true
<property name="url" value="jdbc:mysql:///mybatis002?allowMultiQueries=true"/>
多表联合查询
多表联合查询概述
在开发过程中单表查询不能满足项目需求分析功能，对于复杂业务来讲，关联的表有几张，甚至几十张并且表与表之间的关系相当复杂。为了能够实业复杂功能业务，就必须进行多表查询，在mybatis中提供了多表查询的结果时映射标签，可以实现表之间的一对一、一对多、多对一、多对多关系映射。
MyBatis多表查询之一对一
构建数据库表:person(个人表) IdCard(身份证表)
#person表
CREATE TABLE person(
p_id INT NOT NULL AUTO_INCREMENT,
p_name VARCHAR(30),
PRIMARY KEY(p_id)
);
#IdCard表
CREATE TABLE idcard(
c_id INT NOT NULL AUTO_INCREMENT,
c_cardno VARCHAR(18),
c_uselife DATE,
c_person_id INT NOT NULL,
PRIMARY KEY(c_id),
FOREIGN KEY(c_person_id) REFERENCES person(p_id),
UNIQUE KEY(c_cardno),
UNIQUE KEY(c_person_id)
);
INSERT INTO person(p_name) VALUES('张三'),('李四');
INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('110112199012127821','2029-10-10',1);
INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('120114199911103491','2030-12-01',2);
准备项目环境
构建maven项目，添加依赖
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
编写框架配置文件sqlMapConfig.xml
<?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>
<!-- 配置 mybatis的环境 -->
<environments default="development">
<!-- 配置环境 -->
<environment id="development">
<!-- 配置事务的类型 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置连接数据库的信息:用的是数据源【连接池】-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis002"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="cn.offcn.dao"></package>
</mappers
</configuration>
方式一：嵌套结果方式
创建数据模型Person,IdCard
package cn.offcn.entity;
public class Person {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
public class IdCard {
private int id;
private String cardno;
private Date useLife;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCardno() {
return cardno;
}
public void setCardno(String cardno) {
this.cardno = cardno;
}
public Date getUseLife() {
return useLife;
}
public void setUseLife(Date useLife) {
this.useLife = useLife;
}
}
编写sql语句
实现查询个人信息时，也要查询个人所对应的身份证信息。
select p.*,c.* from
person p,
idcard c
where p.p_id=c.c_person_id and p.p_id=1;
编写PersonIdCard类
public class PersonIdCard extends Person{
private String cardno;
private Date useLife;
public String getCardno() {
return cardno;
}
public void setCardno(String cardno) {
this.cardno = cardno;
}
public Date getUseLife() {
return useLife;
}
public void setUseLife(Date useLife) {
this.useLife = useLife;
}
}
定义持久层接口PersonDao
public interface PersonDao{
/**
* 根据id查询person对象
*/
public PersonIdCard getPersonById(int id);
}
定义 PersonDao.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="cn.offcn.dao.PersonDao">
<!-- 配置查询操作-->
<select id="getPersonById" resultMap="PersonResultMap">
select p.*,c.* from
person p,
idcard c
where p.p_id=c.c_person_id and p.p_id=#{id};
</select>
<resultMap id="PersonResultMap" type="cn.offcn.entity.PersonIdCard">
<id column="p_id" property="id"></id>
<result column="p_id" property="id"></result>
<result column="p_name" property="name"></result>
<result column="c_cardno" property="cardno"></result>
<result column="c_uselife" property="useLife"></result>
</resultMap>
</mapper>
创建 PersonTest 测试类
@Test
public void testGetPersonById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
PersonDao personDao = session.getMapper(PersonDao.class);
PersonIdCard personIdCard=personDao.getPersonById(1);
//打印
System.out.println(personIdCard);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
方式二：嵌套查询方式
修改Person类:添加idCard属性
public class Person {
private int id;
private String name;
private IdCard idCard;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public IdCard getIdCard() {
return idCard;
}
public void setIdCard(IdCard idCard) {
this.idCard = idCard;
}
}
修改 PersonDao 接口中的方法
public interface PersonDao{
/**
* 根据id查询person对象
*/
public Person getPersonById(int id);
}
定义身份证的持久层接口IdCardDao
public interface IdCardDao{
/**
* 根据c_person_id查询IdCard对象
*/
public Person getIdCardByPersonId(int id);
}
定义 IdCardDao.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="cn.offcn.dao.IdCard">
<!-- 配置查询操作-->
<select id="getIdCardByPersonId" resultMap="IdCardResultMap">
select * from
idcard c
where c_person_id=#{id};
</select>
<resultMap id="IdCardResultMap" type="cn.offcn.entity.IdCard">
<id column="c_id" property="id"></id>
<result column="c_cardno" property="cardno"></result>
<result column="c_uselife" property="useLife"></result>
</resultMap>
</mapper>
修改PersonDao.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="cn.offcn.dao.PersonDao">
<!-- 配置查询操作-->
<select id="getPersonById" resultMap="PersonResultMap">
select * from
person p,
where p_id=#{id};
</select>
<resultMap id="PersonResultMap" type="cn.offcn.entity.Person">
<id column="p_id" property="id"></id>
<result column="p_id" property="id"></result>
<result column="p_name" property="name"></result>
<!--映射Person类中的复杂字段idCard对象属性-->
<association property="idCard" javaType="IdCard" column="p_id"
select="cn.offcn.dao.IdCard.getIdCardByPersonId">
</association>
</resultMap>
</mapper>
column:表示取上次查询出来的指定列的值，做为select属性所指定的查询的输入值。
select:表示指定的查询.
PersonTest 类中加入测试方法
@Test
public void testGetPersonById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
PersonDao personDao = session.getMapper(PersonDao.class);
Person person=personDao.getPersonById(1);
//打印
System.out.println(person);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
MyBatis实现一对多查询
创建数据库表:department(部门表),employee(员工表)同时设定部门和员工表的关系
CREATE TABLE department(
d_id INT NOT NULL AUTO_INCREMENT,
d_name VARCHAR(100),
PRIMARY KEY(d_id)
);
CREATE TABLE employee(
e_id INT NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30),
e_gender VARCHAR(6),
e_age INT,
e_depart_id INT,
PRIMARY KEY(e_id),
FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
);
方式一:嵌套结果的方式
创建数据模型department和employee
public class Department {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
public class Employee {
private int id;
private String name;
private String gender;
private Integer age;
public Employee(){}
public Employee(int id,String name, String gender, int age) {
this.id=id;
this.name = name;
this.gender = gender;
this.age = age;
}
public Employee(String name, String gender, int age) {
this.name = name;
this.gender = gender;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
编写一对多的sql查询语句
SELECT d.*,e.*
FROM
department d, employee e
WHERE
e.e_depart_id=d.d_id AND d.d_id=#{id}
Department类中加入List<Employee>属性
private List<Employee> emps;
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
部门持久层DepartmentDao 接口中加入查询方法
import cn.offcn.entity.Department;
public interface DepartmentMapper {
public Department getDepartmentById(int id);
}
部门持久层 DepartmentDao.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="cn.offcn.mapper.DepartmentDao">
<select id="getDepartmentById" resultMap="DepartemntResultMap2">
SELECT d.*,e.* FROM department d, employee e WHERE
e.e_depart_id=d.d_id AND d.d_id=#{id}
</select>
<resultMap id="DepartemntResultMap" type="Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
<collection property="emps" ofType="Employee">
<id column="e_id" property="id"></id>
<result column="e_name" property="name"></result>
<result column="e_gender" property="gender"></result>
<result column="e_age" property="age"></result>
</collection>
</resultMap>
collection：当属性为集合时，使用collection标签进行映射。
测试方法
@Test
public void testGetDepartentById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
Department dept=departmentDao.getDepartentById(1);
//打印
System.out.println(dept);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
方式二:嵌套查询的方式
定义员工的持久层接口EmployeeDao
public interface EmployeeDao {
public List<Employee> getEmployeeByDepartId(int id);
}
定义 EmployeeDao.xml 文件中的查询配置信息
<select id="getEmployeeByDepartId" resultType="Employee">
select e_id id,e_name name,e_gender gender,e_age age
from employee where e_depart_id=#{id}
</select>
修改DepartmentDao.xml配置文件
<select id="getDepartmentById" resultMap="DepartemntResultMap">
select * from department where d_id=#{id}
</select>
<resultMap id="DepartemntResultMap" type="Department">
<id column="d_id" property="id"></id>
<result column="d_name" property="name"></result>
<collection property="emps" ofType="Employee" column="d_id"
select="cn.offcn.mapper.EmployeeMapper.getEmployeeByDepartId">
</collection>
</resultMap>
此处变为单表查询，使分表查询方式进行查询。
测试方法
@Test
public void testGetDepartentById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
Department dept=departmentDao.getDepartentById(1);
//打印
System.out.println(dept);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
MyBatis实现多对多查询
创建数据库表:student(学生表),teacher(老师表)
CREATE TABLE student(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(30),
PRIMARY KEY(sid)
);
CREATE TABLE teacher(
tid INT NOT NULL AUTO_INCREMENT,
tname VARCHAR(30),
PRIMARY KEY(tid)
);
CREATE TABLE student_teacher(
s_id INT NOT NULL,
t_id INT NOT NULL,
PRIMARY KEY(s_id,t_id),
FOREIGN KEY(s_id) REFERENCES student(sid),
FOREIGN KEY(t_id) REFERENCES teacher(tid)
);
INSERT INTO student(sname) VALUES('张三'),('李四');
INSERT INTO teacher (tname) VALUES('刘老师'),('李老师');
INSERT INTO student_teacher(s_id,t_id)
VALUES(1,1),(1,2),(2,1)
方式一:嵌套结果方式
创建数据模型:Student,Teacher,StudentTeacher
public class Student {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
public class Teacher {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
public class StudentTeacher {
private int sid;
private int tid;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
编写多对多的sql语句
SELECT s.*,ts.*,t.*
FROM
student s,student_teacher st,teacher t
WHERE
s.sid=st.s_id AND st.t_id=t.tid AND s.sid=1
Student类中加入List<StudentTeacher>属性
private List<StudentTeacher> studentTeacherList;
public List<StudentTeacher> getStudentTeacherList() {
return studentTeacherList;
}
public void setStudentTeacherList(List<StudentTeacher> studentTeacherList) {
this.studentTeacherList = studentTeacherList;
}
StudentTeacher中加入Teacher属性
private Teacher teacher;
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
学生持久层StudentDao接口中加入查询方法
import cn.offcn.entity.Student;
public interface StudentDao {
public Student getStudentById(int id);
}
学生持久层SudentDao.xml映射文件配置
<select id="getStudentById" resultMap="StudentResultMap">
SELECT s.*,ts.*,t.* FROM student s,teacher_student ts,teacher t
WHERE s.sid=ts.s_id AND ts.t_id=t.tid AND s.sid=#{id}
</select>
<resultMap id="StudentResultMap" type="Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"/>
<collection property="studentTeacherList" ofType="StudentTeacher">
<result column="s_id" property="sid"></result>
<result column="t_id" property="tid"></result>
<association property="teacher" javaType="Teacher">
<id column="tid" property="id"></id>
<result column="tname" property="name"></result>
</association>
</collection>
</resultMap>
测试
@Test
public void testGetStudentById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 StudentDao接口的代理对象
StudentDao studentDao = session.getMapper(StudentDao.class);
Student student= studentDao.getStudentById(1);
//打印
System.out.println(tudent);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}
方式二:嵌套查询方式
定义中间表的持久层接口StudentTeacherDao
public interface StudentTeacherDao {
public List<StudentTeacher> getStudentTeacherBySid(int sid);
}
定义StudentTeacherDao.xml文件中的查询配置信息
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.offcn.dao.StudentTeacherDao">
<select id="getStudentTeacherBySid" resultMap="StudentTeacherResultMap">
select * from teacher_student where s_id=#{sid}
</select>
<resultMap id="StudentTeacherResultMap" type="StudentTeacher">
<result column="s_id" property="sid"></result>
<result column="t_id" property="tid"></result>
<association property="teacher" javaType="Teacher"
column="t_id" select="cn.offcn.dao.TeacherMapper.getTeacherByTid"> </association>
</resultMap>
</mapper>
定义老师持久层的接口TeacherDao
public interface TeacherDao {
public Teacher getTeacherByTid(int tid);
}
定义老师持久层TeacherDao.xml映射文件配置
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.offcn.dao.TeacherDao">
<select id="getTeacherByTid" resultType="Teacher">
select tid id,tname name from teacher where tid=#{tid}
</select>
</mapper>
修改StudentDao持久层的方法
public interface StudentMapper {
public Student getStudentById(int id);
}
修改StudentDao.xml的查询配置信息
<select id="getStudentById" resultMap="StudentResultMap">
select * from student where sid=#{id}
</select>
<resultMap id="StudentResultMap" type="Student">
<id column="sid" property="id"></id>
<result column="sname" property="name"/>
<collection property="studentTeacherList" ofType="StudentTeacher"
column="sid"
select="cn.offcn.mapper.StudentTeacherMapper.getStudentTeacherBySid">
</collection>
</resultMap>
测试方法
@Test
public void testGetStudentById() throws Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 StudentDao接口的代理对象
StudentDao studentDao = session.getMapper(StudentDao.class);
Student student= studentDao.getStudentById(1);
//打印
System.out.println(tudent);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}