java创建一个名为Books的数据库,并在其中建立一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格。编写一个应用程序,用预处理方式在该数据库中实现增加、删除和修改数据以及查询的基本功能程序分析
时间: 2024-03-22 09:37:54 浏览: 22
首先,我们需要安装并配置好 Java 的 JDBC 驱动程序,以便能够连接到 MySQL 数据库。
然后,我们可以编写一个 Java 应用程序,通过 JDBC 驱动程序连接到 MySQL 数据库,并创建一个名为 "Books" 的数据库。接着,在该数据库中创建一个名为 "book_message" 的表,字段包括 "书名"、"作者"、"出版社"、"出版时间" 和 "价格"。
接下来,我们可以通过预处理方式实现增加、删除和修改数据以及查询的基本功能。例如,可以使用 PreparedStatement 对象来执行 SQL 语句,并通过参数占位符来防止 SQL 注入攻击。具体实现步骤如下:
1. 建立数据库连接
```java
String url = "jdbc:mysql://localhost:3306/Books?useSSL=false";
String user = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, user, password);
```
2. 创建表
```java
String sql = "CREATE TABLE book_message (" +
"id INT NOT NULL AUTO_INCREMENT," +
"name VARCHAR(100) NOT NULL," +
"author VARCHAR(100) NOT NULL," +
"publisher VARCHAR(100) NOT NULL," +
"publish_time DATE NOT NULL," +
"price DECIMAL(10,2) NOT NULL," +
"PRIMARY KEY (id))";
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
```
3. 插入数据
```java
String sql = "INSERT INTO book_message (name, author, publisher, publish_time, price) " +
"VALUES (?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Java编程思想");
pstmt.setString(2, "Bruce Eckel");
pstmt.setString(3, "机械工业出版社");
pstmt.setDate(4, Date.valueOf("2006-06-20"));
pstmt.setBigDecimal(5, new BigDecimal("89.00"));
pstmt.executeUpdate();
```
4. 删除数据
```java
String sql = "DELETE FROM book_message WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 1);
pstmt.executeUpdate();
```
5. 修改数据
```java
String sql = "UPDATE book_message SET price = ? WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setBigDecimal(1, new BigDecimal("99.00"));
pstmt.setInt(2, 2);
pstmt.executeUpdate();
```
6. 查询数据
```java
String sql = "SELECT * FROM book_message WHERE author = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Bruce Eckel");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String author = rs.getString("author");
String publisher = rs.getString("publisher");
Date publishTime = rs.getDate("publish_time");
BigDecimal price = rs.getBigDecimal("price");
System.out.println(id + ", " + name + ", " + author + ", " + publisher + ", " + publishTime + ", " + price);
}
```
注意,以上代码仅为示例,实际应用程序中需要进行适当的异常处理、资源释放等操作。