首页 > 代码库 > 多对多关系数据库表 java描述
多对多关系数据库表 java描述
多对多关系 需要建立一张新表存放它们的对应数据
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 );
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 }
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 }
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 }
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 }
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。