一对多的关系:一个学生只能学一个学科,一个科目有多个学生
同样的,mybatis.xml,c3p0-config.xml配置文件省略不展示。
StudentMapper.xml
同样的,mybatis.xml,c3p0-config.xml配置文件省略不展示。
sql语句
create table grades(
gid int(5) primary key,
gname varchar(10)
);
create table students(
sid int(5) primary key,
sname varchar(10),
sgid int(5),
constraint sgid_fk foreign key(sgid) references grades(gid)
);
insert into grades(gid,gname) values(1,'java');
insert into students(sid,sname,sgid) values(1,'哈哈',1);
insert into students(sid,sname,sgid) values(2,'呵呵',1);
Grade.java
/**
* 学科(单方)
* @author AdminTC
*/
public class Grade {
private Integer id;
private String name;
private List<Student> studentList = new ArrayList<Student>();//关联属性
public Grade(){}
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 List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
}
Student.java
/**
* 学生(多方)
* @author AdminTC
*/
public class Student {
private Integer id;
private String name;
private Grade grade;//关联属性
public Student(){}
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 Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
}
GradeMapper.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="gradeNamespace">
<resultMap type="cn.itcast.javaee.mybatis.one2many.Grade" id="gradeMap">
<id property="id" column="gid"/>
<result property="name" column="gname"/>
</resultMap>
<!-- 查询哈哈是哪个学科的 -->
<select id="findByName" parameterType="string" resultMap="gradeMap">
select g.gname
from students s inner join grades g
on s.sgid = g.gid
and s.sname = #{name}
</select>
</mapper>
StudentMapper.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="studentNamespace">
<resultMap type="cn.itcast.javaee.mybatis.one2many.Student" id="studentMap">
<id property="id" column="sid"/>
<result property="name" column="sname"/>
</resultMap>
<!-- 查询java学科有哪些学生信息 -->
<select id="findAllByName" parameterType="string" resultMap="studentMap">
select s.sid,s.sname
from students s inner join grades g
on s.sgid = g.gid
and g.gname = #{name}
</select>
</mapper>
GradeStudentDao.java
/**
* 持久层
* @author AdminTC
*/
public class GradeStudentDao {
/**
* 查询java学科有哪些学生信息
* @param name 表示学科名
*/
public List<Student> findAllByName(String name) throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtil.getSqlSession();
return sqlSession.selectList("studentNamespace.findAllByName",name);
}catch(Exception e){
e.printStackTrace();
throw e;
}finally{
MybatisUtil.closeSqlSession();
}
}
/**
* 查询哈哈是哪个学科的
* @param name 表示学生姓名
*/
public Grade findByName(String name) throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtil.getSqlSession();
return sqlSession.selectOne("gradeNamespace.findByName",name);
}catch(Exception e){
e.printStackTrace();
throw e;
}finally{
MybatisUtil.closeSqlSession();
}
}
public static void main(String[] args) throws Exception{
GradeStudentDao dao = new GradeStudentDao();
List<Student> studentList = dao.findAllByName("java");
System.out.println("java学科有"+studentList.size()+"个学生,它们信息如下:");
for(Student s : studentList){
System.out.println(s.getId()+":"+s.getName());
}
System.out.println("-----------------------------------------------------------");
Grade grade = dao.findByName("哈哈");
System.out.println("哈哈是"+grade.getName()+"学科的");
System.out.println("-----------------------------------------------------------");
grade = dao.findByName("呵呵");
System.out.println("呵呵是"+grade.getName()+"学科的");
}
}