到这里,我意识到这个系统MVC中的C放置在各个V中了,所以C就不介绍了,哈哈哈
接着总结DAO层,这个系统我采用的应该算是标准的DAO设计模式
还没有完全写完的dao包
![](https://i-blog.csdnimg.cn/blog_migrate/017fb95ba00433b9af937a3856aeb89a.png)
DAO的结构图
![](https://i-blog.csdnimg.cn/blog_migrate/9e0442a71ec04b385b41de320dc36d06.png)
DAO数据访问层的开发:
1.设计基类DAOBase类
提供标准的获得和关闭数据库连接的方法
package com.yinger.patientims.dao;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 数据库操作的基础类,它是其他的DAO类的父类
* 定义了建立数据库连接和关闭数据库连接的方法
*/
public class DAOBase {
private Connection connection;
// 得到数据库连接
public Connection getConnection() {
try {
Class.forName( "com.mysql.jdbc.Driver");
connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/patientims", "root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
// 关闭数据库连接
public void closeConnnection() {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection = null;
}
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 数据库操作的基础类,它是其他的DAO类的父类
* 定义了建立数据库连接和关闭数据库连接的方法
*/
public class DAOBase {
private Connection connection;
// 得到数据库连接
public Connection getConnection() {
try {
Class.forName( "com.mysql.jdbc.Driver");
connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/patientims", "root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
// 关闭数据库连接
public void closeConnnection() {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection = null;
}
}
}
}
2.编写各个Model层对象对应的DAO类
例如 PatientDAO
注意一个方法 setOnePatient 方法,这个方法很有用的,其作用是将数据库中取出的一条记录放置到一个Patient对象上,所以该方法
最好是能够独立出来,以便重复使用!
package com.yinger.patientims.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yinger.patientims.model.Patient;
import com.yinger.patientims.util.DBUtil;
public class PatientDAO extends DAOBase {
// 得到所有住院的病人的信息
// SELECT
// p.id,p.name,p.age,p.sex,p.address,p.logtime,p.phone,d.name,b.sickbedno,r.sickroomno
// FROM t_patient p,t_department d,t_sickbed b,t_sickroom r
// WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id
public List <Patient > getPatientInfoList() {
Patient patient;
List <Patient > list = new ArrayList <Patient >();
// 注意:这里有两个name,要使用到别名
String sql = "SELECT p.id,p.name as pname,p.age,p.sex,p.address,p.logtime,p.phone,d.name as dname,b.sickbedno,r.sickroomno "
+ "FROM t_patient p,t_department d,t_sickbed b,t_sickroom r "
+ "WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id";
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yinger.patientims.model.Patient;
import com.yinger.patientims.util.DBUtil;
public class PatientDAO extends DAOBase {
// 得到所有住院的病人的信息
// SELECT
// p.id,p.name,p.age,p.sex,p.address,p.logtime,p.phone,d.name,b.sickbedno,r.sickroomno
// FROM t_patient p,t_department d,t_sickbed b,t_sickroom r
// WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id
public List <Patient > getPatientInfoList() {
Patient patient;
List <Patient > list = new ArrayList <Patient >();
// 注意:这里有两个name,要使用到别名
String sql = "SELECT p.id,p.name as pname,p.age,p.sex,p.address,p.logtime,p.phone,d.name as dname,b.sickbedno,r.sickroomno "
+ "FROM t_patient p,t_department d,t_sickbed b,t_sickroom r "
+ "WHERE p.sickbed_id=b.id and b.sickroom_id=r.id and r.department_id=d.id";
String sql="SELECT * FROM t_table" //查询整个表
Connection connection = null;
// 在finally中使用它是它必须要初始化
// 还有,它一定要放在外面,这样在try外面(finally)才可以被访问到
// 还有,一定要有finally!关闭数据库连接是很重要的!
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
ResultSet resultSet = pStatement.executeQuery();
while (resultSet.next()) {
patient = setOnePatient(resultSet);
list.add(patient);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
// 将数据库中的一条patient记录关联到一个Patient对象中
// 并且它不处理异常,抛出由上层处理
private Patient setOnePatient(ResultSet resultSet) throws Exception {
Patient patient = new Patient();
if (resultSet.getLong( "id") != 0) {
patient.setId(resultSet.getLong( "id"));
}
if (resultSet.getString( "pname") != null) {
patient.setName(resultSet.getString( "pname"));
}
if (resultSet.getString( "sex") != null) {
patient.setSex(resultSet.getString( "sex"));
}
if (resultSet.getString( "phone") != null) {
patient.setPhone(resultSet.getString( "phone"));
}
if (resultSet.getString( "address") != null) {
patient.setAddress(resultSet.getString( "address"));
}
if (resultSet.getInt( "age") != 0) {
patient.setAge(resultSet.getInt( "age"));
}
if (resultSet.getDate( "logtime") != null) {
patient.setLogtime(resultSet.getDate( "logtime"));
}
if (resultSet.getString( "dname") != null) {
// 注意这一步!这里容易发生空指针异常!在一个Patient对象中Department等属性并没有被初始化!
// 所以要在类中new出各个对象,对于SickRoom等等同理
patient.getDepartment().setName(resultSet.getString( "dname"));
}
if (resultSet.getInt( "sickbedno") != 0) {
patient.getSickbed().setSickBedNo(resultSet.getInt( "sickbedno"));
}
if (resultSet.getInt( "sickroomno") != 0) {
patient.getSickroom().setSickRoomNo(resultSet.getInt( "sickroomno"));
}
return patient;
}
// 删除病人信息
public boolean deletePatient(Patient patient) {
Long id = patient.getId();
Connection connection = null;
String sql = "delete from t_patient where id= "+ id + " ";
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
int res = pStatement.executeUpdate();
if (res > 0) {
return true; // 注意:这里虽然是return了,但是这个方法真正返回之前还是要执行finally
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
// 添加病人住院信息
public boolean insertPatient(Patient patient) {
Connection connection = null;
// INSERT INTO
// t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES
// ('patient"+i+"','女',37,'1533535354','2011-10-10','TianJin',"+id+")
String sql = "INSERT INTO t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES ('" + patient.getName() + "','" + patient.getSex() + "',"
+ patient.getAge() + ",'" + patient.getPhone() + "','" + DBUtil.simpleDateFormat.format(patient.getLogtime()) + "','" + patient.getAddress() + "',"
+ patient.getSickbed().getId() + ")"; // 字符串用'"+ patient.getName() + "',整形用"+ patient.getAge() + "
//patient.getLogtime().toLocaleString() 已经不推荐使用了
//注意:时间是有点特别地是使用 DateFormater进行format一下!
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
int res = pStatement.executeUpdate();
if (res > 0) {
return true; // 注意:这里虽然是return了,但是这个方法真正返回之前还是要执行finally
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
// 修改病人住院信息
public boolean updatePatient(Patient patient) {
Connection connection = null;
//UPDATE t_patient as p set p.name='yyh' , p.address='ghalsdhgl' , p.age=24 , p.phone='523659365', p.sex='female'
// where id=6
StringBuffer sql = new StringBuffer( "UPDATE t_patient as p ");
sql.append( " set p.name='" +patient.getName() + "' ");
sql.append( ", p.address='" +patient.getAddress() + "' ");
sql.append( " , p.age=" +patient.getAge() + " ");
sql.append( " , p.phone='" +patient.getPhone() + "' ");
sql.append( ", p.sex='" +patient.getSex() + "' ");
sql.append( " ,p.sickbed_id= " +patient.getSickbed().getId() + " ");
sql.append( " where id=" +patient.getId());
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql.toString());
int res = pStatement.executeUpdate();
if (res > 0) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
}
Connection connection = null;
// 在finally中使用它是它必须要初始化
// 还有,它一定要放在外面,这样在try外面(finally)才可以被访问到
// 还有,一定要有finally!关闭数据库连接是很重要的!
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
ResultSet resultSet = pStatement.executeQuery();
while (resultSet.next()) {
patient = setOnePatient(resultSet);
list.add(patient);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
// 将数据库中的一条patient记录关联到一个Patient对象中
// 并且它不处理异常,抛出由上层处理
private Patient setOnePatient(ResultSet resultSet) throws Exception {
Patient patient = new Patient();
if (resultSet.getLong( "id") != 0) {
patient.setId(resultSet.getLong( "id"));
}
if (resultSet.getString( "pname") != null) {
patient.setName(resultSet.getString( "pname"));
}
if (resultSet.getString( "sex") != null) {
patient.setSex(resultSet.getString( "sex"));
}
if (resultSet.getString( "phone") != null) {
patient.setPhone(resultSet.getString( "phone"));
}
if (resultSet.getString( "address") != null) {
patient.setAddress(resultSet.getString( "address"));
}
if (resultSet.getInt( "age") != 0) {
patient.setAge(resultSet.getInt( "age"));
}
if (resultSet.getDate( "logtime") != null) {
patient.setLogtime(resultSet.getDate( "logtime"));
}
if (resultSet.getString( "dname") != null) {
// 注意这一步!这里容易发生空指针异常!在一个Patient对象中Department等属性并没有被初始化!
// 所以要在类中new出各个对象,对于SickRoom等等同理
patient.getDepartment().setName(resultSet.getString( "dname"));
}
if (resultSet.getInt( "sickbedno") != 0) {
patient.getSickbed().setSickBedNo(resultSet.getInt( "sickbedno"));
}
if (resultSet.getInt( "sickroomno") != 0) {
patient.getSickroom().setSickRoomNo(resultSet.getInt( "sickroomno"));
}
return patient;
}
// 删除病人信息
public boolean deletePatient(Patient patient) {
Long id = patient.getId();
Connection connection = null;
String sql = "delete from t_patient where id= "+ id + " ";
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
int res = pStatement.executeUpdate();
if (res > 0) {
return true; // 注意:这里虽然是return了,但是这个方法真正返回之前还是要执行finally
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
// 添加病人住院信息
public boolean insertPatient(Patient patient) {
Connection connection = null;
// INSERT INTO
// t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES
// ('patient"+i+"','女',37,'1533535354','2011-10-10','TianJin',"+id+")
String sql = "INSERT INTO t_patient(name,sex,age,phone,logtime,address,sickbed_id) VALUES ('" + patient.getName() + "','" + patient.getSex() + "',"
+ patient.getAge() + ",'" + patient.getPhone() + "','" + DBUtil.simpleDateFormat.format(patient.getLogtime()) + "','" + patient.getAddress() + "',"
+ patient.getSickbed().getId() + ")"; // 字符串用'"+ patient.getName() + "',整形用"+ patient.getAge() + "
//patient.getLogtime().toLocaleString() 已经不推荐使用了
//注意:时间是有点特别地是使用 DateFormater进行format一下!
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
int res = pStatement.executeUpdate();
if (res > 0) {
return true; // 注意:这里虽然是return了,但是这个方法真正返回之前还是要执行finally
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
// 修改病人住院信息
public boolean updatePatient(Patient patient) {
Connection connection = null;
//UPDATE t_patient as p set p.name='yyh' , p.address='ghalsdhgl' , p.age=24 , p.phone='523659365', p.sex='female'
// where id=6
StringBuffer sql = new StringBuffer( "UPDATE t_patient as p ");
sql.append( " set p.name='" +patient.getName() + "' ");
sql.append( ", p.address='" +patient.getAddress() + "' ");
sql.append( " , p.age=" +patient.getAge() + " ");
sql.append( " , p.phone='" +patient.getPhone() + "' ");
sql.append( ", p.sex='" +patient.getSex() + "' ");
sql.append( " ,p.sickbed_id= " +patient.getSickbed().getId() + " ");
sql.append( " where id=" +patient.getId());
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql.toString());
int res = pStatement.executeUpdate();
if (res > 0) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
}
再比如:DepartmentDAO类
package com.yinger.patientims.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yinger.patientims.model.Department;
public class DepartmentDAO extends DAOBase{
// private List departmentList = new ArrayList();
// private Department department;
// 得到所有的科室
public List <Department > getDepartmentList(){
Department department;
List <Department > list = new ArrayList <Department >();
Connection connection = null;
String sql = "select id,name from t_department";
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
ResultSet resultSet = pStatement.executeQuery();
while (resultSet.next()) {
department = setOneDepartment(resultSet);
list.add(department);
}
pStatement.close();
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
connection.close(); // Quick Fix : Ctrl + 1
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
// 从数据库中取出一条记录并保存到一个Department中
private Department setOneDepartment(ResultSet resultSet) throws Exception {
Department department = new Department();
if(resultSet.getLong( "id") != 0){
department.setId(resultSet.getLong( "id"));
}
if(resultSet.getString( "name") !=null){
department.setName(resultSet.getString( "name"));
}
return department;
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yinger.patientims.model.Department;
public class DepartmentDAO extends DAOBase{
// private List departmentList = new ArrayList();
// private Department department;
// 得到所有的科室
public List <Department > getDepartmentList(){
Department department;
List <Department > list = new ArrayList <Department >();
Connection connection = null;
String sql = "select id,name from t_department";
try {
connection = getConnection();
PreparedStatement pStatement = connection.prepareStatement(sql);
ResultSet resultSet = pStatement.executeQuery();
while (resultSet.next()) {
department = setOneDepartment(resultSet);
list.add(department);
}
pStatement.close();
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
connection.close(); // Quick Fix : Ctrl + 1
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
// 从数据库中取出一条记录并保存到一个Department中
private Department setOneDepartment(ResultSet resultSet) throws Exception {
Department department = new Department();
if(resultSet.getLong( "id") != 0){
department.setId(resultSet.getLong( "id"));
}
if(resultSet.getString( "name") !=null){
department.setName(resultSet.getString( "name"));
}
return department;
}
}
3.总结:
(1)注意DAO类中每个操作的语句结构,一定要捕捉异常进行处理,一定要有finally,并在其中关闭连接
(2)注意数据库中取出来的数据的类型和Model的该属性的类型
(3)注意方法的返回值,是否要返回值?
(4)注意sql语句的构造,如果多次使用字符串的合并建议使用高效率的StringBuilder,还有构造时也要注意是否需要'',这个最好是
先测试,然后编写代码!