MyBatis 基础知识三
第一:动态SQL
1.1 if 标签
接口定义:
/**
* 更新图书
*/
public int update(Book entity);
mapper 文件
<!-- 更新 -->
<update id="update" parameterType="Book">
update books
<set>
<if test="title != null and title != ''">
title=#{title},
</if>
<if test="price != null">
price=#{price},
</if>
<if test="publishDate != null">
publishDate=#{publishDate},
</if>
</set>
where id=#{id}
</update>
1.2 choose when otherwise
接口定义
/**
* 根据图书名称获取图书对象
* @param name
* @return
*/
public Book getBookByName(@Param(value = "name") String name);
mapper 文件
<!--根据图书名称获取图书对象 -->
<select id="getBookByName" resultType="Book" parameterType="String">
select
id,title,price,publishDate from books where 1 = 1
<!--
1.一旦有条件成立的when,后续的when则不会执行
2.当所有的when都不执行时,才会执行otherwise
-->
<choose>
<when test="name!=null and name.trim()!=''">
and title like '%${name}%'
</when>
<otherwise>
and title='java编程思想--修改'
</otherwise>
</choose>
</select>
1.3 where 和set
接口定义
/**
* 根据图书编号获得图书对象
*/
public Book getBookById(@Param(value = "id")int id);
mapper 文件
<!--获得图书对象通过编号 -->
<select id="getBookById" resultType="Book" parameterType="java.lang.Integer">
select
id,title,price,publishDate from books
<where>
<if test="id!=null">
and id = #{id}
</if>
</where>
</select>
接口定义
/**
* 更新图书
*/
public int update(Book entity);
mapper 文件
<!-- 更新 -->
<update id="update" parameterType="Book">
update books
<trim prefix="set" suffixOverrides=",">
<if test="title != null and title != ''">
title=#{title},
</if>
<if test="price != null">
price=#{price},
</if>
<if test="publishDate != null">
publishDate=#{publishDate},
</if>
</trim>
where id=#{id}
</update>
1.4 foreach
接口定义
public List<Book> getBooksByIds(@Param(value = "ids") int[] ids);
mapper 文件
<select id="getBooksByIds" resultType="Book">
select * from books where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
第二:多表查询
2.1 一对一查询
2.1.1 实现方式一
定义相关实体对象:
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Account implements Serializable {
private Integer id;
private Integer uid;
private String name;
private Double money;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
+ address + "]";
}
}
定义相关Dao接口和xml 文件:
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.Account;
public interface IAccountDao {
List<Account> findAll();
List<Account> selectByPrimaryKey();
}
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.User;
import com.zzg.jdbc.mybatis.plugins.Page;
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
List<User> findAll();
}
<?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.zzg.jdbc.mybatis.dao.IAccountDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.Account"
id="accountMap">
<id property="id" column="id" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
<association property="user"
javaType="com.zzg.jdbc.mybatis.entity.User">
<id property="id" column="sid" />
<result column="username" property="username" />
<result column="birthday" property="birthday" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="accountMap">
select * from account
</select>
<select id="selectByPrimaryKey" resultMap="accountMap">
select a.*, b.id as sid,b.username,b.birthday,b.sex,
b.address from account as a left join user as b on a.uid = b.id where a.id =1
</select>
</mapper>
<?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.zzg.jdbc.mybatis.dao.IUserDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.User" id="user">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="user">
select * from user
</select>
</mapper>
代码测试:
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IAccountDao;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisAccountOneOnOne {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IAccountDao accountDao = session.getMapper(IAccountDao.class);
// 6.使用代理对象执行查询所有方法
List<Account> accounts = accountDao.selectByPrimaryKey();
for (Account user : accounts) {
System.out.println(user.getUser().getUsername());
}
// 7.释放资源
session.close();
in.close();
}
}
2.1.2 实现方式二
定义相关实体对象:
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Account implements Serializable {
private Integer id;
private Integer uid;
private String name;
private Double money;
// private User user;
//
// public User getUser() {
// return user;
// }
// public void setUser(User user) {
// this.user = user;
// }
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}
package com.zzg.jdbc.mybatis.entity.expand;
import java.util.Date;
import com.zzg.jdbc.mybatis.entity.Account;
/**
* mybatis 一对一关系
* @author Administrator
*
*/
@SuppressWarnings("serial")
public class AccountExpand extends Account {
private Integer sid;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
定义相关Dao接口和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.zzg.jdbc.mybatis.dao.IAccountDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.Account"
id="accountMap">
<id property="id" column="id" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
<association property="user"
javaType="com.zzg.jdbc.mybatis.entity.User">
<id property="id" column="sid" />
<result column="username" property="username" />
<result column="birthday" property="birthday" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="accountMap">
select * from account
</select>
<select id="selectByPrimaryKey" resultMap="accountMap">
select a.*, b.id as sid,b.username,b.birthday,b.sex,
b.address from account as a left join user as b on a.uid = b.id where a.id =1
</select>
<select id="findAllExpand" resultType="com.zzg.jdbc.mybatis.entity.expand.AccountExpand">
select a.*, b.id as sid,b.username,b.birthday,b.sex,
b.address from account as a left join user as b on a.uid = b.id where a.id =1
</select>
</mapper>
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.entity.expand.AccountExpand;
public interface IAccountDao {
List<Account> findAll();
List<Account> selectByPrimaryKey();
List<AccountExpand> findAllExpand();
}
代码测试:
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IAccountDao;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.entity.expand.AccountExpand;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisAccountOneOnOne {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IAccountDao accountDao = session.getMapper(IAccountDao.class);
// 6.使用代理对象执行查询所有方法
getAccountExpand(accountDao);
// 7.释放资源
session.close();
in.close();
}
public static void getAccountExpand(IAccountDao accountDao) {
List<AccountExpand> expands = accountDao.findAllExpand();
for (AccountExpand user : expands) {
System.out.println(user.getUsername());
}
}
public static void getAccount(IAccountDao accountDao) {
// 6.使用代理对象执行查询所有方法
List<Account> accounts = accountDao.selectByPrimaryKey();
for (Account user : accounts) {
System.out.println(user.getUser().getUsername());
}
}
}
实现方式三:
实体对象定义:
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Account implements Serializable {
private Integer id;
private Integer uid;
private String name;
private Double money;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
+ address + "]";
}
}
定义相关Dao接口和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.zzg.jdbc.mybatis.dao.IAccountDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.Account"
id="accountMap">
<id property="id" column="id" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
<association property="user"
javaType="com.zzg.jdbc.mybatis.entity.User">
<id property="id" column="sid" />
<result column="username" property="username" />
<result column="birthday" property="birthday" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<resultMap type="com.zzg.jdbc.mybatis.entity.Account"
id="accountExpand">
<id property="id" column="id" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
<association property="user" column="uid" select="selectByPrimaryKeyExpandUser"/>
</resultMap>
<resultMap type="com.zzg.jdbc.mybatis.entity.User" id="user">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="accountMap">
select * from account
</select>
<select id="selectByPrimaryKey" resultMap="accountMap">
select a.*, b.id as sid,b.username,b.birthday,b.sex,
b.address from account as a left join user as b on a.uid = b.id where a.id =1
</select>
<select id="selectByPrimaryKeyExpand" resultMap="accountExpand">
select a.* from account as a where a.id =1
</select>
<select id="selectByPrimaryKeyExpandUser" resultType="java.lang.Integer" resultMap="user">
select * from user where id = #{id}
</select>
<select id="findAllExpand" resultType="com.zzg.jdbc.mybatis.entity.expand.AccountExpand">
select a.*, b.id as sid,b.username,b.birthday,b.sex,
b.address from account as a left join user as b on a.uid = b.id where a.id =1
</select>
</mapper>
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.entity.User;
import com.zzg.jdbc.mybatis.entity.expand.AccountExpand;
public interface IAccountDao {
List<Account> findAll();
List<Account> selectByPrimaryKey();
List<AccountExpand> findAllExpand();
List<Account> selectByPrimaryKeyExpand();
User selectByPrimaryKey(@Param(value = "id") Integer id);
}
定义测试方法
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IAccountDao;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.entity.expand.AccountExpand;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisAccountOneOnOne {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IAccountDao accountDao = session.getMapper(IAccountDao.class);
// 6.使用代理对象执行查询所有方法
getExpand(accountDao);
// 7.释放资源
session.close();
in.close();
}
public static void getAccountExpand(IAccountDao accountDao) {
List<AccountExpand> expands = accountDao.findAllExpand();
for (AccountExpand user : expands) {
System.out.println(user.getUsername());
}
}
public static void getAccount(IAccountDao accountDao) {
// 6.使用代理对象执行查询所有方法
List<Account> accounts = accountDao.selectByPrimaryKey();
for (Account user : accounts) {
System.out.println(user.getUser().getUsername());
}
}
public static void getExpand(IAccountDao accountDao) {
// 6.使用代理对象执行查询所有方法
List<Account> accounts = accountDao.selectByPrimaryKeyExpand();
for (Account user : accounts) {
System.out.println(user.getUser().getUsername());
}
}
}
2.2 一对多查询
实体对象定义:
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
+ address + "]";
}
}
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Account implements Serializable {
private Integer id;
private Integer uid;
private String name;
private Double money;
// private User user;
//
// public User getUser() {
// return user;
// }
// public void setUser(User user) {
// this.user = user;
// }
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}
package com.zzg.jdbc.mybatis.entity.expand;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.Account;
import com.zzg.jdbc.mybatis.entity.User;
/**
* mybatis 一对多关系展示
* @author Administrator
*
*/
@SuppressWarnings("serial")
public class UserExpand extends User {
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
}
定义相关Dao接口和xml 文件:
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.zzg.jdbc.mybatis.entity.User;
import com.zzg.jdbc.mybatis.entity.expand.UserExpand;
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
List<User> findAll();
User selectByPrimaryKey(@Param(value = "id") Integer id);
List<UserExpand> selectByUserList();
}
<?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.zzg.jdbc.mybatis.dao.IUserDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.User" id="user">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</resultMap>
<resultMap type="com.zzg.jdbc.mybatis.entity.expand.UserExpand" id="userExpand" extends="user">
<collection property="accounts" ofType="com.zzg.jdbc.mybatis.entity.Account">
<id property="id" column="sid" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
</collection>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="user">
select * from user
</select>
<select id="selectByPrimaryKey" resultType="java.lang.Integer" resultMap="user">
select * from user where id = #{id}
</select>
<select id="selectByUserList" resultMap="userExpand">
select a.*, b.id as sid, b.name, b.money, b.uid from user as a left join account as b on a.id = b.uid where a.id =1;
</select>
</mapper>
测试方法:
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IAccountDao;
import com.zzg.jdbc.mybatis.dao.IUserDao;
import com.zzg.jdbc.mybatis.entity.expand.UserExpand;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisUserOneOnMore {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IUserDao userDao = session.getMapper(IUserDao.class);
// 6.使用代理对象执行查询所有方法
getExpand(userDao);
// 7.释放资源
session.close();
in.close();
}
public static void getExpand(IUserDao userDao) {
List<UserExpand> list = userDao.selectByUserList();
for(UserExpand expand : list) {
System.out.println(expand.getAccounts().size());
}
}
}
2.3 多对多查询
参考:用户角色权限体系结构设计
2.3.1 角色与用户关系(1:n)
实体对象定义:
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
import java.util.List;
@SuppressWarnings("serial")
public class Role implements Serializable {
private Integer id;
private String roleName;
private String roleDesc;
// 多对多的关系映射:一个角色可以赋予多个用户
private List<User> users;
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
}
package com.zzg.jdbc.mybatis.entity;
import java.io.Serializable;
import java.util.Date;
@SuppressWarnings("serial")
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex + ", address="
+ address + "]";
}
}
定义相关Dao接口和xml 文件:
package com.zzg.jdbc.mybatis.dao;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.Role;
public interface IRoleDao {
/**
* 查询所有角色
* @return
*/
List<Role> findAll();
}
<?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.zzg.jdbc.mybatis.dao.IRoleDao">
<!--定义 role 表的 ResultMap -->
<resultMap id="roleMap" type="com.zzg.jdbc.mybatis.entity.Role">
<id property="id" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="com.zzg.jdbc.mybatis.entity.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<!--查询所有 -->
<select id="findAll" resultMap="roleMap">
select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id = ur.uid
</select>
</mapper>
测试方法:
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IAccountDao;
import com.zzg.jdbc.mybatis.dao.IRoleDao;
import com.zzg.jdbc.mybatis.entity.Role;
import com.zzg.jdbc.mybatis.entity.expand.AccountExpand;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisRoleOneToMany {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IRoleDao roleDao = session.getMapper(IRoleDao.class);
// 6.使用代理对象执行查询所有方法
getOneToMany(roleDao);
// 7.释放资源
session.close();
in.close();
}
public static void getOneToMany(IRoleDao roleDao) {
List<Role> roles = roleDao.findAll();
for (Role role : roles) {
System.out.println(role.getUsers().size());
}
}
}
<?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>
<properties>
<property name="jdbc.driverClass" value="com.mysql.cj.jdbc.Driver"/>
<property name="jdbc.url" value="jdbc:mysql://localhost:3306/myblog?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"/>
<property name="jdbc.username" value="root"/>
<property name="jdbc.password" value="123456"/>
</properties>
<!--
<plugins>
<plugin interceptor="com.zzg.jdbc.mybatis.plugins.PageInterceptor">
</plugin>
</plugins> -->
<!-- 配置 mybatis 的环境 -->
<environments default="mysql">
<!-- 配置 mysql 的环境 -->
<environment id="mysql">
<!-- 配置事务的类型 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置连接数据库的信息:用的是数据源(连接池) -->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClass}" />
<property name="url"
value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 告知 mybatis 映射配置的位置 -->
<mappers>
<!-- 基础版本 -->
<mapper resource="com/zzg/jdbc/mybatis/dao/IUserDao.xml" />
<mapper resource="com/zzg/jdbc/mybatis/dao/IAccountDao.xml" />
<mapper resource="com/zzg/jdbc/mybatis/dao/IRoleDao.xml" />
<!-- 注解版本 -->
<!-- <mapper class="com.zzg.jdbc.mybatis.dao.IUserDao" /> -->
</mappers>
</configuration>
2.3.2 用户与角色关系(1:n)
实体对象定义:建立User 对象的拓展对象UserRoleExpand
package com.zzg.jdbc.mybatis.entity.expand;
import java.util.List;
import com.zzg.jdbc.mybatis.entity.Role;
import com.zzg.jdbc.mybatis.entity.User;
@SuppressWarnings("serial")
public class UserRoleExpand extends User {
private List<Role> roles;
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
}
定义相关Dao接口和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.zzg.jdbc.mybatis.dao.IUserDao">
<resultMap type="com.zzg.jdbc.mybatis.entity.User" id="user">
<id property="id" column="id" />
<result column="username" property="username" />
<result column="birthday" property="birthday" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</resultMap>
<resultMap
type="com.zzg.jdbc.mybatis.entity.expand.UserExpand" id="userExpand"
extends="user">
<collection property="accounts"
ofType="com.zzg.jdbc.mybatis.entity.Account">
<id property="id" column="sid" />
<result column="uid" property="uid" />
<result column="name" property="name" />
<result column="money" property="money" />
</collection>
</resultMap>
<resultMap
type="com.zzg.jdbc.mybatis.entity.expand.UserRoleExpand"
id="userRoleExpand" extends="user">
<collection property="roles"
ofType="com.zzg.jdbc.mybatis.entity.Role">
<id property="id" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
</collection>
</resultMap>
<!--查询用户涉及角色 -->
<select id="findAllRoles" resultMap="userRoleExpand">
select u.*,r.id as rid,r.role_name,r.role_desc from user u
left outer join
user_role ur on ur.uid = u.id
left outer join role r on r.id = ur.rid
</select>
<!-- 配置查询所有操作 -->
<select id="findAll" resultMap="user">
select * from user
</select>
<select id="selectByPrimaryKey" resultType="java.lang.Integer"
resultMap="user">
select * from user where id = #{id}
</select>
<select id="selectByUserList" resultMap="userExpand">
select a.*, b.id as sid,
b.name, b.money, b.uid from user as a left join account as b on a.id =
b.uid where a.id =1;
</select>
</mapper>
测试代码:
package com.zzg.jdbc.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
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 com.zzg.jdbc.mybatis.dao.IRoleDao;
import com.zzg.jdbc.mybatis.dao.IUserDao;
import com.zzg.jdbc.mybatis.entity.Role;
import com.zzg.jdbc.mybatis.entity.expand.UserRoleExpand;
import com.zzg.jdbc.mybatis.plugins.PageUtil;
public class MyBatisUserOneOnMany {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
// 1.读取配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.创建 SqlSessionFactory 的构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 3.使用构建者创建工厂对象 SqlSessionFactory
SqlSessionFactory factory = builder.build(in);
// 4.使用 SqlSessionFactory 生产 SqlSession 对象
SqlSession session = factory.openSession();
PageUtil.setPagingParam(1, 2);
// 5.使用 SqlSession 创建 dao 接口的代理对象
IUserDao userDao = session.getMapper(IUserDao.class);
// 6.使用代理对象执行查询所有方法
getOneToMany(userDao);
// 7.释放资源
session.close();
in.close();
}
public static void getOneToMany(IUserDao userDao) {
List<UserRoleExpand> list = userDao.findAllRoles();
for (UserRoleExpand expand : list) {
System.out.println(expand.getRoles().size());
}
}
}
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。