JAVA简单模拟DVD功能（连接数据库）

 上一个代码的升级版本，采用数据库存储数据

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();
	}

}