mybatis注解多表查询

63 阅读 0 评论 42 点赞

我是靠谱客的博主从容奇迹，最近开发中收集的这篇文章主要介绍mybatis注解多表查询，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

多对一(mybatis中称为一对一)

查询所有账户，并且获取每个账户所属的用户信息

创建两个实体类
User：

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;

Account：

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

    //多对一(mybatis中称之为一对一)的映射：一个账户只能属于一个用户
    private User user;

    public User getUser() {
        return user;
    }

创建两个DAO接口

IAccountDao：

  public interface IAccountDao {
    
        /**
         * 查询所有账户，并且获取每个账户所属的用户信息
         * property = "user"对应要封装的属性
         * column = "uid"用account中的uid去查user表
         * fetchType = FetchType.EAGER立即查询
         * @return
         */


@Select("select * from account")

 @Results(id = "accountMap",value = {
 @Result(id = true,column = "id",property = "id"),
 @Result(column = "uid",property = "uid"),
 @Result(column = "money",property = "money"),
 @Result(property = "user", column = "uid" ,one = @One(select = "sise.cn.dao.IUserDao. findById",fetchType = FetchType.EAGER))
})
    List<Account> findAll();
}

IUserDao：

public interface IUserDao {

    /**
     * 根据id查询用户
     * @param userId
     * @return
     */
    @Select("select * from user where id=#{id}")
    User findById(Integer userId);
}

test：

public class AccountTest {

    private  InputStream in;
    private SqlSessionFactory factory;
    private SqlSession sqlSession;
    private IAccountDao accountDao;

    @Before
    public void init() throws IOException {
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        accountDao = sqlSession.getMapper(IAccountDao.class);
    }

    @After
    public void destroy() throws IOException {

        sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }




    @Test
    public void test(){
        List<Account> accounts = accountDao.findAll();
        for (Account account : accounts) {
            System.out.println("----每个账户的信息-----");
            System.out.println(account);
            System.out.println(account.getUser());
        }
    }
}

一对多

一个用户对应多个账户

user实体类：

public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;

    //一对多关系映射：一个用户对应多个账户
    private List<Account> accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

IAccountDao增加一个方法：

 /**
     * 根据用户id查询账户信息
     * 此处的 uid是从
     * @Result(column = "id", property = "accounts", many = @Many(select = "sise.cn.dao.IAccountDao.findAccountByUid", fetchType = FetchType.LAZY))
     * 这里column = "id"传来的
     * @param 
     * @return
     */
     
    @Select("select * from account where uid = #{uid}")
    List<Account> findAccountByUid(Integer uid);

IUserDao中：
public interface IUserDao {

/**
 * 查询所有用户
 * @return
 */
@Select("select * from user")
@Results(id = "uesrMap",value = {
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "username", property = "username"),
        @Result(column = "address", property = "address"),
        @Result(column = "sex", property = "sex"),
        @Result(column = "birthday", property = "birthday"),
        @Result(column = "id", property = "accounts", many = @Many(select = "sise.cn.dao.IAccountDao.findAccountByUid", fetchType = FetchType.LAZY))
})
List<User> findAll();

}

test：

 @Test
    public void test(){
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println("----每个账户的信息-----");
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
    }