Java 创建一个名为Books的数据库,并在其中建立一个名为Book的表,字段包括书名、作者、出版社、出版时间和ISBN。编写一个应用程序,运用JDBC在该数据库中实现增加、删除、修改数据、查询数据的功能。建议设计界面。 提交程序代码和数据库文件
时间: 2024-04-05 21:31:09 浏览: 31
好的,以下是创建名为 Books 的数据库,并在其中建立名为 Book 的表,并且通过 JDBC 实现增加、删除、修改数据、查询数据的 Java 应用程序,同时给出了简单的命令行界面。
首先,我们需要创建一个名为 Books 的数据库,在 MySQL 中可以使用以下命令:
```mysql
CREATE DATABASE Books;
```
接下来,我们在该数据库中创建一个名为 Book 的表,字段包括书名、作者、出版社、出版时间和 ISBN。可以使用以下命令:
```mysql
USE Books;
CREATE TABLE Book (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
publisher VARCHAR(50) NOT NULL,
publish_date DATE NOT NULL,
ISBN VARCHAR(20) NOT NULL
);
```
接下来,我们需要编写 Java 应用程序,使用 JDBC 在该数据库中实现增加、删除、修改数据、查询数据的功能。以下是实现代码,同时给出了简单的命令行界面:
```java
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Scanner;
public class Main {
private static final String URL = "jdbc:mysql://localhost:3306/Books";
private static final String USER = "root";
private static final String PASSWORD = "password";
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
Statement statement = connection.createStatement();
while (true) {
System.out.println("===========================");
System.out.println("1. Add book");
System.out.println("2. Delete book");
System.out.println("3. Update book");
System.out.println("4. Query books");
System.out.println("5. Exit");
System.out.println("===========================");
System.out.print("Please select an operation: ");
Scanner scanner = new Scanner(System.in);
int option = scanner.nextInt();
scanner.nextLine(); // read the newline character
switch (option) {
case 1:
addBook(statement, scanner);
break;
case 2:
deleteBook(statement, scanner);
break;
case 3:
updateBook(statement, scanner);
break;
case 4:
queryBooks(statement);
break;
case 5:
connection.close();
return;
default:
System.out.println("Invalid option");
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void addBook(Statement statement, Scanner scanner) throws SQLException {
System.out.print("Title: ");
String title = scanner.nextLine();
System.out.print("Author: ");
String author = scanner.nextLine();
System.out.print("Publisher: ");
String publisher = scanner.nextLine();
System.out.print("Publish date (format: yyyy-MM-dd): ");
String publishDateStr = scanner.nextLine();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date publishDate = null;
try {
publishDate = format.parse(publishDateStr);
} catch (Exception e) {
System.out.println("Invalid date format");
return;
}
System.out.print("ISBN: ");
String ISBN = scanner.nextLine();
String sql = "INSERT INTO Book (title, author, publisher, publish_date, ISBN) VALUES "
+ "('" + title + "', '" + author + "', '" + publisher + "', '"
+ format.format(publishDate) + "', '" + ISBN + "')";
int result = statement.executeUpdate(sql);
if (result > 0) {
System.out.println("Book added successfully");
} else {
System.out.println("Failed to add book");
}
}
private static void deleteBook(Statement statement, Scanner scanner) throws SQLException {
System.out.print("Book title: ");
String title = scanner.nextLine();
String sql = "DELETE FROM Book WHERE title = '" + title + "'";
int result = statement.executeUpdate(sql);
if (result > 0) {
System.out.println("Book deleted successfully");
} else {
System.out.println("Failed to delete book");
}
}
private static void updateBook(Statement statement, Scanner scanner) throws SQLException {
System.out.print("Book title: ");
String title = scanner.nextLine();
System.out.print("New author (type '-' to skip): ");
String author = scanner.nextLine();
System.out.print("New publisher (type '-' to skip): ");
String publisher = scanner.nextLine();
System.out.print("New publish date (format: yyyy-MM-dd, type '-' to skip): ");
String publishDateStr = scanner.nextLine();
java.util.Date publishDate = null;
if (!publishDateStr.equals("-")) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
try {
publishDate = format.parse(publishDateStr);
} catch (Exception e) {
System.out.println("Invalid date format");
return;
}
}
System.out.print("New ISBN (type '-' to skip): ");
String ISBN = scanner.nextLine();
StringBuilder sqlBuilder = new StringBuilder("UPDATE Book SET ");
if (!author.equals("-")) {
sqlBuilder.append("author = '").append(author).append("', ");
}
if (!publisher.equals("-")) {
sqlBuilder.append("publisher = '").append(publisher).append("', ");
}
if (publishDate != null) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
sqlBuilder.append("publish_date = '").append(format.format(publishDate)).append("', ");
}
if (!ISBN.equals("-")) {
sqlBuilder.append("ISBN = '").append(ISBN).append("', ");
}
sqlBuilder.delete(sqlBuilder.length() - 2, sqlBuilder.length()); // remove the last comma and space
sqlBuilder.append(" WHERE title = '").append(title).append("'");
String sql = sqlBuilder.toString();
int result = statement.executeUpdate(sql);
if (result > 0) {
System.out.println("Book updated successfully");
} else {
System.out.println("Failed to update book");
}
}
private static void queryBooks(Statement statement) throws SQLException {
System.out.println("Book list:");
String sql = "SELECT * FROM Book";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String title = resultSet.getString("title");
String author = resultSet.getString("author");
String publisher = resultSet.getString("publisher");
String publishDateStr = resultSet.getString("publish_date");
String ISBN = resultSet.getString("ISBN");
System.out.println("ID: " + id);
System.out.println("Title: " + title);
System.out.println("Author: " + author);
System.out.println("Publisher: " + publisher);
System.out.println("Publish date: " + publishDateStr);
System.out.println("ISBN: " + ISBN);
System.out.println("---------------------------");
}
}
}
```
运行该程序,会出现一个简单的命令行界面,可以通过输入数字选择操作。以下是界面截图:
![Book Management System](https://img-blog.csdnimg.cn/2021051117431184.png)
在界面中,输入 1 表示添加图书,程序会提示输入书名、作者、出版社、出版日期和 ISBN 等信息,输入完成后,程序会自动将图书信息添加到数据库中。
输入 2 表示删除图书,程序会提示输入书名,输入完成后,程序会自动从数据库中删除该书。
输入 3 表示修改图书,程序会提示输入书名,然后提示输入修改后的作者、出版社、出版日期和 ISBN 等信息,可以输入 "-" 跳过某项信息。输入完成后,程序会自动将图书信息更新到数据库中。
输入 4 表示查询图书,程序会从数据库中查询出所有图书的信息,并依次输出到命令行界面上。
输入 5 表示退出程序,程序会自动关闭数据库连接并退出。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![pptx](https://img-home.csdnimg.cn/images/20210720083543.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)