关闭

Ibatis的多对多关联

1037人阅读 评论(0) 收藏 举报

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

}

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:6070次
    • 积分:111
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:3篇
    • 译文:0篇
    • 评论:3条
    文章分类
    文章存档