mybatis ----数据级联查询（多对一）

痛定思痛。 2022-08-26 03:51 9阅读 0赞

工程的目录结构：

![SouthEast][]

有两个表，一个文章表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>
      
      
      <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>
         
           <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">
           
           <resultMap type="Article" id="resultUserArticleList">
                 <id property="id" column="aid"/>
                 <result property="title" column="title"/>
                 <result property="content" column="content"/>
                 
                 
                <association property="user" javaType="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:地址:上海

[SouthEast]: /images/20220824/257d5e048a18484cb43329a0f5ba5fce.png