共计 4835 个字符,预计需要花费 13 分钟才能阅读完成。
1、多表查询(一对一)
1.1、使用多表查询前的准备
学生表(students):
班级表(classes):
1.2、需求分析(一对一)
查询所有学生信息,关联查询下班级信息。
注意:
因为一个学生信息只能在一个班级里,所以从查询学生信息出发关联查询班级信息为一对一查询。
如果从班级信息出发查询班级下的学生信息则为一对多查询,因为一个班级可以有多个学生。
方式一 SQL 如下:
select s.*,c.cname from students s,classes c where s.cid=c.cid;
方式二 SQL 如下:
select s.sid,s.sname,s.ssex,s.sage,c.* from students s,classes c where s.cid=c.cid
2、案例实现 - 方式一
2.1、编写实体类文件
Students.java
package com.tyschool.mb005.javabean;
import java.io.Serializable;
public class Students implements Serializable {private int sid;
private String sname;
private String ssex;
private int sage;
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 String getSsex() {return ssex;
}
public void setSsex(String ssex) {this.ssex = ssex;
}
public int getSage() {return sage;
}
public void setSage(int sage) {this.sage = sage;
}
@Override
public String toString() {return "Students{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
'}';
}
}
Classes.java
import java.io.Serializable;
public class Classes implements Serializable {private int cid;
private String cname;
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;
}
@Override
public String toString() {return "Classes{" +
"cid=" + cid +
", cname='" + cname + '\'' +
'}';
}
}
2.2、定义返回值接收类文件
StudentsClasses.java
import java.io.Serializable;
public class StudentsClasses extends Students implements Serializable {private String cname;
public String getCname() {return cname;
}
public void setCname(String cname) {this.cname = cname;
}
@Override
public String toString() {return "StudentsClasses{" +
"cname='" + cname + '\'' +
'}';
}
}
2.3、创建 IStudentsDao.java 文件
import com.tyschool.mb005.javabean.StudentsClasses;
import java.util.List;
public interface IStudentsDao {List<StudentsClasses> findAll();
}
2.4、创建 IStudentsDao.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.tyschool.mb005.students.dao.IStudentsDao">
<select id="findAll" resultType="com.tyschool.mb005.javabean.StudentsClasses">
select s.*,c.cname from students s,classes c where s.cid=c.cid
</select>
</mapper>
注意:
写好的 IStudentsDao.xml 文件要在 SqlMapConfig.xml 中加入映射
<mappers>
<mapper resource="com/tyschool/mb005/user/dao/IStudentsDao.xml"></mapper>
</mappers>
2.5、创建测试类 MbStudentsTest.java 文件
package com.tyschool.mb005.test;
import com.tyschool.mb005.javabean.StudentsClasses;
import com.tyschool.mb005.students.dao.IStudentsDao;
import com.tyschool.mb005.user.dao.IUserDao;
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.IOException;
import java.io.InputStream;
import java.util.List;
public class MbStudentsTest {private InputStream in;
private SqlSession session;
private IStudentsDao studentsDao;
@Test
public void findAll(){List<StudentsClasses> list=studentsDao.findAll();
for(StudentsClasses sc :list){System.out.println(sc);
}
}
@Before
public void init()throws IOException {in= Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
SqlSessionFactory factory=builder.build(in);
session=factory.openSession();
studentsDao=session.getMapper(IStudentsDao.class);
}
@After
public void destroy() throws IOException {session.commit();;
session.close();
in.close();}
}
3、案例实现 - 方式二
使用 resultMap,定义专门的 resultMap 用于映射一对一查询结果。
通过面向对象的关系可以得知,我们可以在 Students 类中加入一个 Classes 类的对象来代表这个学生是哪个班级的。
3.1、修改 Students.java 文件
加入 Classes 对象
private Classes classes;
public Classes getClasses() {return classes;
}
public void setClasses(Classes classes) {this.classes = classes;
}
3.2、修改 IStudentsDao.java 文件
import com.tyschool.mb005.javabean.Students;
import com.tyschool.mb005.javabean.StudentsClasses;
import java.util.List;
public interface IStudentsDao {List<StudentsClasses> findAll();
List<Students> findAll1();
}
3.3、修改 IStudentsDao.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.tyschool.mb005.students.dao.IStudentsDao">
<resultMap id="studentsMap" type="com.tyschool.mb005.javabean.Students">
<id column="sid" property="sid"></id>
<result column="sname" property="sname"></result>
<result column="ssex" property="ssex"></result>
<result column="sage" property="sage"></result>
<association property="classes" javaType="com.tyschool.mb005.javabean.Classes">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
</association>
</resultMap>
<select id="findAll" resultType="com.tyschool.mb005.javabean.StudentsClasses">
select s.*,c.cname from students s,classes c where s.cid=c.cid
</select>
<select id="findAll1" resultMap="studentsMap">
select s.sid,s.sname,s.ssex,s.sage,c.* from students s,classes c where s.cid=c.cid
</select>
</mapper>
正文完
星哥玩云-微信公众号