jdbc方法类封装

封装Java控制数据库的增删改查操作

一、简介

Java操作数据库增删改查时，重复的代码很多，为此有必要专门封装一个工具类实现增删改查的各种数据库操作。以便实现Java后台和数据库信息的交互。

二、具体思路

使用object数组和preparedStatement方法实现数据的再传输。可以模糊化输入的数据类型，以便实现方法的准确调用。另外采用反射方法实现查询对象时各项属性的封装。不因对象不同二出现bug，对所有对象都适应的查询方法。

三、具体实现代码

	public static void init() {
		try {
			Properties prop = new Properties();
			prop.load(new FileInputStream("src/jdbc.properties"));
			driverClass = prop.getProperty("driverClass");
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			password = prop.getProperty("password");
			maxActive = Integer.parseInt(prop.getProperty("maxActive"));
			minIdle = Integer.parseInt(prop.getProperty("minIdle"));
			maxWait = Long.parseLong(prop.getProperty("maxWait"));
			dataSource = new DruidDataSource();
			dataSource.setDriverClassName(driverClass);
			dataSource.setUrl(url);
			dataSource.setUsername(user);
			dataSource.setPassword(password);
			dataSource.setMaxActive(maxActive);
			dataSource.setMinIdle(minIdle);
			dataSource.setMaxWait(maxWait);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static synchronized Connection getConn() {
		if (dataSource == null || dataSource.isClosed()) {
			init();
		}
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void close(ResultSet rs, Statement stat, Connection conn) {
		try {
			if (rs != null)
				rs.close();
			if (stat != null)
				stat.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static int exeUpdate(Connection conn ,String sql,Object... params) {
//		Connection conn = null;
		PreparedStatement ps = null;
		try {
//			conn = getConn();
			ps = conn.prepareStatement(sql);
			if (Objects.nonNull(params)) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			int i = ps.executeUpdate();
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(null, ps, null);
		}
		return 0;
	}
	public static <T> List<T>  queryList(Class<T> t,String sql,Object...  params){
		List<T> data = new ArrayList<>();
		List<Map<String,Object>> list = getDataPair(sql, params);
		if(list.isEmpty()){
			return data;
		}
		for (Map<String, Object> map : list) {
			T obj = parseMapToBean(map,t);
			data.add(obj);
		}
		return data;
	}
	public static List<Map<String,Object>> getDataPair(String sql, Object... params){
		Connection conn= null;
		PreparedStatement  ps= null;
		ResultSet rs = null;
		List<Map<String,Object>> list =new ArrayList<Map<String,Object>>();
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			if(Objects.nonNull(params)){
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			while(rs.next()){
				Map<String ,Object> map = new HashMap<>();
				for (int i = 1; i <= count; i++) {
					String columnName = rsmd.getColumnName(i);
					String columnLable = rsmd.getColumnLabel(i);
					Object value =rs.getObject(columnLable);
					if(Objects.nonNull(value)){
						map.put(columnName, value);
					}
				}
				list.add(map);
				System.out.println(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(rs, ps, conn);
		}
		return list;
	}
	public static <T> T queryOne(Class<T> t,String sql,Object... params){
		List<Map<String,Object>> list =getDataPair(sql, params);
		if(!list.isEmpty()){
			Map<String, Object> map = list.get(0);
			T obj = parseMapToBean(map,t);
			return obj;
		}
		return null;
	}
	private static <T> T parseMapToBean(Map<String, Object> map, Class<T> t) {
		T obj = null;
		try {
			obj = t.newInstance();
			Set<String> keys = map.keySet();//返回对象的属性名
			for (String cname : keys) {
				Field f =t.getDeclaredField(cname);
				String setMethodName = "set"+cname.substring(0,1).toUpperCase()+cname.substring(1);
				Method method = t.getMethod(setMethodName, f.getType());
				method.invoke(obj, map.get(cname));
			}
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return obj;
	}
	public static void main(String[] args) {
		List<Emp> list= queryList(Emp.class, "select * from emp");
		list.forEach(l->System.out.println(l));
	}
}

其中还有一个配置数据库信息的文件，可以根据自己使用的数据库类型来自行配置。