JDBC多表操作---多对多

JDBC对多表的操作中，除了一对多或多对一外，还有多对多的关系；对多对多的操作，会比较复杂点。在这里，以教师和学生的关系来演示JDBC对多表操作中的多对多的操作。首先需要描述清楚教师和学生之间的关系。
我们知道一个教师负责给很多学生授课，同时一个学生一般会听很多教师的课程。
下面分别以代码和Mysql数据库来描述老师和学生这两个对象，具体如下：
用SQL语言来描述老师和学生的关系，需要分别创建一个存储老师数据的表(teacher)，一个存储学生数据的表(student)及一个描述老师和学生关系的表(teacher_student). 如下：

create table teacher(
id int primary key,
name varchar(20),
subject varchar(20)
);

create table student(
id int primary key,
name varchar(20)
);
//此表为老师和学生的关系表
create table teacher_student(
teacher_id int,
student_id int,
primary key(teacher_id, student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);

//老师和学生对象描述如下:
//老师对象

public class Teacher {
private int id; //老师ID
private String name; //老师名称
private String subject; //授课名称
private Set<Student> set = new HashSet<Student>();

public Teacher(){

}


public Teacher(int id, String name, String subject) {
super();
this.id = id;
this.name = name;
this.subject = subject;
}


/**
* @return the id
*/
public int getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(int id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the subject
*/
public String getSubject() {
return subject;
}
/**
* @param subject the subject to set
*/
public void setSubject(String subject) {
this.subject = subject;
}
/**
* @return the set
*/
public Set<Student> getSet() {
return set;
}
/**
* @param set the set to set
*/
public void setSet(Set<Student> set) {
this.set = set;
}
}
//学生对象
public class Student {
private int id; //学生ID
private String name; //学生名字
private Set<Teacher>set = new HashSet<Teacher>(); //学生参与课程的老师集合

public Student(){

}

public Student(int id, String name) {
super();
this.id = id;
this.name = name;
}

/**
* @return the id
*/
public int getId() {
return id;
}

/**
* @param id the id to set
*/
public void setId(int id) {
this.id = id;
}

/**
* @return the name
*/
public String getName() {
return name;
}

/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
}

//老师对象Dao

public interface TeacherDao {

void add(Teacher teacher) throws SQLException; //增加一个老师

Teacher find(int id) throws SQLException; //查找指定id号老师信息

List<Student> getAll(Teacher teacher) throws SQLException; //获取老师所教课程的所有学生信息

}

//学生对象Dao

public interface StudentDao {

void add(Student student) throws SQLException; //增加一个老师

Student find(int id) throws SQLException; //查找指定ID号学生信息

List<Teacher> getAll(Student student) throws SQLException; //获取学生所修课程老师的信息

}

//Dao实现

public class TeacherDaoImpl implements TeacherDao {
/* (non-Javadoc)
* @see cn.itcast.dao.impl.TeacherDao#add(cn.itcast.domain.Teacher)
*/
public void add(Teacher teacher) throws SQLException{
QueryRunner qr = new QueryRunner();
String sql = "insert into teacher(id, name, subject) values(?,?, ?)";
Object[] params = {teacher.getId(), teacher.getName(), teacher.getSubject()};

qr.update(JdbcDbutils.getConnection(), sql, params);

//取出所有学生,存入student表中,同时增加维持老师和学生的关系记录
HashSet<Student>set = (HashSet<Student>) teacher.getSet();
Object param[][] = new Object[set.size()][];
Object param2[][] = new Object[set.size()][];
int index = 0;
sql = "insert into student(id, name) values(?,?)";
String sql2 = "insert into teacher_student(teacher_id, student_id) values(?,?)";
for(Student student : set){
param[index] = new Object[]{student.getId(), student.getName()};
param2[index] = new Object[]{teacher.getId(), student.getId()};
index++;
}
qr.batch(JdbcDbutils.getConnection(), sql, param);
qr.batch(JdbcDbutils.getConnection(), sql2, param2);
//增加一条关系表

}

/* (non-Javadoc)
* @see cn.itcast.dao.impl.TeacherDao#find(int)
*/
public Teacher find(int id) throws SQLException{
QueryRunner qr = new QueryRunner();
String sql = "select * from teacher where id=?";

Object[] param = {id};
return (Teacher)qr.query(JdbcDbutils.getConnection(), sql, new BeanHandler(Student.class), param);
}

/* (non-Javadoc)
* @see cn.itcast.dao.impl.TeacherDao#getAll(cn.itcast.domain.Teacher)
*/

public List<Student> getAll(Teacher teacher) throws SQLException{
QueryRunner qr = new QueryRunner();
//查询关系表，获取与改老师有映射关系的学生IDs
String sql = "select student_id from teacher_student where teacher_id=?";

List<Object[]> lst = new ArrayList<Object[]>();
List<Student> sLst = new ArrayList<Student>();
Object[] param = {teacher.getId()};
lst = (List<Object[]>) qr.query(JdbcDbutils.getConnection(), sql, new ArrayListHandler(), param);
sql = "select * from student where id=?";

for(Object[] id : lst){
sLst.add((Student) qr.query(JdbcDbutils.getConnection(), sql, id[0], new BeanHandler(Student.class)));
}

return sLst;
}
}
//学生Dao实现
public class StudentDaoImpl implements StudentDao {
/* (non-Javadoc)
* @see cn.itcast.dao.impl.StudentDao#add(cn.itcast.domain.Student)
*/
public void add(Student student) throws SQLException{
QueryRunner qr = new QueryRunner();
String sql = "insert into student(id, name) values(?,?)";
Object[] params = {student.getId(), student.getName()};

qr.update(JdbcDbutils.getConnection(), sql, params);

//取出学生所有修课程的所有老师信息
sql = "insert into teacher(id, name, subject) values(?,?,?)";
String sql2 = "insert into teacher_student(teacher_id, student_id) values(?,?)";
HashSet<Teacher>set = (HashSet<Teacher>) student.getSet();
Object[][] param = new Object[set.size()][];
Object[][] param2 = new Object[set.size()][];
int index=0;
for(Teacher t: set){
param[index] = new Object[]{t.getId(),t.getName(), t.getSubject()};
param2[index] = new Object[]{t.getId(), student.getId()};
index++;
}
qr.batch(JdbcDbutils.getConnection(), sql, param);
qr.batch(JdbcDbutils.getConnection(), sql2, param2);
}

/* (non-Javadoc)
* @see cn.itcast.dao.impl.StudentDao#find(int)
*/
public Student find(int id) throws SQLException{
QueryRunner qr = new QueryRunner();
String sql = "select * from student where id=?";

Object[] param = {id};
return (Student)qr.query(JdbcDbutils.getConnection(), sql, new BeanHandler(Student.class), param);
}

/* (non-Javadoc)
* @see cn.itcast.dao.impl.StudentDao#getAll(cn.itcast.domain.Student)
*/

public List<Teacher> getAll(Student student) throws SQLException{
QueryRunner qr = new QueryRunner();
//查询关系表，获取与改老师有映射关系的学生IDs
String sql = "select teacher_id from teacher_student where student_id=?";

List<Object[]> lst = new ArrayList<Object[]>();
List<Teacher> sLst = new ArrayList<Teacher>();
Object[] param = {student.getId()};
lst = (List<Object[]>) qr.query(JdbcDbutils.getConnection(), sql, new ArrayListHandler(), param);
sql = "select * from teacher where id=?";

for(Object[] id : lst){
sLst.add((Teacher) qr.query(JdbcDbutils.getConnection(), sql, id[0], new BeanHandler(Teacher.class)));
}

return sLst;
}
}

//测试程序

@Test
public void TeacherAddTest() throws SQLException {
try {
JdbcDbutils.startTransaction();
Teacher teacher = new Teacher(1, "wuliang", "math");
Student std1 = new Student(1000, "wl");
Student std2 = new Student(2000, "xt");
Student std3 = new Student(3000, "wlxt");

std1.getSet().add(teacher);
std2.getSet().add(teacher);
std3.getSet().add(teacher);

teacher.getSet().add(std1);
teacher.getSet().add(std2);
teacher.getSet().add(std3);

TeacherDao tDao = new TeacherDaoImpl();
tDao.add(teacher);
JdbcDbutils.commit();
} catch (Exception e) {
e.printStackTrace();
JdbcDbutils.rollback();
} finally {
JdbcDbutils.release();
}
}

@Test
public void getAll() throws SQLException {
try {
JdbcDbutils.startTransaction();

TeacherDao tDao = new TeacherDaoImpl();
Teacher teacher = tDao.find(1);
List<Student> lst = tDao.getAll(teacher);
System.out.println("老师是:"+teacher.getName()+"参与起课程的学生有:");
for(Student stu: lst){
System.out.println("\t:"+"ID:"+stu.getId()+" "+"姓名:"+stu.getName());
}

JdbcDbutils.commit();
} catch (Exception e) {
e.printStackTrace();
JdbcDbutils.rollback();
} finally {
JdbcDbutils.release();
}
}

@Test
public void StudentAddTest() throws SQLException {
try {
JdbcDbutils.startTransaction();
Student student = new Student(4000, "wwll");
Teacher t1 = new Teacher(4, "xxtt", "物理");
Teacher t2 = new Teacher(5, "wlxt", "生物");
Teacher t3 = new Teacher(6, "wlws", "化学");

student.getSet().add(t1);
student.getSet().add(t2);
student.getSet().add(t3);

t1.getSet().add(student);
t2.getSet().add(student);
t3.getSet().add(student);

StudentDao tDao = new StudentDaoImpl();
tDao.add(student);
JdbcDbutils.commit();
} catch (Exception e) {
e.printStackTrace();
JdbcDbutils.rollback();
} finally {
JdbcDbutils.release();
}
}