mybatis一对多嵌套查询
一个class中有许多student，属于一对多关系。
本文利用mybatis注解，嵌套查询方式来实现一对多查询，在根据classId查询class时，顺便查询并展示出class中的student
student实体类
package com.itheima.dao;
public class StudentDao {
private Integer id;
private String name;
private Integer age;
private Integer cid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
@Override
public String toString() {
return "StudentDao{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", cid=" + cid +
'}';
}
}
class实体类
package com.itheima.dao;
import java.util.List;
public class ClassDao {
private Integer id;
private String className;
private List<StudentDao> studentsList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
@Override
public String toString() {
return "ClassDao{" +
"id=" + id +
", className='" + className + '\'' +
", studentsList=" + studentsList +
'}';
}
}
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--设置连接数据库的环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--引入映射文件-->
<mappers>
<mapper class="com.itheima.mapper.StudentMapper"/>
<mapper class="com.itheima.mapper.ClassMapper"/>
</mappers>
</configuration>
studentMapper.java
package com.itheima.mapper;
import com.itheima.dao.StudentDao;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentMapper {
@Select("select * from student where cid = #{cid}")
@Results({@Result(id = true, property = "id", column = "id"),
@Result(property = "name",column = "name"),
@Result(property = "age",column = "age"),
@Result(property = "cid", column = "cid")})
List<StudentDao> selectStudentByCid(Integer cid);
}
classMapper.java
package com.itheima.mapper;
import com.itheima.dao.ClassDao;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface ClassMapper {
@Select("select * from class where id = #{id}")
@Results({@Result(id =true, property = "id",column = "id"),
@Result(property = "className",column = "className"),
@Result(property ="studentsList",column = "id",
many = @Many(select = "com.itheima.mapper.StudentMapper.selectStudentByCid"))})
ClassDao selectClassById(Integer id);
}
testMybatis.java
import com.itheima.dao.ClassDao;
import com.itheima.dao.StudentDao;
import com.itheima.mapper.ClassMapper;
import com.itheima.mapper.StudentMapper;
import org.apache.ibatis.annotations.Mapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class testMybatis {
@Test
public void test() throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
ClassMapper classMapper = sqlSession.getMapper(ClassMapper.class);
ClassDao res = classMapper.selectClassById(2);
System.out.println(res.toString());
}
}
数据库sql文件
INSERT INTO `class` (`id`, `className`) VALUES (1, '软件19-1');
INSERT INTO `class` (`id`, `className`) VALUES (2, '软件19-2');
INSERT INTO `student` (`name`, `age`, `cid`, `id`) VALUES ('罗辑', 18, 2, 1);
INSERT INTO `student` (`name`, `age`, `cid`, `id`) VALUES ('张三', 18, 2, 2);
INSERT INTO `student` (`name`, `age`, `cid`, `id`) VALUES ('李四', 18, 2, 3);
查询结果可看出，2班的班级信息和2班的学生信息
注意事项：
1、一对多关系中，‘’多‘’方实体类存‘’一‘’的那方的主键作为外键，‘’一‘’的那方的实体类存‘’多‘’的那方的 list<多的那方的实体类属性>以便查询时展示出来。
在“一”的那方的mapper接口中定义嵌套查询。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程网。
