mybatis查询
针对一对一、多对多查询实现
一对一
一个account表对应一个user用户
一个user用户可能存在多个account账户信息
对账户account表来说,一对一的话,类Account中就需要存在一个user信息。通过getUser()
/setUser(User user)
来实现对user的获取/赋值
package com.example.domain;
public class Account {
public int id;
public String accountName;
public double money;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccountName() {
return accountName;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", accountName='" + accountName + '\'' +
", money=" + money +
", user=" + user +
'}';
}
}
那么对user类来说,account应该是一个集合 List<Account> accounts;
package com.example.domain;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/*
*create table User(
id INT PRIMARY KEY AUTO_INCREMENT,
userName VARCHAR(100),
birthDay DATE,
sex VARCHAR(1),
address varchar(512))
delete from user
SELECT * FROM USER
insert into user(userName,birthDay,sex,address)values('张三','1990-02-01','男','北京')
insert into user(userName,birthDay,sex,address)values('李四','1990-02-01','男','北京');
insert into user(userName,birthDay,sex,address)values('王五','1990-02-01','男','北京');
* 开发流程
* 1、搭建工程
* 2、创建user 和IUser
* 3、创建主配置文件
* 4、创建配置映射文件
*
* 环境搭建的注意事项:
* 1、创建IUserDao.xml和IUserDao.java时是为了和我们之前的知识保持一致
* 2、idea创建目录和创建包是不一样的
* 3、IUserDao.xml映射配置文件的目录必须与java对应起来
* 4、映射配置文件的mapper标签 namespce必须是全限定名
* 5、映射配置文件的mapper标签里的Id必须是IUserDao中的方法名,应该是mybatis根据id来查找。
* */
public class User implements Serializable {
private Integer userId;
private String userName;
private Date userBirthDay;
private String userSex;
private String userAddress;
List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Date getUserBirthDay() {
return userBirthDay;
}
public void setUserBirthDay(Date userBirthDay) {
this.userBirthDay = userBirthDay;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userBirthDay=" + userBirthDay +
", userSex='" + userSex + '\'' +
", userAddress='" + userAddress + '\'' +
", accounts=" + accounts +
'}';
}
}
访问账户中关联人信息
设置IUserDao.xml
<resultMap id="useraccount" type="user">
<id column="id" property="userId"></id>
<result column="name" property="userName"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
<result column="birthday" property="userBirthDay"></result>
<collection property="accounts" ofType="account">
<id column="aid" property="id"></id>
<result column="accountname" property="accountName"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<select id="findUserAccountInfo" resultMap="useraccount">
select user.*,account.id aid,account.accountname,account.money from user left outer join account on account.uid=user.id;
</select>
设置IAcountDao.xml
<mapper namespace="com.example.dao.IAccountDao">
<resultMap id="accountuser" type="account">
<id property="id" column="aid"></id>
<result property="accountName" column="accountname"></result>
<result property="money" column="money"></result>
<association property="user" column="id" javaType="user">
<id column="id" property="userId"></id>
<result property="userName" column="username"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
<result column="birthday" property="userBirthDay"></result>
</association>
</resultMap>
<select id="findAll" resultMap="accountuser">
select user.*,account.id aid,account.accountname,account.money from user,account where user.id=account.uid;
</select>
</mapper>
这两个的区别一个是collection
一个是association
测试
/*
* 查找所有账户信息
* */
@Test
public void TestFindAll(){
List<Account> accounts=accountDao.findAll();
for(Account a : accounts){
System.out.println(a);
}
}
可以看出能够关联出账户人的信息
Account{id=1, accountName='中国银行', money=1000.0, user=User{userId=1, userName='张三', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=2, accountName='工商银行', money=1000.0, user=User{userId=2, userName='李四', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=3, accountName='建设银行', money=1000.0, user=User{userId=3, userName='王五', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
Account{id=4, accountName='中信银行', money=2000.0, user=User{userId=2, userName='李四', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=null}}
测试用户的所有账户信息
@Test
public void TestUserAccount(){
List<User> users=userDao.findUserAccountInfo();
for(User u:users){
System.out.println(u);
}
}
输出
User{userId=1, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=1, accountName='中国银行', money=1000.0, user=null}]}
User{userId=2, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=2, accountName='工商银行', money=1000.0, user=null}, Account{id=4, accountName='中信银行', money=2000.0, user=null}]}
User{userId=3, userName='null', userBirthDay=Thu Feb 01 00:00:00 CST 1990, userSex='男', userAddress='北京', accounts=[Account{id=3, accountName='建设银行', money=1000.0, user=null}]}
User{userId=5, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='?', userAddress='???', accounts=[]}
User{userId=6, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='男', userAddress='济南市历下区', accounts=[]}
User{userId=7, userName='null', userBirthDay=Thu Apr 23 00:00:00 CST 2020, userSex='男', userAddress='济南市历221下区', accounts=[]}
这里用户2的账户信息都归集到一起了
测试多对多
一个学生可以选择多节课,一节课可以被多个学生选择。所以这两个是多对多的关系。
创建实体类student
/course
Student
类
package com.example.domain;
import java.util.List;
/*
*
create table student(id INTEGER PRIMARY KEY AUTO_INCREMENT,
studentcode varchar(100),
studentname varchar(200))
*
insert into student(studentcode,studentname)values('01','小张');
insert into student(studentcode,studentname)values('02','小王');
insert into student(studentcode,studentname)values('03','小李');
insert into student(studentcode,studentname)values('04','小于');
insert into student(studentcode,studentname)values('05','小曹');
insert into student(studentcode,studentname)values('06','小白');
* */
public class Student {
private int id;
private String studentCode;
private String studentName;
private List<Course> coursesList;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getStudentCode() {
return studentCode;
}
public void setStudentCode(String studentCode) {
this.studentCode = studentCode;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public List<Course> getCoursesList() {
return coursesList;
}
public void setCoursesList(List<Course> coursesList) {
this.coursesList = coursesList;
}
@Override
public String toString() {
return "Students{" +
"id=" + id +
", studentCode='" + studentCode + '\'' +
", studentName='" + studentName + '\'' +
", coursesList=" + coursesList +
'}';
}
}
Course
类
package com.example.domain;
import java.util.List;
/*
*
create table course(id INTEGER PRIMARY key AUTO_INCREMENT,
coursename varchar(2))
*
*
insert into course(coursename)values('语文')
insert into course(coursename)values('数学')
insert into course(coursename)values('英语')
*
*
* 中间表信息
* create table cour_stu(id INTEGER PRIMARY KEY AUTO_INCREMENT,
courseid int,
studentid int)
*
insert into cour_stu(courseid,studentid)values(1,1);
insert into cour_stu(courseid,studentid)values(1,2);
insert into cour_stu(courseid,studentid)values(1,3);
insert into cour_stu(courseid,studentid)values(2,4);
insert into cour_stu(courseid,studentid)values(2,1);
insert into cour_stu(courseid,studentid)values(3,1);
insert into cour_stu(courseid,studentid)values(3,2);
insert into cour_stu(courseid,studentid)values(3,3);
insert into cour_stu(courseid,studentid)values(3,4);
insert into cour_stu(courseid,studentid)values(3,5);
insert into cour_stu(courseid,studentid)values(3,6);
* */
public class Course {
private int id;
private String courseName;
private List<Student> students;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Course{" +
"id=" + id +
", courseName='" + courseName + '\'' +
", students=" + students +
'}';
}
}
两个表的关联关系通过cour_stu
来处理
增加mapper结果映射
IStudentDao.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.example.dao.IStudentDao">
<resultMap id="studentcourse" type="student">
<id property="id" column="id"></id>
<result column="studentcode" property="studentCode"></result>
<result column="studentname" property="studentName"></result>
<collection property="coursesList" ofType="course">
<id property="id" column="cid"></id>
<result column="coursename" property="courseName"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="studentcourse">
select a.*,c.id cid,c.coursename from student a left outer join cour_stu b on a.id=b.studentid left outer join course c on c.id=b.courseid;
</select>
</mapper>
ICourseDao.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.example.dao.ICourseDao">
<resultMap id="coursestudent" type="course">
<id property="id" column="id"></id>
<result column="coursename" property="courseName"></result>
<collection property="students" ofType="student">
<id property="id" column="sid"></id>
<result column="studentcode" property="studentCode"></result>
<result column="studentname" property="studentName"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="coursestudent">
select c.*,a.id sid,a.studentcode,a.studentname from course c left outer join cour_stu b on c.id=b.courseid left outer join student a on a.id=b.studentid;
</select>
</mapper>
测试
测试学生
package com.example.test;
import com.example.dao.IStudentDao;
import com.example.domain.Student;
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 StudentTest {
private InputStream in;
private SqlSession session;
private IStudentDao studentDao;
@Before
public void Init() throws Exception{
in= Resources.getResourceAsStream("SqlMapConfigure.xml");
//创建sqlSessionFactory工厂
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory= builder.build(in);
//使用工厂创建一个SqlSession
session=sqlSessionFactory.openSession(true);
//使用sqlSession创建dao代理对象(动态代理)
studentDao=session.getMapper(IStudentDao.class);
}
@After
public void destory() throws Exception{
session.close();
in.close();
}
@Test
public void TestStudentCoure(){
List<Student> students=studentDao.findAll();
for(Student student: students){
System.out.println(student);
}
}
}
输出
Students{id=1, studentCode='01', studentName='小张', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=2, courseName='英语', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=2, studentCode='02', studentName='小王', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=3, studentCode='03', studentName='小李', coursesList=[Course{id=1, courseName='语文', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=4, studentCode='04', studentName='小于', coursesList=[Course{id=2, courseName='英语', students=null}, Course{id=3, courseName='数学', students=null}]}
Students{id=5, studentCode='05', studentName='小曹', coursesList=[Course{id=3, courseName='数学', students=null}]}
Students{id=6, studentCode='06', studentName='小白', coursesList=[Course{id=3, courseName='数学', students=null}]}
测试课程
package com.example.test;
import com.example.dao.ICourseDao;
import com.example.domain.Course;
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 javax.swing.plaf.IconUIResource;
import java.io.InputStream;
import java.util.List;
public class CourseTest {
private InputStream in;
private SqlSession session;
private ICourseDao courseDao;
@Before
public void Init() throws Exception{
in= Resources.getResourceAsStream("SqlMapConfigure.xml");
//创建sqlSessionFactory工厂
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory= builder.build(in);
//使用工厂创建一个SqlSession
session=sqlSessionFactory.openSession(true);
//使用sqlSession创建dao代理对象(动态代理)
courseDao=session.getMapper(ICourseDao.class);
}
@After
public void destory() throws Exception{
session.close();
in.close();
}
@Test
public void TestCourseAll(){
List<Course> courses= courseDao.findAll();
for(Course course:courses ){
System.out.println(course);
}
}
}
输出
Course{id=1, courseName='语文', students=[Students{id=1, studentCode='01', studentName='小张', coursesList=null}, Students{id=2, studentCode='02', studentName='小王', coursesList=null}, Students{id=3, studentCode='03', studentName='小李', coursesList=null}]}
Course{id=2, courseName='英语', students=[Students{id=4, studentCode='04', studentName='小于', coursesList=null}, Students{id=1, studentCode='01', studentName='小张', coursesList=null}]}
Course{id=3, courseName='数学', students=[Students{id=1, studentCode='01', studentName='小张', coursesList=null}, Students{id=2, studentCode='02', studentName='小王', coursesList=null}, Students{id=3, studentCode='03', studentName='小李', coursesList=null}, Students{id=4, studentCode='04', studentName='小于', coursesList=null}, Students{id=5, studentCode='05', studentName='小曹', coursesList=null}, Students{id=6, studentCode='06', studentName='小白', coursesList=null}]}
能够看出多对多都可以完全实现。
总结
Mybatis实现多表查询还是比较方便的,主要还是考验写sql的能力,但是对比netcore的EFCore我感觉还是EFCore更方便,不要写这么多sql和配置信息。改天写个对比实践。