JDBCUtils工具包

---

前言

   现在基本上很少再会让我们自己去写一些JDBC进行数据的写入查询了。但总有特别的时候你会发现，自己写一个JDBCUtils工具包真香。想怎么用怎么用，以下实例代码来源于师兄前辈的分享，以及博主后期使用后的总结，又 增添了一些。仅供参考。如与网上同行前辈的代码有相似之处，请联系博主备注出处。感谢前行者们对于编程事业的分享和付出！每一个可爱的人都值得尊敬！

---

实例

package cc;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtils {

	//数据库更新，
	/* xumz
	* conn 数据库连接
	* sql
	* obj 任意个参数
	* */
	public static int update(Connection conn,String sql,Object...obj) {
		PreparedStatement ps = null;
		int res;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			res=ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			res=-1;
		} finally {
			closeAll(conn, null, ps, null);
		}
		return res;
	}
	//传入参数变为List数组，这种情况有时候比上面的那个uodate更加好用，因为参数变成动态的了，在程序执行前谁都不知道到底有多少个参数
	public static int updateList(Connection conn,String sql,List datalist) {
		PreparedStatement ps = null;
		int res;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < datalist.size(); i++) {
				ps.setObject(i+1, datalist.get(i));
			}
			res=ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			res=-1;
		} finally {
			closeAll(conn, null, ps, null);
		}
		return res;
	}

	/**
	 * 简单查询语句
	 * 返回一个字符串
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return
	 */
	public static String queryForString(Connection conn,String sql,Object...obj){
		PreparedStatement ps = null;
		ResultSet rs = null;
		String res =null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if(rs.next()){
				res= rs.getString(rsmd.getColumnLabel(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
			res=null;
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return res;
	}

	/*
	* 简单查询语句
	* 返回int值
	* */
	public static Integer queryForInt(Connection conn,String sql,Object...obj){
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer res =-1;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if(rs.next()){
				res= rs.getInt(rsmd.getColumnLabel(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
			res=-1;
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return res;
	}

	/*
	* */
	public static Integer queryForIntList(Connection conn,String sql,List datalist){
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer res =-1;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < datalist.size(); i++) {
				ps.setObject(i+1, datalist.get(i));
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if(rs.next()){
				res= rs.getInt(rsmd.getColumnLabel(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
			res=-1;
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return res;
	}

	public static List<Map<String,Object>> queryForList(Connection conn,String sql,Object...obj){
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String,Object> map = null;
			while(rs.next()){
				map = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String colunmName = rsmd.getColumnLabel(i+1);
					Object columnValue = rs.getObject(colunmName);
					map.put(colunmName, columnValue);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return list;
	}

	//重载
	public static List<Map<String,Object>> queryForList_list(Connection conn, String sql, List datalist){
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < datalist.size(); i++) {
				ps.setObject(i+1, datalist.get(i));
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String,Object> map = null;
			while(rs.next()){
				map = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String colunmName = rsmd.getColumnLabel(i+1);
					Object columnValue = rs.getObject(colunmName);
					map.put(colunmName, columnValue);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return list;
	}
    /*当形参为Object[]数组时，调用该方法必须为一个数组
       当形参为Object...objects时，调用就相当灵活了，可以不带参数，可以带一个参数或者多个参数，也可以带数组作为参数*/
	public static List<Map<String,Object>> queryForListForKeyLower(Connection conn,String sql,Object...obj){
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String,Object> map = null;
			while(rs.next()){
				map = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String colunmName = rsmd.getColumnLabel(i+1);
					Object columnValue = rs.getObject(colunmName);
					map.put(colunmName.toLowerCase(), columnValue);
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeAll(conn, null, ps, rs);
		}
		return list;
	}


	//数据库连接方法
	public static Connection openConnection(){
		Connection con = null;
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(
					"url",
					"username",
					"password"
			);
		}catch(SQLException e){
			e.printStackTrace();
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		return con;
	}


	//数据库关闭方法
	public static void closeAll(Connection con,Statement st,PreparedStatement ps,ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

/*	调用方法如：
	Connection conn = JDBCUtils.openConnection();
	String sql_read = "select * from testa ";
	List<Map<String, Object>> list = JDBCUtils.queryForList(conn, sql_read);
		*/

}

创作不易，如果这篇文章能够帮助到你，希望能关注或收藏一下博主，如果文章内容有问题也可留言讨论，我们一起学习，一起进步！！