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