Java实现图书管理系统mysql
时间: 2023-11-05 17:50:43 浏览: 37
首先,需要安装MySQL数据库和Java开发环境。然后按照以下步骤实现图书管理系统:
1. 创建数据库和表
使用MySQL命令行或任何MySQL客户端创建一个名为“library”的数据库。然后在这个数据库中创建三张表:book、reader和borrow。
book表的结构如下:
```sql
CREATE TABLE book (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
publish_date DATE NOT NULL,
publisher VARCHAR(100) NOT NULL,
price FLOAT NOT NULL,
quantity INT(11) NOT NULL,
PRIMARY KEY (id)
);
```
reader表的结构如下:
```sql
CREATE TABLE reader (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
gender VARCHAR(10) NOT NULL,
birthday DATE NOT NULL,
address VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
);
```
borrow表的结构如下:
```sql
CREATE TABLE borrow (
id INT(11) NOT NULL AUTO_INCREMENT,
book_id INT(11) NOT NULL,
reader_id INT(11) NOT NULL,
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (book_id) REFERENCES book(id),
FOREIGN KEY (reader_id) REFERENCES reader(id)
);
```
2. 创建Java项目
在Java开发环境中创建一个新项目,然后导入MySQL驱动程序。可以使用以下Maven依赖项:
```xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
```
3. 编写Java代码
创建Book、Reader和Borrow类来表示数据库中的三张表。可以使用以下代码作为参考:
```java
public class Book {
private int id;
private String title;
private String author;
private Date publishDate;
private String publisher;
private float price;
private int quantity;
// getters and setters
}
public class Reader {
private int id;
private String name;
private String gender;
private Date birthday;
private String address;
private String phone;
// getters and setters
}
public class Borrow {
private int id;
private int bookId;
private int readerId;
private Date borrowDate;
private Date returnDate;
// getters and setters
}
```
然后创建一个MySQL连接类,该类包含连接数据库的代码和执行SQL语句的方法:
```java
public class MySQLConnection {
private static final String URL = "jdbc:mysql://localhost:3306/library?useSSL=false";
private static final String USERNAME = "root";
private static final String PASSWORD = "yourpassword";
private Connection conn;
public MySQLConnection() throws SQLException {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
public void close() throws SQLException {
if (conn != null) {
conn.close();
}
}
public ResultSet executeQuery(String sql) throws SQLException {
Statement stmt = conn.createStatement();
return stmt.executeQuery(sql);
}
public int executeUpdate(String sql) throws SQLException {
Statement stmt = conn.createStatement();
return stmt.executeUpdate(sql);
}
}
```
最后实现图书管理系统的各个功能,例如添加图书、删除图书、借书、还书等。以下是一个添加图书的示例代码:
```java
public class BookDAO {
private MySQLConnection conn;
public BookDAO() throws SQLException {
conn = new MySQLConnection();
}
public void addBook(Book book) throws SQLException {
String sql = "INSERT INTO book (title, author, publish_date, publisher, price, quantity) VALUES ('"
+ book.getTitle() + "', '"
+ book.getAuthor() + "', '"
+ new java.sql.Date(book.getPublishDate().getTime()) + "', '"
+ book.getPublisher() + "', "
+ book.getPrice() + ", "
+ book.getQuantity() + ")";
conn.executeUpdate(sql);
}
}
```
类似地,可以实现其他功能。