/**
* 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");
//连接URL
private 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;
}
}
