JDBC和GUI实现图书管理系统

1.介绍

        主要通过gui页面实现了与mysql数据库实现交互,跟以前写的图书管理系统相比就是从存在集合中变成了存在数据库中.

下面我来介绍一下主要功能吧:        

1.1.通过基本信息添加图书:

1.2.通过ID修改图书

1.3.通过ID查询图书

1.4.通过ID删除图书

2.构建数据库

2.1建库

create database wjq default charset utf8mb4;

2.2建表

create table books
(
    book_id int auto_increment
        primary key,
    title   varchar(100)   not null,
    author  varchar(100)   not null,
    price   decimal(10, 2) not null
);

3.java代码

主要是代码简单,我就没必要高内聚了,我就全部写在同一个代码了

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class BookManagementSystem extends JFrame {

    private JTextField tfBookID;
    private JTextField tfTitle;
    private JTextField tfAuthor;
    private JTextField tfPrice;
    private JTextArea taResult;

    public BookManagementSystem() {
        setTitle("图书管理系统");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setSize(400, 400);
        setLayout(new FlowLayout());

        JLabel lblBookID = new JLabel("图书ID:");
        tfBookID = new JTextField(10);
        JLabel lblTitle = new JLabel("书名:");
        tfTitle = new JTextField(10);
        JLabel lblAuthor = new JLabel("作者:");
        tfAuthor = new JTextField(10);
        JLabel lblPrice = new JLabel("价格:");
        tfPrice = new JTextField(10);
        JButton btnAdd = new JButton("增加");
        JButton btnDelete = new JButton("删除");
        JButton btnUpdate = new JButton("修改");
        JButton btnSearch = new JButton("查询");
        taResult = new JTextArea(10, 30);
        taResult.setEditable(false);

        add(lblBookID);
        add(tfBookID);
        add(lblTitle);
        add(tfTitle);
        add(lblAuthor);
        add(tfAuthor);
        add(lblPrice);
        add(tfPrice);
        add(btnAdd);
        add(btnDelete);
        add(btnUpdate);
        add(btnSearch);
        add(new JScrollPane(taResult));

        btnAdd.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String bookID = tfBookID.getText();
                String title = tfTitle.getText();
                String author = tfAuthor.getText();
                String price = tfPrice.getText();
                if (!bookID.isEmpty() && !title.isEmpty() && !author.isEmpty() && !price.isEmpty()) {
                    addBook(bookID, title, author, price);
                }
                // 清除文本框内容
                tfBookID.setText("");
                tfTitle.setText("");
                tfAuthor.setText("");
                tfPrice.setText("");
            }
        });

        btnDelete.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String bookID = tfBookID.getText();
                if (!bookID.isEmpty()) {
                    deleteBook(bookID);
                }
                // 清除文本框内容
                tfBookID.setText("");
            }
        });

        btnUpdate.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String bookID = tfBookID.getText();
                String title = tfTitle.getText();
                String author = tfAuthor.getText();
                String price = tfPrice.getText();
                if (!bookID.isEmpty() && !title.isEmpty() && !author.isEmpty() && !price.isEmpty()) {
                    updateBook(bookID, title, author, price);
                }
                // 清除文本框内容
                tfBookID.setText("");
                tfTitle.setText("");
                tfAuthor.setText("");
                tfPrice.setText("");
            }
        });

        btnSearch.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String bookID = tfBookID.getText();
                String title = tfTitle.getText();
                String author = tfAuthor.getText();
                searchBooks(bookID, title, author);
                // 清除文本框内容
                tfBookID.setText("");
                tfTitle.setText("");
                tfAuthor.setText("");
            }
        });
    }

    private void addBook(String bookID, String title, String author, String price) {
        try {
            //注册JDBC的驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wjq", "root", "1234");

            // 插入语句
            String insert = "INSERT INTO books (book_id, title, author, price) VALUES (?, ?, ?, ?)";

            // 创建PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.setString(1, bookID);
            pstmt.setString(2, title);
            pstmt.setString(3, author);
            pstmt.setString(4, price);

            // 执行插入
            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                taResult.setText("添加成功！");
            } else {
                taResult.setText("添加失败！");
            }

            // 关闭连接
            pstmt.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    private void deleteBook(String bookID) {
        try {
            // 连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wjq", "root", "1234");

            // 删除语句
            String delete = "DELETE FROM books WHERE book_id = ?";

            // 创建PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.setString(1, bookID);

            // 执行删除
            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                taResult.setText("删除成功！");
            } else {
                taResult.setText("删除失败！");
            }

            // 关闭连接
            pstmt.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private void updateBook(String bookID, String title, String author, String price) {
        try {
            // 连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wjq", "root", "1234");

            // 更新语句
            String update = "UPDATE books SET title = ?, author = ?, price = ? WHERE book_id = ?";

            // 创建PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.setString(3, price);
            pstmt.setString(4, bookID);

            // 执行更新
            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                taResult.setText("修改成功！");
            } else {
                taResult.setText("修改失败！");
            }

            // 关闭连接
            pstmt.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private void searchBooks(String bookID, String title, String author) {
        try {
            // 连接数据库
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/wjq", "root", "1234");

            // 查询语句
            String query = "SELECT * FROM books WHERE (book_id = ? OR ? IS NULL) AND (title LIKE ? OR ? IS NULL) AND (author LIKE ? OR ? IS NULL)";

            // 创建PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setString(1, bookID);
            pstmt.setString(2, bookID);
            pstmt.setString(3, "%" + title + "%");
            pstmt.setString(4, title);
            pstmt.setString(5, "%" + author + "%");
            pstmt.setString(6, author);

            // 执行查询
            ResultSet rs = pstmt.executeQuery();

            // 处理查询结果
            StringBuilder sb = new StringBuilder();
            while (rs.next()) {
                String result = "图书ID: " + rs.getString("book_id") +
                        "\t书名: " + rs.getString("title") +
                        "\t作者: " + rs.getString("author") +
                        "\t价格: " + rs.getString("price");
                sb.append(result).append("\n");
            }
            if (sb.length() > 0) {
                taResult.setText(sb.toString());
            } else {
                taResult.setText("未找到符合条件的图书！");
            }

            // 关闭连接
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                new BookManagementSystem().setVisible(true);
            }
        });
    }
}

有什么不懂的,错误的欢迎私信和评论区指正和提问,谢谢大家