上一个代码的升级版本,采用数据库存储数据
package DataBase;
import java.sql.*;
import java.util.Scanner;
public class DVD_demo {
// 菜鸟教程模板
static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
static final String USER = "scott";
static final String PASS = "tiger";
Connection connection = null;
Statement statement = null;
PreparedStatement preparedStatement = null;
// 建立连接
public void getConn() {
try {
// 注册JDBC驱动
Class.forName(JDBC_DRIVER);
// 打开链接
System.out.println("连接数据库...");
connection = DriverManager.getConnection(DB_URL, USER, PASS);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 关闭连接
public void closeConn() {
try {
if (connection != null)
connection.close();
if (statement != null)
statement.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// System.out.println("Goodbye!");
}
// 开始菜单
public void startMenu() {
Scanner sc = new Scanner(System.in);
System.out.println("---------------------------");
System.out.println("| Welcome to my DVD world |");
System.out.println("|1----------------查 看DVD|");
System.out.println("|2----------------新 增DVD|");
System.out.println("|3----------------删 除DVD|");
System.out.println("|4----------------借 出DVD|");
System.out.println("|5----------------归 还DVD|");
System.out.println("|0----------------退 出DVD|");
System.out.println("---------------------------");
System.out.printf("请选择:");
int choose = sc.nextInt();
switch (choose) {
case 1:
DVD_search();
returnMenu();
break;
case 2:
DVD_add();
returnMenu();
break;
case 3:
DVD_delete();
returnMenu();
break;
case 4:
DVD_lend();
returnMenu();
break;
case 5:
DVD_return();
returnMenu();
break;
case 0:
DVD_out();
break;
default:
System.out.println("输入错误,请输入0返回主界面后重新输入");
returnMenu();
break;
}
}
// 返回主界面
public void returnMenu() {
System.out.println("输入0返回主界面");
Scanner sc = new Scanner(System.in);
if (sc.nextInt() == 0) {
startMenu();
} else {
System.out.println("输入错误,请重新输入");
returnMenu();
}
}
// 查询功能
public void DVD_search() {
getConn();
try {
// 执行查询
System.out.println("实例化Statement对象...");
statement = connection.createStatement();
String sql;
sql = "SELECT CDNAME,CDSTATUE,CDCOUNT,CDDATE FROM CDSTORE";
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("---------------------------------------------------------");
System.out.println("|" + "名称\t\t" + "|" + "状态\t" + "|" + "借出时间\t" + "|" + "借出次数\t" + "|");
// 展开结果集数据库
while (resultSet.next()) {
// 通过字段检索
String name = resultSet.getString("CDNAME");
int statue = resultSet.getInt("CDSTATUE");
int count = resultSet.getInt("CDCOUNT");
int date = resultSet.getInt("CDDATE");
// 输出数据
if (name == null) {
break;
} else if (statue == 1) {
System.out.println("|" + "<<" + name + ">>\t" + "|" + "已借出\t" + "|" + date + "\t" + "|" + count
+ "\t\t" + "|");
} else if (statue == 0) {
System.out.println(
"|" + "<<" + name + ">>\t" + "|" + "可借\t" + "|" + "\t\t" + "|" + count + "\t\t" + "|");
}
}
System.out.println("---------------------------------------------------------");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeConn();
}
}
// 增加DVD功能
public void DVD_add() {
getConn();
try {
System.out.println("请输入要添加的CD名称:");
Scanner input = new Scanner(System.in);
String name = input.next();
int statue = 0;
int count = 0;
int date = 0;
String sql = "insert into CDSTORE (CDNAME,CDSTATUE,CDCOUNT,CDDATE) values(?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
// 为sql参数赋值
preparedStatement.setString(1, name);
preparedStatement.setInt(2, statue);
preparedStatement.setInt(3, count);
preparedStatement.setInt(4, date);
// 这里括号里面不需要加sql,因为前面已经被覆盖了?
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeConn();
}
}
// 删除DVD功能
public void DVD_delete() {
getConn();
try {
System.out.println("请输入要删除的CD名称:");
Scanner input = new Scanner(System.in);
String name = input.next();
statement = connection.createStatement();
String sql;
sql = "SELECT CDNAME FROM CDSTORE";
ResultSet resultSet = statement.executeQuery(sql);
sql = "delete from CDSTORE where CDNAME = '" + name + "'";
int index = -1;
while (resultSet.next()) {
// 通过字段检索
String CDname = resultSet.getString("CDNAME");
if (CDname.equalsIgnoreCase(name)) {
index = 0;
statement.executeUpdate(sql);
System.out.println("删除" + name + "成功!");
}
}
if (index == -1) {
System.out.println("删除失败,未在库存中找到" + name);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeConn();
}
}
// 借出DVD功能
public void DVD_lend() {
getConn();
try {
Scanner input = new Scanner(System.in);
System.out.println("请输入要借出的CD名称:");
String name = input.next();
statement = connection.createStatement();
String sql;
sql = "SELECT CDNAME,CDSTATUE,CDCOUNT,CDDATE FROM CDSTORE";
ResultSet resultSet = statement.executeQuery(sql);
int index = -1;
while (resultSet.next()) {
// 通过字段检索
String CDname = resultSet.getString("CDNAME");
int CDstatue = resultSet.getInt("CDSTATUE");
int CDcount = resultSet.getInt("CDCOUNT");
if (CDname.equalsIgnoreCase(name) && CDstatue == 0) {
index = 0;
int count = CDcount+1;
// System.out.println("CDcount:" + CDcount);
// System.out.println("count" + count);
System.out.println("请输入当前日期:");
int date = input.nextInt();
sql = "update CDSTORE set CDSTATUE = 1 , CDDATE = '" + date + "' ,CDCOUNT = '" + count
+ "' where CDNAME = '" + name + "'";
statement.executeUpdate(sql);
System.out.println("已成功借出:" + name);
} else if (CDname.equalsIgnoreCase(name) && CDstatue == 1) {
index = 0;
System.out.println(name + "已被借出");
}
}
if (index == -1) {
System.out.println("查找失败,未在库存中找到" + name);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeConn();
}
}
// 归还DVD功能
public void DVD_return() {
getConn();
try {
Scanner input = new Scanner(System.in);
System.out.println("请输入要归还的CD名称:");
String name = input.next();
statement = connection.createStatement();
String sql;
sql = "SELECT CDNAME,CDSTATUE,CDCOUNT,CDDATE FROM CDSTORE";
ResultSet resultSet = statement.executeQuery(sql);
int index = -1;
while (resultSet.next()) {
// 通过字段检索
String CDname = resultSet.getString("CDNAME");
int CDstatue = resultSet.getInt("CDSTATUE");
if (CDname.equalsIgnoreCase(name) && CDstatue == 1) {
index = 0;
sql = "update CDSTORE set CDSTATUE = 0 , CDDATE = 0 where CDNAME = '" + name + "'";
statement.executeUpdate(sql);
System.out.println("已成功归还:" + name);
} else if (CDname.equalsIgnoreCase(name) && CDstatue == 0) {
index = 0;
System.out.println(name + "未被借出,无法归还");
}
}
if (index == -1) {
System.out.println("查找失败,未在库存中找到" + name);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeConn();
}
}
// 退出
public void DVD_out() {
System.out.println("感谢您的使用,欢迎下次光临!");
}
}
package DataBase;
public class DVD_test {
public static void main(String[] args) {
DVD_demo DVD = new DVD_demo();
DVD.startMenu();
}
}