可以拿去用咯~
package com.scitel.gdnumcommon.utils;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.scitel.gdnumcommon.entity.Pagination;
import com.scitel.gdnumcommon.entity.BaseVO;
public class BaseDAO {
private static final Log log = LogFactory.getLog(BaseDAO.class);
/**
* 保存数据,新建和修改都用这个
*
* @param con
* @param SQL
* @param params
* @throws Exception
* @author
*/
public void save(Connection con, String SQL, List params)
throws Exception {
PreparedStatement ps = null;
try {
ps = con.prepareStatement(SQL);
if (SQL == null) {
throw new Exception();
}
if (params != null && params.size() > 0) {
int count = 0;
for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
Object object = iterator.next();
setObjectValue(ps, count + 1, object);
}
}
ps.executeUpdate();
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try{
if(ps != null) {
ps.close();
}
}catch(Exception e){
}
}
}
/**
* 保存数据,新建和修改都用这个,通过字段名称匹配类型
* @param con
* @param SQL
* @param voclass
* @param paramMap
* @throws Exception
* @author
*/
public void save(Connection con, String SQL, Class voclass, Map paramMap)
throws Exception {
PreparedStatement ps = null;
try {
ps = con.prepareStatement(SQL);
if (SQL == null) {
throw new Exception();
}
if (paramMap != null && paramMap.size() > 0) {
int count = 0;
for (Iterator iterator = paramMap.keySet().iterator(); iterator.hasNext(); count++) {
String key = (String)iterator.next();
Object object = paramMap.get(key);
setObjectValue(ps, voclass, count+1, key, object);
}
}
ps.executeUpdate();
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try{
if(ps != null) {
ps.close();
}
}catch(Exception e){
}
}
}
/**
* 删除数据
*
* @param con
* @param SQL
* @param params
* @throws Exception
* @author
*/
public void remove(Connection con, String SQL, List params)
throws Exception {
PreparedStatement ps = null;
try {
ps = con.prepareStatement(SQL);
if (SQL == null) {
throw new Exception();
}
if (params != null && params.size() > 0) {
int count = 0;
for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
Object object = iterator.next();
setObjectValue(ps, count + 1, object);
}
}
ps.executeUpdate();
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try{
if(ps != null) {
ps.close();
}
}catch(Exception e){
}
}
}
/**
* 根据ID选择数据
*
* @param con
* @param SQL
* @param id
* @param voclass
* @return
* @throws Exception
* @author
*/
public BaseVO selectById(Connection con, String SQL, String id,
Class voclass) throws Exception {
Object po = null; // 承载值对象
PreparedStatement ps = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
try {
ps = con.prepareStatement(SQL);
if (SQL == null) {
throw new Exception();
}
ps.setString(1, id);
rs = ps.executeQuery();
rsm = rs.getMetaData();
if (rs.next()) {
Map entity = new HashMap();
for (int i = 1; i <= rsm.getColumnCount(); i++) {
String columnName = rsm.getColumnName(i).toLowerCase();
Object columnValue = getObjectValue(rs, voclass, i, columnName);
entity.put(columnName, columnValue);
}
if (voclass != null) {
po = voclass.newInstance();
BeanUtils.populate(po, entity);
}
}
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
}
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
}
}
return (BaseVO) po;
}
/**
* 选择记录,不分页
* @param con
* @param SQL
* @param params
* @param voclass
* @return
* @throws Exception
* @author
*/
public List select(Connection con, String SQL, List params, Class voclass)
throws Exception {
Object vo = null; // 承载值对象
PreparedStatement ps = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
List relist = null;
try {
ps = con.prepareStatement(SQL);
if (SQL == null) {
throw new Exception();
}
if (params != null && params.size() > 0) {
int count = 0;
for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
Object object = iterator.next();
setObjectValue(ps, count + 1, object);
}
}
rs = ps.executeQuery();
rsm = rs.getMetaData();
relist = new ArrayList();
while (rs.next()) {
Map entity = new HashMap();
for (int i = 1; i <= rsm.getColumnCount(); i++) {
String columnName = rsm.getColumnName(i).toLowerCase();
Object columnValue = getObjectValue(rs, voclass, i, columnName);
entity.put(columnName, columnValue);
}
if (voclass != null) {
vo = voclass.newInstance();
BeanUtils.populate(vo, entity);
relist.add(vo);
} else {
relist.add(entity);
}
}
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
}
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
}
}
return relist;
}
/**
* 分页查询
*
* @param con
* @param SQL
* @param params
* @param voclass
* @param pagination
* @return
* @throws Exception
* @author
*/
public List selectPagination(Connection con, String SQL, List params,
Class voclass, Pagination pagination) throws Exception {
if (SQL == null) {
throw new NullPointerException("SQL不能为空!");
}
if (pagination == null) {
throw new NullPointerException("分页类不能为空!");
}
// TODO Auto-generated method stub
Object vo = null; // 承载值对象
PreparedStatement ps = null;
ResultSet rs = null;
ResultSetMetaData rsm = null;
List relist = null;
try {
ps = con.prepareStatement("select count(1) as count_ from ( " + SQL + " )");
if (params != null && params.size() > 0) {
int count = 0;
for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
Object object = iterator.next();
setObjectValue(ps, count + 1, object);
}
}
rs = ps.executeQuery();
if (rs.next()) {
pagination.setTotalCount(rs.getInt(1));
}
if (pagination.getTotalCount() > 0) {
/* 组成分页内容 */
StringBuffer pagingSelect = new StringBuffer(100);
pagingSelect
.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(SQL);
pagingSelect
.append(" ) row_ where rownum <= ?) where rownum_ > ?");
ps = con.prepareStatement(pagingSelect.toString());
int count = 0;
if (params != null && params.size() > 0) {
for (Iterator iterator = params.iterator(); iterator.hasNext(); count++) {
Object object = iterator.next();
setObjectValue(ps, count + 1, object);
}
}
ps.setInt(count + 1, pagination.getPage()
* pagination.getCount());
ps.setInt(count + 2, (pagination.getPage() - 1)
* pagination.getCount());
log.info("pagination.getPage():" + pagination.getPage());
log.info("pagination.getCount():" + pagination.getCount());
rs = ps.executeQuery();
rsm = rs.getMetaData();
relist = new ArrayList();
while (rs.next()) {
Map entity = new HashMap();
for (int i = 1; i <= rsm.getColumnCount(); i++) {
String columnName = rsm.getColumnName(i).toLowerCase();
Object columnValue = getObjectValue(rs, voclass, i, columnName);
entity.put(columnName, columnValue);
}
if (voclass != null) {
vo = voclass.newInstance();
BeanUtils.populate(vo, entity);
relist.add(vo);
} else {
relist.add(entity);
}
}
}
} catch (Exception e) {
log.error(e);
throw e;
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
}
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
}
}
return relist;
}
/**
* 获得SequenceValue
* @param sequenceName
* @return
* @throws Exception
* @author
*/
public Long getSequenceValue(Connection con, String sequenceName)throws Exception {
PreparedStatement ps = null;
ResultSet rs = null;
Long sequenceValue = null;
try{
ps = con.prepareStatement("select " + sequenceName + ".nextval from dual");
rs = ps.executeQuery();
if(rs.next()) {
sequenceValue = new Long(rs.getLong(1));
}
}catch(Exception e){
log.error(e);
throw e;
}finally{
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
}
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
}
}
return sequenceValue;
}
/**
* 把对象传入数据库
* @param ps
* @param count
* @param object
* @author
*/
private final void setObjectValue(PreparedStatement ps, int count, Object object) throws Exception {
log.debug("count is " + count + " object is " + object);
if(object != null) {
if(object instanceof Integer){
ps.setInt(count, ((Integer)object).intValue());
}else if(object instanceof Long) {
ps.setLong(count, ((Long)object).longValue());
}else if(object instanceof BigDecimal){
ps.setBigDecimal(count, (BigDecimal)object);
}else if(object instanceof String){
ps.setString(count, (String)object);
}else if(object instanceof java.util.Date) {
if(object!=null){
long time = ((java.util.Date)object).getTime();
ps.setDate(count, new java.sql.Date(time));
}else{
ps.setDate(count, null);
}
}else{
ps.setObject(count, object);
}
}else{
ps.setNull(count, Types.INTEGER);
}
}
/**
* 把对象传入数据库
* @param ps
* @param clazz
* @param count
* @param columnName
* @param object
* @throws Exception
* @author
*/
private final void setObjectValue(PreparedStatement ps, Class clazz, int count,
String columnName, Object object)throws Exception {
log.debug("count is " + count + " columnName is " + columnName + " object is " + object);
String classType = clazz.getDeclaredField(columnName).getType().getName();
if(classType.equals("java.lang.Integer")){
if(object != null) {
ps.setInt(count, ((Integer)object).intValue());
}else{
ps.setNull(count, Types.INTEGER);
}
}else if(classType.equals("java.lang.Long")) {
if(object != null ) {
ps.setLong(count, ((Long)object).longValue());
}else{
ps.setNull(count, Types.INTEGER);
}
}else if(classType.equals("java.math.BigDecimal")){
if(object != null) {
ps.setBigDecimal(count, (BigDecimal)object);
}else{
ps.setNull(count, Types.NUMERIC);
}
}else if(classType.equals("java.lang.String")){
if(object != null) {
ps.setString(count, (String)object);
}else{
ps.setString(count, null);
}
}else if(classType.equals("java.util.Date")) {
if(object!=null){
long time = ((java.util.Date)object).getTime();
ps.setDate(count, new java.sql.Date(time));
}else{
ps.setDate(count, null);
}
}else{
ps.setObject(count, object);
}
}
/**
* 把数据从数据取出来
* @param rs
* @param clazz
* @param count
* @param columnName
* @return
* @throws Exception
* @author
*/
private final Object getObjectValue(ResultSet rs, Class clazz, int count, String columnName) throws Exception {
Object fieldValue = null;
log.debug("columnName is " + columnName + " count is " + count);
if(columnName != null) {
if("rownum".equals(columnName)) {
fieldValue = new Long(rs.getLong(count));
}else if("rownum_".equals(columnName)) {
fieldValue = new Long(rs.getLong(count));
}else if("count_".equals(columnName)) {
fieldValue = new Long(rs.getLong(count));
}else{
String classType = clazz.getDeclaredField(columnName).getType().getName();
if(classType.equals("java.lang.Integer")){
fieldValue =new Integer( rs.getInt(count));
}else if(classType.equals("java.lang.Long")) {
fieldValue =new Long( rs.getLong(count));
}else if(classType.equals("java.math.BigDecimal")){
fieldValue = rs.getBigDecimal(count);
}else if(classType.equals("java.lang.String")){
fieldValue = rs.getString(count);
}else if(classType.equals("java.util.Date")) {
java.sql.Date date = rs.getDate(count);
if(date!= null){
fieldValue = new java.util.Date(date.getTime());
}
}else{
fieldValue = rs.getString(count);
}
}
}
return fieldValue;
}
}