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&amp;serverTimezone=UTC&amp;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()); 
		} 
	} 
}