本文共 7493 字，大约阅读时间需要 24 分钟。

MyBatis 多对多关系数据库设计与实现

1. 数据库设计

在设置多对多关系时，通常会通过建立第三张关系表来实现。以学生与课程的关系为例，假设我们有 t_student 和 t_courses 两个主要表，关系表为 t_stu_cou。

数据库表结构

create table t_student (    id int primary key auto_increment,    student_name varchar(20));create table t_courses (    id int primary key auto_increment,    courses_name varchar(20));create table t_stu_cou (    id int primary key auto_increment,    fk_stu_id int,    fk_cou_id int);

数据插入

insert into t_student values (null, '米兰');insert into t_student values (null, '凌雪');insert into t_student values (null, '成成');insert into t_student values (null, '睿懿');insert into t_student values (null, '瑞瑞');insert into t_courses values (null, '语文');insert into t_courses values (null, '数学');insert into t_courses values (null, '计算机');insert into t_courses values (null, 'java编程');insert into t_courses values (null, 'html');insert into t_stu_cou values (null, 1, 1);insert into t_stu_cou values (null, 1, 2);insert into t_stu_cou values (null, 2, 3);insert into t_stu_cou values (null, 2, 4);insert into t_stu_cou values (null, 3, 1);insert into t_stu_cou values (null, 3, 5);insert into t_stu_cou values (null, 4, 4);insert into t_stu_cou values (null, 4, 2);

2. JavaBean 定义

CoursesBean.java

package com.cy.mybatis.beans;import java.io.Serializable;import java.util.List;public class CoursesBean implements Serializable {    private static final long serialVersionUID = 1L;    private Integer id;    private String name;    private List
   
     student;    public CoursesBean(Integer id, String name, List
    
      student) {        super();        this.id = id;        this.name = name;        this.student = 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 List
     
       getStudent() { return student; }    public void setStudent(List
      
        student) { this.student = student; }    @Override    public String toString() { return "CoursesBean [id=" + id + ", name=" + name + ", student=" + student + "]"; }}
      
     
    
   

StudentBean.java

package com.cy.mybatis.beans;import java.io.Serializable;import java.util.List;public class StudentBean implements Serializable {    private static final long serialVersionUID = 1L;    private Integer id;    private String name;    private List
   
     courses;    public StudentBean(Integer id, String name, List
    
      courses) {        super();        this.id = id;        this.name = name;        this.courses = courses;    }    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
     
       getCourses() { return courses; }    public void setCourses(List
      
        courses) { this.courses = courses; }    @Override    public String toString() { return "StudentBean [id=" + id + ", name=" + name + ", courses=" + courses + "]"; }}
      
     
    
   

3. 接口定义

CoursesMapper.java

package com.cy.mybatis.mapper;import com.cy.mybatis.beans.CoursesBean;public interface CoursesMapper {    public CoursesBean findCouById(int id);    public CoursesBean findCouAndStu(int id);}

StudentMapper.java

package com.cy.mybatis.mapper;import com.cy.mybatis.beans.StudentBean;public interface StudentMapper {    public StudentBean findStuById(int id);    public StudentBean findStuAndCou(int id);}

4. XML 配置

CoursesMapper.xml

       
            
             
             
         
        
            select * from t_courses where id=#{id}        
            select * from t_student where id in (select fk_stu_id from t_stu_cou where fk_cou_id=#{id})        
            select * from t_courses where id=#{id}    
   

StudentMapper.xml

       
            
             
         
        
            
             
             
         
        
            select * from t_student where id=#{id}        
            select * from t_student where id=#{id}        
            select * from t_courses where id in (select fk_cou_id from t_stu_cou where fk_stu_id=#{id})    
   

5. 测试

package com.cy.mybatis.service;import org.apache.ibatis.session.SqlSession;import com.cy.mybatis.beans.CoursesBean;import com.cy.mybatis.beans.StudentBean;import com.cy.mybatis.mapper.CoursesMapper;import com.cy.mybatis.mapper.StudentMapper;import com.cy.mybatis.tools.DBTools;public class ManyToManyService {    public static void main(String[] args) {        findStudentByCourses();        findCoursesByStudent();    }    private static void findCoursesByStudent() {        SqlSession session = DBTools.getSession();        StudentMapper sm = session.getMapper(StudentMapper.class);        StudentBean sb = sm.findStuAndCou(1);        System.out.println(sb);    }    private static void findStudentByCourses() {        SqlSession session = DBTools.getSession();        CoursesMapper cm = session.getMapper(CoursesMapper.class);        CoursesBean cb = cm.findCouAndStu(2);        System.out.println(cb);    }}

测试结果

运行上述代码，输出结果如下：

DEBUG 2016-02-27 09:56:54,852 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 586269.DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@8f21d]DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_courses where id=?DEBUG 2016-02-27 09:56:54,105 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 2(Integer)DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ===> Preparing: select * from t_student where id in (select fk_stu_id from t_stu_cou where fk_cou_id=?)DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ===> Parameters: 2(Integer)DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==== Total: 2DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Total: 10CoursesBean [id=2, name=数学, student=[StudentBean [id=1, name=米兰, courses=null], StudentBean [id=4, name=睿懿, courses=null]]]DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 23881129.DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@16c65a9]DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_student where id=?DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ===> Preparing: select * from t_courses where id in (select fk_cou_id from t_stu_cou where fk_stu_id=?)DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ===> Parameters: 1(Integer)DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==== Total: 2DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Total: 12StudentBean [id=1, name=米兰, courses=[CoursesBean [id=1, name=语文, student=null], CoursesBean [id=2, name=数学, student=null]]]

转载地址：http://zchs.baihongyu.com/