写一个Java程序,创建一个名为Books的数据库,并在其中建立一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格。编写一个应用程序,用预处理方式在该数据库中实现增加、删除和修改数据以及查询的基本功能。
时间: 2024-02-17 09:04:51 浏览: 36
好的,以下是一个简单的Java程序,用于创建名为Books的数据库,并在其中创建一个名为book_message的表,同时实现增加、删除和修改数据以及查询的基本功能:
```java
import java.sql.*;
public class BooksDatabase {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/Books";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 创建表
String createSql = "CREATE TABLE book_message " +
"(id INTEGER not NULL AUTO_INCREMENT, " +
" book_name VARCHAR(255), " +
" author VARCHAR(255), " +
" publisher VARCHAR(255), " +
" publish_time DATE, " +
" price FLOAT, " +
" PRIMARY KEY ( id ))";
pstmt = conn.prepareStatement(createSql);
pstmt.executeUpdate();
System.out.println("Created table in given database...");
// 增加数据
String addSql = "INSERT INTO book_message (book_name, author, publisher, publish_time, price) " +
"VALUES (?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(addSql);
pstmt.setString(1, "The Da Vinci Code");
pstmt.setString(2, "Dan Brown");
pstmt.setString(3, "Doubleday");
pstmt.setDate(4, Date.valueOf("2003-03-18"));
pstmt.setFloat(5, 25.00f);
pstmt.executeUpdate();
// 查询数据
String querySql = "SELECT * FROM book_message";
pstmt = conn.prepareStatement(querySql);
ResultSet rs = pstmt.executeQuery();
// 输出查询结果
while (rs.next()) {
int id = rs.getInt("id");
String bookName = rs.getString("book_name");
String author = rs.getString("author");
String publisher = rs.getString("publisher");
Date publishTime = rs.getDate("publish_time");
float price = rs.getFloat("price");
System.out.print("id: " + id);
System.out.print(", book name: " + bookName);
System.out.print(", author: " + author);
System.out.print(", publisher: " + publisher);
System.out.print(", publish time: " + publishTime);
System.out.println(", price: " + price);
}
// 删除数据
String deleteSql = "DELETE FROM book_message WHERE book_name = ?";
pstmt = conn.prepareStatement(deleteSql);
pstmt.setString(1, "The Da Vinci Code");
pstmt.executeUpdate();
// 修改数据
String updateSql = "UPDATE book_message SET price = ? WHERE book_name = ?";
pstmt = conn.prepareStatement(updateSql);
pstmt.setFloat(1, 20.00f);
pstmt.setString(2, "The Da Vinci Code");
pstmt.executeUpdate();
rs.close();
pstmt.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
```
请注意,此程序使用MySQL作为数据库,因此您需要安装MySQL并在本地运行它。您还需要将程序中的用户名和密码替换为您自己的MySQL用户名和密码。
此程序使用预处理方式避免SQL注入攻击。如果您想更详细地了解预处理语句的工作原理,请参阅Java官方文档。
阅读全文