0


36、Java——一个案例学会三层架构对数据表的增删改查

✅作者简介:热爱国学的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 数据库 mysql

本文转载自: https://blog.csdn.net/hh867308122/article/details/127971397
版权归原作者 Java Fans 所有, 如有侵权,请联系我们删除。

“36、Java——一个案例学会三层架构对数据表的增删改查”的评论:

还没有评论