JDBC多表操作---多对多

来源：互联网发布：rfid数据采集模块编辑：程序博客网时间：2024/05/01 03:03

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; //老师IDprivate 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; //学生IDprivate 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();//查询关系表，获取与改老师有映射关系的学生IDsString 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();//查询关系表，获取与改老师有映射关系的学生IDsString 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;}}

//测试程序

@Testpublic 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();}}@Testpublic 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();}}@Testpublic 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();}}


                                                     0        0           	
					
					   JDBC多表操作---多对多
	  	   JDBC操作多表----- 一对一
	  	   JDBC多表的CRUD操作
	  	   使用JDBC进行多表操作
	  	   JDBC操作多个表(一对多)
	  	   Spring JDBC多批次操作
	  	   JDBC一对多、多对多实例
	  	   JDBC上关于数据库中多表操作一对多关系和多对多关系的实现方法
	  	   JDBC上关于数据库中多表操作一对多关系和多对多关系的实现方法
	  	   通过jdbc对数据库操作
	  	   JDBC对数据查询操作
	  	   hibernate多表操作之多对多
	  	   Hibernate之多对多表，操作实例
	  	   hibernate多表操作之一对多的单项操作
	  	   hibernate初学8  多表操作之一对多操作
	  	   注解多对多操作
	  	   Hibernate多对多操作
	  	   Hibernate多对多操作
	     		  
	  	   8-企业案例分析-1-2-3-4
	  	   基于visual Studio2013解决面试题之0502字符串左移
	  	   ssh的简单使用
	  	   S3C2440FCLK、HCLK、PCLK的配置
	  	   LSA SVD PLSI Topic Model
	  	   JDBC多表操作---多对多
	  	   如何立即释放VB6中不用的模块
	  	   【wikioi】1078最小生成树 prim 100分C源码
	  	   php实现调用百度地图功能
	  	   java多线程同步块（synchronized(Object o){})的一个例程
	  	   JNIWrapper学习
	  	   项目经理应该以推进者、拉拉队长的身份发挥作用才能让团队发挥更大能量
	  	   JAVA文件上传之DiskFileItemFactory类
	  	   Python3.3搭建简单的HTTP服务器