mybatis： 多对多查询[转]
加入3个包
log4j-1.2.17.jar
mybatis-3.3.0.jar
mysql-connector-java-5.1.8.jar
log4j需要配置
log4j.properties
# Global logging configuration log4j.rootLogger=DEBUG, stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
目录如下
这里需要三个表 ：学生表、课程表和选课表
创建学生表 tb_student 并插入两条数据：
mysql> create table tb_student(
-> s_id int primary key auto_increment,
-> s_name varchar(20),
-> s_sex varchar(10),
-> s_age int);
insert into tb_student(s_name,s_sex,s_age) values('Tom','male',18);
insert into tb_student(s_name,s_sex,s_age) values('Jack','male',19);
创建课程表 tb_course 并插入两条数据：
mysql> create table tb_course(
-> c_id int primary key auto_increment,
-> c_name varchar(20),
-> c_credit int);
insert into tb_course(c_name,c_credit) values('Math',5);
insert into tb_course(c_name,c_credit) values('Computer',4);
由于学生和课程是多对多的关联关系，因此创建中间表：选课表 tb_select_course 并插入数据
mysql> create table tb_select_course(
-> sc_s_id int,
-> sc_c_id int,
-> sc_date date,
-> primary key(sc_s_id,sc_c_id),
-> foreign key(sc_s_id) references tb_student(s_id),
-> foreign key(sc_c_id) references tb_course(c_id));
insert into tb_select_course(sc_s_id,sc_c_id,sc_date) values(1,1,'2017-03-01');
insert into tb_select_course(sc_s_id,sc_c_id,sc_date) values(1,2,'2017-03-01');
insert into tb_select_course(sc_s_id,sc_c_id,sc_date) values(2,1,'2017-03-02');
insert into tb_select_course(sc_s_id,sc_c_id,sc_date) values(2,2,'2017-03-02');
我们的包名是： mybatis4
创建mybatis2.model，并创建相关的数据库model
student.java
package mybatis2.model;
import java.util.List;
public class Student {
private int id;
private String name;
private String sex;
private int age;
private List<Course> courses;
public Student() {
}
public Student(int id, String name, String sex, int age, List<Course> courses) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.courses = courses;
}
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;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public List<Course> getCourses() {
return courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
}
course.java
package mybatis2.model;
import java.util.List;
public class Course {
private int id;
private String name;
private int credit;
private List<Student> students;
public Course() {
}
public Course(int id, String name, int credit, List<Student> students) {
this.id = id;
this.name = name;
this.credit = credit;
this.students = students;
}
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;
}
public int getCredit() {
return credit;
}
public void setCredit(int credit) {
this.credit = credit;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
}
StudentCourseLink.java
package mybatis2.model;
import java.util.Date;
public class StudentCourseLink {
private Student student;
private Course course;
private Date date;
public StudentCourseLink() {
}
public StudentCourseLink(Student student, Course course, Date date) {
this.student = student;
this.course = course;
this.date = date;
}
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
public Course getCourse() {
return course;
}
public void setCourse(Course course) {
this.course = course;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
在mybatis2.mapper包下创建映射文件
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis2.mapper.StudentMapper">
<!-- 查询所有学生及他们的选课课程 -->
<select id="selectStudentCourse" resultMap="StudentCourseMap">
select s.*,c.*
from
tb_student s, tb_course c, tb_select_course sc
where
s.s_id = sc.sc_s_id and c.c_id = sc.sc_c_id
</select>
<!-- 根据学生Id和选课Id,删除学生所选的课程 -->
<delete id="deleteStudentCourseById" parameterType="StudentCourseLink">
delete from tb_select_course where sc_s_id=#{student.s_id} and sc_c_id=#{course.c_id}
</delete>
<!-- resultMap映射实体类和数据库字段一一对应的关系 -->
<resultMap type="Student" id="StudentCourseMap">
<id property="id" column="s_id" />
<result property="name" column="s_name" />
<result property="sex" column="s_sex"/>
<result property="age" column="s_age"/>
<!-- 多对多关系 -->
<collection property="courses" ofType="Course">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<result property="credit" column="c_credit"/>
</collection>
</resultMap>
</mapper>
StudentMapper.java
package mybatis2.mapper;
import java.util.List;
import mybatis2.model.Student;
import mybatis2.model.StudentCourseLink;
public interface StudentMapper {
/**
* 鏌ヨ鎵?鏈夊鐢熷強浠栦滑鐨勮绋?
*/
public List<Student> selectStudentCourse() throws Exception;
/**
* 鍒犻櫎鎸囧畾id鐢ㄦ埛鐨勬煇闂ㄨ绋嬶紙鍜屾牴鎹绋媔d锛夌殑閫夎鎯呭喌
*/
public void deleteStudentCourseById(StudentCourseLink scLink) throws Exception;
}
在src目录下创建mybatis的配置文件
conf.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> <!-- 引入数据库模型 --> <typeAliases> <!-- 引入单个模型 <typeAlias alias="Student" type="mybatis2.model.Student"/> <typeAlias alias="Course" type="mybatis2.model.Course"/> <typeAlias alias="StudentCourseLink" type="mybatis2.model.StudentCourseLink"/> --> <!-- 指定一个类的包名起别名，将包内的java类的类名做为类的别名 --> <package name="mybatis2.model"/> </typeAliases> <!-- 配置mybatis运行环境 --> <environments default="development"> <environment id="development"> <!-- type=j"dbc" 代表用jdbcd的提交和回滚 --> <transactionManager type="JDBC"/> <!-- POOLED表示支持JDBC数据源连接池 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/spring"/> <property name="username" value="root"/> <property name="password" value=""/> </dataSource> </environment> </environments> <!-- 通过mapper映射接口包加载整个包的映射文件 --> <mappers> <!-- 单个映射文件引入--> <mapper resource="mybatis2/mapper/StudentMapper.xml" /> <!--package name="mybatis2/mapper"/--> </mappers> </configuration>
测试类
Test.java
package mybatis2.test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
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 mybatis2.mapper.StudentMapper;
import mybatis2.model.Course;
import mybatis2.model.Student;
public class Test {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("conf.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static SqlSessionFactory getSession()
{
return sqlSessionFactory;
}
public static void main(String[] args)
{
selectStudentCourse();
}
/**
* 鏌ヨ鎵?鏈夊鐢熷強鎵?閫夎绋?
*/
public static void selectStudentCourse()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
try {
List<Student> students = studentMapper.selectStudentCourse();
sqlSession.commit();
for(Student stu: students)
{
System.out.println(stu.getId()+","+stu.getName()+","+stu.getSex()+","+stu.getAge());
List<Course> courses = stu.getCourses();
for(Course cour: courses)
{
System.out.println("__:" + cour.getId()+","+cour.getName()+","+cour.getCredit());
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//释放数据
sqlSession.close();
}
}
