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