Mybatis级联查询

转自：http://blog.csdn.net/yulei_qq/article/details/22039815

工程的目录结构：

有两个表，一个文章表article ，一个用户表user。

 

create table  article (id int(11) not null auto_increment,
                       userid int(11) not null,
                       title varchar(100) not null,
                       content text not null,
                       primary key (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into article(id,userid,title,content) values(1,1,'test_title','text_content');

insert into article(id,userid,title,content) values(2,1,'test_title_2','text_content_2');
insert into article(id,userid,title,content) values(3,1,'test_title_3','text_content_3');
insert into article(id,userid,title,content) values(4,1,'test_title_4','text_content_4');


create table user (id int(11) not null auto_increment,
                   userName varchar(50) default null,
                   userAge int(11) default null,
                   userAddress varchar(200) default null,
                   primary key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into user(id,userName,userAge,userAddress) values(1,'summer','100','上海');

现在要查询,根据用户的ID来查询他所拥有的文章。那么需要编写SQL语句如下：

select a.id, a.userName ,a.userAddress ,b.id aid, b.title,b.content
                                          from user a,article b
                                         where a.id=b.userid and a.id=#{id}

现在就来贴下相关的JAVA代码和映射文件吧,该说的都在注释里了。

 

User.java

 

package com.mybatis.model;

public class User {
 private int id;
 private String userName;
 private String userAge;
 private String userAddress;

public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getUserName() {
    return userName;
}
public void setUserName(String userName) {
    this.userName = userName;
}
public String getUserAge() {
    return userAge;
}
public void setUserAge(String userAge) {
    this.userAge = userAge;
}
public String getUserAddress() {
    return userAddress;
}
 public void setUserAddress(String userAddress) {
    this.userAddress = userAddress;
  }

}

Article.java

 

 

package com.mybatis.model;

public class Article {

    private int id;
    private User user; //文章的用户定义一个User对象，而不是int 类型
    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;
    }

}

接口类

 

IUserDao.java

 

package com.mybatis.dao;

import java.util.List;
import com.mybatis.model.Article;

public interface IUserDao {

 public List<Article> getUserArticles(int id);
}

总配置文件configution.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>
  <typeAliases>
    <!-- 给实体类去一个别名  -->
        <typeAlias type="com.mybatis.model.User" alias="User"/>
        <typeAlias type="com.mybatis.model.Article" alias="Article"/>
  </typeAliases>

  <!-- 数据源配置，这里用MySQL数据库 -->
  <environments default="development">
     <environment id="development">
         <transactionManager type="JDBC"/>
         <dataSource type="POOLED">
               <property name="driver" value="com.mysql.jdbc.Driver"/>
               <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>
               <property name="username" value="root"/>
               <property name="password" value="123456"/>
         </dataSource>
     </environment>
  </environments>

  <mappers>
     <!-- book.xml装载进来，等同于把Dao的实现类装载进来 -->
       <mapper resource="com/mybatis/model/User.xml"/>
  </mappers>

</configuration>

User.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.mybatis.dao.IUserDao">
       <!-- User联合Article进行查询 (多对一的方式)-->
       <resultMap type="Article" id="resultUserArticleList">
             <id property="id" column="aid"/>
             <result property="title" column="title"/>
             <result property="content" column="content"/>

             <!--关联一个用户 ,如果是关联多个的话,就需要用collection了-->
            <association property="user" javaType="User"> <!-- 这个 property="user" 对应的是Article中的User user属性-->
                   <id property="id" column="id"/>
                   <result property="userName" column="userName"/>
                   <result property="userAddress" column="userAddress"/>
            </association>
       </resultMap>

       <select id="getUserArticles" parameterType="int" resultMap="resultUserArticleList">
               select a.id, a.userName ,a.userAddress ,b.id aid, b.title,b.content
                                          from user a,article b
                                         where a.id=b.userid and a.id=#{id}
       </select>
</mapper>

总后，编写个测试类.

 

 

package com.mybatis.test;

import java.io.IOException;
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 com.mybatis.dao.IUserDao;
import com.mybatis.model.Article;

public class Test {

    /***
     * 获得MyBatis SqlSessionFactory
     * SqlSessionFactory 负责创建SqlSession ,一旦创建成功，就可以用SqlSession实例来执行映射语句
     * ，commit,rollback,close等方法
     * @return
     */
    private static SqlSessionFactory getSessionFactory(){
        SqlSessionFactory sessionFactory=null;
        String resource="configuration.xml";
         try {
            sessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sessionFactory;
    }

    /**
     * main 方法
     * @param args
     */
    public static void main(String[] args) {

          SqlSession session=getSessionFactory().openSession();
     try {
         IUserDao userDao=session.getMapper(IUserDao.class);
         //传入用户的id=1
         List<Article> listArticle=userDao.getUserArticles(1);
         for(Article article:listArticle){
             System.out.println(article.getTitle()+":"+article.getContent()+
                     ":作者是:"+article.getUser().getUserName()+":地址:"+
                      article.getUser().getUserAddress());
         }
        } catch (Exception e) {
            e.printStackTrace();
        }

        finally{
            session.close();
        }
    }
}

运行后结果如下：

 

 

test_title:text_content:作者是:summer:地址:上海
test_title_2:text_content_2:作者是:summer:地址:上海
test_title_3:text_content_3:作者是:summer:地址:上海
test_title_4:text_content_4:作者是:summer:地址:上海