JDBC工具类

/** * 2017年6月26日下午3:37:05 */package com.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;/** * * @author huangtao * 2017年6月26日下午3:37:05 * bkm * @parameter * TODO * JDBC连接数据库 * 连接远程数据库，取出数据，放入csv文件 */public class JDBCUtil {private static Log log = LogFactory.getLog(JDBCUtil.class);private RespsonData rd = new RespsonData("success");// 通用返回JSON对象 //驱动类完整路径private static final String DRIVERPATH = ResourceUtil.getConf("NewDriver");//连接URLprivate static final String URL = ResourceUtil.getConf("NewUrl");//用户名private static final String USERNAME = ResourceUtil.getConf("NewUsername");//密码private static final String PASSWORD = ResourceUtil.getConf("NewPassword");static {try {Class.forName(DRIVERPATH);} catch (ClassNotFoundException e) {e.printStackTrace();log.info("JDBC DataBase Load Driver Error!");System.out.println("加载驱动错误！");}}/*** 获取连接* @author huangtao** @return* 2016-9-5*/public static Connection getConnection() {Connection conn = null;try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (SQLException e) {e.printStackTrace();log.info("JDBC DataBase Can Not Connect!");System.out.println("数据库连接不上！");}return conn;}/*** 执行DML语句，insert into、update、delete* @author huangtao** @param sql* @param params* @return* 2016-9-5*/public static int executeUpdate(String sql, Object[] params) {Connection conn = getConnection();PreparedStatement ps = null;int row = 0;try {ps = conn.prepareStatement(sql);if(params != null) {for(int i=0; i<params.length; i++) {ps.setObject(i + 1, params[i]);}}row = ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();log.error("executeUpdate error...",e);} finally {close(conn, ps, null);}return row;} /*** 执行DQL语句，返回List数据* @author huangtao** @param sql* @param params* @return* 2016-9-5*/public static List<Object[]> executeQuery(String sql, Object[] params) {List<Object[]> list = new ArrayList<Object[]>();Connection conn = getConnection();PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);if(params != null) {for(int i=0; i<params.length; i++) {ps.setObject(i + 1, params[i]);}}//执行sql语句，获取结果集rs = ps.executeQuery();//获取结果集的列数int col = rs.getMetaData().getColumnCount();//遍历结果集while(rs.next()) {Object[] obj = new Object[col];for(int i=0; i<col; i++) {obj[i] = rs.getObject(i + 1);}list.add(obj);}} catch (SQLException e) {e.printStackTrace();log.error("executeQuery error...",e);} finally {close(conn, ps, rs);}return list;}public static List<Map<String, Object>> executeQueryForMap(String sql, Object[] params) {List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();Connection conn = getConnection();PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);if(params != null) {for(int i=0; i<params.length; i++) {ps.setObject(i + 1, params[i]);}}//执行sql语句，获取结果集rs = ps.executeQuery();//获取结果集属性信息ResultSetMetaData md = rs.getMetaData();//获取结果集的列数int col = md.getColumnCount();//遍历结果集while(rs.next()) {Map<String, Object> dataMap = new HashMap<String, Object>();for(int i=1; i<=col; i++) {//获取指定位置的字段名称String key = md.getColumnName(i);//获取指定位置的字段值Object value = rs.getObject(i);dataMap.put(key, value);list.add(dataMap);}}} catch (SQLException e) {e.printStackTrace();} finally {close(conn, ps, rs);}return list;}/*** 关闭* @author huangtao** @param conn* @param sta* @param rs* 2016-9-5*/private static void close(Connection conn, Statement sta, ResultSet rs) {try {if(rs != null) {rs.close();}if(sta != null) {sta.close();}if(conn != null) {conn.close();}} catch(SQLException e) {e.printStackTrace();log.error("JDBCUtil1 Release Of Resource Error！",e);System.out.println("释放资源出错！");}}//查询总数public static int executeCount(String sql, Object[] params) {Connection conn = getConnection();PreparedStatement ps = null;ResultSet rs = null;int count = 0;try { ps = conn.prepareStatement(sql);if(params != null) {for(int i=0; i<params.length; i++) {ps.setObject(i+1, params[i]);//字段的索引位置从1开始，所以要加1}}//执行sql语句，获取结果集rs = ps.executeQuery();//遍历结果集while(rs.next()) {count = Integer.parseInt(rs.getObject(1).toString());} } catch (SQLException e) {e.printStackTrace();log.error("executeCount error...",e);} finally {close(conn, ps, rs);}return count;}}