JDBC一对多、多对多实例

    我们知道，在设计一个JAVA bean的时候，要把这些BEAN 的数据存放在数据库中的表结构，然而这些数据库中的表直接又有些特殊的关系，例如员工与部门直接有一对多的关系，学生与老师直接又多对多的关系，那么这些表的关系如何表示呢？
    首先在建立数据库的时候就应该建立这样的对应关系。
    一对多 ，只要建立两个表就能建立这样的关系，因为你可以把多方的那个表设置一个Foreign Key 属性 ，下面是一个部门和员工的表结构关系

在mysql 数据库上应该这样建立表结构：

create table department(id int primary key,name varchar(100));create table employee(id int primary key,name varchar(100),salary float(8,2),dept_id int,constraint dept_id_fk foreign key (dept_id) references department(id)--约束);

在java 程序的javabean中应该如何做呢

public class Department {private Integer id;private String name;private Set<Employee> emps = new HashSet<Employee>();//查看部门，就能查看得到部门下面有哪些员工，所以存放在一个Set集合中。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 Set<Employee> getEmps() {return emps;}public void setEmps(Set<Employee> emps) {this.emps = emps;}@Overridepublic String toString() {return "Department [emps=" + emps + ", id=" + id + ", name=" + name+ "]";} }

public class Employee {private Integer id;private String name;private Float salary;// private Department dept = new Department();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 getSalary() {return salary;}public void setSalary(Float salary) {this.salary = salary;}@Overridepublic String toString() {return "Employee [id=" + id + ", name=" + name + ", salary=" + salary+ "]";} }

在DAO层 如何实现增加 查询数据呢？增加一个部门和查询一个部门的时候要不要显示员工呢?

public class DeparmentDao {private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());public void addDepartment(Department dept){try{//先保存部门的基本信息String sql = "insert into department values(?,?)";Object params[] = {dept.getId(),dept.getName()};qr.update(sql, params);//得到员工，如果有员工信息，保存员工信息Set<Employee> emps = dept.getEmps();if(emps!=null&&emps.size()>0){for(Employee e:emps){sql = "insert into employee values(?,?,?,?)";params = new Object[]{e.getId(),e.getName(),e.getSalary(),dept.getId()};qr.update(sql, params);}}}catch(Exception e){throw new RuntimeException(e);}}//每个部门中的员工要不要查出来？看需求public List<Department> findDepts(boolean lazy){try{//部门的基本信息String sql = "select * from department";List<Department> depts = qr.query(sql, new BeanListHandler<Department>(Department.class));if(depts!=null&&depts.size()>0){for(Department dept:depts){if(lazy){//懒的sql = "select id from employee where dept_id=?";}else{//饿的sql = "select * from employee where dept_id=?";}List<Employee> emps = qr.query(sql,  new BeanListHandler<Employee>(Employee.class), dept.getId());for(Employee e:emps){dept.getEmps().add(e);}}}return depts;}catch(Exception e){throw new RuntimeException(e);}}//每个部门中的员工要不要查出来？看需求.默认情况下，不查public List<Department> findDepts(){return findDepts(true);}}

==============================================================================

====================================多对多的关系================================

下面以老师和学生的关系来说明这个结构
数据库中：

create table teacher(id int primary key,name varchar(100),salary float(8,2));create table student(id int primary key,name varchar(100),grade varchar(100));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));

如何写javabean 和 dao呢 ？

public class Teacher {private Integer id;private String name;private Float salary;private Set<Student> stus = new HashSet<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 getSalary() {return salary;}public void setSalary(Float salary) {this.salary = salary;}public Set<Student> getStus() {return stus;}public void setStus(Set<Student> stus) {this.stus = stus;} }

public class Student {private Integer id;private String name;private String grade;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;}@Overridepublic String toString() {return "Student [grade=" + grade + ", id=" + id + ", name=" + name+ "]";}}

public class TeacherDao {private  QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());public void addTeacher(Teacher t) throws SQLException{//保存教师基本信息String sql = "insert into teacher values(?,?,?)";Object params[] = {t.getId(),t.getName(),t.getSalary()};qr.update(sql, params);//保存学生基本信息//第3方表Set<Student> stus = t.getStus();if(stus!=null&&stus.size()>0){for(Student s:stus){sql = "insert into student values(?,?,?)";params = new Object[]{s.getId(),s.getName(),s.getGrade()};qr.update(sql, params);sql = "insert into teacher_student values(?,?)";params = new Object[]{t.getId(),s.getId()};;qr.update(sql, params);}}}public List<Teacher> findTeacher(boolean lazy) throws SQLException{String sql = "select * from teacher";List<Teacher> ts = qr.query(sql, new BeanListHandler<Teacher>(Teacher.class));if(ts!=null&&ts.size()>0){for(Teacher t:ts){if(lazy){sql = "select id from student where id in (select s_id from teacher_student where t_id=?)";}else{sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";}List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), t.getId());for(Student s:stus){t.getStus().add(s);}}}return ts;}}

工具表工具

public class JdbcUtil {private static DataSource ds;private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();static{try{InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");Properties props = new Properties();props.load(in);BasicDataSourceFactory factory = new BasicDataSourceFactory();ds = factory.createDataSource(props);}catch(Exception e){throw new ExceptionInInitializerError(e);}}public static DataSource getDataSource(){return ds;}public static Connection getConnection() throws SQLException{Connection conn = tl.get();if(conn==null){conn = ds.getConnection();tl.set(conn);}return conn;}public static void startTransaction() throws SQLException{Connection conn = tl.get();if(conn==null){conn = ds.getConnection();tl.set(conn);}conn.setAutoCommit(false);}public static void rollback()throws SQLException{Connection conn = tl.get();if(conn==null){conn = ds.getConnection();tl.set(conn);}conn.rollback();}public static void commit()throws SQLException{Connection conn = tl.get();if(conn==null){conn = ds.getConnection();tl.set(conn);}conn.commit();tl.remove();}public static void release(ResultSet rs,Statement stmt,Connection conn){if(rs!=null){try{rs.close();}catch(Exception e){e.printStackTrace();}rs = null;}if(stmt!=null){try{stmt.close();}catch(Exception e){e.printStackTrace();}stmt = null;}if(conn!=null){try{conn.close();}catch(Exception e){e.printStackTrace();}conn = null;}}}

dbcpconfig.properties的文件 中内容

#连接设置driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day15  #这个是你的数据库地址username=root #这个是你的用户名password=sorry # 这个是你 密码#<!-- 初始化连接 -->initialSize=10#最大连接数量maxActive=20#<!-- 最大空闲连接 -->maxIdle=6#<!-- 最小空闲连接 -->minIdle=3#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->maxWait=60000#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。connectionProperties=useUnicode=true;characterEncoding=utf8#指定由连接池所创建的连接的自动提交（auto-commit）状态。defaultAutoCommit=true#driver default 指定由连接池所创建的连接的只读（read-only）状态。#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）defaultReadOnly=#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLEdefaultTransactionIsolation=REPEATABLE_READ