数据库操作中,常常会涉及到多表操作。多表操作涉及到的大致有:
一对多或多对一关系
多对多关系
这里就一一进行举例来进行演示:
首先针对一对多关系
可以以现实中部门和员工的关系来说明此现象。一般情况下,一个部门会有很多员工,而一个员工通常只属于一个部门。下面是部门和员工的对象关系和数据库关系图:
对应到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();
}
}
}