dao
import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
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.Iterator;
import java.util.List;
import java.util.Map;
import javax.sql.rowset.CachedRowSet;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.kmi.util.Util;
import com.sun.rowset.CachedRowSetImpl;
public class JdbcDaoSupport<T extends Serializable> {
private final Log log = LogFactory.getLog(this.getClass());
public int save(Connection conn, String sql, List<Object> mList) {
getValidConnection(conn);
int autoId = -1;
if (conn == null) {
return autoId;
}
PreparedStatement psment = null;
ResultSet rsment = null;
try {
log.debug("JdbcDaoSupport.Save sql = " + sql);
psment = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
setParameters(psment, mList);
int count = psment.executeUpdate();
if (count >= 0) {
rsment = psment.getGeneratedKeys();
while (rsment.next()) {
autoId = rsment.getInt(1);
}
}
} catch (Exception e) {
e.printStackTrace();
autoId = -1;
} finally {
DBManager.closePreparedStatement(psment);
}
return autoId;
}
/**
* 执行Save或者Update操作
*
* @param conn
* 数据库连接
* @param sql
* Save或者Update SQL
* @param mList
* 参数List
* @return
*/
public boolean saveOrUpdate(Connection conn, String sql, List<Object> mList) {
getValidConnection(conn);
boolean executeState = false;
PreparedStatement psment = null;
try {
psment = conn.prepareStatement(sql);
log.debug("JdbcDaoSupport.saveOrUpdate sql = " + sql);
setParameters(psment, mList);
int count = psment.executeUpdate();
if (count >= 0) {
executeState = true;
}
} catch (Exception e) {
e.printStackTrace();
executeState = false;
} finally {
DBManager.closePreparedStatement(psment);
}
return executeState;
}
/**
* 调用存储过程
*
* @param conn
* @param procName
* @param list
* @return
*/
public List<Object[]> callProc(Connection conn, String procName, List<Object> list) {
if (Util.isStringEmpty(procName)) {
return null;
}
getValidConnection(conn);
CallableStatement callStatement = null;
ResultSet rsment = null;
ResultSetMetaData rsmd = null;
List<Object[]> objList = new ArrayList<Object[]>();
try {
log.debug("JdbcDaoSupport.callProc procName = " + procName);
callStatement = conn.prepareCall(procName);
if (!Util.isCollectionEmpty(list)) {
for (int i = 0; i < list.size(); i++) {
callStatement.setString(i + 1, list.get(i).toString());
}
}
rsment = callStatement.executeQuery();
rsmd = rsment.getMetaData();
int cols = rsmd.getColumnCount();
if (cols > 0) {
while (rsment.next()) {
Object[] obj = new Object[cols];
for (int i = 0; i < obj.length; i++) {
obj[i] = rsment.getObject(i + 1);
}
objList.add(obj);
}
}else{
return null;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBManager.closeResultSet(rsment);
DBManager.closeCallableStatement(callStatement);
}
return objList;
}
/**
* 查询(返回List<Map<name,value>>)
*
* @param conn
* @param sql
* @param list
* @return
*/
public List<Map<String, Object>> queryForMap(Connection conn, String sql, List<Object> list) {
if (Util.isStringEmpty(sql)) {
return null;
}
getValidConnection(conn);
PreparedStatement psment = null;
ResultSet rsment = null;
ResultSetMetaData rsmd = null;
List<Map<String, Object>> objList = new ArrayList<Map<String, Object>>();
Map<String, Object> map = null;
try {
log.debug("JdbcDaoSupport.queryForMap sql = " + sql);
psment = conn.prepareStatement(sql);
setParameters(psment, list);
rsment = psment.executeQuery();
rsmd = rsment.getMetaData();
int cols = rsmd.getColumnCount();
while (rsment.next()) {
map = new HashMap<String, Object>();
for (int i = 0; i < cols; i++) {
map.put(rsmd.getColumnName(i + 1), rsment.getObject(i + 1));
}
objList.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBManager.closeResultSet(rsment);
DBManager.closePreparedStatement(psment);
}
return objList;
}
private void setParameters(PreparedStatement psment, List<Object> mList) throws SQLException {
if (Util.isCollectionEmpty(mList)) {
return;
}
for (int i = 0; i < mList.size(); i++) {
psment.setObject(i + 1, mList.get(i));
}
}
private List<?> getEntity(List<Map<String, Object>> entityList, Class<?> clazz) {
List<Object> mList = new ArrayList<Object>();
try {
String key = null;
Object obj = null;
Object tempVale = null;
Field[] fa = clazz.getDeclaredFields();
for (Map<String, Object> map : entityList) {
obj = (Object) Class.forName(clazz.getName()).newInstance();
for (Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator(); iterator.hasNext();) {
Map.Entry<String, Object> entry = iterator.next();
key = entry.getKey();
tempVale = entry.getValue();
if (tempVale == null) {
continue;
}
for (Field field : fa) {
if (field.getName().equalsIgnoreCase(key)) {
field.setAccessible(true);
field.set(obj, entry.getValue());
}
}
}
mList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return mList;
}
/**
* 执行批量添加
*
* @param conn
* @param sql
* @param paramList
* @return
*/
public boolean batchExecRecord(Connection conn, String sql, List<List<Object>> paramList) {
boolean status = false;
PreparedStatement psment = null;
try {
getValidConnection(conn);
psment = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < paramList.size(); i++) {
List<Object> param = paramList.get(i);
for (int j = 0; j < param.size(); j++) {
psment.setObject(j + 1, param.get(j));
}
psment.addBatch();
}
int[] b = psment.executeBatch();
if (b != null && b.length > 0) {
status = true;
}
} catch (Exception e) {
status = false;
e.printStackTrace();
}
return status;
}
/**
* 执行查询SQL
*
* @param conn
* @param sql
* @param mList
* @return
*/
public CachedRowSet executeQuerySQL(Connection conn, String sql, List<Object> list) {
// TODO Auto-generated method stub
if (Util.isStringEmpty(sql)) {
return null;
}
PreparedStatement psment = null;
ResultSet rsment = null;
CachedRowSet crs = null;
try {
log.debug("JdbcDaoSupport.Method executeQuerySQL = " + sql);
psment = conn.prepareStatement(sql);
setParameters(psment, list);
rsment = psment.executeQuery();
crs = new CachedRowSetImpl();
crs.populate(rsment);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBManager.closeResultSet(rsment);
DBManager.closePreparedStatement(psment);
}
return crs;
}
public void getValidConnection(Connection conn) {
try {
if (conn == null || conn.isClosed()) {
conn = DBManager.getConnection();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 清空表数据
*
* @param conn
* 数据库连接
* @param tableName
* 表的名字
* @return
*/
public boolean emptyTableData(Connection conn, String tableName) {
boolean status = false;
if (Util.isStringEmpty(tableName)) {
return status;
}
PreparedStatement psment = null;
System.out.println("TRUNCATE table :" + tableName);
StringBuffer buff = new StringBuffer("TRUNCATE TABLE ");
buff.append(tableName);
try {
psment = conn.prepareStatement(buff.toString());
psment.executeUpdate();
status = true;
} catch (SQLException e) {
status = false;
e.printStackTrace();
} finally {
DBManager.closePreparedStatement(psment);
}
return status;
}
}
DBManager
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBManager {
private static final String DB_LOCATION = "dbcpconfig.properties";
private static BasicDataSource dataSource = null;
public DBManager() {
}
public static void init() {
if (dataSource != null) {
try {
dataSource.close();
} catch (Exception e) {
e.printStackTrace();
}
dataSource = null;
}
try {
Properties properties = new Properties();
InputStream input = null;
try {
input = DBManager.class.getClassLoader().getResourceAsStream(DB_LOCATION);
properties.load(input);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (input != null) {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
public static synchronized Connection getConnection() {
Connection conn = null;
try {
if (dataSource == null) {
init();
}
if (dataSource != null) {
conn = dataSource.getConnection();
}
conn.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public synchronized static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public synchronized static void closeResultSet(ResultSet rsment) {
if (rsment != null) {
try {
rsment.close();
rsment = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public synchronized static void closeCachedRowSet(CachedRowSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public synchronized static void closeStatement(Statement stment) {
if (stment != null) {
try {
stment.close();
stment = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public synchronized static void closeCallableStatement(CallableStatement call) {
if (call != null) {
try {
call.close();
call = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public synchronized static void closePreparedStatement(PreparedStatement psment) {
if (psment != null) {
try {
psment.close();
psment = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static DataSource ds;
}
Util
package com.kmi.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Collection;
public class Util {
/**
* 判断字符串是否为空
* @param str
* @return
*/
public static boolean isStringEmpty(String str) {
return str == null || str.length() == 0;
}
/**
* 判断集合是否为空
* @param collection
* @return
*/
public static boolean isCollectionEmpty(Collection<?> collection) {
return collection == null || collection.isEmpty();
}
/**
* 判断数组是否为空
* @param obj
* @return
*/
public static boolean isArrayEmpty(Object[] obj) {
return obj == null || obj.length == 0;
}
/**
* 文件保存
*
* @param file
* 要保存的文件
* @param path
* 保存的文件路径
* @param fileName
* 文件名称
* @return boolean
*/
public static boolean saveFile(File file, String path, String fileName) {
boolean b = false;
try {
// String root = ServletActionContext.getRequest().getRealPath("/") + path;
// String root = PropertiesUtils.getProperties("asm_file_path")+path;
String root = path;
File f = new File(root);
if (!f.isDirectory()) {
f.mkdirs();
}
InputStream is = new FileInputStream(file);
File destFile = new File(root, fileName);
OutputStream os = new FileOutputStream(destFile);
byte[] buffer = new byte[1025];
int length = 0;
while ((length = is.read(buffer)) > 0) {
os.write(buffer, 0, length);
}
is.close();
os.close();
b = true;
} catch (Exception e) {
b = false;
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
}