在弄Mybatis的一对多关联的时候,不知道有没有遇到这样的问题,本来数据库中对应有多条数据,然而关联出来却只有一条数据,比如数据库中有个班级表和学生表,数据库中一个班级中对应了多个学生,但使用Mybatis做一对多关联的时候,查询出来的却只有一条。如果出现这样的问题,那么就是两张数据表中的主键重名了,导致在关联查询时分不清到底是那一张表了,因此有关联的数据表时,主键id不要重名,一对多关联实现如下:
数据表:
CREATE TABLE tab_class (
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50)
)
CREATE TABLE tab_student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50),
c_id INT
)
实体类:
package com.tenghu.mybatis.model;
import java.util.List;
/**
* 班级实体类
* @author Arvin
*
*/
public class Classes {
private int cId;
private String cName;
private List<Student> studentList;
public int getcId() {
return cId;
}
public void setcId(int cId) {
this.cId = cId;
}
public String getcName() {
return cName;
}
public void setcName(String cName) {
this.cName = cName;
}
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
}
package com.tenghu.mybatis.model;
/**
* 学生实体
* @author Arvin
*
*/
public class Student {
private int sId;
private String sName;
private Classes classes;
public int getsId() {
return sId;
}
public void setsId(int sId) {
this.sId = sId;
}
public String getsName() {
return sName;
}
public void setsName(String sName) {
this.sName = sName;
}
public Classes getClasses() {
return classes;
}
public void setClasses(Classes classes) {
this.classes = classes;
}
}
映射文件:
<?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.tenghu.mybatis.model.xml.ClassesMapper">
<resultMap type="Classes" id="getClasses">
<id property="cId" column="c_id"/>
<result property="cName" column="c_name"/>
<!-- 映射到一个集合 表示多的一方-->
<collection property="studentList" ofType="Student">
<id property="sId" column="s_id"/>
<result property="sName" column="s_name"/>
</collection>
</resultMap>
<!-- 根据班级id查询 -->
<select id="queryClassesById" resultMap="getClasses" parameterType="int">
SELECT * FROM tab_class c,tab_student s WHERE c.c_id=s.c_id AND c.c_id=#{id}
</select>
</mapper>
<?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.tenghu.mybatis.model.xml.StudentMapper">
<resultMap type="Student" id="getStudent">
<id property="sId" column="s_id"/>
<result property="sName" column="s_name"/>
<!-- 表示一的一方 -->
<association property="classes" javaType="Classes">
<id property="cId" column="c_id"/>
<result property="cName" column="c_name"/>
</association>
</resultMap>
<!-- 根据学生id查询 -->
<select id="queryStudentById" parameterType="int" resultMap="getStudent">
SELECT * FROM tab_class c,tab_student s WHERE c.c_id=s.c_id AND s.s_id=#{id}
</select>
</mapper>
mybatis工具类:
package com.tenghu.mybatis.util;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
* Mybatis工具类
* @author Arvin
*
*/
public class MybatisUtil {
private MybatisUtil(){}
//声明SqlSessionFactory对象
private static SqlSessionFactory sqlSessionFactory;
static{
//mybatis主配置文件
String mybatisResource="mybatis-config.xml";
//数据库属性文件
String databaseProps="jdbc.properties";
try {
//获取mybatis住配置文件资源
InputStream inputStream=Resources.getResourceAsStream(mybatisResource);
//创建属性对象
Properties props=new Properties();
//加数据库属性配置文件资源
props.load(Resources.getResourceAsStream(databaseProps));
//创建SqlSession工厂对象
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream, props);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 开启SqlSession,不自动提交
* @return
*/
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
/**
* 开启SqlSession,自动提交
* @param isAuto
* @return
*/
public static SqlSession openSessionAuto(){
return sqlSessionFactory.openSession(true);
}
/**
* 关闭SqlSession对象
* @param sqlSession
*/
public static void closeSqlSession(SqlSession sqlSession){
if(null!=sqlSession){
sqlSession.close();
}
}
}
package com.tenghu.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.tenghu.mybatis.model.Student;
import com.tenghu.mybatis.util.MybatisUtil;
/**
* 学生实体测试类
* @author Arvin
*
*/
public class StudentTest {
private String namespace="com.tenghu.mybatis.model.xml.StudentMapper.";
@Test
public void testQueryStudentById(){
//获取SqlSession
SqlSession sqlSession=MybatisUtil.openSession();
try {
//查询
Student student=sqlSession.selectOne(namespace+"queryStudentById", 1);
//输出
System.out.println(student.getsName());
System.out.println(student.getClasses().getcName());
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭SqlSession
MybatisUtil.closeSqlSession(sqlSession);
}
}
}
package com.tenghu.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.tenghu.mybatis.model.Classes;
import com.tenghu.mybatis.model.Student;
import com.tenghu.mybatis.util.MybatisUtil;
/**
* 班级测试类
* @author Arvin
*
*/
public class ClassesTest {
private String namespace="com.tenghu.mybatis.model.xml.ClassesMapper.";
@Test
public void testQueryClassById(){
//获取SqlSession
SqlSession sqlSession=MybatisUtil.openSession();
try {
//查询
Classes classes=sqlSession.selectOne(namespace+"queryClassesById",1);
//输出
System.out.println(classes.getcName());
for (Student student : classes.getStudentList()) {
System.out.println(student.getsName());
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭SqlSession
MybatisUtil.closeSqlSession(sqlSession);
}
}
}
主键id相同的情况,这里就不测试了,如果没有遇到那样的情况,可以去试试。