利用三层结构+JDBC开发一个简单的图书管理系统

好的，下面是一个简单的图书管理系统的示例代码，采用三层结构和JDBC技术实现。

首先，我们需要创建一个数据库，并且创建一个名为books的表，用于存储图书信息。表的结构如下：

CREATE TABLE books (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  author VARCHAR(50) NOT NULL,
  price DOUBLE NOT NULL,
  PRIMARY KEY (id)
);

接下来，我们需要编写对数据库的基本操作，包括连接数据库、关闭数据库、查询图书信息、添加图书信息、修改图书信息和删除图书信息等。

下面是一个简单的JDBC工具类，用于连接数据库和执行SQL语句：

package com.example.bookstore.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCUtil {
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://localhost:3306/bookstore";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER_CLASS);
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void closeConnection(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();
            }
        }
    }
}

然后，我们需要编写一个Book类，用于封装图书信息：

package com.example.bookstore.model;

public class Book {
    private int id;
    private String name;
    private String author;
    private double price;
    
    public Book() {}
    
    public Book(int id, String name, String author, double price) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.price = price;
    }
    
    // 省略getter和setter方法
}

接下来，我们需要编写一个BookDAO类，用于实现对图书信息的增删改查操作：

package com.example.bookstore.dao;

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 com.example.bookstore.model.Book;
import com.example.bookstore.util.JDBCUtil;

public class BookDAO {
    public void addBook(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "INSERT INTO books (name, author, price) VALUES (?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, book.getName());
            ps.setString(2, book.getAuthor());
            ps.setDouble(3, book.getPrice());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps, null);
        }
    }
    
    public void deleteBook(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "DELETE FROM books WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps, null);
        }
    }
    
    public void updateBook(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "UPDATE books SET name = ?, author = ?, price = ? WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, book.getName());
            ps.setString(2, book.getAuthor());
            ps.setDouble(3, book.getPrice());
            ps.setInt(4, book.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps, null);
        }
    }
    
    public List<Book> listBooks() {
        List<Book> books = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM books";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                Book book = new Book(id, name, author, price);
                books.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps, rs);
        }
        return books;
    }
    
    public Book getBook(int id) {
        Book book = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "SELECT * FROM books WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                book = new Book(id, name, author, price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(conn, ps, rs);
        }
        return book;
    }
}

最后，我们可以编写一个简单的控制台程序，用于测试BookDAO类的功能：

package com.example.bookstore;

import java.util.List;
import java.util.Scanner;

import com.example.bookstore.dao.BookDAO;
import com.example.bookstore.model.Book;

public class BookStore {
    public static void main(String[] args) {
        BookDAO bookDAO = new BookDAO();
        Scanner scanner = new Scanner(System.in);
        while (true) {
            System.out.println("请选择操作：");
            System.out.println("1. 添加图书");
            System.out.println("2. 删除图书");
            System.out.println("3. 修改图书");
            System.out.println("4. 查询图书");
            System.out.println("5. 退出程序");
            String choice = scanner.nextLine();
            switch (choice) {
            case "1":
                System.out.println("请输入图书名称：");
                String name = scanner.nextLine();
                System.out.println("请输入图书作者：");
                String author = scanner.nextLine();
                System.out.println("请输入图书价格：");
                double price = scanner.nextDouble();
                scanner.nextLine();
                Book book = new Book(0, name, author, price);
                bookDAO.addBook(book);
                System.out.println("添加成功！");
                break;
            case "2":
                System.out.println("请输入图书ID：");
                int id = scanner.nextInt();
                scanner.nextLine();
                bookDAO.deleteBook(id);
                System.out.println("删除成功！");
                break;
            case "3":
                System.out.println("请输入图书ID：");
                int id2 = scanner.nextInt();
                scanner.nextLine();
                Book book2 = bookDAO.getBook(id2);
                if (book2 == null) {
                    System.out.println("图书不存在！");
                } else {
                    System.out.println("请输入图书名称（原名称：" + book2.getName() + "）：");
                    String name2 = scanner.nextLine();
                    System.out.println("请输入图书作者（原作者：" + book2.getAuthor() + "）：");
                    String author2 = scanner.nextLine();
                    System.out.println("请输入图书价格（原价格：" + book2.getPrice() + "）：");
                    double price2 = scanner.nextDouble();
                    scanner.nextLine();
                    book2.setName(name2);
                    book2.setAuthor(author2);
                    book2.setPrice(price2);
                    bookDAO.updateBook(book2);
                    System.out.println("修改成功！");
                }
                break;
            case "4":
                List<Book> books = bookDAO.listBooks();
                if (books.isEmpty()) {
                    System.out.println("暂无图书信息！");
                } else {
                    for (Book b : books) {
                        System.out.println(b.getId() + " " + b.getName() + " " + b.getAuthor() + " " + b.getPrice());
                    }
                }
                break;
            case "5":
                scanner.close();
                System.exit(0);
                break;
            default:
                System.out.println("输入有误，请重新输入！");
                break;
            }
        }
    }
}

以上就是一个简单的图书管理系统的示例代码，采用了三层结构和JDBC技术实现。