多对多关系数据库表 java描述

浏览数：25 / 时间：2015年06月12日

多对多关系需要建立一张新表存放它们的对应数据

sql语句

 1 create table teacher(
 2     id int primary key,
 3     name varchar(100),
 4     money float(8,2)
 5 );
 6 create table student(
 7     id int primary key,
 8     name varchar(100),
 9     grade varchar(10)
10 );
11 create table teacher_student(
12     t_id int,
13     s_id int,
14     primary key(t_id,s_id),
15     constraint t_id_fk foreign key(t_id) references teacher(id),
16     constraint s_id_fk foreign key(s_id) references student(id)
17 );

View Code

domain

Teacher.java

 1 package cn.itcast.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.ArrayList;
 5 import java.util.List;
 6 /**
 7 create table teacher(
 8     id int primary key,
 9     name varchar(100),
10     money float(8,2)
11 );
12 create table student(
13     id int primary key,
14     name varchar(100),
15     grade varchar(10)
16 );
17 create table teacher_student(
18     t_id int,
19     s_id int,
20     primary key(t_id,s_id),
21     constraint t_id_fk foreign key(t_id) references teacher(id),
22     constraint s_id_fk foreign key(s_id) references student(id)
23 );
24  * @author wzhting
25  *
26  */
27 public class Teacher implements Serializable {
28     private Integer id;
29     private String name;
30     private float money;
31     private List<Student> stus = new ArrayList<Student>();
32     public Integer getId() {
33         return id;
34     }
35     public void setId(Integer id) {
36         this.id = id;
37     }
38     public String getName() {
39         return name;
40     }
41     public void setName(String name) {
42         this.name = name;
43     }
44     public float getMoney() {
45         return money;
46     }
47     public void setMoney(float money) {
48         this.money = money;
49     }
50     public List<Student> getStus() {
51         return stus;
52     }
53     public void setStus(List<Student> stus) {
54         this.stus = stus;
55     }
56     
57 }

View Code

Student.java

 1 package cn.itcast.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.ArrayList;
 5 import java.util.List;
 6 
 7 public class Student implements Serializable {
 8     private Integer id;
 9     private String name;
10     private String grade;
11     private List<Teacher> ts = new ArrayList<Teacher>();
12     public Integer getId() {
13         return id;
14     }
15     public void setId(Integer id) {
16         this.id = id;
17     }
18     public String getName() {
19         return name;
20     }
21     public void setName(String name) {
22         this.name = name;
23     }
24     public String getGrade() {
25         return grade;
26     }
27     public void setGrade(String grade) {
28         this.grade = grade;
29     }
30     public List<Teacher> getTs() {
31         return ts;
32     }
33     public void setTs(List<Teacher> ts) {
34         this.ts = ts;
35     }
36     
37 }

View Code

daoImpl.java

 1 package cn.itcast.dao.impl;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.apache.commons.dbutils.QueryRunner;
 7 import org.apache.commons.dbutils.handlers.BeanHandler;
 8 import org.apache.commons.dbutils.handlers.BeanListHandler;
 9 import org.apache.commons.dbutils.handlers.ScalarHandler;
10 
11 import cn.itcast.domain.Student;
12 import cn.itcast.domain.Teacher;
13 import cn.itcast.util.DBCPUtil;
14 
15 public class TeacherDaoImpl {
16     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
17     public void addTeacher(Teacher t) throws SQLException{
18         //保存老师的基本信息
19         qr.update("insert into teacher(id,name,money) values(?,?,?)", t.getId(),t.getName(),t.getMoney());
20         //判断t中是否有学生
21         List<Student> stus = t.getStus();
22         //有学生：判断该学生是否在student表中；在，不添加了；不在，添加进去
23         if(stus!=null&&stus.size()>0){
24             for(Student s:stus){
25                 Object num =  qr.query("select 1 from student where id=?", new ScalarHandler(1), s.getId());//判断学生是否已在数据库中
26                 if(num==null){
27                     //学生信息不存在
28                     qr.update("insert into student (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade());
29                 }
30                 //在第三方表中建立老师和学生的关系
31                 qr.update("insert into teacher_student (t_id,s_id) values(?,?)", t.getId(),s.getId());
32             }
33         }
34         
35     }
36     
37     public Teacher findTeacher(Integer id) throws SQLException{
38         //查询老师的基本信息
39         Teacher t = qr.query("select * from teacher where id=?", new BeanHandler<Teacher>(Teacher.class), id);
40         if(t!=null){
41         //根据老师的id查学生的基本信息：方式三种
42 //            String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";//子查询
43 //            String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";//隐式内连接
44             String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";;//显式内连接
45             List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
46             t.setStus(stus);
47         }
48         return t;
49     }
50 }

View Code

test

测试

 1 package cn.itcast.test;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.junit.Test;
 7 
 8 import cn.itcast.dao.impl.TeacherDaoImpl;
 9 import cn.itcast.domain.Student;
10 import cn.itcast.domain.Teacher;
11 
12 public class TeacherDaoImplTest {
13     private TeacherDaoImpl dao = new TeacherDaoImpl();
14     @Test
15     public void testAddTeacher() throws SQLException {
16         Teacher t1 = new Teacher();
17         t1.setId(1);
18         t1.setName("bxd");
19         t1.setMoney(20000);
20         
21         Teacher t2 = new Teacher();
22         t2.setId(2);
23         t2.setName("wzt");
24         t2.setMoney(15000);
25         
26         Student s1 = new Student();
27         s1.setId(1);
28         s1.setName("gfy");
29         s1.setGrade("A");
30         
31         Student s2 = new Student();
32         s2.setId(2);
33         s2.setName("wxy");
34         s2.setGrade("A");
35         
36         t1.getStus().add(s1);
37         t1.getStus().add(s2);
38         
39         t2.getStus().add(s1);
40         t2.getStus().add(s2);
41         
42         dao.addTeacher(t1);
43         dao.addTeacher(t2);
44         
45     }
46 
47     @Test
48     public void testFindTeacher() throws SQLException {
49         Teacher t = dao.findTeacher(2);
50         System.out.println(t.getName());
51         List<Student> stus = t.getStus();
52         for(Student s:stus)
53             System.out.println(s.getName());
54     }
55 
56 }