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 {
	@Test
	public 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();
		}

	}
	
	@Test
	public 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();
		}

	}
	
	@Test
	public 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();
		}

	}
}