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 stus = new ArrayList();

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 getStus() {

51 return stus;

52 }

53 public void setStus(List 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 ts = new ArrayList();

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 getTs() {

31 return ts;

32 }

33 public void setTs(List 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 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.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 stus = qr.query(sql, new BeanListHandler(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 stus = t.getStus();

52 for(Student s:stus)

53 System.out.println(s.getName());

54 }

55

56 }