正文
JavaWeb_(Mybatis框架)关联查询_六
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
系列博文：
JavaWeb_(Mybatis框架)JDBC操作数据库和Mybatis框架操作数据库区别_一传送门
JavaWeb_(Mybatis框架)使用Mybatis对表进行增、删、改、查操作_二 传送门
JavaWeb_(Mybatis框架)Mapper动态代理开发_三 传送门
JavaWeb_(Mybatis框架)主配置文件介绍_四 传送门
JavaWeb_(Mybatis框架)输入和输出参数_五 传送门
JavaWeb_(Mybatis框架)关联查询_六传送门 传送门
JavaWeb_(Mybatis框架)动态sql_七传送门传送门
数据库中存在两张表user表和country表
User表和Country表属于一对一：一个用户属于一个国家
Country表和User表属于一对多：一个国家有多个用户
一、一对一的查询
通过user为基准，查询user表中所有数据
SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id
Gary.sql
UserVO.class中包含Country对象，通过用户去查询它属于哪一个国家
package com.Gary.bean;//包装类
public class UserVo extends User{ //包装类 private Country country; public Country getCountry() {
return country;
} public void setCountry(Country country) {
this.country = country;
} @Override
public String toString() {
return "UserVo [country=" + country + ", toString()=" + super.toString() + ", getU_id()=" + getU_id()
+ ", getU_password()=" + getU_password() + "]";
}}
UserVo.class
在UserMapper.xml中编写SQL语句，查询所有用户包装类
<id> 和 <result>区别：
id 和 result 元素都将一个列的值映射到一个简单数据类型（String, int, double, Date 等）的属性或字段。
这两者之间的唯一不同是，id 元素表示的结果将是对象的标识属性，这会在比较对象实例时用到。 这样可以提高整体的性能，尤其是进行缓存和嵌套结果映射（也就是连接映射）的时候。
<!-- 查询所有用户包装类 -->
<resultMap type="UserVo" id="uservolist">
<!-- 必须把想要查询数据库的语句都写上 -->
<id property="u_id" column="u_id"/>
<id property="u_username" column="u_username"/>
<id property="u_sex" column="u_sex"/>
<association property="country" javaType="Country">
<!-- 必须把想要查询数据库的语句都写上 -->
<result property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
</association>
</resultMap> <select id="selectAllUserVo" resultMap="uservolist">
SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id
</select>
<?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.Gary.mapper.UserMapper"> <select id="selectUserById" parameterType="Integer" resultType="user">
select * from user where u_id = #{id}
</select> <!-- #{}占位符 尽量使用#{}来解决问题 -->
<!-- ${}字符串拼接 容易sql注入 (or 1 = 1) --> <!-- ${value}中间的字符串一定需要使用value -->
<select id="selectUserByName" parameterType="String" resultType="com.Gary.bean.User">
<!-- select * from user where u_username like '%${value}%' -->
select * from user where u_username like "%"#{name}"%"
</select> <!-- 添加用户 参数为全包名 -->
<insert id="insertUser" parameterType="com.Gary.bean.User">
insert into user values(null,#{u_username},#{u_password},#{u_sex},#{u_createTime},#{u_cid})
</insert> <!-- 根据id修改username字段的语句 -->
<update id="updateUser" parameterType="com.Gary.bean.User">
update user set u_username = #{u_username} where u_id = #{u_id}
</update> <!-- 根据id删除用户 -->
<delete id="deleteUserById" parameterType="Integer">
delete from user where u_id = #{id}
</delete> <!-- 根据UserVo中的User对象的u_id去查询查询用户 -->
<select id="selectUserByVoId" parameterType="UserVo" resultType="user">
select * from user where u_id = #{user.u_id}
</select> <!-- 查询用户的总条数 -->
<select id="selectUserCount" resultType="Integer">
select count(*) from user
</select> <!-- 查询所有用户包装类 -->
<resultMap type="UserVo" id="uservolist">
<!-- 必须把想要查询数据库的语句都写上 -->
<id property="u_id" column="u_id"/>
<id property="u_username" column="u_username"/>
<id property="u_sex" column="u_sex"/>
<association property="country" javaType="Country">
<!-- 必须把想要查询数据库的语句都写上 -->
<result property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
</association>
</resultMap> <select id="selectAllUserVo" resultMap="uservolist">
SELECT * FROM USER u LEFT JOIN country c ON u.u_cid = c.c_id
</select></mapper>
UserMapper.xml
package com.Gary.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 org.junit.Test;import com.Gary.bean.Country;
import com.Gary.bean.User;
import com.Gary.bean.UserVo;
import com.Gary.mapper.CountryMapper;
import com.Gary.mapper.UserMapper;public class MapperTest6 { @Test
public void Test6() throws IOException {
//读取配置文件
String resource = "sqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); //创建sqlSessionFactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in); //生产一个sqlSession
SqlSession session = ssf.openSession(); UserMapper mapper = session.getMapper(UserMapper.class); List<UserVo> list = mapper.selectAllUserVo(); for(UserVo userVo:list) {
System.out.println(userVo);
} }}
MapperTest6.java
二、一对多的查询
通过country表查询用户（用户id = 国家id）
SELECT c.c_id ,c.c_countryname , c.c_capital , u.u_id , u.u_username FROM country c LEFT JOIN USER u ON u.u_id = c.c_id
Gary.sql
CountryVo.class中包含User对象集合
package com.Gary.bean;import java.util.List;public class CountryVo extends Country{ //需要维护一个User集合 private List<User> userList; public List<User> getUserList() {
return userList;
} public void setUserList(List<User> userList) {
this.userList = userList;
} @Override
public String toString() {
return super.toString() + "userList = " + userList;
}}
CountryVo.java
查询国家中存在的用户存在的SQL语句
<!-- 查询所有CountryVo -->
<resultMap type="CountryVo" id="countryVo">
<id property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
<result property="c_capital" column="c_capital"/>
<!-- 一对多关系 -->
<collection property = "userList" ofType="User">
<id property="u_id" column="u_id"/>
<result property="u_username" column="u_username"/>
</collection>
</resultMap>
<select id="selectAllCountryVo" resultMap="countryVo">
SELECT c.c_id ,c.c_countryname , c.c_capital , u.u_id , u.u_username FROM country c LEFT JOIN USER u ON u.u_id = c.c_id
</select>
<?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.Gary.mapper.CountryMapper"> <resultMap type="Country" id="country">
<result property="id" column="c_id"/>
</resultMap> <!-- 查询所有 -->
<select id="selectAll" resultMap="country">
select * from country
</select> <!-- 查询所有CountryVo -->
<resultMap type="CountryVo" id="countryVo">
<id property="id" column="c_id"/>
<result property="c_countryname" column="c_countryname"/>
<result property="c_capital" column="c_capital"/>
<!-- 一对多关系 -->
<collection property = "userList" ofType="User">
<id property="u_id" column="u_id"/>
<result property="u_username" column="u_username"/>
</collection>
</resultMap>
<select id="selectAllCountryVo" resultMap="countryVo">
SELECT c.c_id ,c.c_countryname , c.c_capital , u.u_id , u.u_username FROM country c LEFT JOIN USER u ON u.u_id = c.c_id
</select></mapper>
CountryMapper.xml
package com.Gary.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 org.junit.Test;import com.Gary.bean.Country;
import com.Gary.bean.CountryVo;
import com.Gary.bean.User;
import com.Gary.bean.UserVo;
import com.Gary.mapper.CountryMapper;
import com.Gary.mapper.UserMapper;public class MapperTest7 { @Test
public void Test7() throws IOException {
//读取配置文件
String resource = "sqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); //创建sqlSessionFactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in); //生产一个sqlSession
SqlSession session = ssf.openSession(); CountryMapper mapper = session.getMapper(CountryMapper.class);
List<CountryVo> list = mapper.selectAllCountryVo(); for(CountryVo countryVo : list){
System.out.println(countryVo);
} }}
MapperTest7.java
