✅作者简介：热爱国学的Java后端开发者，修心和技术同步精进。
🍎个人主页：Java Fans的博客
🍊个人信条：不迁怒，不贰过。小知识，大智慧。
💞当前专栏：Java案例分享专栏
✨特色专栏：国学周更-心性养成之路
🥭本文内容：【JAVA高级】——玩转JDBC中的三层架构
更多内容点击👇
       【JAVA高级】——吃透JDBC中的事务及事务的封装

文章目录

• • 什么是三层
  • 三层架构项目搭建步骤
  • • 项目环境搭建
    • 创建book表
    • • 创建表
      • 向表中插入数据
    • 创建entity实体类Book
    • 创建BookDao接口
    • 创建BookDaoImpl实现类
    • 创建BookService接口
    • 创建BookServiceImpl实现类
    • 编写测试类
    • • 测试增加数据
      • 测试删除数据
      • 测试修改数据
      • 测试查询单个
      • 测试查询所有

什么是三层

（1）表示层（View）

• 命名：XxxView
• 职责：收集用户的数据和需求、数据

（2）业务逻辑层（service）

• 命名：XxxServiceImpl
• 职责：数据加工处理、调用DAO完成业务实现、控制事务

（3）数据访问层（Dao）

• 命名：XxxDaoImpl
• 职责：向业务层提供数据，将业务层加工后的数据同步到数据库

三层架构项目搭建步骤

项目环境搭建

1）新建一个项目，在项目中创建lib文件夹，将MySQL数据库jar包放在lib文件夹中，配置环境。

2）在src文件夹下面创建db.properties文件，编写数据库driver、url、user、password信息。

3）创建三层需要的各个包。

（1）utils包：存放工具类（DBUtils类、DateUtils类）（2）entity包：存放实体类（Xxx.java）（3）dao包：存放DAO接口impl包：存放DAO接口实现类（4）service包：存放service接口impl：存放service接口实现类（5）view包：存放程序启动类或测试类（main）

创建book表

创建表

CREATE TABLE IF NOT EXISTS `book`(
`bid` INT PRIMARY KEY AUTO_INCREMENT COMMENT '图书编号',
`isbn` VARCHAR(20) UNIQUE NOT NULL COMMENT '国际标准书号',
`name` VARCHAR(20) NOT NULL COMMENT '书名',
`author` VARCHAR(20) NOT NULL COMMENT '作者',
`press` VARCHAR(20) NOT NULL COMMENT '出版社',
`price` DOUBLE NOT NULL COMMENT '价格',
`classification` VARCHAR(20) NOT NULL COMMENT '分类'
);

向表中插入数据

INSERT INTO `book`(`bid`,`isbn`,`name`,`author`,`press`,`price`,`classification`)
VALUES
(1001,'978-7-5170-7654-4','SQL从入门到精通','张三','中国水利水电出版社',79.80,'数据库');INSERT INTO `book`(`bid`,`isbn`,`name`,`author`,`press`,`price`,`classification`)
VALUES
(1002,'976-9-5245-7633-5','java从入门到精通','李四','清华大学出版社',99.80,'程序设计');

创建entity实体类Book

package com.cxyzxc.www.entity;import java.util.Date;/*** entity实体类Booke类*/
public class Book {/** 图书编号 */private int bid;/** 国际标准书号 */private String isbn;/** 书名 */private String name;/** 作者 */private String author;/** 出版社 */private String press;/** 价格 */private double price;/** 分类 */private String classification;/** 出版日期 */private Date pubdate;public Book() {super();}public Book(String isbn, String name, String author, String press,double price, String classification, Date pubdate) {super();this.isbn = isbn;this.name = name;this.author = author;this.press = press;this.price = price;this.classification = classification;this.pubdate = pubdate;}public Book(int bid, String isbn, String name, String author, String press,double price, String classification, Date pubdate) {super();this.bid = bid;this.isbn = isbn;this.name = name;this.author = author;this.press = press;this.price = price;this.classification = classification;this.pubdate = pubdate;}public int getBid() {return bid;}public void setBid(int bid) {this.bid = bid;}public String getIsbn() {return isbn;}public void setIsbn(String isbn) {this.isbn = isbn;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public String getPress() {return press;}public void setPress(String press) {this.press = press;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}public String getClassification() {return classification;}public void setClassification(String classification) {this.classification = classification;}public Date getPubdate() {return pubdate;}public void setPubdate(Date pubdate) {this.pubdate = pubdate;}@Overridepublic String toString() {return "Book [bid=" + bid + ", isbn=" + isbn + ", name=" + name+ ", author=" + author + ", press=" + press + ", price="+ price + ", classification=" + classification + ", pubdate="+ pubdate + "]";}}

创建BookDao接口

package com.cxyzxc.www.dao;import java.util.List;import com.cxyzxc.www.entity.Book;
/*** 定义BookDao接口，接口中定义对book表增删改查的方法*/
public interface BookDao {//增int insert(Book book);//删int delete(int bid);//改int update(Book book);//查单个Book selectOne(int bid);//查所有List selectAll();}

创建BookDaoImpl实现类

package com.cxyzxc.www.dao.impl;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.www.dao.BookDao;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.utils.DBUtils;
import com.cxyzxc.www.utils.DateUtils;
/*** 定义BookDaoImpl类，实现BookDao接口，重写接口中的增删改查方法*/
public class BookDaoImpl implements BookDao {@Overridepublic int insert(Book book) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "INSERT INTO `book`(`isbn`,`name`,`author`,`press`,`price`,`classification`,`pubdate`)VALUES(?,?,?,?,?,?,?);";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, book.getIsbn());preparedStatement.setString(2, book.getName());preparedStatement.setString(3, book.getAuthor());preparedStatement.setString(4, book.getPress());preparedStatement.setDouble(5, book.getPrice());preparedStatement.setString(6, book.getClassification());preparedStatement.setDate(7,DateUtils.utilDateToSqlDate(book.getPubdate()));int result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();}return 0;}@Overridepublic int delete(int bid) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "DELETE FROM `book` WHERE `bid`=?;";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setInt(1, bid);int result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();}return 0;}@Overridepublic int update(Book book) {Connection connection = null;PreparedStatement preparedStatement = null;connection = DBUtils.getConnection();String sql = "UPDATE `book` SET `isbn`=?,`name`=?,`author`=?,`press`=?,`price`=?,`classification`=?,`pubdate`=? WHERE `bid`=?;";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setString(1, book.getIsbn());preparedStatement.setString(2, book.getName());preparedStatement.setString(3, book.getAuthor());preparedStatement.setString(4, book.getPress());preparedStatement.setDouble(5, book.getPrice());preparedStatement.setString(6, book.getClassification());preparedStatement.setDate(7,DateUtils.utilDateToSqlDate(book.getPubdate()));preparedStatement.setDouble(8, book.getBid());int result = preparedStatement.executeUpdate();return result;} catch (SQLException e) {e.printStackTrace();}return 0;}@Overridepublic Book selectOne(int bid) {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Book book = null;connection = DBUtils.getConnection();String sql = "Select * FROM `book` WHERE `bid`=?;";try {preparedStatement = connection.prepareStatement(sql);// 绑定参数preparedStatement.setInt(1, bid);resultSet = preparedStatement.executeQuery();if (resultSet.next()) {int bookid = resultSet.getInt(1);String isbn = resultSet.getString(2);String name = resultSet.getString(3);String author = resultSet.getString(4);String press = resultSet.getString(5);double price = resultSet.getDouble(6);String classification = resultSet.getString(7);Date pubdate = resultSet.getDate(8);book = new Book(bookid, isbn, name, author, press, price,classification, pubdate);}return book;} catch (SQLException e) {e.printStackTrace();}return null;}@Overridepublic List selectAll() {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;Book book = null;List bookList = new ArrayList();connection = DBUtils.getConnection();String sql = "Select * FROM `book`;";try {preparedStatement = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();while (resultSet.next()) {int bookid = resultSet.getInt(1);String isbn = resultSet.getString(2);String name = resultSet.getString(3);String author = resultSet.getString(4);String press = resultSet.getString(5);double price = resultSet.getDouble(6);String classification = resultSet.getString(7);Date pubdate = resultSet.getDate(8);book = new Book(bookid, isbn, name, author, press, price,classification, pubdate);bookList.add(book);}return bookList;} catch (SQLException e) {e.printStackTrace();}return null;}}

创建BookService接口

package com.cxyzxc.www.service;import java.util.List;import com.cxyzxc.www.entity.Book;
/*** 定义BookService接口，接口中定义业务逻辑方法*/
public interface BookService {//添加图书int addBook(Book book);//删除图书int deleteBook(int bid);//修改图书int updateBook(Book book);//查询一本图书Book selectOne(int bid);//查询所有图书List selectAll();}

创建BookServiceImpl实现类

package com.cxyzxc.www.service.impl;import java.util.List;import com.cxyzxc.www.dao.BookDao;
import com.cxyzxc.www.dao.impl.BookDaoImpl;
import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
/*** 定义BookServiceImpl类，实现BookService接口，重写BookService接口中的所有方法*/
public class BookServiceImpl implements BookService {BookDao bookDao = new BookDaoImpl();@Overridepublic int addBook(Book book) {// 首先查询一下插入的图书在数据库中是否存在Book book2 = bookDao.selectOne(book.getBid());if (book2 == null) {return bookDao.insert(book);} else {System.out.println("插入的图书已经存在，插入失败");}return 0;}@Overridepublic int deleteBook(int bid) {// 查询要删除的图书是否存在Book book2 = bookDao.selectOne(bid);if (book2 == null) {System.out.println("删除的图书不存在，无法删除");} else {return bookDao.delete(bid);}return 0;}@Overridepublic int updateBook(Book book) {// 查询要修改的图书是否存在Book book2 = bookDao.selectOne(book.getBid());if (book2 == null) {System.out.println("你要修改的图书不存在");} else {return bookDao.update(book);}return 0;}@Overridepublic Book selectOne(int bid) {Book book2 = bookDao.selectOne(bid);return book2;}@Overridepublic List selectAll() {List bookList = bookDao.selectAll();return bookList;}}

编写测试类

测试增加数据

package com.cxyzxc.www.view;import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
import com.cxyzxc.www.utils.DateUtils;public class Test01InsertBook {public static void main(String[] args) {BookService bookService = new BookServiceImpl();// 添加图书Book book = new Book("978-9-9456-3286-9", "JSP从入门到精通", "李二狗", "邮电出版社",129, "编程设计", DateUtils.strDateToUtilDate("2022-01-13"));int result = bookService.addBook(book);if (result == 1) {System.out.println("图书添加成功");} else {System.out.println("图书添加失败");}}}

测试删除数据

package com.cxyzxc.www.view;import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;public class Test02DeleteBook {public static void main(String[] args) {BookService bookService = new BookServiceImpl();// 删除图书int result = bookService.deleteBook(1003);if (result == 1) {System.out.println("删除成功");} else {System.out.println("删除失败");}}}

测试修改数据

package com.cxyzxc.www.view;import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;
import com.cxyzxc.www.utils.DateUtils;public class Test03UpdateBook {public static void main(String[] args) {BookService bookService = new BookServiceImpl();// //修改图书Book book = new Book(1002, "976-9-5245-7633-5", "JSP从入门到放弃", "李四","清华大学出版社", 109.8, "编程设计",DateUtils.strDateToUtilDate("2022-10-13"));int result = bookService.updateBook(book);if (result == 1) {System.out.println("修改成功");} else {System.out.println("修改失败");}}}

测试查询单个

package com.cxyzxc.www.view;import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;public class Test04SelectOneBook {public static void main(String[] args) {BookService bookService = new BookServiceImpl();Book book = bookService.selectOne(1003);if (book == null) {System.out.println("没有你要查找的图书");} else {System.out.println(book);}}}

测试查询所有

package com.cxyzxc.www.view;import java.util.List;import com.cxyzxc.www.entity.Book;
import com.cxyzxc.www.service.BookService;
import com.cxyzxc.www.service.impl.BookServiceImpl;public class Test05SelectAllBook {public static void main(String[] args) {BookService bookService = new BookServiceImpl();List books = bookService.selectAll();if (books.isEmpty()) {System.out.println("数据库里没有书的数据");} else {for (int i = 0; i < books.size(); i++) {System.out.println(books.get(i));}}}}