2020-10-13(DVD連線資料庫，附部分程式碼)

吳槑槑發表於2020-10-13
資料庫
package main;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Scanner;

import modeo.DVD;

public class Tools {

	Connection con = null;
	Statement stm = null;
	PreparedStatement pre = null;
	ResultSet rs = null;
	
	public void showMenu() {
		System.out.println("請根據下面的編號，選擇您的操作:");
		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("6.歸還DVD");
		System.out.println("7.退出系統");
		System.out.println("請選擇：");
	}

	// 查詢所有的DVD資料
	public void showDVD() throws Exception {
		System.out.println("查詢成功，DVD詳細內容如下:");
		boolean status = true;
		ArrayList<DVD> list = new ArrayList<>();
		con = JDBCUtil.getCon();
		stm = con.createStatement();
		String sql = "SELECT * from dvd_1;";
		rs = stm.executeQuery(sql);
		// 把查詢之後返回的ResultSet rs 儲存到list集合中去,方便操作
		while (rs.next()) {
			String id = rs.getString("id");
			String name = rs.getString("DVD_name");
			String type = rs.getString("type");
			// DVD物件中的狀態為boolean型別
			int s = rs.getInt("state");
			if (s == 1) {
				status = true;
			} else if (s == 0) {
				status = false;
			}
			/**
			 * 獲取時間型別的方法， 
			 * 1.getTimestamp()獲取年月日時分秒
			 * 2.getDate()只能獲取年月日，時分秒會預設為00:00:00 
			 * 3.注意獲取的時間可能為null
			 */
			Timestamp timestamp = rs.getTimestamp("lend_time");
			Timestamp timestamp2 = rs.getTimestamp("return_time");
			java.util.Date lendTime = null;
			java.util.Date returnTime = null;
			if (timestamp == null) {
				lendTime = null;
			} else {
				lendTime = new Date(timestamp.getTime());
			}
			if (timestamp2 == null) {
				returnTime = null;
			} else {
				returnTime = new Date(timestamp2.getTime());
			}
			DVD d = new DVD(id, name, type, status, lendTime, returnTime);
			list.add(d);
		}
		JDBCUtil.close(con, stm, rs);
		for (DVD dvd : list) {
			System.out.println(dvd.toString());
		}
	}

	// 新增DVD資料
	public void addDVD() throws Exception {
		Scanner sc = new Scanner(System.in);
		System.out.println("請輸入要新增DVD的編號：");
		String s = sc.next();
		if (searchID(s)) {
			System.out.println("你輸入的DVD已存在，新增失敗....");
		} else {
			System.out.println("請輸入要新增DVD的片名:");
			String name = sc.next();
			System.out.println("請輸入要新增DVD的型別:");
			String type = sc.next();
			con = JDBCUtil.getCon();
			String sql = "INSERT INTO dvd_1(id,DVD_name,type,state) VALUES (?,?,?,0);";
			pre = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pre.setString(1, s);
			pre.setString(2, name);
			pre.setString(3, type);
			int executeUpdate = pre.executeUpdate();
			if (executeUpdate > 0) {
				System.out.println("新增成功");
			} else {
				System.out.println("新增失敗");
			}
			con.commit(); // 提交
			JDBCUtil.close(con, pre);
		}
		// sc.close();
	}

	// 刪除DVD資料
	public void delDVD() throws Exception {
		Scanner sc = new Scanner(System.in);
		System.out.println("請輸入要刪除DVD的編號:");
		String s = sc.next();
		if (searchID(s)) {
			con = JDBCUtil.getCon();
			String sql = "DELETE FROM dvd_1 WHERE id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, s);

			int executeUpdate = pre.executeUpdate();
			if (executeUpdate > 0) {
				System.out.println("刪除成功");
			} else {
				System.out.println("你輸入的DVD錯誤，刪除失敗....");
			}
		} else {
			System.out.println("你輸入的DVD不存在，刪除失敗....");
		}
		JDBCUtil.close(con, pre);
	}

	// 修改DVD資料
	public void changeDVD() throws Exception {
		System.out.println("請輸入您要修改的DVD的編號:");
		Scanner sc = new Scanner(System.in);
		String s = sc.next();
		if (searchID(s)) {
			System.out.println("請輸入修改後DVD的編號:");
			String n = sc.next();
			if (searchID(n)) {
				System.out.println("你修改的DVD編號已存在，修改失敗....");
				return;
			} else {
				System.out.println("請輸入修改後的DVD的片名:");
				String name = sc.next();
				System.out.println("請輸入修改後的DVD的型別:");
				String type = sc.next();
				con = JDBCUtil.getCon();
				String sql = "UPDATE dvd_1 SET id=?,DVD_name=?,type=? WHERE id=?;";
				pre = con.prepareStatement(sql);
				pre.setString(1, n);
				pre.setString(2, name);
				pre.setString(3, type);
				pre.setString(4, s);

				int executeUpdate = pre.executeUpdate();
				if (executeUpdate > 0) {
					System.out.println("修改成功!");
				}
			}
		} else {
			System.out.println("您所要修改的DVD不存在....");
		}
		
	}

	// 借出DVD
	public void lendDVD() throws Exception {
		System.out.println("請輸入您要借的DVD的編號:");
		Scanner sc = new Scanner(System.in);
		String s = sc.next();
		if (searchID(s)) {
			con = JDBCUtil.getCon();
			String sql = "SELECT state FROM dvd_1 where id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, s);
			rs = pre.executeQuery();
			while (rs.next()) {
				int int1 = rs.getInt("state");
				if (int1 == 1) {
					System.out.println("您所要借的DVD已被借出，請等待歸還後再借，抱歉~");
				} else {
					Date d = new Date();
					java.sql.Timestamp date = utilDateToSqlDate(d);
					con = JDBCUtil.getCon();
					String sqls = "UPDATE dvd_1 SET state=1,lend_time=? WHERE id=?;";
					pre = con.prepareStatement(sqls);
					pre.setTimestamp(1, date);
					pre.setString(2, s);
					int executeUpdate = pre.executeUpdate();
					if (executeUpdate > 0) {
						System.out.println("借閱成功!!!");
					} else {
						System.out.println("未知錯誤...");
					}
				}
			}
		} else {
			System.out.println("您所要借的DVD不存在!!!");
		}
	}

	// 歸還DVD
	public void backDVD() throws Exception {
		System.out.println("請輸入您要歸還的DVD的編號:");
		Scanner sc = new Scanner(System.in);
		String s = sc.next();
		if (searchID(s)) {
			con = JDBCUtil.getCon();
			String sql = "SELECT state FROM dvd_1 where id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, s);
			rs = pre.executeQuery();
			while (rs.next()) {
				int int1 = rs.getInt("state");
				if (int1 == 0) {
					System.out.println("Error:你所歸還的書籍未借出!");
				} else {
					Date d = new Date();
					java.sql.Timestamp date = utilDateToSqlDate(d);
					con = JDBCUtil.getCon();
					String sqls = "UPDATE dvd_1 SET state=0,return_time=? WHERE id=?;";
					pre = con.prepareStatement(sqls);
					pre.setTimestamp(1, date);
					pre.setString(2, s);
					int executeUpdate = pre.executeUpdate();
					if (executeUpdate > 0) {
						System.out.println("歸還成功!!!");
					} else {
						System.out.println("未知錯誤...");
					}
				}
			}
		} else {
			System.out.println("您所要歸還的DVD不存在!!!");
		}

	}

	// 判斷編號是否存在
	public Boolean searchID(String id) throws Exception {
		// SELECT count(id) FROM dvd where id=? limit 1

		con = JDBCUtil.getCon();
		String sql = "SELECT id FROM dvd_1 where id=? limit 1;";
		pre = con.prepareStatement(sql);
		pre.setString(1,id);
		rs = pre.executeQuery();
		if (rs.next()) {
			return true;
		} else {
			return false;
		}
	} 
	
	
	//util的Date改為Sql的Date(Timestamp)
	public java.sql.Timestamp utilDateToSqlDate(java.util.Date date) {
        long longtime = date.getTime();
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(longtime);
        return sqlDate;
	}
}


package main;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
	private static String driver=null;
	private static String url=null;
	private static String username=null;
	private static String password=null;
	
	
	static{
		InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
		Properties p = new Properties();
		try {
			p.load(in);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		driver=p.getProperty("driver");
		url=p.getProperty("url");
		username=p.getProperty("username");
		password=p.getProperty("password");
		
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驅動註冊失敗");
		}
	}
	
	//獲取Connection
	public static Connection getCon() throws Exception {
		return DriverManager.getConnection(url, username, password);
	}
	
	//關閉資源
	public static void close(Connection con,Statement stm,ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stm != null) {
				stm.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
	
	}

	public static void close(Connection con,Statement stm) {
		JDBCUtil.close(con, stm, null);
	
	}
}

 

相關文章