2020-10-13(DVD连接数据库，附部分代码)

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