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();
  }

}