MyBatis多表查询
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());
}
}
}
测试结果
欢迎关注我的微信公众号：安卓圈
