✅作者简介：热爱国学的Java后端开发者，修心和技术同步精进。
🍎个人主页：乐趣国学的博客
🍊个人信条：不迁怒，不贰过。小知识，大智慧。
💞当前专栏：JAVA开发者成长之路
✨特色专栏：国学周更-心性养成之路
🥭本文内容：【JAVA高级】——初识JDBC中DAO数据访问对象
更多内容点击👇
【JAVA高级】——初识JDBC中DAO数据访问对象

本文目录

• • 💖 什么是业务
  • 💖 service开发流程（注册账号业务）
  • • ✨ 业务需求和逻辑
    • ✨ 业务实现步骤
    • • 💫 创建表employee
      • 💫 向employee表中插入数据
      • 💫 封装实体类Employee
      • 💫 编写EmployeeDaoImpl类（Dao层代码）
      • 💫 编写EmployeeServiceImpl类（业务层代码）
      • 💫 测试注册业务
  • 💖 Service开发流程（转账业务）
  • • ✨ 业务需求和逻辑
    • ✨ 业务实现步骤
    • • 💫 创建表account
      • 💫 向account表中插入数据
      • 💫 封装实体类Account
      • 💫 编写AccountDaoImpl类（Dao层代码）
      • 💫 编写AccountServiceImpl类（业务层代码）
      • 💫 测试转账业务
  • 💖 投票传送门

💖 什么是业务

（1）业务即用户完成的一个功能，可以有一个或者多个Dao的调用组成。（软件所提供的一个功能都叫业务）。

（2）你去银行取钱是一个业务，存钱是另一个业务，生活中你做的任何一件事都可以看成是一个业务。有的业务只需要一步就能完成，有的业务需要多个步骤才能完成。

💖 service开发流程（注册账号业务）

✨ 业务需求和逻辑

（1）业务需求：

• 注册一个employee账户，存入employee表中

（2）业务逻辑：

1. 首先在数据库中查询你要注册的账号是否存在（根据身份证号码查询，身份证号码具有唯一性）
2. 如果查询不到你想要注册的账号，则将你注册的账号存入employee表中
3. 如果查询到了你要注册的账号名称，则提示账号已存在，不能注册

✨ 业务实现步骤

💫 创建表employee

CREATE TABLE IF NOT EXISTS `employee`(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL,`age` INT NOT NULL,`gender` VARCHAR(3),`bornDate` DATE NOT NULL,`identityCard` VARCHAR(18) UNIQUE NOT NULL,`phone` VARCHAR(11) NOT NULL,`email` VARCHAR(20) NOT NULL,`address` VARCHAR(30) NOT NULL
);

💫 向employee表中插入数据

INSERT INTO `employee` VALUES(1001,'zhangsan',24,'男','1998-12-16','340825199812161316','13845687890','1319866912@qq.com','安徽合肥蜀山区');INSERT INTO `employee` VALUES(1002,'lisi',25,'男','1997-12-26','340825199712261396','13845682233','1396548732@qq.com','安徽合肥瑶海区');

💫 封装实体类Employee

package com.cxyzxc.examples05;import java.util.Date;public class Employee {/** 员工编号 */private int id;/** 姓名 */private String name;/** 年龄 */private int age;/** 性别 */private String gender;/** 出生日期 */private Date bornDate;/** 身份证号码 */private String identityCard;/** 手机号码 */private String phone;/** 邮箱 */private String email;/** 住址 */private String address;// 无参构造方法public Employee() {super();}// 有参构造方法public Employee(String name, int age, String gender, Date bornDate,String identityCard, String phone, String email, String address) {super();this.name = name;this.age = age;this.gender = gender;this.bornDate = bornDate;this.identityCard = identityCard;this.phone = phone;this.email = email;this.address = address;}public Employee(int id, String name, int age, String gender, Date bornDate,String identityCard, String phone, String email, String address) {super();this.id = id;this.name = name;this.age = age;this.gender = gender;this.bornDate = bornDate;this.identityCard = identityCard;this.phone = phone;this.email = email;this.address = address;}// getXxx()/setXxx()方法public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public Date getBornDate() {return bornDate;}public void setBornDate(Date bornDate) {this.bornDate = bornDate;}public String getIdentityCard() {return identityCard;}public void setIdentityCard(String identityCard) {this.identityCard = identityCard;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}// 重写Object类中的toString()方法@Overridepublic String toString() {return "id=" + id + ", name=" + name + ", age=" + age + ", gender="+ gender + ", bornDate=" + bornDate + ", identityCard="+ identityCard + ", phone=" + phone + ", email=" + email+ ", address=" + address;}
}

💫 编写EmployeeDaoImpl类（Dao层代码）

 * （1）该类中提供对teacher表进行增、删、改、查单个、查所有5个方法。* * （2）该类中的代码只做数据库访问操作，不做任何业务逻辑操作。* * （3）该类只对数据库一张表进行操作，从而实现复用

package com.cxyzxc.examples05;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;import com.cxyzxc.examples04.DBUtils;
import com.cxyzxc.examples04.DateUtils;public class EmployeeDaoImpl {// 新增：向employee表中插入一条数据（一条数据对应一个Employee对象）,插入成功，返回一个受影响行数值（int类型）public int insert(Employee employee) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "insert into `employee`(name,age,gender,bornDate,identityCard,phone,email,address) values(?,?,?,?,?,?,?,?);";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);// 绑定参数，赋值preparedStatement.setString(1, employee.getName());preparedStatement.setInt(2, employee.getAge());preparedStatement.setString(3, employee.getGender());// 将java.util.Date类型日期时间转换为java.sql.Date类型，然后插入到数据库中preparedStatement.setDate(4,DateUtils.utilDateToSqlDate(employee.getBornDate()));preparedStatement.setString(5, employee.getIdentityCard());preparedStatement.setString(6, employee.getPhone());preparedStatement.setString(7, employee.getEmail());preparedStatement.setString(8, employee.getAddress());// 执行SQL语句，返回受影响的行数值int result = preparedStatement.executeUpdate();// 将结果返回给调用者return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 删除：根据员工id删除一条数据,删除成功，返回一个受影响行数值（int类型）public int delete(int id) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "delete from employee where id = ?;";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);// 绑定参数，赋值preparedStatement.setInt(1, id);// 执行SQL语句，返回受影响的行数值int result = preparedStatement.executeUpdate();// 将结果返回给调用者return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 修改：修改employee表中的数据，可能对任意的一个字段进行修改，所以方法中直接传递一个对象进行修改,修改成功，返回一个受影响行数值（int类型）public int update(Employee employee) {Connection connection = null;PreparedStatement preparedStatement = null;String sql = "update employee set name = ?,age = ?,gender = ?,bornDate = ?,identityCard = ?,phone = ?,email = ?,address = ? where id = ?;";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);// 绑定参数，赋值// 绑定参数，赋值preparedStatement.setString(1, employee.getName());preparedStatement.setInt(2, employee.getAge());preparedStatement.setString(3, employee.getGender());// 将java.util.Date类型日期时间转换为java.sql.Date类型，然后插入到数据库中preparedStatement.setDate(4,DateUtils.utilDateToSqlDate(employee.getBornDate()));preparedStatement.setString(5, employee.getIdentityCard());preparedStatement.setString(6, employee.getPhone());preparedStatement.setString(7, employee.getEmail());preparedStatement.setString(8, employee.getAddress());preparedStatement.setInt(9, employee.getId());// 执行SQL语句，返回受影响的行数值int result = preparedStatement.executeUpdate();// 将结果返回给调用者return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 查询单个：根据员工id查询一条数据，查询成功返回一个结果集（ResultSet类型）,从结果集中取出元素，封装成一个Employee对象，将该对象返回public Employee select(int id) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Employee employee = null;String sql = "select * from employee where id = ?;";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);// 绑定参数，赋值preparedStatement.setInt(1, id);resultSet = preparedStatement.executeQuery();// 处理结果集，因为是查询单个数据，所以不需要循环遍历获取数据，集合中有数据就取出来if (resultSet.next()) {employee = new Employee();int employeeid = resultSet.getInt("id");String name = resultSet.getString("name");int age = resultSet.getInt("age");String gender = resultSet.getString("gender");Date bornDate = resultSet.getDate("bornDate");String identityCard = resultSet.getString("identityCard");String phone = resultSet.getString("phone");String email = resultSet.getString("email");String address = resultSet.getString("address");// 将获取的赋值给teacher对象employee.setId(employeeid);employee.setName(name);employee.setAge(age);employee.setGender(gender);employee.setBornDate(bornDate);employee.setIdentityCard(identityCard);employee.setPhone(phone);employee.setEmail(email);employee.setAddress(address);}return employee;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return null;}// 查询单个：根据员工身份证号码查询一条数据，查询成功返回一个结果集（ResultSet类型）,从结果集中取出元素，封装成一个Employee对象，将该对象返回public Employee select(String identityCard) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Employee employee = null;String sql = "select * from employee where identityCard = ?;";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);// 绑定参数，赋值preparedStatement.setString(1, identityCard);resultSet = preparedStatement.executeQuery();// 处理结果集，因为是查询单个数据，所以不需要循环遍历获取数据，集合中有数据就取出来if (resultSet.next()) {employee = new Employee();int employeeid = resultSet.getInt("id");String name = resultSet.getString("name");int age = resultSet.getInt("age");String gender = resultSet.getString("gender");Date bornDate = resultSet.getDate("bornDate");String idCard = resultSet.getString("identityCard");String phone = resultSet.getString("phone");String email = resultSet.getString("email");String address = resultSet.getString("address");// 将获取的赋值给teacher对象employee.setId(employeeid);employee.setName(name);employee.setAge(age);employee.setGender(gender);employee.setBornDate(bornDate);employee.setIdentityCard(idCard);employee.setPhone(phone);employee.setEmail(email);employee.setAddress(address);}return employee;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return null;}// 查询所有：将employee表中的所有数据全部查询出来，查询成功返回一个结果集（ResultSet类型）,从结果集中取出元素，封装成多个Employee对象，将多个对象存储在集合中，返回这个集合public List selectAll() {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Employee employee = null;List employeeList = new ArrayList();String sql = "select * from employee;";connection = DBUtils.getConnection();try {preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();// 查询出来多个结果，存在resultSet结果集中，遍历该结果集while (resultSet.next()) {int employeeid = resultSet.getInt("id");String name = resultSet.getString("name");int age = resultSet.getInt("age");String gender = resultSet.getString("gender");Date bornDate = resultSet.getDate("bornDate");String identityCard = resultSet.getString("identityCard");String phone = resultSet.getString("phone");String email = resultSet.getString("email");String address = resultSet.getString("address");employee = new Employee(employeeid, name, age, gender,bornDate, identityCard, phone, email, address);employeeList.add(employee);}return employeeList;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return null;}
}

💫 编写EmployeeServiceImpl类（业务层代码）

 * 注册账号的业务逻辑： * 		1、查询你要注册的员工是否存在（根据身份证号码进行查询） * 		2、如果要注册的员工不存在，就注册账号* 		3、如果注册的员工已经存在，就返回已经注册该员工

package com.cxyzxc.examples05;public class EmployeeServiceImpl {public void register(Employee employee) {EmployeeDaoImpl employeeDaoImpl = new EmployeeDaoImpl();// 1、查询你要注册的员工是否存在Employee emp = employeeDaoImpl.select(employee.getIdentityCard());// 2、如果要注册的员工不存在（emp为null），就注册账号if (emp == null) {employeeDaoImpl.insert(employee);System.out.println("员工注册成功（数据插入成功）");} else {System.out.println("该员工已经注册（数据插入失败）");}}
}

💫 测试注册业务

package com.cxyzxc.examples05;public class TestRegister {public static void main(String[] args) {EmployeeServiceImpl employeeServiceImpl = new EmployeeServiceImpl();Employee employee = new Employee("赵六", 30, "男",DateUtils.strDateToUtilDate("1992-11-29"),"340825199211291123", "13877884455", "13877884455@qq.com","安徽合肥肥西");employeeServiceImpl.register(employee);}
}

💖 Service开发流程（转账业务）

✨ 业务需求和逻辑

（1）业务需求：

• 一个账户向另一个账户转账

（2）业务逻辑：

1. 首先在数据库中查询转账账号是否存在
2. 然后在数据库中查询转账账号密码是否存在
3. 再验证转账账号的余额是否足够转账
4. 再验证收款账号是否存在
5. 执行转账操作，转账账号余额减少，收款账号余额增加（减少的金额与增加的金额相等）

✨ 业务实现步骤

💫 创建表account

CREATE TABLE IF NOT EXISTS `account`(`cardNo` VARCHAR(20) PRIMARY KEY,`pwd` VARCHAR(20) NOT NULL,`name` VARCHAR(20) NOT NULL,`balance` DOUBLE NOT NULL COMMENT '账户余额'
);

💫 向account表中插入数据

INSERT INTO account VALUES('6001','123456','zhangsan',10000);
INSERT INTO account VALUES('6002','123456','lisi',5000);

💫 封装实体类Account

package com.cxyzxc.examples06;public class Account {/** 账号 */private String cardNo;/** 密码 */private String pwd;/** 用户名 */private String name;/** 账户余额 */private double balance;// 无参构造方法public Account() {super();}// 有参构造方法public Account(String cardNo, String pwd, String name, double balance) {super();this.cardNo = cardNo;this.pwd= pwd;this.name = name;this.balance = balance;}// getXxx()/setXxx()方法public String getCardNo() {return cardNo;}public void setCardNo(String cardNo) {this.cardNo = cardNo;}public String getPassword() {return pwd;}public void setPassword(String pwd) {this.pwd= pwd;}public String getName() {return name;}public void setName(String name) {this.name = name;}public double getBalance() {return balance;}public void setBalance(double balance) {this.balance = balance;}// 重写Object类中的toString()方法@Overridepublic String toString() {return "cardNo=" + cardNo + ", pwd=" + pwd+ ", name=" + name+ ", balance=" + balance;}
}

💫 编写AccountDaoImpl类（Dao层代码）

package com.cxyzxc.examples06;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class AccountDaoImpl {// 新增：插入一个Account对象到数据库中public int insert(Account account) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "insert into account values(?,?,?,?)";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, account.getCardNo());preparedStatement.setString(2, account.getPassword());preparedStatement.setString(3, account.getName());preparedStatement.setDouble(4, account.getBalance());// 执行SQLint result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 删除：根据卡号，删除账号public int delete(String cardNo) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "delete from account where cardNo = ?;";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, cardNo);// 执行SQLint result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 修改public int update(Account account) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "update account set pwd= ?,name = ?,balance = ? where cardNo=?;";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, account.getPassword());preparedStatement.setString(2, account.getName());preparedStatement.setDouble(3, account.getBalance());preparedStatement.setString(4, account.getCardNo());// 执行SQLint result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, null);}return 0;}// 查询单个public Account select(String cardNo) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Account account = null;connection = DBUtils.getConnection();String sql = "select * from account where cardNo = ?";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, cardNo);// 执行SQLresultSet = preparedStatement.executeQuery();if (resultSet.next()) {String cardNumber = resultSet.getString("cardNo");String pwd= resultSet.getString("pwd");String name = resultSet.getString("name");double balance = resultSet.getDouble("balance");account = new Account(cardNumber, pwd, name, balance);}return account;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, resultSet);}return null;}// 查询所有public List selectAll() {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Account account = null;List accountList = new ArrayList();connection = DBUtils.getConnection();String sql = "select * from account;";try {preparedStatement = connection.prepareStatement(sql);// 执行SQLresultSet = preparedStatement.executeQuery();while (resultSet.next()) {String cardNumber = resultSet.getString("cardNo");String pwd= resultSet.getString("pwd");String name = resultSet.getString("name");double balance = resultSet.getDouble("balance");account = new Account(cardNumber, pwd, name, balance);accountList.add(account);}return accountList;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(connection, preparedStatement, resultSet);}return null;}
}

💫 编写AccountServiceImpl类（业务层代码）

 * 转账业务*	@param fromNo 转账人账号*	@param pwd 转账人账号密码* 	@param toNo 收款人账号* 	@param money 转账金额

package com.cxyzxc.examples06;public class AccountServiceImpl {public void transfer(String fromNo, String pwd, String toNo,double money) {AccountDaoImpl accountDaoImpl = new AccountDaoImpl();try {// 1.验证fromNo账号是否存在Account fromAccount = accountDaoImpl.select(fromNo);if (fromAccount == null) {throw new RuntimeException("卡号不存在");}// 2.验证fromNo的密码是否正确if (!fromAccount.getPassword().equals(pwd)) {throw new RuntimeException("密码错误");}// 3.验证余额是否充足if (fromAccount.getBalance() < money) {throw new RuntimeException("余额不足");}// 4.验证toNo账号是否存在Account toAccount = accountDaoImpl.select(toNo);if (toAccount == null) {throw new RuntimeException("对方卡号不存在");}// 5.减少fromNo账号的余额fromAccount.setBalance(fromAccount.getBalance() - money);accountDaoImpl.update(fromAccount);// 6.增加toNo账号的余额toAccount.setBalance(toAccount.getBalance() + money);accountDaoImpl.update(toAccount);System.out.println("转账成功");} catch (Exception e) {System.out.println("转账失败");e.printStackTrace();}}
}

💫 测试转账业务

package com.cxyzxc.examples06;public class TestTransfer {public static void main(String[] args) {AccountServiceImpl AccountServiceImpl = new AccountServiceImpl();AccountServiceImpl.transfer("6001", "123456", "6002", 2000);}
}

💖 投票传送门