如何利用java和sqlserver建立簡易圖書管理系統

如何利用Java和SQL Server建立簡易圖書管理系統

引言

圖書管理系統是圖書館或書店中用于管理圖書信息、借閱記錄、用戶信息等的重要工具。通過Java和SQL Server的結合，我們可以構建一個簡易的圖書管理系統，實現圖書的增刪改查、借閱歸還等功能。本文將詳細介紹如何使用Java和SQL Server來構建這樣一個系統。

系統需求分析

在開始開發之前，我們需要明確系統的基本需求：

1. 圖書管理：能夠添加、刪除、修改和查詢圖書信息。
2. 用戶管理：能夠添加、刪除、修改和查詢用戶信息。
3. 借閱管理：能夠記錄用戶的借閱和歸還信息。
4. 數據存儲：使用SQL Server作為數據庫，存儲圖書、用戶和借閱記錄。

環境準備

在開始開發之前，我們需要準備好開發環境：

1. Java開發環境：安裝JDK和IDE（如IntelliJ IDEA或Eclipse）。
2. SQL Server：安裝SQL Server數據庫，并創建一個新的數據庫用于存儲圖書管理系統的數據。
3. JDBC驅動：下載并配置SQL Server的JDBC驅動，以便Java程序能夠連接和操作數據庫。

數據庫設計

首先，我們需要設計數據庫表結構。假設我們的系統需要以下三張表：

1. Books表：存儲圖書信息。
2. Users表：存儲用戶信息。
3. BorrowRecords表：存儲借閱記錄。

Books表結構

Users表結構

BorrowRecords表結構

Java程序開發

1. 數據庫連接

首先，我們需要編寫一個類來管理數據庫連接。這個類將負責加載JDBC驅動、建立數據庫連接以及關閉連接。

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

public class DatabaseConnection {
    private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=LibraryDB";
    private static final String USER = "sa";
    private static final String PASSWORD = "your_password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2. 圖書管理功能

接下來，我們編寫一個類來實現圖書的增刪改查功能。

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

public class BookManager {

    public void addBook(Book book) throws SQLException {
        String sql = "INSERT INTO Books (Title, Author, Publisher, PublishDate, ISBN, Status) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, book.getTitle());
            statement.setString(2, book.getAuthor());
            statement.setString(3, book.getPublisher());
            statement.setDate(4, new java.sql.Date(book.getPublishDate().getTime()));
            statement.setString(5, book.getIsbn());
            statement.setString(6, book.getStatus());
            statement.executeUpdate();
        }
    }

    public void deleteBook(int bookId) throws SQLException {
        String sql = "DELETE FROM Books WHERE BookID = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, bookId);
            statement.executeUpdate();
        }
    }

    public void updateBook(Book book) throws SQLException {
        String sql = "UPDATE Books SET Title = ?, Author = ?, Publisher = ?, PublishDate = ?, ISBN = ?, Status = ? WHERE BookID = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, book.getTitle());
            statement.setString(2, book.getAuthor());
            statement.setString(3, book.getPublisher());
            statement.setDate(4, new java.sql.Date(book.getPublishDate().getTime()));
            statement.setString(5, book.getIsbn());
            statement.setString(6, book.getStatus());
            statement.setInt(7, book.getBookId());
            statement.executeUpdate();
        }
    }

    public List<Book> getAllBooks() throws SQLException {
        List<Book> books = new ArrayList<>();
        String sql = "SELECT * FROM Books";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                Book book = new Book();
                book.setBookId(resultSet.getInt("BookID"));
                book.setTitle(resultSet.getString("Title"));
                book.setAuthor(resultSet.getString("Author"));
                book.setPublisher(resultSet.getString("Publisher"));
                book.setPublishDate(resultSet.getDate("PublishDate"));
                book.setIsbn(resultSet.getString("ISBN"));
                book.setStatus(resultSet.getString("Status"));
                books.add(book);
            }
        }
        return books;
    }
}

3. 用戶管理功能

類似地，我們可以編寫一個類來實現用戶的管理功能。

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

public class UserManager {

    public void addUser(User user) throws SQLException {
        String sql = "INSERT INTO Users (Name, Email, Phone) VALUES (?, ?, ?)";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, user.getName());
            statement.setString(2, user.getEmail());
            statement.setString(3, user.getPhone());
            statement.executeUpdate();
        }
    }

    public void deleteUser(int userId) throws SQLException {
        String sql = "DELETE FROM Users WHERE UserID = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, userId);
            statement.executeUpdate();
        }
    }

    public void updateUser(User user) throws SQLException {
        String sql = "UPDATE Users SET Name = ?, Email = ?, Phone = ? WHERE UserID = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, user.getName());
            statement.setString(2, user.getEmail());
            statement.setString(3, user.getPhone());
            statement.setInt(4, user.getUserId());
            statement.executeUpdate();
        }
    }

    public List<User> getAllUsers() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM Users";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                User user = new User();
                user.setUserId(resultSet.getInt("UserID"));
                user.setName(resultSet.getString("Name"));
                user.setEmail(resultSet.getString("Email"));
                user.setPhone(resultSet.getString("Phone"));
                users.add(user);
            }
        }
        return users;
    }
}

4. 借閱管理功能

最后，我們編寫一個類來實現借閱記錄的管理功能。

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

public class BorrowManager {

    public void borrowBook(int userId, int bookId) throws SQLException {
        String sql = "INSERT INTO BorrowRecords (UserID, BookID, BorrowDate) VALUES (?, ?, ?)";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setInt(1, userId);
            statement.setInt(2, bookId);
            statement.setDate(3, new java.sql.Date(System.currentTimeMillis()));
            statement.executeUpdate();
        }
    }

    public void returnBook(int recordId) throws SQLException {
        String sql = "UPDATE BorrowRecords SET ReturnDate = ? WHERE RecordID = ?";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            statement.setInt(2, recordId);
            statement.executeUpdate();
        }
    }

    public List<BorrowRecord> getAllBorrowRecords() throws SQLException {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT * FROM BorrowRecords";
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                BorrowRecord record = new BorrowRecord();
                record.setRecordId(resultSet.getInt("RecordID"));
                record.setUserId(resultSet.getInt("UserID"));
                record.setBookId(resultSet.getInt("BookID"));
                record.setBorrowDate(resultSet.getDate("BorrowDate"));
                record.setReturnDate(resultSet.getDate("ReturnDate"));
                records.add(record);
            }
        }
        return records;
    }
}

系統測試

在完成上述功能后，我們可以編寫一個簡單的測試類來驗證系統的功能是否正常。

public class LibrarySystemTest {
    public static void main(String[] args) {
        try {
            // 測試圖書管理功能
            BookManager bookManager = new BookManager();
            Book book = new Book();
            book.setTitle("Java Programming");
            book.setAuthor("John Doe");
            book.setPublisher("Tech Press");
            book.setPublishDate(new java.util.Date());
            book.setIsbn("1234567890");
            book.setStatus("Available");
            bookManager.addBook(book);

            // 測試用戶管理功能
            UserManager userManager = new UserManager();
            User user = new User();
            user.setName("Alice");
            user.setEmail("alice@example.com");
            user.setPhone("1234567890");
            userManager.addUser(user);

            // 測試借閱管理功能
            BorrowManager borrowManager = new BorrowManager();
            borrowManager.borrowBook(1, 1);

            // 查詢并打印所有圖書、用戶和借閱記錄
            System.out.println("All Books:");
            bookManager.getAllBooks().forEach(System.out::println);

            System.out.println("All Users:");
            userManager.getAllUsers().forEach(System.out::println);

            System.out.println("All Borrow Records:");
            borrowManager.getAllBorrowRecords().forEach(System.out::println);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

總結

通過本文的介紹，我們學習了如何使用Java和SQL Server構建一個簡易的圖書管理系統。該系統實現了圖書、用戶和借閱記錄的管理功能，并通過JDBC與SQL Server數據庫進行交互。雖然這個系統還比較簡單，但它為后續的功能擴展和優化提供了基礎。希望本文能對你理解和開發類似的系統有所幫助。