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

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

sql语句

create table teacher(
    id int primary key,
    name varchar(100),
    money float(8,2)
);
create table student(
    id int primary key,
    name varchar(100),
    grade varchar(10)
);
create table teacher_student(
    t_id int,
    s_id int,
    primary key(t_id,s_id),
    constraint t_id_fk foreign key(t_id) references teacher(id),
    constraint s_id_fk foreign key(s_id) references student(id)
);

domain

Teacher.java

package cn.itcast.domain;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
/**
create table teacher(
    id int primary key,
    name varchar(100),
    money float(8,2)
);
create table student(
    id int primary key,
    name varchar(100),
    grade varchar(10)
);
create table teacher_student(
    t_id int,
    s_id int,
    primary key(t_id,s_id),
    constraint t_id_fk foreign key(t_id) references teacher(id),
    constraint s_id_fk foreign key(s_id) references student(id)
);
 * @author wzhting
 *
 */
public class Teacher implements Serializable {
    private Integer id;
    private String name;
    private float money;
    private List<Student> stus = new ArrayList<Student>();
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getMoney() {
        return money;
    }
    public void setMoney(float money) {
        this.money = money;
    }
    public List<Student> getStus() {
        return stus;
    }
    public void setStus(List<Student> stus) {
        this.stus = stus;
    }
    
}

Student.java

package cn.itcast.domain;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

public class Student implements Serializable {
    private Integer id;
    private String name;
    private String grade;
    private List<Teacher> ts = new ArrayList<Teacher>();
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getGrade() {
        return grade;
    }
    public void setGrade(String grade) {
        this.grade = grade;
    }
    public List<Teacher> getTs() {
        return ts;
    }
    public void setTs(List<Teacher> ts) {
        this.ts = ts;
    }
    
}

daoImpl.java

package cn.itcast.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.domain.Student;
import cn.itcast.domain.Teacher;
import cn.itcast.util.DBCPUtil;

public class TeacherDaoImpl {
    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
    public void addTeacher(Teacher t) throws SQLException{
        //保存老师的基本信息
        qr.update("insert into teacher(id,name,money) values(?,?,?)", t.getId(),t.getName(),t.getMoney());
        //判断t中是否有学生
        List<Student> stus = t.getStus();
        //有学生：判断该学生是否在student表中；在，不添加了；不在，添加进去
        if(stus!=null&&stus.size()>0){
            for(Student s:stus){
                Object num =  qr.query("select 1 from student where id=?", new ScalarHandler(1), s.getId());//判断学生是否已在数据库中
                if(num==null){
                    //学生信息不存在
                    qr.update("insert into student (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade());
                }
                //在第三方表中建立老师和学生的关系
                qr.update("insert into teacher_student (t_id,s_id) values(?,?)", t.getId(),s.getId());
            }
        }
        
    }
    
    public Teacher findTeacher(Integer id) throws SQLException{
        //查询老师的基本信息
        Teacher t = qr.query("select * from teacher where id=?", new BeanHandler<Teacher>(Teacher.class), id);
        if(t!=null){
        //根据老师的id查学生的基本信息：方式三种
//            String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";//子查询
//            String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";//隐式内连接
            String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";;//显式内连接
            List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
            t.setStus(stus);
        }
        return t;
    }
}

test

测试

package cn.itcast.test;

import java.sql.SQLException;
import java.util.List;

import org.junit.Test;

import cn.itcast.dao.impl.TeacherDaoImpl;
import cn.itcast.domain.Student;
import cn.itcast.domain.Teacher;

public class TeacherDaoImplTest {
    private TeacherDaoImpl dao = new TeacherDaoImpl();
    @Test
    public void testAddTeacher() throws SQLException {
        Teacher t1 = new Teacher();
        t1.setId(1);
        t1.setName("bxd");
        t1.setMoney(20000);
        
        Teacher t2 = new Teacher();
        t2.setId(2);
        t2.setName("wzt");
        t2.setMoney(15000);
        
        Student s1 = new Student();
        s1.setId(1);
        s1.setName("gfy");
        s1.setGrade("A");
        
        Student s2 = new Student();
        s2.setId(2);
        s2.setName("wxy");
        s2.setGrade("A");
        
        t1.getStus().add(s1);
        t1.getStus().add(s2);
        
        t2.getStus().add(s1);
        t2.getStus().add(s2);
        
        dao.addTeacher(t1);
        dao.addTeacher(t2);
        
    }

    @Test
    public void testFindTeacher() throws SQLException {
        Teacher t = dao.findTeacher(2);
        System.out.println(t.getName());
        List<Student> stus = t.getStus();
        for(Student s:stus)
            System.out.println(s.getName());
    }

}