2020-10-15(JDBC用druid连接池封装)，嗯，，初具规模吧~

package dao.Impl;

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

import dao.DVDdao;
import modeo.DVD;
import util.JDBCUtil;

public class DVDdaoImpl implements DVDdao {

	Connection con = null;
	Statement stm = null;
	PreparedStatement pre = null;
	ResultSet rs = null;
	ArrayList<DVD> list = new ArrayList<>();

	@Override
	public List<DVD> showDvd() {
		try {
			boolean status = true;
			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);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int addDvdByDvd(DVD dvd) {
		int a = 0;
		try {
			con = JDBCUtil.getCon();
			String sql = "INSERT INTO dvd_1(id,DVD_name,type,state) VALUES (?,?,?,0);";
			pre = con.prepareStatement(sql);
			pre.setString(1, dvd.getId());
			pre.setString(2, dvd.getName());
			pre.setString(3, dvd.getType());
			a = pre.executeUpdate();
			JDBCUtil.close(con, pre);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	@Override
	public int delDvdById(String id) {
		int a = 0;
		try {
			con = JDBCUtil.getCon();
			String sql = "DELETE FROM dvd_1 WHERE id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, id);
			a = pre.executeUpdate();
			JDBCUtil.close(con, pre);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	@Override
	public int changeDvdById(String id, DVD dvd) {
		int a = 0;
		try {

			con = JDBCUtil.getCon();
			String sql = "UPDATE dvd_1 SET id=?,DVD_name=?,type=? WHERE id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, dvd.getId());
			pre.setString(2, dvd.getName());
			pre.setString(3, dvd.getType());
			pre.setString(4, id);

			a = pre.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	@Override
	public int lendDvdById(String id) {
		int a = 0;
		try {

			Date d = new Date();
			java.sql.Timestamp date = new java.sql.Timestamp(d.getTime());
			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, id);
			a = pre.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	@Override
	public int backDvdById(String id) {
		int a = 0;
		try {

			Date d = new Date();
			java.sql.Timestamp date = new java.sql.Timestamp(d.getTime());
			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, id);
			a = pre.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	@Override
	public Boolean searchDvdById(String id) {
		boolean b = true;
		try {
			// 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()) {
				b = true;
			} else {
				b = false;
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			JDBCUtil.close(con, pre);
		}
		return b;
	}

	@Override
	public int seachDvdStateById(String id) {
		int a = 0;
		try {
			con = JDBCUtil.getCon();
			String sql = "SELECT state FROM dvd_1 where id=?;";
			pre = con.prepareStatement(sql);
			pre.setString(1, id);
			rs = pre.executeQuery();
			while (rs.next()) {
				a = rs.getInt("state");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}

}

package main;

import java.util.Scanner;

import method.Tools;

public class Main {
	public static void main(String[] args) {
		System.out.println("*****************欢迎来到DVD管理系统**********************");
		Tools dm = new Tools();
		Scanner sc = new Scanner(System.in);
		int s = 0;
		try {
			do {
				dm.showMenu();
				s = sc.nextInt();
				switch (s) {
				case 1:
					dm.showDVD();
					break;
				case 2:
					dm.addDVD();
					break;
				case 3:
					dm.delDVD();
					break;
				case 4:
					dm.changeDVD();
					break;
				case 5:
					dm.lendDVD();
					break;
				case 6:
					dm.backDVD();
					break;
				case 7:
					System.out.println("欢迎下次再来!!!");
					break;
				default:
					System.out.println("您输入的有误，请重新输入:");
					break;
				}

			} while (s != 7);
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}	
		sc.close();
	}

}

package method;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Scanner;

import dao.DVDdao;
import dao.Impl.DVDdaoImpl;
import modeo.DVD;

public class Tools {

	
	Connection con = null;
	Statement stm = null;
	PreparedStatement pre = null;
	ResultSet rs = null;
	DVDdao dao = new DVDdaoImpl();
	List<DVD> list = 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详细内容如下:");
		list = dao.showDvd();
		for (DVD dvd : list) {
			System.out.println(dvd.toString());
		}
		list.clear();
	}

	// 新增DVD资料
	public void addDVD() throws Exception {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入要新增DVD的编号：");
		String id = sc.next();
		if (searchID(id)) {
			System.out.println("你输入的DVD已存在，新增失败....");
		} else {
			System.out.println("请输入要新增DVD的片名:");
			String name = sc.next();
			System.out.println("请输入要新增DVD的类型:");
			String type = sc.next();
			DVD dvd = new DVD(id, name, type);
			int a = dao.addDvdByDvd(dvd);
			if (a > 0) {
				System.out.println("添加成功");
			} else {
				System.out.println("添加失败");
			}
		}
		
	}

	// 删除DVD资料
	public void delDVD() throws Exception {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入要删除DVD的编号:");
		String id = sc.next();
		if (searchID(id)) {
			int a = dao.delDvdById(id);
			if (a > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("你输入的DVD错误，删除失败....");
			}
		} else {
			System.out.println("你输入的DVD不存在，删除失败....");
		}
		
	}

	// 修改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();
				DVD dvd = new DVD(n, name, type);
				int a = dao.changeDvdById(s, dvd);

				if (a > 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 id = sc.next();
		if (searchID(id)) {
			int a = dao.seachDvdStateById(id);
			if (a == 1) {
				System.out.println("您所要借的DVD已被借出，请等待归还后再借，抱歉~");
			} else {
				int b = dao.lendDvdById(id);
				if (b > 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 id = sc.next();
		if (searchID(id)) {
			int a = dao.seachDvdStateById(id);
			if (a == 0) {
				System.out.println("Error:你所归还的书籍未借出!");
			} else {
				int b = dao.backDvdById(id);
				if (b > 0) {
					System.out.println("归还成功!!!");
				} else {
					System.out.println("未知错误...");
				}
			}
		} else {
			System.out.println("您所要归还的DVD不存在!!!");
		}

	}

	// 判断编号是否存在
	public Boolean searchID(String id) throws Exception {
		return dao.searchDvdById(id);
	}

}

package util;

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

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class JDBCUtil {

	private static DruidDataSource ds;
	private static Properties p = new Properties();
	
	
	static{

		try {
			//加载配置文件内容
			InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            p.load(in);
            ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(p);	
            // ds = DruidDataSourceFactory.createDataSource(p);	
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("驱动注册失败");
		}
	}
	
	//获取Connection
	public static Connection getCon(){
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//关闭资源
	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);
	
	}
}