黑马程序员
我们知道,在设计一个Java bean的时候,要把这些BEAN 的数据存放在数据库中的表结构,然而这些数据库中的表直接又有些特殊的关系,例如员工与部门直接有一对多的关系,学生与老师直接又多对多的关系,那么这些表的关系如何表示呢?
首先在建立数据库的时候就应该建立这样的对应关系。
一对多 ,只要建立两个表就能建立这样的关系,因为你可以把多方的那个表设置一个Foreign Key 属性 ,下面是一个部门和员工的表结构关系
在MySQL 数据库上应该这样建立表结构:
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中应该如何做呢
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层 如何实现增加 查询数据呢?增加一个部门和查询一个部门的时候要不要显示员工呢?
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); } }
多对多的关系
下面以老师和学生的关系来说明这个结构
数据库中:
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呢 ?
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; } }
工具表工具
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
#连接设置
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