数据库_jdbc_dbutils的使用+存储复杂对象

概述

day17java工程搭建步骤:1,src下新建lib目录,导入第3方jar包commons-dbcp-1.2.2.jarcommons-dbutils-1.2.jarcommons-pool.jarmysql-connector-java-5.0.8-bin.jar2,src下导入dbcpconfig.properties,并改库名day173,表的设计(一对多)一对多的关系演示,表的设计,多的一方设置外键列!mysql -uroot -prootset 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 -prootset 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包,JdbcUtilsdao包,(重点)TeacherDao专门负责Teacher对象的CRUDservice包,Tservice,薄薄的,new个Teacher对象,赋值,调用TeacherDao进行CRUDDservice,薄薄的,new个Department对象,赋值,调用DepartmentDao进行CRUD

User位于domain包

package cn.itcast.domain;import java.util.Date;//改类对应Demo1_Dbutils,演示Dbutils的使用,完成CRUDpublic 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 -prootset 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表的插入@Testpublic void insert() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";//Object[] params={1,"林黛玉","12345","[e-mail]",new Date()};Object[] params={2,"薛宝钗","12354","[e-mail]",new Date()};qr.update(sql, params);}//方法2,使用Dbutils完成user表的更新@Testpublic void update() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());String sql="update users set password=? where id=?";Object[] params={"0123456",1};qr.update(sql, params);}//方法3,使用Dbutils完成user表的删除@Testpublic void delete() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());String sql="delete from users where id=?";//由于只有一个参数,故可不能参数数组qr.update(sql,1);}//方法4,使用Dbutils完成user表的查询,将第一条记录封装到User类里面@Testpublic void query_1() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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@Testpublic void query_2() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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表的批量插入(关键是二维数组使用)@Testpublic void batch() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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","[e-mail]",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 -prootset 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 SummaryAbstractListHandlerAbstract 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[].*/@Testpublic void ArrayHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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林黛玉[e-mail]2013-12-29*/}/*Dbutils的结果集处理器_ArrayListHandler的使用ArrayListHandler(不需要参数)作用是:  将结果集的每行记录的值保存到数组里再将这些数组,保存到一个集合里面!converts the ResultSet into a List of Object[]s*/@Testpublic void ArrayListHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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林黛玉[e-mail]2013-12-292薛宝钗[e-mail]2013-12-29*/}}}/*Dbutils的结果集处理器_ColumnListHandler的使用ColumnListHandler(需要参数:列名)作用是:  只将指定列名的数据存入list(如取出所有名字)converts one ResultSet column into a List of Objects.*/@Testpublic void ColumnListHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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//自己写一个ColumnListHandlerclass ColumnListHandler_my implements ResultSetHandler{private String columnName;public ColumnListHandler_my(String columnName) {super();this.columnName = columnName;}public Object handle(ResultSet rs) throws SQLException {// 覆盖接口的方法//取出构造时指定的列名下所有的值,加到list,并返回listList 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*/@Testpublic void KeyedHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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 : 12354email : [e-mail] : 薛宝钗id : 2birthday : 2013-12-29password : 12345email : [e-mail] : 林黛玉id : 1birthday : 2013-12-29*/}}}/*Dbutils的结果集处理器_MapHandler的使用MapHandler(不需要参数)作用是:  只将结果集的第一行都封装到Map(键为字段名,值为该字段的值)converts the first ResultSet row into a Map.*/@Testpublic void MapHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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 : 12345email : [e-mail] : 林黛玉id : 1birthday : 2013-12-29*/}}/*Dbutils的结果集处理器_MapListHandler的使用MapListHandler(不需要参数)作用是:  只将结果集的每一行都封装到Map(键为字段名,值为该字段的值),再将这些Map逐个添加到集合listconverts a ResultSet into a List of Maps.*/@Testpublic void MapListHandler() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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 : 12345email : [e-mail] : 林黛玉id : 1birthday : 2013-12-29password : 12354email : [e-mail] : 薛宝钗id : 2birthday : 2013-12-29*/}}}/*Dbutils的结果集处理器_ScalarHandler的使用ScalarHandler(需要参数:列名或列索引)作用是:  将指定列的值封装成一个Object对象返回适合统计count(*)  count(user.id) 注意千万不能有空格! converts one ResultSet column into an Object. //ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个Object */@Testpublic void ScalarHandler_1() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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}@Testpublic void ScalarHandler_2() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());String sql="select count(*) from users";//ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个ObjectObject obj=qr.query(sql, new ScalarHandler(1));Integer i=((Long)obj).intValue();System.out.println(i);}@Testpublic void totalRecord_3() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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);}@Testpublic void totalRecord_4() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner qr=new QueryRunner(JdbcUtils.getDataSource());String sql="select count(*) from users";//ScalarHandler(参数int第几列,String列名) 将指定的列值存入到一个ObjectInteger i=((Long) qr.query(sql, new ScalarHandler(1))).intValue();System.out.println(i);}//方法4,使用Dbutils完成user表的查询,将第一条记录封装到User类里面@Testpublic void query_1() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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@Testpublic void query_2() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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表的批量插入(关键是二维数组使用)@Testpublic void batch() throws SQLException{//要使用Dbutils,不管三七二十一,先new个QueryRunnerQueryRunner 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","[e-mail]",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.jarcommons-pool.jar2,设置src下的dbcpconfig.properties配置文件信息如库名!3,新建一个工具类如:JdbcUtils_DBCP4,定义成员记住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对多的关系演示CRUDpublic 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添加一个部门对象到数据库@Testpublic 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 -prootset 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);}@Testpublic 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.Driverurl=jdbc:mysql://localhost:3306/day17#url=jdbc:mysql://localhost:3306/day16#url=jdbc:mysql://localhost:3306/day14_customerusername=rootpassword=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, SERIALIZABLEdefaultTransactionIsolation=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