✅作者简介：热爱国学的Java后端开发者，修心和技术同步精进。
?个人主页：Java Fans的博客
?个人信条：不迁怒，不贰过。小知识，大智慧。
?当前专栏：Java案例分享专栏
✨特色专栏：国学周更-心性养成之路
?本文内容：一个案例学会三层架构对数据表的增删改查
文章目录
案例要求建book表配置连接数据库创建实体类：Book类工具类DBUtils数据库工具类DateUtils日期类 数据访问层DAO层BookDao接口BookDaoImpl类（实现BookDao接口） 服务层Service层BookService接口BookServiceImpl类（实现BookService接口） 视图层View层TestBookServiceImpl测试类 完整代码
案例要求
使用读取配置文件的形式连接数据库，利用三层架构对book表进行增删改查操作。增加了接口知识点的优化，便于方法名的统一命名。
book表字段如下
bid int 非空 主键 自增长 唯一 -->图书编号
isbn varchar(20) 非空 唯一 -->国际标准书号
name varchar(20) 非空 -->书名
author varchar(20) 非空 -->作者
press varchar(20) 非空 -->出版社
press double 非空 -->价格
classification varchar(20) 非空 -->分类
建book表
CREATE TABLE `book` ( `bid` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书编号', `isbn` varchar(20) 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 '分类', PRIMARY KEY (`bid`), UNIQUE KEY `isbn` (`isbn`)) );配置连接数据库
添加mysql-connector-java-5.1.0-bin.jar文件并使用。
添加db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/java221803
username=root
password=huanghuang
创建实体类：Book类
定义一个Book类，添加和Book表字段相同的私有变量
// 定义属性private int bid;private String isbn;private String name;private String author;private String press;private double price;private String classification;添加一个无参构造
public Book() {super();}添加一个全部字段的有参构造
public Book(int bid, String isbn, String name, String author, String press,double price, String classification) {super();this.bid = bid;this.isbn = isbn;this.name = name;this.author = author;this.press = press;this.price = price;this.classification = classification;}添加一个不带aid字段的有参构造
public Book(String isbn, String name, String author, String press,double price, String classification) {super();this.isbn = isbn;this.name = name;this.author = author;this.press = press;this.price = price;this.classification = classification;}给私有变量添加get/set方法
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;}重写ToString方法
@Overridepublic String toString() {return "Book [bid=" + bid + ", isbn=" + isbn + ", name=" + name+ ", author=" + author + ", press=" + press + ", price="+ price + ", classification=" + classification + "]";}工具类
将一些重复的功能封装成一些工具。
DBUtils数据库工具类
声明一个ThreadLocal对象，用来存储Connection连接对象；注册驱动、获取连接、然后释放资源方法。
private static final Properties PROPERTIES = new Properties();// 声明一个ThreadLocal<Connection>对象，用来存储Connection连接对象private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();static {InputStream is = DBUtils.class.getResourceAsStream("/db.properties");try {PROPERTIES.load(is);// 1、注册驱动Class.forName(PROPERTIES.getProperty("driver"));} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}}// 2、获取连接方法public static Connection getConnection() {Connection connection = threadLocal.get();// 2、获取连接对象try {// 如果连接对象为null，创建一个连接对象if (connection == null) {connection = DriverManager.getConnection(PROPERTIES.getProperty("url"),PROPERTIES.getProperty("username"),PROPERTIES.getProperty("password"));threadLocal.set(connection);}} catch (SQLException e) {e.printStackTrace();}return connection;}// 3、释放资源方法public static void closeAll(ResultSet resultSet, Statement statement,Connection connection) {try {if (resultSet != null) {resultSet.close();}if (statement != null) {statement.close();}if (connection != null) {connection.close();// 移除 ThreadLocal<Connection> 对象threadLocal.remove();}} catch (SQLException e) {e.printStackTrace();}}开启事务
public static void startTransaction() {Connection connection = getConnection();try {connection.setAutoCommit(false);} catch (SQLException e) {e.printStackTrace();}}提交事务
public static void commitTransaction() {Connection connection = getConnection();try {connection.commit();} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(null, null, connection);}}回滚事务
public static void rollbackTransaction() {Connection connection = getConnection();try {connection.rollback();} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(null, null, connection);}}DateUtils日期类
public class DateUtils {// 声明一个SimpleDateFormat类型的静态常量public static final SimpleDateFormat SIMPLEDATEFORMAT=new SimpleDateFormat("yyyy-MM-dd");// 定义方法实现将字符串类型的数据转换成java.util.Date类型public static java.util.Date strToUtilDate(String strDate){try {return SIMPLEDATEFORMAT.parse(strDate);} catch (ParseException e) {e.printStackTrace();}return null;}// 定义方法实现将java.util.Date类型转换为字符串public static String utilDateToString(java.util.Date utilDate){return SIMPLEDATEFORMAT.format(utilDate);}// 定义方法实现架构java.util.Date类型转换为java.sql.Date类型public static java.sql.Date utilDateToSqlDate(java.util.Date utilDate){return new java.sql.Date(utilDate.getTime());}}数据访问层DAO层
DAO层叫数据访问层，全称为data access object，属于一种比较底层，比较基础的操作，具体到对于某个表的增删改查，也就是说某个DAO一定是和数据库的某一张表一一对应的，其中封装了增删改查基本操作，建议DAO只做原子操作，增删改查。
BookDao接口
package cn.bdqn.dao;import java.util.List;import cn.bdqn.entity.Book;public interface BookDao {//增int insert(Book book);//删int delete(int bid);//改int update(Book book);//查单个Book selectOne(int bid);//查所有List<Book> selectAll();}BookDaoImpl类（实现BookDao接口）
package cn.bdqn.dao.lmpl;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 cn.bdqn.dao.BookDao;import cn.bdqn.entity.Book;import cn.bdqn.utils.DBUtils;public class BookDaoImpl implements BookDao {//全局变量PreparedStatement preS=null;Connection connection;@Overridepublic int insert(Book book) {//获取链接connection=DBUtils.getConnection();String sql="insert into book(isbn,name,author,press,price,classification) values(?,?,?,?,?,?);";try {preS=connection.prepareStatement(sql);// 绑定参数//preS.setInt(1, book.getBid());preS.setString(1, book.getIsbn());preS.setString(2, book.getName());preS.setString(3, book.getAuthor());preS.setString(4, book.getPress());preS.setDouble(5, book.getPrice());preS.setString(6, book.getClassification());return preS.executeUpdate();} catch (SQLException e) {e.printStackTrace();}finally{DBUtils.closeAll(null, preS, null);}return 0;}@Overridepublic int delete(int bid) {//获取链接connection = DBUtils.getConnection();String sql = "delete from book where bid = ?;";try {preS = connection.prepareStatement(sql);// 绑定参数preS.setInt(1, bid);return preS.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(null, preS, null);}return 0;}@Overridepublic int update(Book book) {//获取链接connection = DBUtils.getConnection();String sql = "update book set isbn = ?,name = ?, author= ?,press = ?,price = ?,classification = ? where bid=?;";try {preS = connection.prepareStatement(sql);// 绑定参数preS.setString(1, book.getIsbn());preS.setString(2, book.getName());preS.setString(3, book.getAuthor());preS.setString(4, book.getPress());preS.setDouble(5, book.getPrice());preS.setString(6, book.getClassification());preS.setInt(7, book.getBid());return preS.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(null, preS, null);}return 0;}@Overridepublic Book selectOne(int bid) {ResultSet resultSet = null;Book book = null;//获取连接connection = DBUtils.getConnection();String sql = "select * from book where bid = ?";try {preS = connection.prepareStatement(sql);// 绑定参数preS.setInt(1, bid);resultSet = preS.executeQuery();if (resultSet.next()) {book=new Book(bid, resultSet.getString(2), resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getDouble(6),resultSet.getString(7));}return book;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(resultSet, preS, null);}return null;}@Overridepublic List<Book> selectAll() {ResultSet resultSet = null;Book book = null;List<Book> bookList = new ArrayList<Book>();//获取连接connection = DBUtils.getConnection();String sql = "select * from book";try {preS = connection.prepareStatement(sql);resultSet = preS.executeQuery();while (resultSet.next()) {book=new Book(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),resultSet.getDouble(6),resultSet.getString(7));bookList.add(book);}return bookList;} catch (SQLException e) {e.printStackTrace();} finally {DBUtils.closeAll(resultSet, preS, null);}return null;}}服务层Service层
Service层叫服务层，被称为服务，粗略的理解就是对一个或多个DAO进行的再次封装，封装成一个服务，所以这里也就不会是一个原子操作了，需要事物控制。
BookService接口
package cn.bdqn.service;import java.util.List;import cn.bdqn.entity.Book;public interface BookService {//添加图书int addBook(Book book);//删除图书int deleteBook(int bid);//修改图书int updateBook(Book book);//查询一本图书Book selectOne(int bid);//查询所有图书List<Book> selectAll();}BookServiceImpl类（实现BookService接口）
package cn.bdqn.service.Impl;import java.util.List;import cn.bdqn.dao.lmpl.BookDaoImpl;import cn.bdqn.entity.Book;import cn.bdqn.service.BookService;public class BookServiceImpl implements BookService {// 定义PeopleDaoImpl对象BookDaoImpl bookDaoImpl = new BookDaoImpl();@Overridepublic int addBook(Book book) {int num=bookDaoImpl.insert(book);return num;}@Overridepublic int deleteBook(int bid) {int num=bookDaoImpl.delete(bid);return num;}@Overridepublic int updateBook(Book book) {int num=bookDaoImpl.update(book);return num;}@Overridepublic Book selectOne(int bid) {Book book=bookDaoImpl.selectOne(bid);return book;}@Overridepublic List<Book> selectAll() {List<Book> bookList=bookDaoImpl.selectAll();return bookList;}}视图层View层
TestBookServiceImpl测试类
package cn.bdqn.view;import java.util.List;import cn.bdqn.entity.Book;import cn.bdqn.service.Impl.BookServiceImpl;public class TestBookServiceImpl {public static void main(String[] args) {// 创建BookServiceImpl对象BookServiceImpl bookServiceImpl = new BookServiceImpl();// 增Book book = new Book("977-8-3154-6587-3", "jQuery从入门到精通", "王五","北京大学出版社", 88.88, "前端开发");if (bookServiceImpl.addBook(book) != 0) {System.out.println("图书添加成功！");}// 删if (bookServiceImpl.deleteBook(1001)!= 0) {System.out.println("图书删除成功！");}//改Book book1 = new Book(1003, "977-8-3154-6587-4", "jQuery从入门到精通", "王六","北京大学出版社", 88.88, "前端开发");if (bookServiceImpl.updateBook(book1)!= 0) {System.out.println("图书修改成功！");}//查if(bookServiceImpl.selectOne(1002)!=null){System.out.println("图书查询成功！图书信息为："+bookServiceImpl.selectOne(1002).toString());}//查所有List<Book> bookList=bookServiceImpl.selectAll();if(bookList.size()>0){System.out.println("查询到所有图书为：");for (Book book2 : bookList) {System.out.println(book2);}}}}完整代码
book表的三层架构完整代码
