java创建一个名为Books的数据库,并在其中建立一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格。编写一个应用程序,实现对该数据库中数据的添加、修改、删除和查询功能。
时间: 2024-12-17 18:37:38 浏览: 12
在Java中,为了创建一个名为Books的数据库和一个名为book_message的数据表,我们通常会使用JDBC(Java Database Connectivity)API。这是一个连接Java应用程序与SQL数据库的标准API。下面是一个简单的步骤概述:
首先,你需要设置JDBC驱动并连接到数据库。假设你使用的是MySQL:
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCDriver {
private static final String URL = "jdbc:mysql://localhost:3306/Books";
private static final String USER = "username";
private static final String PASS = "password";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASS);
}
}
```
然后,你可以创建`book_message`表的SQL脚本:
```sql
CREATE TABLE book_message (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255),
publisher VARCHAR(255),
publication_date DATE,
price DECIMAL(10, 2)
);
```
接下来,你可以编写四个方法分别对应添加、修改、删除和查询操作:
1. 添加(Insertion):
```java
public void addBook(Book book) throws SQLException {
String sql = "INSERT INTO book_message (title, author, publisher, publication_date, price) VALUES (?, ?, ?, ?, ?)";
try (Connection conn = JDBCDriver.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, book.getTitle());
pstmt.setString(2, book.getAuthor());
pstmt.setString(3, book.getPublisher());
pstmt.setDate(4, new java.sql.Date(book.getPublicationDate().getTime()));
pstmt.setDouble(5, book.getPrice());
pstmt.executeUpdate();
}
}
```
2. 修改(Update):
```java
public void updateBook(Book updatedBook) throws SQLException {
String sql = "UPDATE book_message SET author=?, publisher=?, publication_date=?, price=? WHERE id=?";
try (Connection conn = JDBCDriver.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, updatedBook.getAuthor());
pstmt.setString(2, updatedBook.getPublisher());
pstmt.setDate(3, new java.sql.Date(updatedBook.getPublicationDate().getTime()));
pstmt.setDouble(4, updatedBook.getPrice());
pstmt.setInt(5, updatedBook.getId());
pstmt.executeUpdate();
}
}
```
3. 删除(Deletion):
```java
public void deleteBook(int id) throws SQLException {
String sql = "DELETE FROM book_message WHERE id=?";
try (Connection conn = JDBCDriver.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
pstmt.executeUpdate();
}
}
```
4. 查询(Querying):
```java
public List<Book> getAllBooks() throws SQLException {
List<Book> books = new ArrayList<>();
String sql = "SELECT * FROM book_message";
try (Connection conn = JDBCDriver.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
int id = rs.getInt("id");
String title = rs.getString("title");
// ... 从结果集中获取其他字段并构造Book对象
books.add(new Book(id, title)); // 假设Book有getter方法
}
}
return books;
}
```
这只是一个基础示例,实际应用中需要处理异常,以及使用PreparedStatement来防止SQL注入等问题。
阅读全文