jdbc工具类

此类主要实现数据库连接获取，增删改查操作封装，资源关闭的操作

在写相关类的增删改查时，反复获取连接，还有大量的重复代码，不一样的只体现在sql语句与结果的处理，还有传入参数的不一致，所以相同的代码都可以进行封装，这样在使用时会方便很多，统统一句话解决，具体实现如下

包依赖：mysql驱动包：mysql-connector-java-5.1.39-bin.jar，druid连接池：druid-1.1.10.jar

使用连接池是为了在连接上进行些限制，与封装无关

public class DbUtils {
	
	// Druid连接池基本配置与连接配置
	private static DruidDataSource pool;
	private static String url;
	private static String username;
	private static String password;
	private static int initialSize;
	private static int minIdle;
	private static int maxActive;
	private static long maxWait;
	private static String fileName = "/jdbc.properties";
	// 类加载时先初始化连接
	static {
		init();
	}
	// 读取属性文件
	private static void loadProp(String propName){
		fileName = propName;
		try {
			// 属性文件位于src目录中时,加"/"则不要使用ClassLoader;如果使用ClassLoader则无需"/"
			InputStream is = DbUtils.class.getResourceAsStream(fileName);
			Properties p = new Properties();
			p.load(is);
			url = p.getProperty("jdbc.url");
			username = p.getProperty("jdbc.username");
			password = p.getProperty("jdbc.password");
			
			initialSize = Integer.parseInt( p.getProperty("initialSize"));
			minIdle = Integer.parseInt(p.getProperty("minIdle"));
			maxActive = Integer.parseInt( p.getProperty("maxActive"));
			maxWait = Long.parseLong( p.getProperty("maxWait"));
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	// 初始化资源池
	private static void init(){
		pool = new DruidDataSource();
		loadProp(fileName);
		pool.setUrl(url);
		pool.setUsername(username);
		pool.setPassword(password);
		// 设置连接池中初始连接数
		pool.setInitialSize(initialSize);
		// 最小闲置连接
		pool.setMinIdle(minIdle);
		// 设置最大连接数
		pool.setMaxActive(maxActive);
		// 设置最大的等待时间
		pool.setMaxWait(maxWait);
	}
	// 获取连接
	public static Connection getConn(){
		try {
			if(pool == null || pool.isClosed()){
				init();
			}
			return pool.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	// 关闭资源
	public static void close(ResultSet rs, PreparedStatement ps, Connection conn){
		try {
			if(rs != null){
				rs.close();
			}
			if(ps != null){
				ps.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	// 更新操作，包括（insert,update,delete）
	public static boolean update(Connection conn, String sql, Object... objs) {
		int result = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				ps.setObject(i + 1, objs[i]);
			}
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DbUtils.close(null, ps, null);
		}
		if(result == 0){
			return false;
		}
		return true;
	}
	// 根据id查询
	public static <T> T queryById(Class<T> t, String sql, int id){
		Connection conn = null;
		T obj = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				Map<String,Object> map = new HashMap<>();
				
				for (int i = 1; i <= columnCount; i++) {
					String columnName = rsmd.getColumnName(i);
					Object value = rs.getObject(columnName);
					map.put(columnName, value);
				}
				
				if(!map.isEmpty()){
					Set<String> columnNames = map.keySet();
					obj = t.newInstance();
					for(String column : columnNames){
						Object value = map.get(column);
						Field f  = t.getDeclaredField(column);
						f.setAccessible(true);
						f.set(obj, value);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} finally{
			DbUtils.close(rs, ps, conn);
		}
		return obj;
	}
	// 查询全部
	public static <T> List<T> queryAll(Class<T> t,String sql, Object... objs) {
		List<T> list = new ArrayList<>();
		Connection conn = null;
		T obj = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				ps.setObject(i + 1, objs[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			Map<String,Object> map = new HashMap<>();
			
			while(rs.next()){
				map.clear();
				int columnCount = rsmd.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					String columnName = rsmd.getColumnName(i);
					map.put(columnName, rs.getObject(columnName));
				}
				if(!map.isEmpty()){
					Set<String> columnNames = map.keySet();
					obj = t.newInstance();
					for(String column : columnNames){
						Object value = map.get(column);
						Field f  = t.getDeclaredField(column);
						f.setAccessible(true);
						f.set(obj, value);
					}
					list.add(obj);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally{
			DbUtils.close(rs, ps, conn);
		}
		return list.isEmpty() ? null : list;
	}
}

实现类（IBaseDAO是增删改查的接口）

public class EmpDAO implements IBaseDAO<Employee> {

	private Connection conn;
	
	public EmpDAO(Connection conn){
		this.conn = conn;
	}
	
	@Override
	public boolean deleteById(Employee t) {
		return DbUtils.update(conn,"delete from employee where num = ?", t.getNum());
	}

	@Override
	public Employee findById(Employee emp) {
		return DbUtils.queryById(Employee.class, "select * from employee where num = ?", emp.getNum());
	}

	@Override
	public List<Employee> findByPage(int pageNow, int pageSize) {
		return DbUtils.queryAll(Employee.class, "select * from Employee limit ?,?",(pageNow-1)*pageSize, pageSize);
	}

	@Override
	public boolean update(Employee t) {
		String sql = "update employee set name=?,addr=?,zip=?,tel=?,email=?,depno=?,birth=?,sex=? where num =?";
		return DbUtils.update(conn, sql, 
				t.getName(),
				t.getAddr(),
				t.getZip(),
				t.getTel(),
				t.getEmail(),
				t.getDepno(),DateFormat.getDateInstance().format(t.getBirth()), 
				t.getSex(),
				t.getNum());
	}

	@Override
	public boolean add(Employee t) {
		return DbUtils.update(conn,"insert into employee(name,addr,zip,tel,email,depno,birth,sex) values(?,?,?,?,?,?,?,?)",  
				t.getName(), 
				t.getAddr(), 
				t.getZip(), 
				t.getTel(), 
				t.getEmail(), 
				t.getDepno(),
				DateFormat.getDateInstance().format(t.getBirth()), 
				t.getSex());
	}
	
}

Properties配置文件（置于src目录下）

jdbc.url = jdbc:mysql://127.0.0.1:3306/hrms
jdbc.username = root
jdbc.password = root