✅作者简介:热爱国学的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表
CREATETABLE`book`(`bid`int(11)NOTNULLAUTO_INCREMENTCOMMENT'图书编号',`isbn`varchar(20)NOTNULLCOMMENT'国际标准书号',`name`varchar(20)NOTNULLCOMMENT'书名',`author`varchar(20)NOTNULLCOMMENT'作者',`press`varchar(20)NOTNULLCOMMENT'出版社',`price`doubleNOTNULLCOMMENT'价格',`classification`varchar(20)NOTNULLCOMMENT'分类',PRIMARYKEY(`bid`),UNIQUEKEY`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表字段相同的私有变量
// 定义属性privateint bid;private String isbn;private String name;private String author;private String press;privatedouble price;private String classification;
添加一个无参构造
publicBook(){super();}
添加一个全部字段的有参构造
publicBook(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字段的有参构造
publicBook(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方法
publicintgetBid(){return bid;}publicvoidsetBid(int bid){this.bid = bid;}public String getIsbn(){return isbn;}publicvoidsetIsbn(String isbn){this.isbn = isbn;}public String getName(){return name;}publicvoidsetName(String name){this.name = name;}public String getAuthor(){return author;}publicvoidsetAuthor(String author){this.author = author;}public String getPress(){return press;}publicvoidsetPress(String press){this.press = press;}publicdoublegetPrice(){return price;}publicvoidsetPrice(double price){this.price = price;}public String getClassification(){return classification;}publicvoidsetClassification(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连接对象;注册驱动、获取连接、然后释放资源方法。
privatestaticfinal Properties PROPERTIES =newProperties();// 声明一个ThreadLocal<Connection>对象,用来存储Connection连接对象privatestatic ThreadLocal<Connection> threadLocal =newThreadLocal<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、获取连接方法publicstatic 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、释放资源方法publicstaticvoidcloseAll(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();}}
开启事务
publicstaticvoidstartTransaction(){
Connection connection =getConnection();try{
connection.setAutoCommit(false);}catch(SQLException e){
e.printStackTrace();}}
提交事务
publicstaticvoidcommitTransaction(){
Connection connection =getConnection();try{
connection.commit();}catch(SQLException e){
e.printStackTrace();}finally{
DBUtils.closeAll(null, null, connection);}}
回滚事务
publicstaticvoidrollbackTransaction(){
Connection connection =getConnection();try{
connection.rollback();}catch(SQLException e){
e.printStackTrace();}finally{
DBUtils.closeAll(null, null, connection);}}
DateUtils日期类
publicclassDateUtils{// 声明一个SimpleDateFormat类型的静态常量publicstaticfinal SimpleDateFormat SIMPLEDATEFORMAT=newSimpleDateFormat("yyyy-MM-dd");// 定义方法实现将字符串类型的数据转换成java.util.Date类型publicstatic java.util.Date strToUtilDate(String strDate){try{return SIMPLEDATEFORMAT.parse(strDate);}catch(ParseException e){
e.printStackTrace();}return null;}// 定义方法实现将java.util.Date类型转换为字符串publicstatic String utilDateToString(java.util.Date utilDate){return SIMPLEDATEFORMAT.format(utilDate);}// 定义方法实现架构java.util.Date类型转换为java.sql.Date类型publicstatic java.sql.Date utilDateToSqlDate(java.util.Date utilDate){returnnewjava.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;publicinterfaceBookDao{//增intinsert(Book book);//删intdelete(int bid);//改intupdate(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;publicclassBookDaoImplimplementsBookDao{//全局变量
PreparedStatement preS=null;
Connection connection;@Overridepublicintinsert(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);}return0;}@Overridepublicintdelete(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);}return0;}@Overridepublicintupdate(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);}return0;}@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=newBook(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 =newArrayList<Book>();//获取连接
connection = DBUtils.getConnection();
String sql ="select * from book";try{
preS = connection.prepareStatement(sql);
resultSet = preS.executeQuery();while(resultSet.next()){
book=newBook(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;publicinterfaceBookService{//添加图书intaddBook(Book book);//删除图书intdeleteBook(int bid);//修改图书intupdateBook(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;publicclassBookServiceImplimplementsBookService{// 定义PeopleDaoImpl对象
BookDaoImpl bookDaoImpl =newBookDaoImpl();@OverridepublicintaddBook(Book book){int num=bookDaoImpl.insert(book);return num;}@OverridepublicintdeleteBook(int bid){int num=bookDaoImpl.delete(bid);return num;}@OverridepublicintupdateBook(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;publicclassTestBookServiceImpl{publicstaticvoidmain(String[] args){// 创建BookServiceImpl对象
BookServiceImpl bookServiceImpl =newBookServiceImpl();// 增
Book book =newBook("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 =newBook(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表的三层架构完整代码
版权归原作者 Java Fans 所有, 如有侵权,请联系我们删除。