BCSP-玄子JAVA开发之JAVA数据库编程CH-09_数据访问和DAO模式

9.1 Properties 配置文件

9.1.1 为什么使用 Properties 类

使用JDBC技术访问数据库数据的关键代码

private String driver = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/hospital?serverTimezone=GMT-8";
private  String user = “root"; 	
private  String password= “123456"; 
// 修改后需重新编译
Connection conn = null;
public Connection getConnection() {if(conn==null) {try {Class.forName(driver);conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {//省略代码……}}	return conn;// 返回连接对象
}

让用户脱离程序本身修改相关的变量设置——使用配置文件

9.1.2 properties配置文件

Java的配置文件常为properties文件

• 后缀为.properties
• 以“键＝值”格式储存数据
• 使用“＃”添加注释

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EdI0WH83-1680668206329)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230404204942.png)]

通常，为数据库访问添加的配置文件是database.properties

9.1.3 读取配置文件信息

使用java.util包下的Properties类读取配置文件

使用Properties配置文件的方式改造医院管理系统

package XaunZiShare;import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;public class HospitalSystem {private static Logger logger = LogManager.getLogger(HospitalSystem.class.getName());public static void main(String[] args) {Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;String patientID = null;boolean isExist = false;//根据控制台提示输入用户身份证号和密码Scanner input = new Scanner(System.in);System.out.println("用户登录");System.out.print("请输入身份证号:");String identityNum = input.next();System.out.print("请输入密码:");String password = input.next();Properties params = new Properties();String configFile = "database.properties";//配置文件路径String url = null;String username = null;String pwd = null;//加载配置文件到输入流中try {InputStream is = HospitalSystem.class.getClassLoader().getResourceAsStream(configFile);params.load(is);//根据指定的获取对应的值String driver = params.getProperty("driver");url = params.getProperty("url");username = params.getProperty("username");pwd = params.getProperty("password");// 1、加载驱动Class.forName(driver);} catch (IOException e) {logger.error(e);} catch (ClassNotFoundException e) {logger.error(e);}try {// 2、建立连接conn = DriverManager.getConnection(url, username, pwd);//3、构造PreparedStatement对象pstmt = conn.prepareStatement("SELECT patientID, patientName FROM patient WHERE identityNum=? and password=?");pstmt.setString(1, identityNum);pstmt.setString(2, password);rs = pstmt.executeQuery();//4、验证用户名和密码if (rs.next()) {patientID = rs.getString("patientID");System.out.println("欢迎" + rs.getString("patientName") + "登录系统！");while (!isExist) {System.out.println("1.查询检查记录\\t 0.退出");System.out.print("请输入要执行的操作:");String action = input.next();if (action.equals("1")) {pstmt = conn.prepareStatement("SELECT depName, checkItemName, checkResult, checkItemCost, examDate FROM prescription p  INNER JOIN department d ON p.depID = d.depID INNER JOIN checkitem c ON p.checkItemID = c.checkItemID WHERE p.patientID=?;");pstmt.setString(1, patientID);rs = pstmt.executeQuery();System.out.println("检查科室\\t检查项目\\t检查结果\\t检查费用\\t检查时间");while (rs.next()) {System.out.println(rs.getString("depName") + "\\t" + rs.getString("checkItemName") + "\\t" + rs.getString("checkResult") + "\\t" + rs.getString("checkItemCost") + "\\t" + rs.getString("examDate") + "\\t");}} else if (action.equals("0")) {isExist = true;System.out.println("再见");} else {System.out.println("输入错误，请重新输入");}}} else {System.out.println("密码错误！");}} catch (SQLException e) {logger.error(e);} finally {// 5、关闭数据库连接try {if (null != rs) {rs.close();}if (null != pstmt) {pstmt.close();}if (null != conn) {conn.close();}} catch (SQLException e) {logger.error(e);}}}
}

9.2 DAO 模式

9.2.1 什么是 DAO

非常流行的数据访问模式——DAO模式

• Data Access Object（数据存取对象)
• 位于业务逻辑和持久化数据之间
• 实现对持久化数据的访问

DAO起着转换器的作用，把实体类转换为数据库中的记录

9.2.2 DAO 模式的组成

组成部分

• DAO接口
• DAO实现类
• 实体类
• 数据库连接和关闭工具类

优势

• 隔离了数据访问代码和业务逻辑代码
• 隔离了不同数据库实现

9.2.3 使用实体类传递数据

数据访问代码和业务逻辑代码之间通过实体类来传输数据

实体类特征

• 属性一般使用private修饰
• 提供public修饰的getter/setter方法
• 实体类提供无参构造方法，根据业务提供有参构造
• 实现java.io.Serializable接口，支持序列化机制

9.2.4 实体类

实体类（Entity）是Java应用程序中与数据库表对应的类

• 用于存储数据，并提供对这些数据的访问
• 通常，实现类是持久的，需要存储于文件或数据库中
• 访问操作数据库时，以实体类的方式组织数据库中的实体及关系
• 通常，在Java工程中创建一个名为entity的Package，用于集中保存实体类
• 一个数据库表对应一个实体类

9.2.5 定义实体类

package XaunZiShare;import java.io.Serializable;public class Patient implements Serializable {private static final long serialVersionUID = -8762235641468472877L;private String patientID;  //病人编号private String password; //登录密码private String birthDate; //出生日期private String gender; //性别private String patientName; //姓名private String phoneNum; //联系电话private String email; //邮箱private String identityNum; //身份证号private String address; //地址/*** 无参构造方法*/public Patient() {}/*** 有参构造方法，根据需要提供** @param identityNum 身份证号* @param name        姓名*/public Patient(String identityNum, String name) {this.identityNum = identityNum;this.patientName = name;}public static long getSerialVersionUID() {return serialVersionUID;}public String getPatientID() {return patientID;}public void setPatientID(String patientID) {this.patientID = patientID;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getBirthDate() {return birthDate;}public void setBirthDate(String birthDate) {this.birthDate = birthDate;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getPatientName() {return patientName;}public void setPatientName(String patientName) {this.patientName = patientName;}public String getPhoneNum() {return phoneNum;}public void setPhoneNum(String phoneNum) {this.phoneNum = phoneNum;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getIdentityNum() {return identityNum;}public void setIdentityNum(String identityNum) {this.identityNum = identityNum;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}
}

9.2.6 使用实体类传递数据

package XaunZiShare;import com.javamysql.entity.Patient;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;public class HospitalSystem {private static Logger logger = LogManager.getLogger(HospitalSystem.class.getName());public static void main(String[] args) {Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;Patient patient = new Patient();boolean isExist = false;//根据控制台提示输入用户身份证号和密码Scanner input = new Scanner(System.in);System.out.println("用户登录");System.out.print("请输入身份证号:");patient.setIdentityNum(input.next());System.out.print("请输入密码:");patient.setPassword(input.next());Properties params = new Properties();String configFile = "database.properties";//配置文件路径String url = null;String username = null;String pwd = null;//加载配置文件到输入流中try {InputStream is = HospitalSystem.class.getClassLoader().getResourceAsStream(configFile);params.load(is);//根据指定的获取对应的值String driver = params.getProperty("driver");url = params.getProperty("url");username = params.getProperty("username");pwd = params.getProperty("password");// 1、加载驱动Class.forName(driver);} catch (IOException e) {logger.error(e);} catch (ClassNotFoundException e) {logger.error(e);}try {// 2、建立连接conn = DriverManager.getConnection(url, username, pwd);//3、构造PreparedStatement对象pstmt = conn.prepareStatement("SELECT * FROM patient WHERE identityNum=? and password=?");pstmt.setString(1, patient.getIdentityNum());pstmt.setString(2, patient.getPassword());rs = pstmt.executeQuery();//4、验证用户名和密码if (rs.next()) {//从MySQL读取用户信息，并加载到patient对象中patient.setPatientID(rs.getString("patientID"));patient.setAddress(rs.getString("address"));patient.setBirthDate(rs.getString("birthDate"));patient.setEmail(rs.getString("email"));patient.setGender(rs.getString("gender"));patient.setPatientID(rs.getString("patientName"));patient.setPhoneNum(rs.getString("phoneNum"));System.out.println("欢迎" + patient.getPatientName() + "登录系统！");while (!isExist) {System.out.println("1.查询检查记录\\t2.查询病人信息\\t 0.退出");System.out.print("请输入要执行的操作:");String action = input.next();if (action.equals("1")) {pstmt = conn.prepareStatement("SELECT depName, checkItemName, checkResult, checkItemCost, examDate FROM prescription p  INNER JOIN department d ON p.depID = d.depID INNER JOIN checkitem c ON p.checkItemID = c.checkItemID WHERE p.patientID=?;");pstmt.setString(1, patient.getPatientID());rs = pstmt.executeQuery();System.out.println("检查科室\\t检查项目\\t检查结果\\t检查费用\\t检查时间");while (rs.next()) {System.out.println(rs.getString("depName") + "\\t" + rs.getString("checkItemName") + "\\t" + rs.getString("checkResult") + "\\t" + rs.getString("checkItemCost") + "\\t" + rs.getString("examDate") + "\\t");}} else if (action.equals("2")) {System.out.println(patient.getPatientID() + "\\t" + patient.getPatientName() + "\\t" + patient.getGender() + "\\t" + patient.getBirthDate() + "\\t" + patient.getIdentityNum() + "\\t" + patient.getPhoneNum() + "\\t" + patient.getEmail() + "\\t" + patient.getAddress());} else if (action.equals("0")) {isExist = true;System.out.println("再见");} else {System.out.println("输入错误，请重新输入");}}} else {System.out.println("密码错误！");}} catch (SQLException e) {logger.error(e);} finally {// 5、关闭数据库连接try {if (null != rs) {rs.close();}if (null != pstmt) {pstmt.close();}if (null != conn) {conn.close();}} catch (SQLException e) {logger.error(e);}}}
}

9.2.7 实体类的特征

实体类特征

• 属性一般使用private修饰
• 提供public修饰的getter/setter方法
• 实体类提供无参构造方法，根据业务提供有参构造
• 实现java.io.Serializable接口，支持序列化机制，可以将该对象转换成字节序列而保存在磁盘上或在网络上传输

如果实体类实现了java.io.Serializable接口，应该定义属性serialVersionUID，解决不同版本之间的序列化问题

为serialVersionUID赋值的方法

• 手动

• 使用IDEA生成

• private static final long serialVersionUID = -8762235641468472877L;
  

一旦为一个实体类的serialVersionUID赋值，就不要再修改；否则，在反序列化之前版本的数据时，会报java.io.InvalidClassException异常

9.3 实现 JDBC 封装

9.3.1 JDBC

将程序中数据在瞬时状态和持久状态间转换的机制为数据持久化

JDBC

• 读取
• 删除
• 查找
• 修改
• 保存

9.3.2 持久化的实现方式

数据库

普通文件

XML文件

9.3.3 为什么进行 JDBC 封装

Scanner input = new Scanner(System.in);
System.out.print("请输入登录名：");
String name=input.next();
System.out.print("请输入登录密码：");
String password=input.next();
// 业务相关代码
// ……省略加载驱动
try {conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hospital?serverTimezone=GMT-8","root", "123456");// … …省略代码 … …if(rs.next())System.out.println("登录成功，欢迎您！");elseSystem.out.println("登录失败，请重新输入！");	// ……省略代码
} catch (SQLException e) {			// ……省略代码
} finally {}
// 数据访问代码

业务代码和数据访问代码耦合

• 可读性差
• 不利于后期修改和维护
• 不利于代码复用

采用面向接口编程，可以降低代码间的耦合性

采用面向接口编程，可以降低代码间的耦合性

业务逻辑代码调用数据访问接口

9.3.4 使用 DAO 模式改造 Hospital

将HospitalSystem中对病人的所有数据库操作抽象成接口

对病人的数据库操作包括修改病人信息、通过身份证号和密码验证登录

设计接口时，尽量以对象为单位，给调用者提供面向对象的接口

• 使用实体类作为接口的参数和返回值，可以让接口更加清晰简洁
• 如果以Patient类的各个属性为形参进行传递，不仅会导致参数个数很多，还会增加接口和实现类中方法的数量等

接口的命名，应以简明为主

• “实体类名+Dao”格式如
• PatientDao
• 作为工程中相对独立的模块
• 所有DAO接口文件都放在dao包中

接口由不同数据库的实现类分别实现

PatientDao 接口

package XaunZiShare;import com.javamysql.entity.Patient;public interface PatientDao {/*** 更新病人信息** @param patient 病人*/int update(Patient patient);/*** 根据身份证号和登录密码返回病人信息** @param identityNum 身份证号* @param pwd         登录密码* @return 病人*/Patient getPatientByIdNumAndPwd(String identityNum, String pwd);
}

PatientDao实现类的方法：update()方法

package XaunZiShare;import com.javamysql.HospitalSystem;
import com.javamysql.dao.PatientDao;
import com.javamysql.entity.Patient;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class PatientDaoMySQLImpl implements PatientDao {private static Logger logger = LogManager.getLogger(HospitalSystem.class.getName());@Overridepublic int update(Patient patient) {Connection conn = null;PreparedStatement pstmt = null;int result = 0;Properties params = new Properties();String configFile = "database.properties";//配置文件路径String url = null;String username = null;String password = null;//加载配置文件到输入流中try {InputStream is = HospitalSystem.class.getClassLoader().getResourceAsStream(configFile);params.load(is);//根据指定的获取对应的值String driver = params.getProperty("driver");url = params.getProperty("url");username = params.getProperty("username");password = params.getProperty("password");// 1、加载驱动Class.forName(driver);} catch (IOException e) {logger.error(e);} catch (ClassNotFoundException e) {logger.error(e);}try {// 2、建立连接conn = DriverManager.getConnection(url, username, password);//3、构造PreparedStatement对象pstmt = conn.prepareStatement("UPDATE patient SET address=?, birthDate=?, email=?, gender=?, patientName=?, phoneNum=?, identityNum=?,password=? WHERE patientID=?");pstmt.setString(1, patient.getAddress());pstmt.setString(2, patient.getBirthDate());pstmt.setString(3, patient.getEmail());pstmt.setString(4, patient.getGender());pstmt.setString(5, patient.getPatientName());pstmt.setString(6, patient.getPhoneNum());pstmt.setString(7, patient.getIdentityNum());pstmt.setString(8, patient.getPassword());pstmt.setString(9, patient.getPatientID());result = pstmt.executeUpdate();} catch (SQLException e) {logger.error(e);} finally {// 5、关闭数据库连接try {if (null != pstmt) {pstmt.close();}if (null != conn) {conn.close();}} catch (SQLException e) {logger.error(e);}}return result;}@Overridepublic Patient getPatientByIdNumAndPwd(String identityNum, String pwd) {Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;Patient patient = null;Properties params = new Properties();String configFile = "database.properties";//配置文件路径String url = null;String username = null;String password = null;//加载配置文件到输入流中try {InputStream is = HospitalSystem.class.getClassLoader().getResourceAsStream(configFile);params.load(is);//根据指定的获取对应的值String driver = params.getProperty("driver");url = params.getProperty("url");username = params.getProperty("username");password = params.getProperty("password");// 1、加载驱动Class.forName(driver);} catch (IOException e) {logger.error(e);} catch (ClassNotFoundException e) {logger.error(e);}try {// 2、建立连接conn = DriverManager.getConnection(url, username, password);//3、构造PreparedStatement对象pstmt = conn.prepareStatement("SELECT * FROM patient WHERE identityNum=? and password=?");pstmt.setString(1, identityNum);pstmt.setString(2, pwd);rs = pstmt.executeQuery();//4、验证用户名和密码if (rs.next()) {//从MySQL读取用户信息，并加载到patient对象中patient = new Patient();patient.setPatientID(rs.getString("patientID"));patient.setAddress(rs.getString("address"));patient.setBirthDate(rs.getString("birthDate"));patient.setEmail(rs.getString("email"));patient.setGender(rs.getString("gender"));patient.setPatientName(rs.getString("patientName"));patient.setPhoneNum(rs.getString("phoneNum"));patient.setIdentityNum(rs.getString("identityNum"));patient.setPassword(rs.getString("password"));}} catch (SQLException e) {logger.error(e);} finally {// 5、关闭数据库连接try {if (null != rs) {rs.close();}if (null != pstmt) {pstmt.close();}if (null != conn) {conn.close();}} catch (SQLException e) {logger.error(e);}}return patient;}
}

通用的操作是否能够进一步简化？

9.4 BaseDao基类

9.4.1 将通用的操作（打开、关闭连接等）封装到基类

package XaunZiShare;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class BaseDao {private static String driver;// 数据库驱动字符串private static String url;// 连接URL字符串private static String user;// 数据库用户名private static String password;// 用户密码// 数据连接对象static {//静态代码块,在类加载的时候执行init();}Connection conn = null;/*** 初始化连接参数,从配置文件里获得*/public static void init() {Properties params = new Properties();String configFile = "database.properties";//配置文件路径//加载配置文件到输入流中InputStream is = BaseDao.class.getClassLoader().getResourceAsStream(configFile);try {//从输入流中读取属性列表params.load(is);} catch (IOException e) {e.printStackTrace();}//根据指定的获取对应的值driver = params.getProperty("driver");url = params.getProperty("url");user = params.getProperty("username");password = params.getProperty("password");}/*** 获取数据库连接对象*/public Connection getConnection() {try {if (conn == null || conn.isClosed()) {// 获取连接并捕获异常try {Class.forName(driver);conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {e.printStackTrace();// 异常处理}}} catch (SQLException e) {e.printStackTrace();}return conn;// 返回连接对象}/*** 关闭数据库连接** @param conn 数据库连接* @param stmt Statement对象* @param rs   结果集*/public void closeAll(Connection conn, Statement stmt, ResultSet rs) {// 若结果集对象不为空,则关闭if (rs != null) {try {rs.close();} catch (Exception e) {e.printStackTrace();}}// 若Statement对象不为空,则关闭if (stmt != null) {try {stmt.close();} catch (Exception e) {e.printStackTrace();}}// 若数据库连接对象不为空,则关闭if (conn != null) {try {conn.close();} catch (Exception e) {e.printStackTrace();}}}
}

9.4.2 基类 BaseDao：增、删、改的通用方法

    /*** 增、删、改的操作** @param preparedSql 预编译的 SQL 语句* @param param       参数的字符串数组* @return 影响的行数*/public int exceuteUpdate(String preparedSql, Object[] param) {PreparedStatement pstmt = null;int num = 0;conn = getConnection();try {pstmt = conn.prepareStatement(preparedSql);if (param != null) {for (int i = 0; i < param.length; i++) {//为预编译sql设置参数pstmt.setObject(i + 1, param[i]);}}num = pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(conn, pstmt, null);}return num;}

9.4.3 实现类实现接口并继承 BaseDao 基类

package XaunZiShare;import com.javamysql.HospitalSystem;
import com.javamysql.dao.BaseDao;
import com.javamysql.dao.PatientDao;
import com.javamysql.entity.Patient;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;import java.sql.*;
import java.util.ArrayList;
import java.util.List;public class PatientDaoMySQLImpl extends BaseDao implements PatientDao {private static Logger logger = LogManager.getLogger(HospitalSystem.class.getName());@Overridepublic int update(Patient patient) {//构造SQL语句String preparedSQL = "UPDATE patient SET address=?, birthDate=?, email=?, gender=?, patientName=?, phoneNum=?, identityNum=?,password=? WHERE patientID=?";//构造SQL执行参数数组List<String> params = new ArrayList<String>();params.add(patient.getAddress());params.add(patient.getBirthDate());params.add(patient.getEmail());params.add(patient.getGender());params.add(patient.getPatientName());params.add(patient.getPhoneNum());params.add(patient.getIdentityNum());params.add(patient.getPassword());params.add(patient.getPatientID());//调用BaseDao中的更新return exceuteUpdate(preparedSQL, params.toArray());}@Overridepublic Patient getPatientByIdNumAndPwd(String identityNum, String pwd) {Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;Patient patient = null;conn = getConnection();try {//构造PreparedStatement对象pstmt = conn.prepareStatement("SELECT * FROM patient WHERE identityNum=? and password=?");pstmt.setString(1, identityNum);pstmt.setString(2, pwd);rs = pstmt.executeQuery();//验证用户名和密码if (rs.next()) {//从MySQL读取用户信息，并加载到patient对象中patient = new Patient();patient.setPatientID(rs.getString("patientID"));patient.setAddress(rs.getString("address"));patient.setBirthDate(rs.getString("birthDate"));patient.setEmail(rs.getString("email"));patient.setGender(rs.getString("gender"));patient.setPatientName(rs.getString("patientName"));patient.setPhoneNum(rs.getString("phoneNum"));patient.setIdentityNum(rs.getString("identityNum"));patient.setPassword(rs.getString("password"));}} catch (SQLException e) {logger.error(e);} finally {//关闭数据库连接closeAll(conn, pstmt, rs);}return patient;}
}

此种封装JDBC的模式称为DAO模式

9.5 完整项目结构图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H5xqAyRp-1680668206332)(./assets/SRC.png)]

service 为业务层，将基础实现类，整合为复杂业务