本次任务需要设计一个图书管理系统,可以使用Oracle数据库来存储数据。以下是实现该系统的基本步骤:
- 创建数据库表
首先需要创建用于存储图书信息的数据库表,包括图书编号、图书名称、作者、出版社、出版日期等字段。建议使用主键来唯一标识每一本图书。
CREATE TABLE book (
book_id INT PRIMARY KEY,
book_name VARCHAR2(50),
author VARCHAR2(50),
publisher VARCHAR2(50),
publish_date DATE
);
2.插入数据
插入图书信息数据至book表中。
INSERT INTO book (book_id, book_name, author, publisher, publish_date) VALUES (1, 'Java编程思想', 'Bruce Eckel', '机械工业出版社', TO_DATE('2002-03-01', 'YYYY-MM-DD'));
INSERT INTO book (book_id, book_name, author, publisher, publish_date) VALUES (2, '深入浅出MySQL', '姜承尧', '电子工业出版社', TO_DATE('2013-05-01', 'YYYY-MM-DD'));
INSERT INTO book (book_id, book_name, author, publisher, publish_date) VALUES (3, 'C++ Primer Plus', 'Stephen Prata', '人民邮电出版社', TO_DATE('2012-11-01', 'YYYY-MM-DD'));
3创建用户和授权
创建一个用于连接数据库的用户,并为其授权,使其拥有所有book表的读写权限。
CREATE USER book_user IDENTIFIED BY password;
GRANT ALL PRIVILEGES ON book TO book_user;
4.开发Java程序
使用JDBC驱动连接Oracle数据库,并实现基本的增删改查等功能。
具体代码示例如下:
import java.sql.*;
public class BookManager {
// 数据库连接参数
private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
private static final String DB_USER = "book_user";
private static final String DB_PASSWORD = "password";
// 数据库连接对象、会话对象和结果集对象
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
// 连接并初始化数据库
public void init() {
try {
// 加载JDBC驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 获取数据库连接
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// 创建会话对象
stmt = conn.createStatement();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭数据库连接和会话对象
public void close() {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 查询图书信息
public void query() {
try {
String sql = "SELECT * FROM book";
rs = stmt.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt("book_id");
String name = rs.getString("book_name");
String author = rs.getString("author");
String publisher = rs.getString("publisher");
Date date = rs.getDate("publish_date");
System.out.println(id + "\t" + name + "\t" + author + "\t" + publisher + "\t" + date);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
// 添加图书信息
public void add(int id, String name, String author, String publisher, Date date) {
try {
String sql = "INSERT INTO book VALUES (" + id + ", '" + name + "', '" + author + "', '" + publisher + "', TO_DATE('" + date + "', 'YYYY-MM-DD'))";
stmt.executeUpdate(sql);
System.out.println("添加成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
// 修改图书信息
public void update(int id, String name, String author, String publisher, Date date) {
try {
String sql = "UPDATE book SET book_name = '" + name + "', author = '" + author + "', publisher = '" + publisher + "', publish_date = TO_DATE('" + date + "', 'YYYY-MM-DD') WHERE book_id = " + id;
stmt.executeUpdate(sql);
System.out.println("修改成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
// 删除图书信息
public void delete(int id) {
try {
String sql = "DELETE FROM book WHERE book_id = " + id;
stmt.executeUpdate(sql);
System.out.println("删除成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
BookManager bm = new BookManager();
bm.init();
bm.query();
bm.add(4, "Python编程入门", "Mark Lutz", "人民邮电出版社", Date.valueOf("2014-06-01"));
bm.update(1, "Java核心技术", "Cay S. Horstmann", "机械工业出版社", Date.valueOf("2017-05-01"));
bm.delete(3);
bm.query();
bm.close();
}
}