✅作者简介：热爱国学的Java后端开发者，修心和技术同步精进。
????个人主页：Java Fans的博客
????个人信条：不迁怒，不贰过。小知识，大智慧。
????当前专栏：Java案例分享专栏
✨特色专栏：国学周更-心性养成之路
????本文内容：一个案例学会三层架构对数据表的增删改查
文章目录
案例要求
使用读取配置文件的形式连接数据库，利用三层架构对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方法
@Override
public 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;
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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;
}
@Override
public 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();
@Override
public int addBook(Book book) {
int num=bookDaoImpl.insert(book);
return num;
}
@Override
public int deleteBook(int bid) {
int num=bookDaoImpl.delete(bid);
return num;
}
@Override
public int updateBook(Book book) {
int num=bookDaoImpl.update(book);
return num;
}
@Override
public Book selectOne(int bid) {
Book book=bookDaoImpl.selectOne(bid);
return book;
}
@Override
public 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);
}
}
}
}
完整代码
