How to develop a simple online library system using MySQL and Java

How to use MySQL and Java to develop a simple online library system

Introduction:
With the popularity and development of the Internet, the online library system has It has become an important part of modern library services. By utilizing the MySQL database and the Java programming language, we can develop a simple yet powerful online library system. This article will introduce in detail how to build and implement an online library system based on MySQL and Java, and provide relevant code examples.

Step One: Database Design
First, we need to design a suitable database schema to store the data of the library system. The following is a simple database schema example:

1. Books table (books)

   • Book ID (book_id)
   • Book name (title )
   • Author(author)
   • Publication date(publication_date)
   • Borrowing status(status)

2. Reader list (readers)

   • Reader ID (reader_id)
   • Reader name (name)
   • Phone number (phone_number)
   • Email (email)

3. Borrowing record table (borrow_records)

   • Borrowing record ID (record_id)
   • Book ID (book_id)
   • Reader ID (reader_id)
   • Borrowing date (borrow_date)
   • Return date (return_date)

The above is just a simple Database schema example, there may be more tables and fields in reality. It can be adjusted and expanded according to actual needs.

Step 2: Database connection and data operation
Next, we need to connect to the database through Java code and implement operations on the database. The following is a sample code for using Java JDBC to connect to a MySQL database:

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

public class DBConnection {
   private static final String url = "jdbc:mysql://localhost:3306/library_system";
   private static final String user = "root";
   private static final String password = "password";
   private static Connection conn = null;
   private static Statement stmt = null;
      
   public static Connection getConnection() {
      try {
         Class.forName("com.mysql.jdbc.Driver");
         conn = DriverManager.getConnection(url, user, password);
      } catch (ClassNotFoundException | SQLException e) {
         e.printStackTrace();
      }
      return conn;
   }
   
   public static ResultSet executeQuery(String query) {
      ResultSet rs = null;
      try {
         stmt = getConnection().createStatement();
         rs = stmt.executeQuery(query);
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return rs;
   }
   
   public static void executeUpdate(String query) {
      try {
         stmt = getConnection().createStatement();
         stmt.executeUpdate(query);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

In the above code, we define a DBConnection class, which contains two static methods getConnection and executeQuery and a static method executeUpdate. Through these methods, we can connect to the database and perform query and update operations.

Step 3: Implement library system functions
With the foundation of database connection and data operations, we can start to implement the functions of the online library system. The following is a simple sample code that implements the borrowing and returning functions of books:

import java.sql.ResultSet;
import java.sql.SQLException;

public class LibrarySystem {
   public static void main(String[] args) {
      borrowBook(1, 1); // 借阅书籍ID为1的书籍，读者ID为1的读者
      returnBook(1, 1); // 归还书籍ID为1的书籍，读者ID为1的读者
   }
   
   public static void borrowBook(int bookId, int readerId) {
      // 更新借阅记录表
      String borrowRecordQuery = "INSERT INTO borrow_records (book_id, reader_id, borrow_date) " +
                                 "VALUES (" + bookId + ", " + readerId + ", NOW())";
      DBConnection.executeUpdate(borrowRecordQuery);
      
      // 更新书籍表的借阅状态
      String updateBookStatusQuery = "UPDATE books SET status = '借出' WHERE book_id = " + bookId;
      DBConnection.executeUpdate(updateBookStatusQuery);
      
      System.out.println("书籍ID " + bookId + " 成功借阅给读者ID " + readerId);
   }
   
   public static void returnBook(int bookId, int readerId) {
      // 更新借阅记录表的归还日期
      String returnDateQuery = "UPDATE borrow_records SET return_date = NOW() " +
                               "WHERE book_id = " + bookId + " AND reader_id = " + readerId;
      DBConnection.executeUpdate(returnDateQuery);
      
      // 更新书籍表的借阅状态
      String updateBookStatusQuery = "UPDATE books SET status = '可借' WHERE book_id = " + bookId;
      DBConnection.executeUpdate(updateBookStatusQuery);
      
      System.out.println("书籍ID " + bookId + " 已成功归还");
   }
}

In the above code, we perform query and update operations by calling methods in the DBConnection class. The borrowBook and returnBook methods implement the borrowing and returning functions respectively, and print relevant information on the console.

Conclusion:
Through the combination of MySQL database and Java programming language, we can easily develop a simple online library system. Through reasonable database design and writing corresponding Java code, we can realize the borrowing and returning functions of books. Of course, the above example code is just a simple example. In actual situations, there may be more complex requirements, which need to be adjusted and expanded according to specific application scenarios.

Reference link:
https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

https://www.mysqltutorial.org/

The above is the detailed content of How to develop a simple online library system using MySQL and Java. For more information, please follow other related articles on the PHP Chinese website!