JDBC操作多个表(一对多)

数据库操作中，常常会涉及到多表操作。多表操作涉及到的大致有：

一对多或多对一关系

多对多关系

这里就一一进行举例来进行演示：

首先针对一对多关系

可以以现实中部门和员工的关系来说明此现象。一般情况下，一个部门会有很多员工，而一个员工通常只属于一个部门。下面是部门和员工的对象关系和数据库关系图：

对应到code如下：

package cn.itcast.domain;import java.util.HashSet;//deparment类public class Deparment {private int id;private String name;private HashSet<Employee> set = new HashSet<Employee>();   //一对多public Deparment(){}public Deparment(int id, String name) {super();this.id = id;this.name = name;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public HashSet<Employee> getSet() {return set;}public void setSet(HashSet<Employee> set) {this.set = set;}}

//员工类

package cn.itcast.domain;public class Employee {private String id;private String name;private float salary;private int deparment_id;public Employee(){}public Employee(String id, String name, float salary, int deparment_id) {super();this.id = id;this.name = name;this.salary = salary;this.deparment_id = deparment_id;}public String getId() {return id;}public void setId(String 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 int getDeparment_id() {return deparment_id;}public void setDeparment_id(int deparment_id) {this.deparment_id = deparment_id;}}

mysql数据库，deparment和employee表的定义

create table deparment( id int primary key, name varchar(20) );

create table employee(id varchar(40) primary key,name varchar(20),salary float,deparment_id int,constraint deparment_id_FK foreign key(deparment_id) references deparment(id));

//部门和员工Dao接口

public interface DeparmentDao {void add(Deparment department) throws SQLException;//增加一个部门Deparment findDeparment(int id) throws SQLException, DeparmentNotExistExceptin;//查找一个部门List<Employee> getAllEmployees(int deparment_id) throws SQLException;//获取一个部门所有员工信息}

public interface EmployeeDao {void add(Employee employee) throws SQLException;//增加一个员工Employee find(String id) throws SQLException;//查找一个员工}

//DaoImpl实现

package cn.itcast.dao.impl;import java.sql.SQLException;import java.util.HashSet;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 cn.itcast.dao.DeparmentDao;import cn.itcast.domain.Deparment;import cn.itcast.domain.Employee;import cn.itcast.exception.DeparmentNotExistExceptin;import cn.itcast.utils.JdbcC3p0Utils;import cn.itcast.utils.JdbcDbutils;/* create table deparment( id int primary key, name varchar(20) ); */public class DemparmentDaoImpl implements DeparmentDao {public void add(Deparment department) throws SQLException{QueryRunner qr = new QueryRunner();//添加department 表String sql = "insert into deparment(id, name) values(?, ?)";Object[] param = {department.getId(), department.getName()};qr.update(JdbcDbutils.getConnection(), sql, param);//添加employee表sql = "insert into employee(id, name, salary, deparment_id) values(?,?,?,?)";HashSet<Employee> set = department.getSet();Object[][] params = new Object[set.size()][];int index = 0;for(Employee employee: set){params[index++] = new Object[]{employee.getId(), employee.getName(), employee.getSalary(), department.getId()};}qr.batch(JdbcDbutils.getConnection(), sql, params);}public Deparment findDeparment(int deparment_id) throws SQLException, DeparmentNotExistExceptin{QueryRunner qr = new QueryRunner();String sql = "select * from deparment where id=?";Object[] params = {deparment_id};Deparment dp = (Deparment)qr.query(JdbcDbutils.getConnection(), sql, new BeanHandler(Deparment.class), params);if(dp==null){throw new DeparmentNotExistExceptin();}//获取employee信息sql = "select * from employee where deparment_id = ?";params = new Object[]{dp.getId()};List<Employee> lst = (List<Employee>) qr.query(JdbcDbutils.getConnection(), sql, new BeanListHandler(Employee.class), params);for(Employee employee: lst){dp.getSet().add(employee);}return dp;}public List<Employee> getAllEmployees(int deparment_id) throws SQLException{QueryRunner qr = new QueryRunner();String sql = "select * from deparment where id=?";Object[] params = {deparment_id};Deparment dp = (Deparment)qr.query(JdbcDbutils.getConnection(), sql, new BeanHandler(Deparment.class), params);//获取employee信息sql = "select * from employee where deparment_id = ?";params = new Object[]{dp.getId()};List<Employee> lst = (List<Employee>) qr.query(JdbcDbutils.getConnection(), sql, new BeanListHandler(Employee.class), params);return lst;}}

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 cn.itcast.dao.EmployeeDao;import cn.itcast.domain.Employee;import cn.itcast.utils.JdbcDbutils;/* create table employee(id varchar(40) primary key,name varchar(20),salary float,deparment_id int,constraint deparment_id_FK foreign key(deparment_id) references deparment(id)); */public class EmployeeDaoImpl implements EmployeeDao {public void add(Employee employee) throws SQLException{QueryRunner qr  = new QueryRunner();String sql = "insert into employee(id, name, salary, deparment_id) values(?,?,?,?)";Object[] params = {employee.getId(), employee.getName(), employee.getSalary(), employee.getDeparment_id()};qr.update(JdbcDbutils.getConnection(), sql, params);}public Employee find(String id) throws SQLException{QueryRunner qr  = new QueryRunner();String sql = "select from employee where id=?";Object[] params = {id};return (Employee) qr.query(JdbcDbutils.getConnection(), sql, new BeanHandler(Employee.class), params);}}

//测试类

package cn.itcast.junit;import java.util.HashSet;import org.junit.Test;import cn.itcast.dao.DeparmentDao;import cn.itcast.dao.EmployeeDao;import cn.itcast.dao.impl.DemparmentDaoImpl;import cn.itcast.dao.impl.EmployeeDaoImpl;import cn.itcast.domain.Deparment;import cn.itcast.domain.Employee;import cn.itcast.exception.DeparmentNotExistExceptin;import cn.itcast.utils.JdbcDbutils;public class DaoTest {@Testpublic void DemparmentAddTest() {try {JdbcDbutils.startTransaction();Deparment deparment = new Deparment(2, "softer4");Employee employee1 = new Employee("pa0001", "wuliang", 1000,deparment.getId());Employee employee2 = new Employee("pa0002", "xiaotao", 2000,deparment.getId());Employee employee3 = new Employee("pa0003", "wlxt", 3000,deparment.getId());deparment.getSet().add(employee1);deparment.getSet().add(employee2);deparment.getSet().add(employee3);DeparmentDao daoImpl = new DemparmentDaoImpl();daoImpl.add(deparment);JdbcDbutils.commit();} catch (Exception e) {e.printStackTrace();JdbcDbutils.rollback();} finally {JdbcDbutils.release();}}@Testpublic void DemparmentFindTest() {try {JdbcDbutils.startTransaction();Deparment deparment = new Deparment();DeparmentDao daoImpl = new DemparmentDaoImpl();deparment = daoImpl.findDeparment(2);//获取部分信息System.out.println("deparment id:"+deparment.getId());System.out.println("deparment name:"+deparment.getName());//获取员工信息for(Employee employee: deparment.getSet()){System.out.println("\t------------");System.out.println("\t"+"id:"+employee.getId());System.out.println("\t"+"name:"+employee.getName());System.out.println("\t"+"salary:"+employee.getSalary());}JdbcDbutils.commit();} catch (Exception e) {e.printStackTrace();JdbcDbutils.rollback();} finally {JdbcDbutils.release();}}@Testpublic void EmployeeAddTest() {try {JdbcDbutils.startTransaction();Employee employee = new Employee("cv0001", "wuliang", 1000, 2);DeparmentDao dpDaoImpl = new DemparmentDaoImpl();Deparment deparment = dpDaoImpl.findDeparment(employee.getDeparment_id());EmployeeDao emDaoImpl = new EmployeeDaoImpl();emDaoImpl.add(employee);JdbcDbutils.commit();} catch(DeparmentNotExistExceptin de){System.out.println("对不起登记错误，部门不存在!!!!");}catch (Exception e) {e.printStackTrace();JdbcDbutils.rollback();} finally {JdbcDbutils.release();}}}