JavaWeb_(Mybatis框架)关联查询_六

　　系列博文：　　

　　　　JavaWeb_(Mybatis框架)JDBC操作数据库和Mybatis框架操作数据库区别_一　　　　传送门

　　　　JavaWeb_(Mybatis框架)使用Mybatis对表进行增、删、改、查操作_二　　　　　　传送门

　　　　JavaWeb_(Mybatis框架)Mapper动态代理开发_三　　　　　　　　　　　　　　　传送门

　　　　JavaWeb_(Mybatis框架)主配置文件介绍_四　　　　　　　　　　　　　　　　　传送门

　　　　JavaWeb_(Mybatis框架)输入和输出参数_五　　　　　　　　　　　　　　　　　　传送门

　　　　JavaWeb_(Mybatis框架)关联查询_六传送门　　　　　　　　　　　　　　　　　　传送门

　　　　JavaWeb_(Mybatis框架)动态sql_七传送门　　　　　　　　　　　　　　　　　　　传送门

　　数据库中存在两张表user表和country表

　　 JavaWeb_(Mybatis框架)关联查询_六-LMLPHP 　　

　　User表和Country表属于一对一：一个用户属于一个国家

　　Country表和User表属于一对多：一个国家有多个用户

　　 JavaWeb_(Mybatis框架)关联查询_六-LMLPHP

一、一对一的查询

　　通过user为基准，查询user表中所有数据

　　 JavaWeb_(Mybatis框架)关联查询_六-LMLPHP

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

JavaWeb_(Mybatis框架)关联查询_六-LMLPHP

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）

　　 JavaWeb_(Mybatis框架)关联查询_六-LMLPHP

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

JavaWeb_(Mybatis框架)关联查询_六-LMLPHP

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