基于JDBC的书库系统（MySQL）

一、创建数据库中的表

1、需求

有一张表叫javabook【创建表要求使用sql语句进行】
表中列 bookid 整数自增类型
表中列 bprice 小数类型
表中列 bookname 字符串类型长度不能小于50

工程和包要求：
domain
dao
service
test

2、实现

create table javabook(
    bookid int primary key auto_increment,
    bprice decimal(8,2),
    bookname varchar(50) not null
);

3、项目结构

二、Idea连接数据库

1、创建文件目录

2、导入文件Jar包

3、连接数据库

package dao;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * @program: Pay.java
 * @ClassName BaseDao
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-11 20:10
 * @Version 1.0
 **/

public class BaseDao {
    public static final String DRIVERCLASSNAME = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/ruixi?sslMode=REQUIRED&characterEncoding=UTF-8&connectionTimeZone=GMT%2B8&forceConnectionTimeZoneToSession=true";
    public static final String USER = "root";
    public static final String PASS = "admin";

    /**
     * 获取数据库连接
     */
    public Connection getConnection() {
        //创建连接引用
        Connection conn = null;
        try {
            //装载驱动
            Class.forName(DRIVERCLASSNAME);
            //连接数据库
            conn = DriverManager.getConnection(URL, USER, PASS);
            //输出连接
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

三、编写Java代码

1、entity层的实体

package entity;

/**
 * @program: Pay.java
 * @ClassName Book
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 18:40
 * @Version 1.0
 **/

public class Book {
    private int bid;
    private String bname;
    private double price;

    public Book(String bname, double price) {
        this.bname = bname;
        this.price = price;
    }

    public void setBid(int bid) {
        this.bid = bid;
    }

    public int getBid() {
        return bid;
    }

    public String getBname() {
        return bname;
    }

    public double getPrice() {
        return price;
    }

    @Override
    public String toString() {
        return "Book{" +
                "bid=" + bid +
                ", bname='" + bname + '\'' +
                ", price=" + price +
                '}';
    }
}

2、Dao层

2.1、Dao层连接

package dao;

import java.sql.*;

/**
 * @program: Pay.java
 * @ClassName BaseDao
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-11 20:10
 * @Version 1.0
 **/

public class BaseDao {
    public static final String DRIVERCLASSNAME = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/ruixi?sslMode=REQUIRED&characterEncoding=UTF-8&connectionTimeZone=GMT%2B8&forceConnectionTimeZoneToSession=true";
    public static final String USER = "root";
    public static final String PASS = "admin";

    /**
     * 获取数据库连接
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            //装载驱动.3

            Class.forName(DRIVERCLASSNAME);
            //连接数据库
            conn = DriverManager.getConnection(URL, USER, PASS);
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    void closedAll(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

2.2、Dao层接口

package dao;
import entity.Book;

import java.util.List;

/**
 * @program: Pay.java
 * @ClassName BookDao
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 18:35
 * @Version 1.0
 **/

public interface BookDao {
    //添加书
    void addBook(Book book);
    //更新书
    void updateBook(Book book);
    //根据书名删除书
    void deleteAllBooks(String bname);
    //模糊查询书
    List<Book> findBooks(String bname);
}

2.3、Dao层impl实现

package dao.impl;

import dao.BaseDao;
import dao.BookDao;
import entity.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: Pay.java
 * @ClassName BookDaoImpl
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 18:40
 * @Version 1.0
 **/

public class BookDaoImpl extends BaseDao implements BookDao {
    @Override
    public void addBook(Book book) {
        //创建连接引用
        Connection conn = null;
        //创建操作引用
        PreparedStatement ps = null;
        //SQL操作语句
        String sql = "insert into javabook(bprice,bookname) values(?,?)";
        try {
            //进行数据库连接（调用BaseDao中的方法）
            conn = getConnection();
            //创建操作对象
            ps = conn.prepareStatement(sql);
            //处理sql操作参数
            ps.setDouble(1, book.getPrice());
            ps.setString(2, book.getBname());
            //执行 插入修改删除
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closedAll(conn, ps, null);
        }
    }

    @Override
    public void updateBook(Book book) {
        //创建连接引用
        Connection conn = null;
        //创建操作引用
        PreparedStatement ps = null;
        //SQL操作语句
        String sql = "update javabook set bprice=?,bookname=? where bookid=?";
        try {
            //进行数据库连接（调用BaseDao中的方法）
            conn = getConnection();
            //创建操作对象
            ps = conn.prepareStatement(sql);
            //处理sql操作参数
            ps.setDouble(1, book.getPrice());
            ps.setString(2, book.getBname());
            ps.setInt(3,book.getBid());
            //执行 插入修改删除
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closedAll(conn, ps, null);
        }
    }

    @Override
    public void deleteAllBooks(String bname) {
//创建连接引用
        Connection conn = null;
        //创建操作引用
        PreparedStatement ps = null;
        //SQL操作语句
        String sql = "delete from javabook where bookname=?";
        try {
            //进行数据库连接（调用BaseDao中的方法）
            conn = getConnection();
            //创建操作对象
            ps = conn.prepareStatement(sql);
            //处理sql操作参数
            ps.setString(1, bname);
            //执行 插入修改删除
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closedAll(conn, ps, null);
        }
    }

    @Override
    public List<Book> findBooks(String bname) {
        //存储数据
        List<Book> books = new ArrayList<Book>();
        //创建连接引用
        Connection conn = null;
        //创建操作引用
        PreparedStatement ps = null;
        //SQL操作语句
        String sql = "select * from javabook where bname like ?";
        try {
            //进行数据库连接（调用BaseDao中的方法）
            conn = getConnection();
            //创建操作对象
            ps = conn.prepareStatement(sql);
            //处理sql操作参数
            ps.setString(1, "%"+bname+"%");
            //执行 插入修改删除
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Book book = new Book(resultSet.getString("bname"),resultSet.getDouble("bprice"));
                book.setBid(resultSet.getInt("id"));
                books.add(book);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closedAll(conn, ps, null);
        }


        return books;
    }

}

3、Service层

3.1、接口

package service;

import entity.Book;

import java.util.List;

/**
 * @program: Pay.java
 * @ClassName BookService
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 19:20
 * @Version 1.0
 **/

public interface BookService {
    //添加书
    void addBook(Book book);
    //更新书
    void updateBook(Book book);
    //根据书名删除书
    void deleteAllBooks(String bname);
    //模糊查询书
    List<Book> findBooks(String bname);
}

3.2、实现

package service.impl;

import dao.BookDao;
import dao.impl.BookDaoImpl;
import entity.Book;
import service.BookService;

import java.util.List;

/**
 * @program: Pay.java
 * @ClassName BookServiceImpl
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 19:22
 * @Version 1.0
 **/

public class BookServiceImpl implements BookService {
    //面向接口编程
    private BookDao bookDao;

    public BookServiceImpl() {
        bookDao = new BookDaoImpl();
    }

    @Override
    public void addBook(Book book) {
        bookDao.addBook(book);
    }

    @Override
    public void updateBook(Book book) {
        bookDao.updateBook(book);
    }

    @Override
    public void deleteAllBooks(String bname) {
        bookDao.deleteAllBooks(bname);
    }

    @Override
    public List<Book> findBooks(String bname) {
        return bookDao.findBooks(bname);
    }
}

4、测试类

package test;

import entity.Book;
import service.BookService;
import service.impl.BookServiceImpl;

import java.util.List;

/**
 * @program: Pay.java
 * @ClassName Test
 * @description:
 * @author: 苏芮溪
 * @create: 2024−11-12 19:24
 * @Version 1.0
 **/

public class Test {
    public static void main(String[] args) {
        Book book1 = new Book("java入门",12.5);
        Book book2 = new Book("java初级",25);
        Book book3 = new Book("java高级",50);

        //插入三本书
        BookService bs = new BookServiceImpl();
        bs.addBook(book1);
        bs.addBook(book2);
        bs.addBook(book3);

        //用汇编覆盖掉java初级
        Book book4 = new Book("汇编",22.5);
        book4.setBid(2);
        bs.updateBook(book4);

        //通过书名删除
        String bname = "java入门";
        bs.deleteAllBooks(bname);

        //模糊查询书名带有”java“的书
        List<Book> books = bs.findBooks("java");
        for (Book book : books) {
            System.out.println(book);
        }

    }
}