JDBC上关于数据库中多表操作一对多关系和多对多关系的实现方法--转

 

原文地址----

https://www.cnblogs.com/pangguoming/p/7028322.html

黑马程序员

我们知道，在设计一个Java bean的时候，要把这些BEAN 的数据存放在数据库中的表结构，然而这些数据库中的表直接又有些特殊的关系，例如员工与部门直接有一对多的关系，学生与老师直接又多对多的关系，那么这些表的关系如何表示呢？
首先在建立数据库的时候就应该建立这样的对应关系。
一对多 ，只要建立两个表就能建立这样的关系，因为你可以把多方的那个表设置一个Foreign Key 属性 ，下面是一个部门和员工的表结构关系
在MySQL 数据库上应该这样建立表结构：

1 2 3 4 5 6 7 8 9 10 11 12

create table department( id int primary key, name varchar(100) );   create table employee( id int primary key, name varchar(100), salary float(8,2), dept_id int, constraint dept_id_fk foreign key (dept_id) references department(id)//这个其实是约束条件，不是表格的属性值。 );

　　在java 程序的javabean中应该如何做呢 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

public class Department {     private Integer id;     private String name;     private Set<Employee> emps = new HashSet<Employee>(); //????????????????????????????Set????       public Integer getId() {         return id;     }       public void setId(Integer id) {         this.id = id;     }       public String getName() {         return name;     }       public void setName(String name) {         this.name = name;     }       public Set<Employee> getEmps() {         return emps;     }       public void setEmps(Set<Employee> emps) {         this.emps = emps;     }       @Override     public String toString() {         return "Department [emps=" + emps + ", id=" + id + ", name=" + name +         "]";     } }     public class Employee {     private Integer id;     private String name;     private Float salary;       // private Department dept = new Department();     public Integer getId() {         return id;     }       public void setId(Integer 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;     }       @Override     public String toString() {         return "Employee [id=" + id + ", name=" + name + ", salary=" + salary +         "]";     } }

　　在DAO层 如何实现增加 查询数据呢？增加一个部门和查询一个部门的时候要不要显示员工呢?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

public class DeparmentDao {     private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());       public void addDepartment(Department dept) {         try {             //??????????             String sql = "insert into department values(?,?)";             Object[] params = { dept.getId(), dept.getName() };             qr.update(sql, params);               //???????????????????             Set<Employee> emps = dept.getEmps();               if ((emps != null) && (emps.size() > 0)) {                 for (Employee e : emps) {                     sql = "insert into employee values(?,?,?,?)";                     params = new Object[] {                             e.getId(), e.getName(), e.getSalary(), dept.getId()                         };                     qr.update(sql, params);                 }             }         } catch (Exception e) {             throw new RuntimeException(e);         }     }       //??????????????????     public List<Department> findDepts(boolean lazy) {         try {             //???????             String sql = "select * from department";             List<Department> depts = qr.query(sql,                     new BeanListHandler<Department>(Department.class));               if ((depts != null) && (depts.size() > 0)) {                 for (Department dept : depts) {                     if (lazy) {                         //??                         sql = "select id from employee where dept_id=?";                     } else {                         //??                         sql = "select * from employee where dept_id=?";                     }                       List<Employee> emps = qr.query(sql,                             new BeanListHandler<Employee>(Employee.class),                             dept.getId());                       for (Employee e : emps) {                         dept.getEmps().add(e);                     }                 }             }               return depts;         } catch (Exception e) {             throw new RuntimeException(e);         }     }       //??????????????????.????????     public List<Department> findDepts() {         return findDepts(true);     } }

　　

 

多对多的关系

下面以老师和学生的关系来说明这个结构
数据库中：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

create table teacher( id int primary key, name varchar(100), salary float(8,2) );   create table student( id int primary key, name varchar(100), grade varchar(100) );   create table teacher_student( t_id int, s_id int, primary key(t_id,s_id), constraint t_id_fk foreign key(t_id) references teacher(id), constraint s_id_fk foreign key(s_id) references student(id) );

　　如何写javabean 和 dao呢 ？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

public class Teacher {     private Integer id;     private String name;     private Float salary;     private Set<Student> stus = new HashSet<Student>();       public Integer getId() {         return id;     }       public void setId(Integer 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 Set<Student> getStus() {         return stus;     }       public void setStus(Set<Student> stus) {         this.stus = stus;     } }     public class Student {     private Integer id;     private String name;     private String grade;       public Integer getId() {         return id;     }       public void setId(Integer id) {         this.id = id;     }       public String getName() {         return name;     }       public void setName(String name) {         this.name = name;     }       public String getGrade() {         return grade;     }       public void setGrade(String grade) {         this.grade = grade;     }       @Override     public String toString() {         return "Student [grade=" + grade + ", id=" + id + ", name=" + name +         "]";     } }     public class TeacherDao {     private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());       public void addTeacher(Teacher t) throws SQLException {         //????????         String sql = "insert into teacher values(?,?,?)";         Object[] params = { t.getId(), t.getName(), t.getSalary() };         qr.update(sql, params);           //????????         //?3??         Set<Student> stus = t.getStus();           if ((stus != null) && (stus.size() > 0)) {             for (Student s : stus) {                 sql = "insert into student values(?,?,?)";                 params = new Object[] { s.getId(), s.getName(), s.getGrade() };                 qr.update(sql, params);                 sql = "insert into teacher_student values(?,?)";                 params = new Object[] { t.getId(), s.getId() };                 ;                 qr.update(sql, params);             }         }     }       public List<Teacher> findTeacher(boolean lazy) throws SQLException {         String sql = "select * from teacher";         List<Teacher> ts = qr.query(sql,                 new BeanListHandler<Teacher>(Teacher.class));           if ((ts != null) && (ts.size() > 0)) {             for (Teacher t : ts) {                 if (lazy) {                     sql = "select id from student where id in (select s_id from teacher_student where t_id=?)";                 } else {                     sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";                 }                   List<Student> stus = qr.query(sql,                         new BeanListHandler<Student>(Student.class), t.getId());                   for (Student s : stus) {                     t.getStus().add(s);                 }             }         }           return ts;     } }

　　工具表工具

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

public class JdbcUtil {     private static DataSource ds;     private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();       static {         try {             InputStream in = JdbcUtil.class.getClassLoader()                                            .getResourceAsStream("dbcpconfig.properties");             Properties props = new Properties();             props.load(in);               BasicDataSourceFactory factory = new BasicDataSourceFactory();             ds = factory.createDataSource(props);         } catch (Exception e) {             throw new ExceptionInInitializerError(e);         }     }       public static DataSource getDataSource() {         return ds;     }       public static Connection getConnection() throws SQLException {         Connection conn = tl.get();           if (conn == null) {             conn = ds.getConnection();             tl.set(conn);         }           return conn;     }       public static void startTransaction() throws SQLException {         Connection conn = tl.get();           if (conn == null) {             conn = ds.getConnection();             tl.set(conn);         }           conn.setAutoCommit(false);     }       public static void rollback() throws SQLException {         Connection conn = tl.get();           if (conn == null) {             conn = ds.getConnection();             tl.set(conn);         }           conn.rollback();     }       public static void commit() throws SQLException {         Connection conn = tl.get();           if (conn == null) {             conn = ds.getConnection();             tl.set(conn);         }           conn.commit();         tl.remove();     }       public static void release(ResultSet rs, Statement stmt, Connection conn) {         if (rs != null) {             try {                 rs.close();             } catch (Exception e) {                 e.printStackTrace();             }               rs = null;         }           if (stmt != null) {             try {                 stmt.close();             } catch (Exception e) {                 e.printStackTrace();             }               stmt = null;         }           if (conn != null) {             try {                 conn.close();             } catch (Exception e) {                 e.printStackTrace();             }               conn = null;         }     } }

　　

dbcpconfig.properties的文件 中内容
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day15  #这个是你的数据库地址
username=root #这个是你的用户名
password=sorry # 这个是你 密码


#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=20

#<!-- 最大空闲连接 -->
maxIdle=6

#<!-- 最小空闲连接 -->
minIdle=3

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
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=REPEATABLE_READ