多对一关联查询

一、数据库关系。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 AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `userName` varchar(50) DEFAULT NULL,  `gender` int(11) DEFAULT NULL,  `hobby` varchar(255) DEFAULT NULL,  `member` int(11) DEFAULT NULL,  `userAge` int(11) DEFAULT NULL,  `userAddress` varchar(200) DEFAULT NULL,  `reg_time` varchar(200) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

 二、创建跟表对应关系的model

public class Article {    private int id;    private User user;    private String title;    private String content;    setter()... getter()...}

public class User {    private int id;    private String userName;    private String userAge;    private Gender gender;    private Hobby hobby;    private Member member;    private String userAddress;    private Date regTime;       setters()&getters()}

 

 三、新建对应的mapper映射器组合

 

public interface ArticleMapper {    public List
     
       getUserArticles();}
     

 

　　组合一：

            
              SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content        FROM `user` u,`article` a        WHERE u.id=a.userid    

 

　　组合二：

     
             
              
               
               
               
           
          
              
               
               
               
               
           
              
              
               SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.title,a.content        FROM `user` u,`article` a        WHERE u.id=a.userid    
     

　　组合三：

     
             
              
               
               
               
           
          
              
        　　　　 
                SELECT u.id,u.userName,u.gender,u.userAddress,a.id aid,a.userid,a.title,a.content        FROM `user` u,`article` a        WHERE u.id=a.userid    
     

 

         
              
               
               
               
               
               
               
               
           
          
              select * from `user` where id = #{id}        
     

 

四、在configuration.xml 配置别名和注册映射器：

 

五、测试 

 

public class ArticleTest extends BaseTest{    public static void main(String[] args) {        selectArticlesWithUser();    }    public static void selectArticlesWithUser() {        SqlSessionFactory sessionFactory =getSession();        SqlSession session = sessionFactory.openSession();        ArticleMapper mapper = session.getMapper(ArticleMapper.class);        List
     
       list = mapper.getUserArticles();        for(Article a : list) {            System.out.println(a.getTitle() + "," + a.getUser().getUserName() + "," + a.getUser().getGender());        }    }}
     

一对多关联查询 

一、数据库关系。user表和article表示一对多的关系

二、创建跟表对应关系的model

public class User {    private int id;    private String userName;    private String userAge;    private Gender gender;    private Hobby hobby;    private Member member;    private String userAddress;    private Date regTime;    private List
     
       articleList;    setters()&getters()}
     

 

 三、新建对应的mapper映射器组合

@Repositorypublic interface UserMapper {        public List
     
       getUserArticlesById(Long id);    }
     

 

 组合一：

     
         
              
               
               
               
               
               
               
               
               
               
                   
                    
                    
                    
                
           
          
              SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content        FROM `user` u,`article` a        WHERE u.id=a.userid and u.id=#{id}    
     

 

组合二：这种方法适合SQL类似 SELECT * FROM table_name，就是没有where条件的情况

             
              
              
              
              
              
              
              
              
              
              
          
                 SELECT u.id, u.userName, u.userAddress, u.gender, a.id aid,a.userid, a.title, a.content        FROM `user` u,`article` a        WHERE u.id=a.userid and u.id=#{id}    

 

Article.xml

     
             
              
               
               
           
          
              SELECT            id, title, content        FROM            article a        WHERE            a.userid=#{userId}    
     

 

 

四、测试

import com.yihaomen.mybatis.dao.UserMapper;import com.yihaomen.mybatis.model.Article;import com.yihaomen.mybatis.model.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;import java.io.IOException;import java.io.Reader;import java.util.List;public class TestGetUser {    private static SqlSessionFactory sqlSessionFactory;    private static Reader reader;    static {        try {            reader = Resources.getResourceAsReader("configuration.xml");            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);        } catch (IOException e) {            e.printStackTrace();        }    }    public static SqlSessionFactory getSession() {        return sqlSessionFactory;    }    public static void getUserArticlesById(Long id) {        SqlSession session = sqlSessionFactory.openSession();        UserMapper userMapper = session.getMapper(UserMapper.class);        List
     
       users = userMapper.getUserArticlesById(id);        for(User u : users) {            System.out.println("------------------");            System.out.println(u.getUserName());            for(Article a : u.getArticleList()) {                System.out.println(a.getTitle() + "," + a.getContent());            }        }    }    public static void main(String[] args) {        getUserArticlesById(11L);    }}
     

 

 

 参考：

 [1] 《mybati实战教程》

 [2] CSDN博客，