概述
day17java工程搭建步骤:
1,src下新建lib目录,导入第3方jar包
commons-dbcp-1.2.2.jar
commons-dbutils-1.2.jar
commons-pool.jar
mysql-connector-java-5.0.8-bin.jar
2,src下导入dbcpconfig.properties,并改库名day17
3,表的设计(一对多)
一对多的关系演示,表的设计,多的一方设置外键列!
mysql -uroot -proot
set character_set_client=gb2312;
set character_set_results=gb2312;
use day17;
create table department
(
id varchar(40) primary key,
name varchar(40)
);
create table employee
(
id varchar(40) primary key,
name varchar(40),
salary double,
department_id varchar(40),
constraint department_id_FK foreign key(department_id) references department(id)
);
//删除drop 外键列foreign key 约束名是department_id_FK
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete set null;
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete cascade;
4,表的设计(多对多)
//多对多的表设计,中间表,联合主键+两个外键列
mysql -uroot -proot
set character_set_client=gb2312;
set character_set_results=gb2312;
create database day17 character set utf8 collate utf8_general_ci;
use day17;
create table teacher
(
id varchar(40) primary key,
name varchar(40),
salary double
) ;
create table student
(
id varchar(40) primary key,
name varchar(40)
);
//多对多的表设计,中间表,联合主键+两个外键列
create table teacher_student
(
teacher_id varchar(40),
student_id varchar(40),
primary key(teacher_id,student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);
alter table teacher_student drop foreign key teacher_id_FK;
alter table teacher_student add constraint teacher_id_FK foreign key(teacher_id) references teacher(id) on delete cascade;
alter table teacher_student drop foreign key student_id_FK;
alter table teacher_student add constraint student_id_FK foreign key(student_id) references student(id) on delete cascade;
5,建立包
domain包,
Department类,(成员String id,String name,Set employees)
Employee类,(成员String id,String name,Department d)
Teacher类,(成员String id,String name,double salary,set students)生成getter setter方法
Student类,(成员String id,String name)生成getter setter方法
utils包,
JdbcUtils
dao包,(重点)
TeacherDao专门负责Teacher对象的CRUD
service包,
Tservice,薄薄的,new个Teacher对象,赋值,调用TeacherDao进行CRUD
Dservice,薄薄的,new个Department对象,赋值,调用DepartmentDao进行CRUD
User位于domain包
package cn.itcast.domain;
import java.util.Date;
//改类对应Demo1_Dbutils,演示Dbutils的使用,完成CRUD
public class User {
private int id;
private String name;
private String password;
private String email;
private Date birthday;
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 String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
Demo1_Dbutils位于demo包
package cn.itcast.demo;
import java.sql.SQLException;
import java.util.Date;
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.junit.Test;
import cn.itcast.domain.User;
import cn.itcast.utils.JdbcUtils;
//演示Dbutils的使用,完成CRUD
//Dbutils是Hibernate的备选,是对JDBC的简单封装,核心是QueryRunner和ResultSetHandler
//导入commons-dbutils-1.2.jar
/*
创建库和表:
mysql -uroot -proot
set character_set_client=gb2312;
set character_set_results=gb2312;
use day17;
create table users(
id int primary key,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
);
*/
public class Demo1_Dbutils {
//方法1,使用Dbutils完成user表的插入
@Test
public void insert() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
// Object[] params={1,"林黛玉","12345","lindaiyu@163.com",new Date()};
Object[] params={2,"薛宝钗","12354","xuebaochai@163.com",new Date()};
qr.update(sql, params);
}
//方法2,使用Dbutils完成user表的更新
@Test
public void update() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="update users set password=? where id=?";
Object[] params={"0123456",1};
qr.update(sql, params);
}
//方法3,使用Dbutils完成user表的删除
@Test
public void delete() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="delete from users where id=?";
//由于只有一个参数,故可不能参数数组
qr.update(sql,1);
}
//方法4,使用Dbutils完成user表的查询,将第一条记录封装到User类里面
@Test
public void query_1() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users where id=?";
//由于只有一个参数,故可不能参数数组
User u1=(User) qr.query(sql, 1, new BeanHandler(User.class));
System.out.println(u1.getName());
System.out.println(u1.getBirthday());
}
//方法5,使用Dbutils完成user表的查询,将所有记录逐一封装到User类里面,
//再将所有User逐一添加到list,返回list
@Test
public void query_2() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
List list=(List) qr.query(sql, new BeanListHandler(User.class));
System.out.println(list.size());
//上面这儿停个断点,然后Debug As Junit Test
}
//方法6,使用Dbutils完成user表的批量插入(关键是二维数组使用)
@Test
public void batch() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
//(关键是二维数组使用)插入3条记录,每条记录需要5个参数
Object[][] params=new Object[3][5];
for (int i = 0; i < params.length; i++) {
params[i]=new Object[]{3+i,"路人"+(i+1),"123","123@qq.com",new Date()};
}
qr.batch(sql, params);
}
}
Demo2_Dbutils位于demo包
package cn.itcast.demo;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import cn.itcast.domain.User;
import cn.itcast.utils.JdbcUtils;
//演示Dbutils的各个结果集处理器的使用
//Dbutils是Hibernate的备选,是对JDBC的简单封装,核心是QueryRunner和ResultSetHandler
//导入commons-dbutils-1.2.jar
/*
创建库和表:
mysql -uroot -proot
set character_set_client=gb2312;
set character_set_results=gb2312;
use day17;
create table users(
id int primary key,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date
);
Class Summary
AbstractListHandler Abstract class that simplify development of ResultSetHandler classes that convert ResultSet into List.
ArrayHandler 作用是: converts a ResultSet into an Object[].
ArrayListHandler 作用是: converts the ResultSet into a List of Object[]s.
BeanHandler 作用是: converts the first ResultSet row into a JavaBean.
BeanListHandler 作用是: converts a ResultSet into a List of beans.
ColumnListHandler 作用是: converts one ResultSet column into a List of Objects.
KeyedHandler 作用是: returns a Map of Maps.
MapHandler 作用是: converts the first ResultSet row into a Map.
MapListHandler 作用是: converts a ResultSet into a List of Maps.
ScalarHandler 作用是: converts one ResultSet column into an Object.
*/
public class Demo2_Dbutils {
/*Dbutils的结果集处理器_ArrayHandler的使用
ArrayHandler(不需要参数) 作用是: 只将结果集的第一行记录的值保存到数组里!
converts a ResultSet into an Object[].*/
@Test
public void ArrayHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
Object[] arr=(Object[]) qr.query(sql, new ArrayHandler());
for (Object obj : arr) {
System.out.println(obj);
}
/*
1
林黛玉
12345
lindaiyu@163.com
2013-12-29*/
}
/*Dbutils的结果集处理器_ArrayListHandler的使用
ArrayListHandler(不需要参数) 作用是: 将结果集的每行记录的值保存到数组里
再将这些数组,保存到一个集合里面!
converts the ResultSet into a List of Object[]s*/
@Test
public void ArrayListHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
List list=(List) qr.query(sql, new ArrayListHandler());
for (Object arr : list) {
Object[] a=new Object[]{};
a=(Object[]) arr;
for (Object value : a) {
System.out.println(value);
/*
1
林黛玉
12345
lindaiyu@163.com
2013-12-29
2
薛宝钗
12354
xuebaochai@163.com
2013-12-29*/
}
}
}
/*Dbutils的结果集处理器_ColumnListHandler的使用
ColumnListHandler(需要参数:列名) 作用是: 只将指定列名的数据存入list(如取出所有名字)
converts one ResultSet column into a List of Objects.*/
@Test
public void ColumnListHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
List list=(List) qr.query(sql, new ColumnListHandler_my("name"));//需指定列名
for (Object obj : list) {
System.out.println(obj);//打印的是姓名列的所有值
//林黛玉
//薛宝钗
}
}
//自己实现将指定列的数据存入list
//自己写一个ColumnListHandler
class ColumnListHandler_my implements ResultSetHandler{
private String columnName;
public ColumnListHandler_my(String columnName) {
super();
this.columnName = columnName;
}
public Object handle(ResultSet rs) throws SQLException {
// 覆盖接口的方法
//取出构造时指定的列名下所有的值,加到list,并返回list
List list=new ArrayList();
while(rs.next()){
Object obj=rs.getObject(columnName);
list.add(obj);
}
return list;
}
}
/*ResultSetHandler 接口的实现类 KeyedHandler(keyName):
* 将结果集中的每一行数据都封装到一个Map里(键为列名,值为列上值),
* 再把这些map再存到一个map里,其key为指定的参数keyName。
*/
/*Dbutils的结果集处理器_KeyedHandler的使用(难点是泛型!)
KeyedHandler(需要参数:列名作为key) 作用是: 将结果集的每一行都封装到
一个小map(键为字段名,值为该字段的值),再将这些map存到另一个大map里面,
(键为构造时的参数,即指定的列名为大map的键,值为小map)
returns a Map of Maps*/
@Test
public void KeyedHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
//表在设计时id是int类型,泛型是对象,所以键为Integer,
//值是一个MAP(键是字段名,所以是String,值是字段上的值,所以是Object)
Map<Integer, Map<String, Object>> map=(Map) qr.query(sql, new KeyedHandler("id"));
for (Map.Entry<Integer, Map<String, Object>> en : map.entrySet()) {
int id=en.getKey();
Map<String, Object> map_0=en.getValue();
for (Map.Entry<String, Object> entry : map_0.entrySet()) {
String columnName=entry.getKey();
Object columnValue=entry.getValue();
System.out.println(columnName+" : "+columnValue);
/*
password : 12354
email : xuebaochai@163.com
name : 薛宝钗
id : 2
birthday : 2013-12-29
password : 12345
email : lindaiyu@163.com
name : 林黛玉
id : 1
birthday : 2013-12-29*/
}
}
}
/*Dbutils的结果集处理器_MapHandler的使用
MapHandler(不需要参数) 作用是: 只将结果集的第一行都封装到Map
(键为字段名,值为该字段的值)
converts the first ResultSet row into a Map.*/
@Test
public void MapHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
Map<String, Object> map=(Map<String, Object>) qr.query(sql, new MapHandler());
for (Map.Entry<String, Object> entry : map.entrySet()) {
String columnName=entry.getKey();
Object columnValue=entry.getValue();
System.out.println(columnName+" : "+columnValue);
/*
password : 12345
email : lindaiyu@163.com
name : 林黛玉
id : 1
birthday : 2013-12-29*/
}
}
/*Dbutils的结果集处理器_MapListHandler的使用
MapListHandler(不需要参数) 作用是: 只将结果集的每一行都封装到Map
(键为字段名,值为该字段的值),再将这些Map逐个添加到集合list
converts a ResultSet into a List of Maps.*/
@Test
public void MapListHandler() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
List<Map<String, Object>> list=(List<Map<String, Object>>) qr.query(sql, new MapListHandler());
for (Map<String, Object> map : list) {
for (Map.Entry<String, Object> entry : map.entrySet()) {
String columnName=entry.getKey();
Object columnValue=entry.getValue();
System.out.println(columnName+" : "+columnValue);
/*
password : 12345
email : lindaiyu@163.com
name : 林黛玉
id : 1
birthday : 2013-12-29
password : 12354
email : xuebaochai@163.com
name : 薛宝钗
id : 2
birthday : 2013-12-29*/
}
}
}
/*Dbutils的结果集处理器_ScalarHandler的使用
ScalarHandler(需要参数:列名或列索引) 作用是: 将指定列的值封装成一个Object对象返回
适合统计count(*) count(user.id) 注意千万不能有空格!
converts one ResultSet column into an Object.
//ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个Object
*/
@Test
public void ScalarHandler_1() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select count(*) from users";
Object obj=qr.query(sql, new ScalarHandler(1));
//System.out.println(obj); 2
//注意obj直接转成Integer会出现类转换异常!?
Long totalRecord=(Long) obj;
Integer i= totalRecord.intValue();
System.out.println(i);//2
}
@Test
public void ScalarHandler_2() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select count(*) from users";
//ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个Object
Object obj=qr.query(sql, new ScalarHandler(1));
Integer i=((Long)obj).intValue();
System.out.println(i);
}
@Test
public void totalRecord_3() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select count(*) from users";
Object[] obj=(Object[]) qr.query(sql, new ArrayHandler());
//因为ArrayHandler()返回的对象数组中的数字全是Long类型!不能强转成Integer
//对象数组的第1个成员中保存的就是总记录数
Integer i=((Long)obj[0]).intValue();
System.out.println(i);
}
@Test
public void totalRecord_4() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select count(*) from users";
//ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个Object
Integer i=((Long) qr.query(sql, new ScalarHandler(1))).intValue();
System.out.println(i);
}
//方法4,使用Dbutils完成user表的查询,将第一条记录封装到User类里面
@Test
public void query_1() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users where id=?";
//由于只有一个参数,故可不能参数数组
User u1=(User) qr.query(sql, 1, new BeanHandler(User.class));
System.out.println(u1.getName());
System.out.println(u1.getBirthday());
}
//方法5,使用Dbutils完成user表的查询,将所有记录逐一封装到User类里面,
//再将所有User逐一添加到list,返回list
@Test
public void query_2() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from users";
List list=(List) qr.query(sql, new BeanListHandler(User.class));
System.out.println(list.size());
//上面这儿停个断点,然后Debug As Junit Test
}
//方法6,使用Dbutils完成user表的批量插入(关键是二维数组使用)
@Test
public void batch() throws SQLException{
//要使用Dbutils,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
//(关键是二维数组使用)插入3条记录,每条记录需要5个参数
Object[][] params=new Object[3][5];
for (int i = 0; i < params.length; i++) {
params[i]=new Object[]{3+i,"路人"+(i+1),"123","123@qq.com",new Date()};
}
qr.batch(sql, params);
}
}
JdbcUtils位于utils包
package cn.itcast.utils;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
/**演示开源数据库连接池DBCP的使用
DBCP内部增强Connection的close方法使用的是装饰模式!
1,导入两个jar包到工程下的lib目录,变成奶瓶
commons-dbcp-1.2.2.jar
commons-pool.jar
2,设置src下的dbcpconfig.properties配置文件信息如库名!
3,新建一个工具类如:JdbcUtils_DBCP
4,定义成员记住DBCP创建出来的数据源(即连接池)
5,静态代码块中用BasicDataSourceFactory创建数据源(即连接池)
6,定义获取连接的方法
7,定义释放连接的方法
*/
public class JdbcUtils {
private static DataSource ds;
static{
try {
String pro_name="dbcpconfig.properties";
InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream(pro_name);
Properties pro = new Properties();
pro.load(in);
//DBCP连接池--固定代码:由工厂创建数据源(即连接池)
BasicDataSourceFactory factory=new BasicDataSourceFactory();
//用类成员记住根据配置文件创建出来的连接池!
ds=factory.createDataSource(pro);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource(){
//Dbutils工具构造函数需要一个连接池
return ds;
}
}
Department位于domain包
package cn.itcast.domain;
import java.util.HashSet;
import java.util.Set;
public class Department {
private String id;
private String name;
//能不设计,就不设计!尽量不要记住多的一方
private Set<Employee> employees=new HashSet<Employee>();
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 Set<Employee> getEmployees() {
return employees;
}
public void setEmployees(Set<Employee> employees) {
this.employees = employees;
}
}
Employee位于domain包
package cn.itcast.domain;
public class Employee {
private String id;
private String name;
private double salary;
private String department_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 double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public String getDepartment_id() {
return department_id;
}
public void setDepartment_id(String department_id) {
this.department_id = department_id;
}
}
DepartmentDao位于dao包
package cn.itcast.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.itcast.domain.Department;
import cn.itcast.domain.Employee;
import cn.itcast.utils.JdbcUtils;
//1对多的关系演示CRUD
public class DepartmentDao {
/*一对多的关系演示,表的设计,多的一方设置外键列!
create table department
(
id varchar(40) primary key,
name varchar(40)
);
create table employee
(
id varchar(40) primary key,
name varchar(40),
salary double,
department_id varchar(40),
constraint department_id_FK foreign key(department_id) references department(id)
);
//删除drop 外键列foreign key 约束名是department_id_FK
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete set null;
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete cascade;
*/
//这时候就是将1对多的复杂对象添加到数据库中!(涉及到多表操作!)
public void add(Department d) throws SQLException{
//为了简化开发,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
//1.把department对象的数据插入到department表
String sql="insert into department(id,name) values(?,?)";
Object[] params={d.getId(),d.getName()};
qr.update(sql, params);
//2.把department对象中维护的所有员工插入到员工表
//3.更新员工表的外键列,说明员工的部门
Set<Employee> set=d.getEmployees();
for (Employee e : set) {
sql="insert into employee(id,name,salary,department_id) values(?,?,?,?)";
params=new Object[]{e.getId(),e.getName(),e.getSalary(),d.getId()};
qr.update(sql, params);
}
}
//实际开发中,涉及到查找时,看需求,尽量不要查找出将多的一方!内存溢出!
//实在要查(如订单:订单项),就用分页查询,limit X,Y
//避免使用1对多,而要用多对1
public Department find(String id) throws SQLException {
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
//1.找部门表,查出部门的基本信息
String sql="select * from department where id=?";
Department d=(Department) qr.query(sql, id, new BeanHandler(Department.class));
//2.找员工表,找出部门下面所有员工(实际开发中慎用,内存溢出!)
sql="select * from employee where department_id=?";
List<Employee> list=(List<Employee>) qr.query(sql, id, new BeanListHandler(Employee.class));
//List.add(list)是将list集合作为一个成员加入到List
//List.addAll(list)是将list中的每一个成员逐一添加到List,所以这儿要用addAll
d.getEmployees().addAll(list);
return d;
}
public void delete_tranditional(String id) throws SQLException {
// 传统方式删除1对多的Department对象
//传统方法:先解除关系,即在多的一方将外键列置空,然后删除部门表
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
String sql="update employee set department_id=null where department_id=?";
qr.update(sql, id);
sql="delete from department where id=?";
qr.update(sql, id);
}
public void delete_cascade(String id) throws SQLException{
//简单方法:创建员工表的时候设置级联(参数MYSQL文档)!
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
//先删除掉employee表中原来的外键约束
String sql="alter table employee drop foreign key department_id_FK";
qr.update(sql);
//然后添加一个带级联的外键约束
sql="alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete set null";
qr.update(sql);
//最后执行删除动作
sql= "delete from department where id=?";
qr.update(sql, id);
/*
//设置级联置空(on delete set null)
//删除drop 外键列foreign key 约束名是department_id_FK
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete set null;
//设置级联删除(on delete cascade),部门解散的,员工全开除!
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete cascade;
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE(级联删除) | SET NULL(级联置空) | NO ACTION
*/
}
}
Dservice位于service包
package cn.itcast.service;
import java.sql.SQLException;
import org.junit.Test;
import cn.itcast.dao.DepartmentDao;
import cn.itcast.domain.Department;
import cn.itcast.domain.Employee;
public class Dservice {
//薄薄的业务层,调用DAO添加一个部门对象到数据库
@Test
public void addDepartment() throws SQLException{
Department d=new Department();
d.setId("1");
d.setName("工程部");
Employee e1=new Employee();
e1.setId("1");
e1.setName("唐部长");
e1.setSalary(1000);
e1.setDepartment_id(d.getId());
Employee e2=new Employee();
e2.setId("2");
e2.setName("桥梁技术员甲");
e2.setSalary(100);
e2.setDepartment_id(d.getId());
Employee e3=new Employee();
e3.setId("3");
e3.setName("道路技术员乙");
e3.setSalary(100);
e3.setDepartment_id(d.getId());
d.getEmployees().add(e1);
d.getEmployees().add(e2);
d.getEmployees().add(e3);
//现在要将1对多的关系中的复杂对象Department存入数据库!
DepartmentDao dao=new DepartmentDao();
dao.add(d);
}
//薄薄的业务层,调用DAO根据id,查找出一个部门对象
@Test
public void find() throws SQLException{
//现在要将1对多的关系中的复杂对象Department查找出来!
DepartmentDao dao=new DepartmentDao();
Department d=dao.find("1");
System.out.println(d);
}
@Test
public void delete1() throws SQLException{
//现在要将1对多的关系中的复杂对象Department删除!
DepartmentDao dao=new DepartmentDao();
dao.delete_tranditional("1");
}
@Test
public void delete2() throws SQLException{
//现在要将1对多的关系中的复杂对象Department级联删除!
DepartmentDao dao=new DepartmentDao();
dao.delete_cascade("1");
}
}
Teacher位于domain包
package cn.itcast.domain;
import java.util.HashSet;
import java.util.Set;
public class Teacher {
private String id;
private String name;
private double salary;
private Set<Student> students=new HashSet<Student>();
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 double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Set<Student> getStudents() {
return students;
}
public void setStudents(Set<Student> students) {
this.students = students;
}
}
Student位于domain包
package cn.itcast.domain;
public class Student {
private String id;
private String name;
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;
}
}
TeacherDao位于dao包
package cn.itcast.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.itcast.domain.Student;
import cn.itcast.domain.Teacher;
import cn.itcast.utils.JdbcUtils;
//多对多的关系演示CRUD
/*多对多的表设计,中间表,联合主键+两个外键列
mysql -uroot -proot
set character_set_client=gb2312;
set character_set_results=gb2312;
create database day17 character set utf8 collate utf8_general_ci;
use day17;
create table teacher
(
id varchar(40) primary key,
name varchar(40),
salary double
) ;
create table student
(
id varchar(40) primary key,
name varchar(40)
);
//多对多的表设计,中间表,联合主键+两个外键列
create table teacher_student
(
teacher_id varchar(40),
student_id varchar(40),
primary key(teacher_id,student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);
先删除外键约束
alter table teacher_student drop foreign key teacher_id_FK;
再添加一个外键约束(级联删除,即teacher表删除的时候,中间表的记录会被删除)
alter table teacher_student add constraint teacher_id_FK foreign key(teacher_id) references teacher(id) on delete cascade;
先删除外键约束
alter table teacher_student drop foreign key student_id_FK;
再添加一个外键约束(级联删除,即student表删除的时候,中间表的记录会被删除)
alter table teacher_student add constraint student_id_FK foreign key(student_id) references student(id) on delete cascade;
*/
public class TeacherDao {
//多对多关系的方法1:添加一个Teacher对象到数据库
//应该放到一个事务里面执行将多对多的关系中的Teacher存入数据库
public void add(Teacher t) throws SQLException {
//为了简化开发,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
//1.取出Teacher基本信息,存Teacher表
String sql="insert into teacher(id,name,salary) values(?,?,?)";
Object[] params={t.getId(),t.getName(),t.getSalary()};
qr.update(sql, params);
//2.取出Teacher所有学生的数据,存学生表
Set<Student> set=t.getStudents();
for (Student s : set) {
sql="insert into student(id,name) values(?,?)";
//数组的静态初始化只能执行一次!
params=new Object[]{s.getId(),s.getName()};
qr.update(sql, params);
//3.更新中间表,说明老师和学生的关系
sql="insert into teacher_student(teacher_id,student_id) values(?,?)";
params=new Object[]{t.getId(),s.getId()};
qr.update(sql, params);
}
}
//多对多关系的方法2:查找并返回一个Teacher对象(涉及多表查询)
public Teacher find(String id) throws SQLException{
//为了简化开发,不管三七二十一,先new个QueryRunner
QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());
//1.找Teacher表,找出Teacher的基本信息
String sql="select * from teacher where id=?";
Teacher t=(Teacher) qr.query(sql, id, new BeanHandler(Teacher.class));
//多表查询(重点)
//2.找出老师的所有学生(实际开发中尽量不用,内存溢出)
//sql="select * from teacher_student ts,student s where ts.teacher_id=? and ts.student_id=s.id";
//s.*代表只取出所有学生的信息
sql="select s.* from teacher_student ts,student s where ts.teacher_id=? and ts.student_id=s.id";
List<Student> list=(List<Student>) qr.query(sql, id, new BeanListHandler(Student.class));
t.getStudents().addAll(list);
return t;
}
//多对多关系的方法3:从数据库删除出一个对象(创建表的时候可以使用级联)
public void delete(String id) throws SQLException{
//为了简化开发,不管三七二十一,先new个QueryRunner
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "delete from teacher where id=?";
qr.update(sql, id);
}
}
Tservice位于service包
package cn.itcast.service;
import java.sql.SQLException;
import org.junit.Test;
import cn.itcast.dao.TeacherDao;
import cn.itcast.domain.Student;
import cn.itcast.domain.Teacher;
public class Tservice {
public static void main(String[] args) throws SQLException {
TeacherDao dao=new TeacherDao();
Teacher t=dao.find("1");
System.out.println(t);
}
@Test
public void addTeacher() throws SQLException{
Teacher t=new Teacher();
t.setId("1");
t.setName("贾代儒");
t.setSalary(1000);
Student st1=new Student();
st1.setId("1");
st1.setName("宝玉");
Student st2=new Student();
st2.setId("2");
st2.setName("秦钟");
t.getStudents().add(st1);
t.getStudents().add(st2);
//现在要将多对多的关系中的复杂对象Teacher存入数据库!
TeacherDao dao = new TeacherDao();
dao.add(t);
}
@Test
public void findTeacher() throws SQLException{
TeacherDao dao=new TeacherDao();
Teacher t=dao.find("1");
System.out.println(t);
}
}
dbcpconfig.properties位于src目录
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day17
#url=jdbc:mysql://localhost:3306/day16
#url=jdbc:mysql://localhost:3306/day14_customer
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 即等1分钟后仍没连接,这时才告诉人家,呆会再来,暂无连接! -->
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, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED
用到的第3方jar包
mysql-connector-java-5.0.8-bin.jar
commons-dbcp-1.2.2.jar
commons-pool.jar
commons-dbutils-1.2.jar