联合查询、多对一查询
有一段时间没更新博客了,有点懒了,话不多说
本文主要讲述MyBatis的多对一的复杂查询
假设目前我们有两张表student表
class_id(INT 5) | username(VARCHAR(50) |
---|---|
1 | 张三 |
2 | 李四 |
3 | 王五 |
1 | 刘洋 |
1 | 刘f |
1 | 刘LIU |
class表
id(INT 5) | name(VARCHAR(10) |
---|---|
1 | java放弃班 |
2 | python入土班 |
3 | DB删除跑路班 |
现在要求查询student表所有数据,输出学生姓名和该生所在班级名称
分析:
- 一个班级对应多个学生
- 因此是多对一关系
- 使用嵌套resultMap将结果映射到集合
首先建立sql查询语句,并且配置Mapper映射器
<select id="findAllStudent_Class" resultMap="resultUser_C">
SELECT * FROM
student,class where student.class_id = class.id
</select>
其次是resultMap映射结果集
<resultMap type="domain.User" id="resultUser_C">
<id property="username" column="username" />
<result property="password" column="password" />
<!-- association:映射到JavaBean的某个复杂类型属性,比如Class类。 -->
<association property="class_id" column="class_id"
javaType="domain.Class">
<id property="id" column="id" />
<result property="name" column="name" />
</association>
</resultMap>
java类
domain.User类
package domain;
public class User {
String username;
String password;
String no;
Department deptid;
Class class_id;
public Class getClass_id() {
return class_id;
}
public void setClass_id(Class class_id) {
this.class_id = class_id;
}
public Department getDeptid() {
return deptid;
}
public void setDeptid(Department deptid) {
this.deptid = deptid;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
domain.Class类
package domain;
public class Class {
private int id;
private String name;
public Class(){
}
public Class(Integer id,String name){
super();
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
最后是java执行代码
package time9_16;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
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 domain.User;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
//1.得到数据库连接
InputStream inputStream = null;
User user = new User();
user.setNo("179000201");
user.setUsername("张三");
try {
inputStream = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.得到执行sql的对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.执行sql语句
List<User> list = sqlSession.selectList("findAllStudent_Class",user);
//4.输出结果
for (User user1 : list) {
System.out.println(user1.getUsername()+":"+user1.getClass_id().getName());
}
//5.关闭数据库
sqlSession.close();
}
}
运行输出结果
张三:java放弃班
李四:python入土班
王五:数据库删库跑路班
刘洋:java放弃班
刘f:java放弃班
刘LIU:java放弃班