MyBatis的多表查询只需要DAO接口和sql语句即可,主要的是mapper里的返回字段要正确
现在有4张表
SELECT * FROM mybatis.user;select * from mybatis.role;select * from mybatis.account;select * from mybatis.user_role;
user
role
account
user_role
工程结构
DAO层
import com.jinke.domain.Account;
import java.util.List;
public interface AccountDao {
List<Account> findAll();}
import com.jinke.domain.Role;
import java.util.List;
public interface RoleDao {
List<Role> findAll();}
import com.jinke.domain.User;
import java.util.List;
public interface UserDao { List<User> findAll();}
实体类
import java.io.Serializable;
public class Account implements Serializable {
private Integer id; private Integer uid; private Double money;
private User user;
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public Integer getUid() { return uid; }
public void setUid(Integer uid) { this.uid = uid; }
public Double getMoney() { return money; }
public void setMoney(Double money) { this.money = money; }
public User getUser() { return user; }
public void setUser(User user) { this.user = user; }
@Override public String toString() { return "Account{" + "id=" + id + ", uid=" + uid + ", money=" + money + '}'; }}
import java.io.Serializable;import java.util.List;
public class Role implements Serializable { private Integer id; private String rolename; private String roledesc;
private List<User> users;
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getRolename() { return rolename; }
public void setRolename(String rolename) { this.rolename = rolename; }
public String getRoledesc() { return roledesc; }
public void setRoledesc(String roledesc) { this.roledesc = roledesc; }
public List<User> getUsers() { return users; }
public void setUsers(List<User> users) { this.users = users; }
@Override public String toString() { return "Role{" + "id=" + id + ", rolename='" + rolename + '\'' + ", roledesc='" + roledesc + '\'' + '}'; }}
import java.io.Serializable;import java.util.Date;import java.util.List;
public class User implements Serializable { private Integer id; private String username; private String address; private String sex; private Date birthday;
private List<Role> roles;
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getUsername() { return username; }
public void setUsername(String username) { this.username = username; }
public String getAddress() { return address; }
public void setAddress(String address) { this.address = address; }
public String getSex() { return sex; }
public void setSex(String sex) { this.sex = sex; }
public Date getBirthday() { return birthday; }
public void setBirthday(Date birthday) { this.birthday = birthday; }
public List<Role> getRoles() { return roles; }
public void setRoles(List<Role> roles) { this.roles = roles; }
@Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", address='" + address + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + '}'; }}
sql配置文件
AccountDao.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.jinke.dao.AccountDao">
<resultMap id="accountUserMap" type="account"> <id property="id" column="aid"/> <result property="uid" column="uid"/> <result property="money" column="money"/> <!--一对一的关系映射,配置封装user的内容--> <association property="user" column="uid" javaType="user"> <id property="id" column="id"/> <result column="username" property="username"/> <result column="address" property="address"/> <result column="sex" property="sex"/> <result column="birthday" property="birthday"/> </association> </resultMap>
<select id="findAll" resultMap="accountUserMap"> select * from user a,account b where a.id = b.uid </select></mapper>
RoleDao.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.jinke.dao.RoleDao">
<resultMap id="roleMap" type="role"> <id property="id" column="rid"/> <result property="rolename" column="rolename"/> <result property="roledesc" column="roledesc"/> <collection property="users" ofType="user"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="sex" column="sex"/> <result property="address" column="address"/> <result property="birthday" column="birthday"/> </collection> </resultMap>
<select id="findAll" resultMap="roleMap"> select u.*,r.id as rid,r.rolename,r.roledesc from mybatis.role as r left outer join mybatis.user_role as ur on r.id = ur.rid left outer join mybatis.user as u on u.id = ur.uid </select></mapper>
UserDao.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.jinke.dao.UserDao">
<resultMap id="userAccountMap" type="user"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="address" column="address"/> <result property="sex" column="sex"/> <result property="birthday" column="birthday"/> <collection property="roles" ofType="role"> <id property="id" column="rid"/> <result property="rolename" column="rolename"/> <result property="roledesc" column="roledesc"/> </collection> </resultMap>
<select id="findAll" resultMap="userAccountMap"> select u.*,r.id as rid,r.rolename,r.roledesc from mybatis.user as u left outer join mybatis.user_role as ur on u.id = ur.uid left outer join mybatis.role as r on r.id = ur.rid </select>
<select id="findById" parameterType="Integer" resultType="user"> select * from user where id = #{uid}; </select></mapper>
测试类
import com.jinke.dao.AccountDao;import com.jinke.dao.RoleDao;import com.jinke.dao.UserDao;import com.jinke.domain.Account;import com.jinke.domain.Role;import com.jinke.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;import org.junit.After;import org.junit.Before;import org.junit.Test;
import java.io.InputStream;import java.util.List;
public class MyBatisTest {
private InputStream in; private SqlSession sqlSession; private UserDao userDao; private AccountDao accountDao; private RoleDao roleDao;
@Before public void init() throws Exception { in = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); sqlSession = factory.openSession(); userDao = sqlSession.getMapper(UserDao.class); accountDao = sqlSession.getMapper(AccountDao.class); roleDao = sqlSession.getMapper(RoleDao.class); }
@After public void destroy() throws Exception { sqlSession.commit(); sqlSession.close(); in.close(); }
@Test public void testFindAccount() { List<Account> accounts = accountDao.findAll(); for (Account account : accounts) { System.out.println(account + " " + account.getUser()); } }
@Test public void testFindUser() { List<User> users = userDao.findAll(); for (User user : users) { System.out.println(user + " " + user.getRoles()); } }
@Test public void testFindRole() { List<Role> roles = roleDao.findAll(); for (Role role : roles) { System.out.println(role + " " + role.getUsers()); } }}
测试结果