java和数据库实现 (1)输出所有图书的信息:包括每本图书的图书编号、图书名称、图书单价和库存数量。 (2)顾客购买图书:根据提示输入图书编号来购买图书,并根据提示输入购买图书数量(用户必须连续购书三次)。 (3)购书完毕后输出顾客的订单信息:包括订单号、订单明细、订单总额和订单日期。 (4)购书完毕后,将订单信息及订单详细信息分别插入Orders表和Ordre_detail表。
时间: 2024-01-18 15:02:28 浏览: 25
实现上述需求,需要编写Java代码和使用数据库进行交互。以下是代码示例:
1. 输出所有图书信息的代码:
```
import java.sql.*;
public class BookInfo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/bookstore";
String user = "root";
String password = "123456";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
String sql = "SELECT book_id, book_name, book_price, book_stock FROM book";
rs = stmt.executeQuery(sql);
while (rs.next()) {
int book_id = rs.getInt("book_id");
String book_name = rs.getString("book_name");
double book_price = rs.getDouble("book_price");
int book_stock = rs.getInt("book_stock");
System.out.println("图书编号:" + book_id + ",图书名称:" + book_name + ",图书单价:" + book_price + ",库存数量:" + book_stock);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
2. 购买图书的代码:
```
import java.sql.*;
import java.util.Scanner;
public class BuyBooks {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/bookstore";
String user = "root";
String password = "123456";
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
ResultSet rs = null;
Scanner sc = new Scanner(System.in);
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
String sql1 = "SELECT book_id, book_name, book_price, book_stock FROM book WHERE book_id = ?";
String sql2 = "UPDATE book SET book_stock = book_stock - ? WHERE book_id = ?";
pstmt1 = conn.prepareStatement(sql1);
pstmt2 = conn.prepareStatement(sql2);
int total = 0;
for (int i = 0; i < 3; i++) {
System.out.print("请输入要购买的图书编号:");
int book_id = sc.nextInt();
System.out.print("请输入要购买的数量:");
int quantity = sc.nextInt();
pstmt1.setInt(1, book_id);
rs = pstmt1.executeQuery();
if (rs.next()) {
int stock = rs.getInt("book_stock");
if (quantity > stock) {
System.out.println("库存不足!");
} else {
double price = rs.getDouble("book_price");
total += price * quantity;
pstmt2.setInt(1, quantity);
pstmt2.setInt(2, book_id);
pstmt2.executeUpdate();
System.out.println("购买成功!");
}
} else {
System.out.println("该图书不存在!");
}
}
conn.commit();
System.out.println("订单总额:" + total);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt1 != null) {
pstmt1.close();
}
if (pstmt2 != null) {
pstmt2.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
3. 输出订单信息的代码:
```
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
public class OrderInfo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/bookstore";
String user = "root";
String password = "123456";
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
pstmt1 = conn.prepareStatement("INSERT INTO orders(order_id, order_date, order_total) VALUES (?, ?, ?)");
pstmt2 = conn.prepareStatement("INSERT INTO order_detail(order_id, book_id, quantity, amount) VALUES (?, ?, ?, ?)");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String dateStr = sdf.format(new Date());
pstmt1.setInt(1, 1);
pstmt1.setString(2, dateStr);
pstmt1.setDouble(3, 0);
pstmt1.executeUpdate();
double total = 0;
rs = pstmt1.getGeneratedKeys();
if (rs.next()) {
int order_id = rs.getInt(1);
for (int i = 0; i < 3; i++) {
pstmt2.setInt(1, order_id);
System.out.print("请输入第" + (i+1) + "本图书编号:");
int book_id = new Scanner(System.in).nextInt();
pstmt2.setInt(2, book_id);
System.out.print("请输入第" + (i+1) + "本图书数量:");
int quantity = new Scanner(System.in).nextInt();
pstmt2.setInt(3, quantity);
String sql = "SELECT book_price FROM book WHERE book_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, book_id);
ResultSet rs1 = pstmt.executeQuery();
if (rs1.next()) {
double price = rs1.getDouble("book_price");
double amount = price * quantity;
pstmt2.setDouble(4, amount);
total += amount;
pstmt2.executeUpdate();
} else {
System.out.println("该图书不存在!");
}
}
pstmt1.setDouble(3, total);
pstmt1.executeUpdate();
System.out.println("订单信息:");
System.out.println("订单号:" + order_id);
System.out.println("订单明细:");
rs = pstmt2.executeQuery("SELECT book_id, quantity, amount FROM order_detail WHERE order_id = " + order_id);
while (rs.next()) {
int book_id = rs.getInt("book_id");
int quantity = rs.getInt("quantity");
double amount = rs.getDouble("amount");
System.out.println("图书编号:" + book_id + ",数量:" + quantity + ",金额:" + amount);
}
System.out.println("订单总额:" + total);
System.out.println("订单日期:" + dateStr);
} else {
System.out.println("生成订单失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt1 != null) {
pstmt1.close();
}
if (pstmt2 != null) {
pstmt2.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
4. 将订单信息插入数据库的代码:
在购买图书的代码中已经包含了将订单信息和订单详细信息插入数据库的代码,这里不再重复。需要注意的是,在创建orders表和order_detail表时,需要设置order_id为主键,并将order_id设置为自增长,以保证每个订单的订单号唯一。