java创建一个名为Books的数据库,并在其中建立一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格。编写一个应用程序,用预处理方式在该数据库中实现增加、删除和修改数据以及查询的基本功能算法描述
时间: 2024-03-22 18:37:42 浏览: 70
java数据库编程,对数据库的一系列操作
首先,我们需要使用 JDBC 连接到 MySQL 数据库。以下是建立连接的示例代码:
```java
import java.sql.*;
public class BooksDatabase {
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/Books";
private static final String USER = "root";
private static final String PASS = "password";
private static Connection conn = null;
private static PreparedStatement stmt = null;
public static void main(String[] args) {
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected to database successfully!");
} catch (ClassNotFoundException e) {
System.err.println("Could not find JDBC driver");
e.printStackTrace();
} catch (SQLException e) {
System.err.println("Could not connect to database");
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
```
接下来,我们需要创建一个名为 book_message 的表,包括书名、作者、出版社、出版时间和价格这 5 个字段。以下是创建表的示例代码:
```java
try {
stmt = conn.prepareStatement("CREATE TABLE book_message ("
+ "book_name VARCHAR(255),"
+ "author VARCHAR(255),"
+ "publisher VARCHAR(255),"
+ "publish_date DATE,"
+ "price DECIMAL(10,2))");
stmt.executeUpdate();
System.out.println("Table created successfully!");
} catch (SQLException e) {
System.err.println("Could not create table");
e.printStackTrace();
}
```
接下来,我们将编写增加、删除和修改数据以及查询的基本功能。以下是增加数据的示例代码:
```java
try {
stmt = conn.prepareStatement("INSERT INTO book_message "
+ "(book_name, author, publisher, publish_date, price) "
+ "VALUES (?, ?, ?, ?, ?)");
stmt.setString(1, "Java核心技术");
stmt.setString(2, "Cay S. Horstmann");
stmt.setString(3, "机械工业出版社");
stmt.setDate(4, Date.valueOf("2020-01-01"));
stmt.setDouble(5, 89.00);
stmt.executeUpdate();
System.out.println("Data inserted successfully!");
} catch (SQLException e) {
System.err.println("Could not insert data");
e.printStackTrace();
}
```
以下是删除数据的示例代码:
```java
try {
stmt = conn.prepareStatement("DELETE FROM book_message WHERE book_name = ?");
stmt.setString(1, "Java核心技术");
stmt.executeUpdate();
System.out.println("Data deleted successfully!");
} catch (SQLException e) {
System.err.println("Could not delete data");
e.printStackTrace();
}
```
以下是修改数据的示例代码:
```java
try {
stmt = conn.prepareStatement("UPDATE book_message SET price = ? WHERE book_name = ?");
stmt.setDouble(1, 99.00);
stmt.setString(2, "Java核心技术");
stmt.executeUpdate();
System.out.println("Data updated successfully!");
} catch (SQLException e) {
System.err.println("Could not update data");
e.printStackTrace();
}
```
以下是查询数据的示例代码:
```java
try {
stmt = conn.prepareStatement("SELECT * FROM book_message");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getString("book_name") + "\t");
System.out.print(rs.getString("author") + "\t");
System.out.print(rs.getString("publisher") + "\t");
System.out.print(rs.getDate("publish_date") + "\t");
System.out.print(rs.getDouble("price") + "\n");
}
} catch (SQLException e) {
System.err.println("Could not query data");
e.printStackTrace();
}
```
以上就是实现增加、删除和修改数据以及查询的基本功能的完整代码。需要注意的是,在使用 PreparedStatement 执行 SQL 语句时,应该避免使用字符串拼接,而应该使用占位符和设置参数的方式,以避免 SQL 注入攻击。
阅读全文