Mybatis 多对一查询

来源：互联网发布：mac版魔兽世界大脚插件编辑：程序博客网时间：2024/06/06 00:35

阅读前请先看标题为： 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:返回类型为基本数据类型、类类型

0 0