Ibatis的多对多关联

转载 2011年01月14日 14:47:00

我的MySql脚本为:

DROP TABLE IF EXISTS `student`;
create table student(
           student_id  VARCHAR(100) PRIMARY KEY,
           student_name VARCHAR(100)
);
DROP TABLE IF EXISTS `teacher`;
create table teacher(
          teacher_id VARCHAR(100) PRIMARY KEY,
          teacher_name VARCHAR(100)
);
DROP TABLE IF EXISTS `teacher_student`;
create table teacher_student(
          ts_id VARCHAR(100) PRIMARY KEY,
          teacher_id VARCHAR(100),
          student_id VARCHAR(100),
          FOREIGN KEY teacher_id references teacher(teacher_id),
          FOREIGN KEY student_id references student(student_id)
);

alter table teacher_student  add constraint  ts1   unique  nonclutered (teacher_id,student_id); 

insert into student values ('001','liufang');
insert into student values ('002','jianghaiying');
insert into student values ('003','duanhuixia');

insert into teacher values ('001','gaowei');
insert into teacher values ('002','lihua');
insert into teacher values ('003','xugang');

insert into teacher_student values('1','001','001');
insert into teacher_student values('2','002','001');
insert into teacher_student values('3','003','001');
insert into teacher_student values('4','001','002');
insert into teacher_student values('4','002','002');
insert into teacher_student values('5','003','003');

我的Ibatis配置文件为:

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap     
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"     
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Student_Teacher">

  <!-- Use type aliases to avoid typing the full classname every time. -->
  <typeAlias alias="Student_Teacher.Student" type="com.foundersoftware.wishjob.demo.ibatis.manytomany.model.Student" />
  <typeAlias alias="Student_Teacher.Teacher" type="com.foundersoftware.wishjob.demo.ibatis.manytomany.model.Teacher"/>
  <!-- Result maps describe the mapping between the columns returned
       from a query, and the class properties.  A result map isn't
       necessary if the columns (or aliases) match to the properties
       exactly. -->
  <resultMap id="studentResult_basic" class="Student_Teacher.Student">
    <result property="studentId" column="student_id" jdbcType="varchar"/>
    <result property="studentName" column="student_name" jdbcType="varchar"/>
  </resultMap>
 
  <resultMap id="studentResult" class="Student_Teacher.Student" extends="studentResult_basic" >
    <result property="teacherList" column="student_id" select="Student_Teacher.selectBystudentId" />
  </resultMap>
 
  <resultMap id="teacherResult_basic" class="Student_Teacher.Teacher">
    <result property="teacherId" column="teacher_id" jdbcType="varchar"/>
    <result property="teacherName" column="teacher_name" jdbcType="varchar"/>
   </resultMap>
  
   <resultMap id="teacherResult" class="Student_Teacher.Teacher" extends="teacherResult_basic">
    <result property="studentList" column="teacher_id" select="Student_Teacher.selectByteacherId" />
  </resultMap>
 
  <!-- select all the properies of a student including the teacherList by
     using Id-->
  <select id="SelectStudentById" parameterClass="String" resultMap="studentResult">
      select * from student where student_id = #studentId#
  </select>

  <!-- select all the properies of students including the teacherList -->
  <select id="SelectAllStudents" resultMap="studentResult">
      select * from student
  </select>
 
  <select id="selectByteacherId" parameterClass="String" resultMap="studentResult">
        select s.* from student s where s.student_id in
        (select student_id from teacher_student where teacher_id=#value#)
  </select>
   
  <select id="SelectTeacherById" parameterClass="String" resultMap="teacherResult">
      select * from teacher where teacher_id = #teacherId#
  </select>
 
 
  <select id="selectBystudentId" parameterClass="String" resultMap="teacherResult">
        select t.* from Teacher t where t.teacher_id in
        (select teacher_id from teacher_student where student_id=#value#)
  </select>
 
  <!-- Insert example, using the Student parameter class -->
  <insert id="insertStudent" parameterClass="Student_Teacher.Student" >
    insert into student (
    student_id,
    student_name)
    values(#studentId#,#studentName#)
  </insert> 
 
  <!-- Update example, using the Student parameter class -->
  <update id="updateStudent" parameterClass="Student_Teacher.Student">
    update student set
      student_name = #studentName#
    where
      student_id = #studentId#
  </update>
 
   <!-- Delete example, using an integer as the parameter class -->
  <delete id="deleteStudentById" parameterClass="string">
    delete from student where student_id = #student_id#
  </delete>
</sqlMap>


我有两个Model类,分别为:

package com.foundersoftware.wishjob.demo.ibatis.manytomany.model;

import java.util.ArrayList;
import java.util.List;

public class Student {
 
 private String studentId;
 
 private String studentName;
 
 private List<Teacher> teacherList = new ArrayList<Teacher>();
 
 public String getStudentId() {
  return studentId;
 }

 public void setStudentId(String studentId) {
  this.studentId = studentId;
 }

 public String getStudentName() {
  return studentName;
 }

 public void setStudentName(String studentName) {
  this.studentName = studentName;
 }

  public String toString() {
         return "Student{" +
                 "studentId=" + studentId +
                 ", studentName='" + studentName + '/'' +
                 ", TeacherList='" + teacherList.size() + '/'' +
                 '}';
     }

     public String out() {
         StringBuffer sb = new StringBuffer();
         sb.append("Student{" +
                 "studentId=" + studentId +
                 ", studentName='" + studentName + '/'' +
                 ", TeacherList='" + teacherList.size() + '/'');
         for (Teacher teacher : teacherList) {
             sb.append("/n/t").append(teacher.toString());
         }
         return sb.toString();
}
}


package com.foundersoftware.wishjob.demo.ibatis.manytomany.model;

import java.util.ArrayList;
import java.util.List;

public class Teacher {
 
 private String teacherId;
 
 private String teacherName;
 
 private List<Student> studentList= new ArrayList<Student>();
 
 public String getTeacherId() {
  return teacherId;
 }

 public void setTeacherId(String teacherId) {
  this.teacherId = teacherId;
 }

 public String getTeacherName() {
  return teacherName;
 }

 public void setTeacherName(String teacherName) {
  this.teacherName = teacherName;
 }
 
  public String toString() {
         return "Teacher{" +
                 "teacherId=" + teacherId +
                 ", teacherName='" + teacherName + '/'' +
                 ", studentList=" + studentList.size() +
                 '}';
     }

     public String out(){
         StringBuffer sb= new StringBuffer();
         if(studentList.size()>0){
             sb.append("Teacher{" +
                 "teacherId=" + teacherId +
                 ", teacherName='" + teacherName + '/'' +
                 ", studentList=" + studentList.size());
             for(Student s: studentList){
                 sb.append("/n/t").append(s.toString());
             }
             sb.append("/n}");
         }
         return sb.toString();
     }
 
}


我的DAO层为:

package com.foundersoftware.wishjob.demo.ibatis.manytomany.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.foundersoftware.wishjob.demo.ibatis.manytomany.model.*;
import com.foundersoftware.wishjob.util.SqlMapFactory;
import com.ibatis.sqlmap.client.SqlMapClient;

public class Student_TeacherDAOIbatisImpl  {

//我的SqlMapFactory工具类已经在Ibatis单表的文章中给出了~
 private static SqlMapClient sqlMapper = SqlMapFactory.getInstance();

 public void add(Student student) {
  try {
   sqlMapper.insert("Student_Teacher.insertStudent", student);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
 }

 public void update(Student student) {
  try {
   sqlMapper.update("Student_Teacher.updateStudent", student);
  } catch (SQLException e) {
   e.printStackTrace();
  } 
 }


 @SuppressWarnings("unchecked")
 public List<Student> query() {
  List <Student>list = new ArrayList<Student>();
  try {
   list = sqlMapper.queryForList("Student_Teacher.SelectAllStudents");
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return list;
 }


 public void delete(String student_id) {
  try {
   sqlMapper.delete("Student_Teacher.deleteStudentById", student_id);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

 public Student getStudentById(String student_id) {
  Student student = new Student();
  try {
   student = (Student) sqlMapper.queryForObject("Student_Teacher.SelectStudentById", student_id);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return student;
 }
}


 
我的Test为:

package com.foundersoftware.wishjob.demo.ibatis.manytomany.test;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.foundersoftware.wishjob.demo.ibatis.manytomany.dao.Student_TeacherDAOIbatisImpl;
import com.foundersoftware.wishjob.demo.ibatis.manytomany.model.*;
public class ManytomanyTest {
 
 public static void main(String[] args){
  
  Student_TeacherDAOIbatisImpl test = new Student_TeacherDAOIbatisImpl();
  List<Student> student = new ArrayList<Student>();
  try{
   //select all students from the table "student"!
   student = test.query();
   Iterator<Student> it=student.iterator();
   System.out.println("select all students from table student:");
         while(it.hasNext()){
          Student s1=(Student)it.next();
          System.out.println(s1.out());;
         }
        
         //select all the properties of a student including the teacherList by
   //using the StudentId("001")
   Student studentTest = test.getStudentById("001");
   System.out.println(studentTest.out());
        
         //insert student
         Student s2= new Student();
         s2.setStudentId("004");
         s2.setStudentName("liqie");
         test.add(s2);
         System.out.println("insert sucess");
        
         //update student
         Student s3=test.getStudentById("001");
         s3.setStudentName("duanhuixia");
         test.update(s3);
         System.out.println("update sucess");
        
         //delete student
         Student s4=test.getStudentById("003");
         test.delete(s4.getStudentId());
         System.out.println("delete sucess");
        
  }catch(Exception e){
   e.printStackTrace();
  }
 }

}

 

ibatis多对多映射

ibatis的多对多映射配置方法和多对一映射配置方法差不多,不同的是,多对多映射,数据库设计上需要一个记录两个类关系的中间表,本文以学生-老师为例,在ibatis的sqlmap中配置多对多关系。1,建...
  • aloie
  • aloie
  • 2009年06月22日 16:33
  • 4106

hibernate多对多关联映射

  • 2013年07月30日 23:53
  • 790KB
  • 下载

Mybatis多对多关联查询

  • 2016年03月15日 21:54
  • 1.12MB
  • 下载

使用ibatis映射实体间一对多、多对一的关系

1.需求说明:现有学生类包含学生姓名、年龄、性别、及所在班级这几个属性。注:所在班级是班级类的一个实例,属于自定义类型的属性。下面将介绍如何在ibatis框架的 配置文件中映射这个属性。 2.假设...
  • dongjak
  • dongjak
  • 2012年07月19日 08:48
  • 5675

Hibernate多对多关联关系demo

  • 2015年04月10日 10:30
  • 792KB
  • 下载

iBATIS一对多/多对多N+1问题解决方案

博客分类: j2ee iBATISSQLQQXMLJ# 对于iBATIS一对多/多对多的问题,传统的办法是在一对多/多对多关联的属性上再做一次子查询,这个解决办法很简单易懂,但是有个缺点,...

hibernate5(14)注解映射[6]多对多中间表关联

在我们的角色管理系统中,一个用户可以有多种角色,一种角色可以赋予多个用户,显然用户和角色就是典型的多对多关系。又或者博客网站上,用户与文章点赞记录也是一个多对多关系,即一个用户可以点赞多篇文章,一篇文...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Ibatis的多对多关联
举报原因:
原因补充:

(最多只允许输入30个字)