关系多表取出,根据面向对象思想通常是将数据封装为对象,由一个主对象包装其他子对象,子对象包装子子对象,由主对象的dao进行数据库操作,从而简化逻辑,防止混乱。
如对teacher与student这种多对多的关系,设计好teacher表,student表,teacher_student表后,在java中设计由teacher来维护三个表,student可以放弃对teacher_student表的操作。当发生学生换老师的这种只改变teacher_student表的操作时,不能使用update,而是先delete后add来实现。
设计代码如下:
domain类:
package com.hao.domain;
import java.util.Set;
public class Teacher {
private String id;
private String name;
private double salary;
private Set<Student> students;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Set<Student> getStudents() {
return students;
}
public void setStudents(Set<Student> students) {
this.students = students;
}
}
package com.hao.domain;
import java.util.Set;
public class Student {
private String id;
private String name;
//这里的teachers字段不是给dao层使用的,这样设计是方便其他层有相应的需求
private Set<Teacher> teachers;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Teacher> getTeachers() {
return teachers;
}
public void setTeachers(Set<Teacher> teachers) {
this.teachers = teachers;
}
}
dao层:
package com.hao.dao;
import com.hao.domain.Student;
import com.hao.domain.Teacher;
import com.hao.utils.DaoUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class TeacherDao {
private QueryRunner qr = new QueryRunner(DaoUtils.getDataSource());
public void add(Teacher teacher) {
try {
//老师加入老师表
String sql = "insert into teacher(id,name,salary) values(?,?,?)";
qr.update(sql, teacher.getId(), teacher.getName(), teacher.getSalary());
Set<Student> students = teacher.getStudents();
String teacherId = teacher.getId();
for (Student student : students) {
//学生加入学生表
sql = "insert into student(id,name) values(?,?)";
qr.update(sql, student.getId(), student.getName());
//关系插入映射表
sql = "insert into teacher_student(teacher_id,student_id) values(?,?)";
qr.update(teacherId, student.getId());
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void delete(String id) {
String sql = "delete from teacher where id = ?";
try {
qr.update(sql, id);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void update(Teacher teacher) {
try {
//更新老师表
String sql = "update teacher set name=?,salary=? where id=?";
qr.update(sql, teacher.getName(), teacher.getSalary(), teacher.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Teacher find(String id) {
try {
//找到老师
String sql = "select name,salary from teacher where id=?";
Teacher teacher = qr.query(sql, new BeanHandler<Teacher>(Teacher.class), id);
teacher.setId(id);
//找到老师的学生,这个可以没有,看需求
sql = "select st.id,st.name from student st,teacher_student te where te.teacher_id=? and te.student_i=dst.id";
List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
teacher.setStudents(new HashSet<Student>(students));
return teacher;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}