Mybatis 多对一查询

最新推荐文章于 2022-11-03 16:10:48 发布

RonaldoZH

最新推荐文章于 2022-11-03 16:10:48 发布

阅读量622

点赞数

分类专栏： Mybatis

本文链接：https://blog.csdn.net/RonaldoZH/article/details/50264233

版权

Mybatis 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

阅读前请先看标题为： Mybatis 单表查询的博文

1.所需sql

--创建序列

create sequence users_sequence
increment by 1
start with 1
maxvalue 999999999;

--建用户表

CREATE TABLE users(id number PRIMARY KEY , NAME VARCHAR2(20), age number);
INSERT INTO users(id,NAME, age) VALUES(users_sequence.nextval,'孤傲苍狼', 27);
INSERT INTO users(id,NAME, age) VALUES(users_sequence.nextval,'白虎神皇', 27);

--建文章表

CREATE TABLE articles(id number PRIMARY KEY , title VARCHAR2(20), userid number,content varchar2(20));
INSERT INTO articles(id,title, userid,content) VALUES(1,'探索星球起源',2,'内容1');
INSERT INTO articles(id,title, userid,content) VALUES(2,'探索星球起源2', 3,'内容2');

2.整体结构

3.文章及用户类源码如下：

package me.gacl.domain;

public class Article {

	 private int id;
    private User user;
    private String title;
    private String content;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
}

package me.gacl.domain;

public class User {
	
	//实体类的属性和表的字段名称一一对应,若sql语句中没有与属性相关字段，则取默认值
	     private int id;
	     private String name;
	     private int age;
	 
	     public int getId() {
	         return id;
	     }
	 
	     public void setId(int id) {
	         this.id = id;
	     }
	 
	     public String getName() {
	         return name;
	     }
	 
	     public void setName(String name) {
	         this.name = name;
	     }
	 
	     public int getAge() {
	         return age;
	     }
	 
	     public void setAge(int age) {
	         this.age = age;
	     }
	 
	     @Override
	    public String toString() {
	        return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
	     }
}

4.文章及用户的映射文件

4.1articleMapper.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="me.gacl.mapping.articleMapper">
    
    <span style="color: rgb(56, 53, 44); font-family: Verdana, Arial, Helvetica, sans-serif, ����; line-height: 16px; background-color: rgb(237, 238, 239);"><!-- 为了返回对象或list 类型而定义的returnMap --></span>
    <!-- Users 联合文章进行查询 方法之一的配置 (多对一的方式)  -->  
    <resultMap id="resultUserArticleList" type="Article">
       
        <result property="title" column="title" />
        <result property="content" column="content" />
        
        <association property="user" javaType="User">
           
            <result property="name" column="name" />
            <result property="age" column="age" />            
        </association>        
    </resultMap>
     
<span style="white-space:pre">	<span style="color: rgb(56, 53, 44); font-family: Verdana, Arial, Helvetica, sans-serif, ����; line-height: 16px; background-color: rgb(237, 238, 239);"><!-- 返回对象或list 的select 语句，注意 resultMap 的值是指向前面定义好的 --></span></span>
	<select id="getUserArticles" parameterType="int" resultMap="resultUserArticleList">
       select users.id,users.name,users.age,articles.id aid,articles.title,articles.content from users,articles 
              where users.id=articles.userid and users.id=#{id}
    </select>
</mapper>

4.1.1 articleMapper.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="me.gacl.mapping.articleMapper">
    
    
    <!-- Users 联合文章进行查询 方法之二的配置 (多对一的方式)  -->  
    <resultMap id="resultUserArticleList" type="Article">
       
        <result property="title" column="title" />
        <result property="content" column="content" />
        
        <association property="user" javaType="User" resultMap="resultUserList">
           
                        
        </association>        
    </resultMap>
     <resultMap id="resultUserList" type="User">      
            <result property="name" column="name" />
            <result property="age" column="age" />            
    </resultMap>
     

	<select id="getUserArticles" parameterType="int" resultMap="resultUserArticleList">
       select users.id,users.name,users.age,articles.id aid,articles.title,articles.content from users,articles 
              where users.id=articles.userid and users.id=#{id}
    </select>
</mapper>

4.2 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="me.gacl.mapping.userMapper">
 <select id="getUser" parameterType="map" 
        resultType="User">
        select * from users where id=#{id} and name=#{name}
    </select>
    <select id="getUserList" 
        resultType="me.gacl.domain.User">
        select * from users 
    </select>
</mapper>

5.测试类及配置文件

5.1测试类

package me.gacl.test;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import me.gacl.domain.Article;
import me.gacl.domain.User;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


public class Test {

	public static void main(String[] args) throws IOException {
		 //mybatis的配置文件
        //String resource = "conf.xml";
        //使用类加载器加载mybatis的配置文件（它也加载关联的映射文件）
        //InputStream is = Test.class.getClassLoader().getResourceAsStream(resource);
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
        //Reader reader = Resources.getResourceAsReader(resource); 
        //构建sqlSession的工厂
        //SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession();
        /**
         * 映射sql的标识字符串，
         * me.gacl.mapping.userMapper是userMapper.xml文件中mapper标签的namespace属性的值，
         * getUser是select标签的id属性值，通过select标签的id属性值就可以找到要执行的SQL
         */
        String statement = "me.gacl.mapping.userMapper.getUser";//映射sql的标识字符串
        //执行查询返回一个唯一user对象的sql
        User wser1 = new User();
        wser1.setId(2);
        wser1.setName("孤傲苍狼");
        Map map = new HashMap();
        map.put("id", 2);
        map.put("name","孤傲苍狼" );
        User user = (User)session.selectOne(statement, map);
//      List<User> list = (List)session.selectList(statement);
//      for(User user:list){
//    	  System.out.println(user); 
//      }
        
//        int user = (Integer)session.selectOne(statement);
        System.out.println(user); 
        
        
        List<Article> articles = session.selectList("me.gacl.mapping.articleMapper.getUserArticles",2);
       
        for(Article article:articles){
            System.out.println(article.getTitle()+":"+article.getContent()+
                    ":作者是:"+article.getUser().getName()+":年龄:"+
                     article.getUser().getAge()+"id:"+article.getId());
        }
	}
}

5.2 配置文件 conf.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>
  <span style="white-space:pre">	</span><!--设置别名，在映射文件里可以直接使用别名的类-->
  	<typeAliases> 
        <typeAlias alias="User" type="me.gacl.domain.User"/> 
        <typeAlias alias="Article" type="me.gacl.domain.Article"/> 
    </typeAliases>
    
  
  
     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC" />
             <!-- 配置数据库连接信息 -->
             <dataSource type="POOLED">
                 <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@192.168.0.212:1521:orcl" />
                <property name="username" value="newjw_zgzf141024" />
                <property name="password" value="newjw_zgzf141024" />
             </dataSource>
         </environment>
     </environments>
      <mappers>
      
      	<mapper resource="me/gacl/mapping/userMapper.xml"/>
      	<mapper resource="me/gacl/mapping/articleMapper.xml"/>
      </mappers>
 </configuration>

配置文成，运行即可出现上图控制台的结果。

注意：最好配置别名，在映射文件中使用特别方便，在对应类中有属性，而查询语句中没有，则属性值取默认值（类中的属性最好与表对应，否则很麻烦）

parameterType：传递多个参数可以用bean 反射。也可以用map。亦可以使用基本数据类型。parameterType="map" ，查询语句

#{start} 中的start 就是 map的key，若是 bean则为字段名。

resultMap：返回类型为resultmap，是在统一映射文件中配置的resultmap标签（一般为类）

resultType:返回类型为基本数据类型、类类型