Java创建一个名为Books的数据库,并在其中建立一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格。编写一个应用程序,用预处理方式在该数据库中实现增加、删除和修改数据以及查询的基本功能。
时间: 2024-03-10 16:49:48 浏览: 117
好的,我会为您解答这个问题。
首先,我们可以使用Java语言来创建一个名为Books的数据库,可以使用以下代码:
```java
import java.sql.*;
public class CreateDatabase {
static final String DB_URL = "jdbc:mysql://localhost/";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("连接到数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("创建数据库...");
stmt = conn.createStatement();
String sql = "CREATE DATABASE Books";
stmt.executeUpdate(sql);
System.out.println("数据库已创建...");
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
```
接下来,我们可以在该数据库中创建一个名为book_message的表,字段包括书名、作者、出版社、出版时间和价格,可以使用以下代码:
```java
import java.sql.*;
public class CreateTable {
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;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("连接到数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("创建表...");
stmt = conn.createStatement();
String sql = "CREATE TABLE book_message " +
"(book_id INT not NULL AUTO_INCREMENT, " +
" book_name VARCHAR(255) not NULL, " +
" author VARCHAR(255) not NULL, " +
" publisher VARCHAR(255) not NULL, " +
" publication_date DATE not NULL, " +
" price DECIMAL(10,2) not NULL, " +
" PRIMARY KEY ( book_id ))";
stmt.executeUpdate(sql);
System.out.println("表已创建...");
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
```
现在,我们已经成功创建了一个名为Books的数据库和一个名为book_message的表。
接下来,我们可以编写一个应用程序来实现增加、删除和修改数据以及查询的基本功能,可以使用以下代码:
```java
import java.sql.*;
public class DatabaseExample {
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;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("连接到数据库...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// 增加数据
System.out.println("增加数据...");
String insertSql = "INSERT INTO book_message (book_name, author, publisher, publication_date, price) VALUES (?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(insertSql);
pstmt.setString(1, "The Great Gatsby");
pstmt.setString(2, "F. Scott Fitzgerald");
pstmt.setString(3, "Scribner");
pstmt.setDate(4, Date.valueOf("1925-04-10"));
pstmt.setDouble(5, 22.50);
pstmt.executeUpdate();
System.out.println("数据已增加...");
// 删除数据
System.out.println("删除数据...");
String deleteSql = "DELETE FROM book_message WHERE book_id=?";
pstmt = conn.prepareStatement(deleteSql);
pstmt.setInt(1, 1);
pstmt.executeUpdate();
System.out.println("数据已删除...");
// 修改数据
System.out.println("修改数据...");
String updateSql = "UPDATE book_message SET price=? WHERE book_name=?";
pstmt = conn.prepareStatement(updateSql);
pstmt.setDouble(1, 25.00);
pstmt.setString(2, "The Great Gatsby");
pstmt.executeUpdate();
System.out.println("数据已修改...");
// 查询数据
System.out.println("查询数据...");
String selectSql = "SELECT * FROM book_message";
pstmt = conn.prepareStatement(selectSql);
rs = pstmt.executeQuery();
while (rs.next()) {
int bookId = rs.getInt("book_id");
String bookName = rs.getString("book_name");
String author = rs.getString("author");
String publisher = rs.getString("publisher");
Date publicationDate = rs.getDate("publication_date");
double price = rs.getDouble("price");
System.out.print("book_id: " + bookId);
System.out.print(", book_name: " + bookName);
System.out.print(", author: " + author);
System.out.print(", publisher: " + publisher);
System.out.print(", publication_date: " + publicationDate);
System.out.println(", price: $" + price);
}
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
} catch (SQLException se1) {
}
try {
if (pstmt != null)
pstmt.close();
} catch (SQLException se2) {
}
try {
if (conn != null)
conn.close();
} catch (SQLException se3) {
se3.printStackTrace();
}
}
}
}
```
上述代码使用预处理方式执行了增加、删除和修改数据以及查询的基本功能。
注意:在实际应用中,应该对输入的数据进行过滤和验证,防止SQL注入等安全问题。
阅读全文