以空间换时间,允许创建非标准的表
一对多:
package cn.itcast.web.jdbc.dao;import java.util.ArrayList;import java.util.List;import cn.itcast.web.jdbc.domain.Department;import cn.itcast.web.jdbc.domain.Employee;public class One2ManyTest { public static void main(String[] args) throws Exception { Department d = new Department(); d.setName("软件部"); Employee e1 = new Employee(); e1.setName("小王"); Employee e2 = new Employee(); e2.setName("小何"); List多对多:employeeList = new ArrayList (); employeeList.add(e1); employeeList.add(e2); d.setEmployeeList(employeeList); e1.setDepartment(d); e2.setDepartment(d); //保存部门记录时,同时保存对应的员工记录 Dao dao = new Dao(); dao.save(d); }}
package cn.itcast.web.jdbc.dao;import java.util.ArrayList;import java.util.List;import cn.itcast.web.jdbc.domain.Student;import cn.itcast.web.jdbc.domain.Teacher;public class Many2ManyTest { public static void main(String[] args) throws Exception { Teacher t1 = new Teacher(); t1.setName("赵"); Teacher t2 = new Teacher(); t2.setName("蔡"); Student s1 = new Student(); s1.setName("小王"); Student s2 = new Student(); s2.setName("小何"); ListDAO:teacherList = new ArrayList (); teacherList.add(t1); teacherList.add(t2); List studentList = new ArrayList (); studentList.add(s1); studentList.add(s2); t1.setStudentList(studentList); t2.setStudentList(studentList); s1.setTeacherList(teacherList); s2.setTeacherList(teacherList); Dao dao = new Dao(); dao.save(t1); }}
package cn.itcast.web.jdbc.dao;import java.sql.SQLException;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.ArrayHandler;import cn.itcast.web.jdbc.domain.Department;import cn.itcast.web.jdbc.domain.Employee;import cn.itcast.web.jdbc.domain.Student;import cn.itcast.web.jdbc.domain.Teacher;import cn.itcast.web.jdbc.util.JdbcUtil;public class Dao { public void save(Department d) throws SQLException { if(d==null){ throw new SQLException(); } QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); //NO1:向department表中插入一条记录 String sql = "insert into department(name) values(?)"; runner.update(sql,d.getName()); //NO2:取得插入department表中的主键 sql = "select id from department where name = ?"; Object[] array = (Object[]) runner.query(sql,d.getName(),new ArrayHandler()); int did = (Integer) array[0]; //NO3:向employee表中插入N条记录 sql = "insert into employee(name,did) values(?,?)"; for(Employee e : d.getEmployeeList()){ runner.update(sql,new Object[]{e.getName(),did}); } } public void save(Teacher t) throws SQLException { if(t==null){ throw new SQLException(); } QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource()); //NO1:向teacher表中插入一条记录 String sql = "insert into teacher(name) values(?)"; runner.update(sql,t.getName()); //NO2:取得teacher表中插入记录的主键 sql = "select id from teacher where name = ?"; Object[] array = (Object[]) runner.query(sql,t.getName(),new ArrayHandler()); int tid = (Integer) array[0]; //NO3:向student表中插入N条记录 sql = "insert into student(name) values(?)"; for(Student s : t.getStudentList()){ runner.update(sql,s.getName()); //NO4:取得student表中插入记录的主键 String temp1 = "select id from student where name = ?"; array = (Object[]) runner.query(temp1,s.getName(),new ArrayHandler()); int sid = (Integer)array[0]; //NO5:向middle表中插入N条记录 String temp2 = "insert into middle(tid,sid) values(?,?)"; runner.update(temp2,new Object[]{tid,sid}); } }}