美文网首页
MyBatis关联查询

MyBatis关联查询

作者: 花无缺_0159 | 来源:发表于2019-02-21 10:56 被阅读0次

    【目录】
    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>

    测试类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.CountryVo;
import com.test.mapper.CountryMapper;

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

    进行测试。

    测试结果

    相关文章

      网友评论

          本文标题：MyBatis关联查询

          本文链接：https://www.haomeiwen.com/subject/vblzeqtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MyBatis关联查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！