MyBatis关联查询

2019-02-21 本文已影响0人花无缺_0159

【目录】
1 一对一
2 一对多

在项目开始之前准备好数据库。
数据库database：

创建User表

插入一些数据

创建country表

插入一些数据

1 一对一

关系图

项目目录

相关代码：
主配置文件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>

  <environments default="development">
    <environment id="development">
    <!-- 使用jdbc的事务 -->
      <transactionManager type="JDBC"/>
      <!-- 使用连接池 连接数据库 -->
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/database?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="admin"/>
      </dataSource>
    </environment>
  </environments>
  
 <!-- 配置映射器的位置 -->
  <mappers>
    <mapper resource="mapper/UserMapper.xml"/> 
   </mappers> 
   
</configuration>

User.java：

package com.test.bean;

import java.util.Date;

public class User {

    /**
     * CREATE TABLE `user` (
      `u_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
      `u_username` VARCHAR(64) NOT NULL COMMENT '用户名',
      `u_password` VARCHAR(64) NOT NULL COMMENT '用户密码',
      `u_sex` VARCHAR(16) DEFAULT NULL COMMENT '用户性别',
      `u_createTime` DATETIME DEFAULT NULL COMMENT '用户创建时间',
      `u_cid` INT(11) NOT NULL COMMENT '用户国家id',
     */
    
    private Integer u_id;
    private String u_username;
    private String u_password;
    private String u_sex;
    private Date u_createTime;
    private Integer u_cid;
    
    public Integer getU_id() {
        return u_id;
    }
    public void setU_id(Integer u_id) {
        this.u_id = u_id;
    }
    public String getU_username() {
        return u_username;
    }
    public void setU_username(String u_username) {
        this.u_username = u_username;
    }
    public String getU_password() {
        return u_password;
    }
    public void setU_password(String u_password) {
        this.u_password = u_password;
    }
    public String getU_sex() {
        return u_sex;
    }
    public void setU_sex(String u_sex) {
        this.u_sex = u_sex;
    }
    public Date getU_createTime() {
        return u_createTime;
    }
    public void setU_createTime(Date u_createTime) {
        this.u_createTime = u_createTime;
    }
    public Integer getU_cid() {
        return u_cid;
    }
    public void setU_cid(Integer u_cid) {
        this.u_cid = u_cid;
    }
    @Override
    public String toString() {
        return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex
                + ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]";
    }
}

Country.java：

package com.test.bean;

public class Country {
//    `c_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '国家id',
//    `c_countryname` VARCHAR(128) NOT NULL COMMENT '国家名称',
//    `c_capital` VARCHAR(128) DEFAULT NULL COMMENT '国家首都名称',
    
    private Integer id;
    private String c_countryname;
    private String c_capital;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getC_countryname() {
        return c_countryname;
    }
    public void setC_countryname(String c_countryname) {
        this.c_countryname = c_countryname;
    }
    public String getC_capital() {
        return c_capital;
    }
    public void setC_capital(String c_capital) {
        this.c_capital = c_capital;
    }
    @Override
    public String toString() {
        return "Country [id=" + id + ", c_countryname=" + c_countryname + ", c_capital=" + c_capital + "]";
    }

}

User包装类UserVo.java：

package com.test.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 + ", getU_id()=" + getU_id() + ", getU_username()=" + getU_username()
                + ", getU_sex()=" + getU_sex() + "]";
    }
    
    
    
}

动态代理接口UserMapper.java：

package com.test.mapper;

import java.util.List;

import com.test.bean.UserVo;

public interface UserMapper {

    //查询所有用户包装类
        public List<UserVo> selectAllUserVo();
}

映射文件UserMapper.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.test.mapper.UserMapper">
<!--        //查询所有用户包装类
    public List<UserVo> selectAllUserVo(); -->
    <resultMap type="com.test.bean.UserVo" id="uservolist">
    <!-- 多表查询时要查询的字段都不可以省略-->
    <!-- id和result功能相同，一般id写主键，这样可以提高性能-->
        <id property="u_id" column="u_id"/>
        <result property="u_username" column="u_username"/>
        <result property="u_sex" column="u_sex"/>
        <association property="country" javaType="com.test.bean.Country">
            <id property="id" column="c_id"/>
            <result property="c_countryname" column="c_countryname"/>
        </association>
    </resultMap>
    <select id="selectAllUserVo" resultMap="uservolist">
        SELECT 
        u.`u_id`, 
        u.`u_username`,
        u.`u_sex`, 
        c.`c_id`, 
        c.`c_countryname` 
        FROM USER u 
        LEFT JOIN 
        country c 
        ON u.`u_cid`=c.`c_id`
    </select>
</mapper>

测试类MapperTest.java：

package com.test.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.test.bean.UserVo;
import com.test.mapper.UserMapper;


public class MapperTest {
        
    @Test
    public void Test_selectAllUserVo() 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);
        }
    }
    
}

进行测试。

测试结果

2 一对多

关系图

项目目录

主配置文件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>

  <environments default="development">
    <environment id="development">
    <!-- 使用jdbc的事务 -->
      <transactionManager type="JDBC"/>
      <!-- 使用连接池 连接数据库 -->
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/database?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="admin"/>
      </dataSource>
    </environment>
  </environments>
  
 <!-- 配置映射器的位置 -->
  <mappers>
    <mapper resource="mapper/CountryMapper.xml"/> 
   </mappers> 
   
</configuration>

Country.java：

package com.test.bean;

public class Country {
//    `c_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '国家id',
//    `c_countryname` VARCHAR(128) NOT NULL COMMENT '国家名称',
//    `c_capital` VARCHAR(128) DEFAULT NULL COMMENT '国家首都名称',
    
    private Integer id;
    private String c_countryname;
    private String c_capital;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getC_countryname() {
        return c_countryname;
    }
    public void setC_countryname(String c_countryname) {
        this.c_countryname = c_countryname;
    }
    public String getC_capital() {
        return c_capital;
    }
    public void setC_capital(String c_capital) {
        this.c_capital = c_capital;
    }
    @Override
    public String toString() {
        return "Country [id=" + id + ", c_countryname=" + c_countryname + ", c_capital=" + c_capital + "]";
    }

}

User.java：

package com.test.bean;

import java.util.Date;

public class User {

    /**
     * CREATE TABLE `user` (
      `u_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
      `u_username` VARCHAR(64) NOT NULL COMMENT '用户名',
      `u_password` VARCHAR(64) NOT NULL COMMENT '用户密码',
      `u_sex` VARCHAR(16) DEFAULT NULL COMMENT '用户性别',
      `u_createTime` DATETIME DEFAULT NULL COMMENT '用户创建时间',
      `u_cid` INT(11) NOT NULL COMMENT '用户国家id',
     */
    
    private Integer u_id;
    private String u_username;
    private String u_password;
    private String u_sex;
    private Date u_createTime;
    private Integer u_cid;
    
    public Integer getU_id() {
        return u_id;
    }
    public void setU_id(Integer u_id) {
        this.u_id = u_id;
    }
    public String getU_username() {
        return u_username;
    }
    public void setU_username(String u_username) {
        this.u_username = u_username;
    }
    public String getU_password() {
        return u_password;
    }
    public void setU_password(String u_password) {
        this.u_password = u_password;
    }
    public String getU_sex() {
        return u_sex;
    }
    public void setU_sex(String u_sex) {
        this.u_sex = u_sex;
    }
    public Date getU_createTime() {
        return u_createTime;
    }
    public void setU_createTime(Date u_createTime) {
        this.u_createTime = u_createTime;
    }
    public Integer getU_cid() {
        return u_cid;
    }
    public void setU_cid(Integer u_cid) {
        this.u_cid = u_cid;
    }
    @Override
    public String toString() {
        return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + ", u_sex=" + u_sex
                + ", u_createTime=" + u_createTime + ", u_cid=" + u_cid + "]";
    }
}

Country包装类CountryVo.java：

package com.test.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 "CountryVo [userList=" + userList + ", toString()=" + super.toString() + "]";
        
        return super.toString()  + "   userList = " + userList;
    }
    
}

动态代理接口CountryMapper.java：

package com.test.mapper;

import java.util.List;

import com.test.bean.CountryVo;

public interface CountryMapper {

    //查询所有CountryVo
    List<CountryVo> selectAllCountryVo();
}

映射文件CountryMapper.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.test.mapper.CountryMapper">
    
    <!--        //查询所有CountryVo
    List<CountryVo> selectAllCountryVo(); -->
    <resultMap type="com.test.bean.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="com.test.bean.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_cid`=c.`c_id`
    </select>
    
</mapper>