清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>
import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @描述：利用jdbc进行常见的查询
* @author richersky
* @日期：2010-06-27
*/
public class EntityDaoImplJdbc {
private String datasourse;
/**
* 根据sql语句查询数据
* @param sql
* @param page
* @return
* @throws Exception
*/
public Page findSql(String sql, Page page) throws Exception{
JdbcUtil jdbcUtil = null;
try {
StringBuffer ssql = new StringBuffer();
ssql.append(sql);
//获取条件对应的值集合
List valueList = page.getValues();
LogUtil.infoSql(EntityDaoImplJdbc.class,valueList,"SQL语句:",ssql.toString());
jdbcUtil = new JdbcUtil(datasourse);
PreparedStatement preparedStatement = jdbcUtil.createPreparedStatement(ssql.toString());
int liSQLParamIndex = 1;
if(valueList!=null){
for(int i=0;i<valueList.size();i++){
Object obj = valueList.get(i);
this.setParameterValue(preparedStatement, i+1, obj);
liSQLParamIndex++;
}
}
ResultSet rs = preparedStatement.executeQuery();
List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
Map<String,Integer> metaDataMap = null;
while(rs.next()){
if(rs.isFirst()){
metaDataMap = this.getMetaData(rs);
}
dataList.add(this.setData(rs,metaDataMap));
}
page.setDataList(dataList);
}catch (Exception e) {
LogUtil.error(this.getClass(), e,"利用jdbc进行查询时出错！");
throw e;
}finally{
if(jdbcUtil!=null){
jdbcUtil.freeCon();
}
}
return page;
}
/**
* 根据sql查询出单条记录
* @param sql
* @return Map<String,Object>
* @throws Exception
*/
public Map<String,Object> findUniqueBySql(String sql,List<Object> valueList) throws Exception{
JdbcUtil jdbcUtil = null;
Map<String,Object> map = null;
try {
LogUtil.infoSql(EntityDaoImplJdbc.class,valueList,"SQL语句:",sql);
jdbcUtil = new JdbcUtil(datasourse);
PreparedStatement preparedStatement= jdbcUtil.createPreparedStatement(sql);
if(valueList!=null){
for(int i=0;i<valueList.size();i++){
Object obj = valueList.get(i);
this.setParameterValue(preparedStatement, i+1, obj);
}
}
ResultSet rs = preparedStatement.executeQuery();
Map<String,Integer> metaDataMap = null;
if(rs.next()){
metaDataMap = this.getMetaData(rs);
map = this.setData(rs,metaDataMap);
}
}catch (Exception e) {
LogUtil.error(this.getClass(), e,"利用jdbc进行查询时出错！");
throw e;
}finally{
if(jdbcUtil!=null){
jdbcUtil.freeCon();
}
}
return map;
}
/**
* 设置PreparedStatement预处理sql语句的值
* @param pStatement
* @param piIndex
* @param pValueObject
* @throws Exception
*/
private void setParameterValue(PreparedStatement pStatement, int piIndex,Object pValueObject) throws Exception {
if (pValueObject instanceof String) {
pStatement.setString(piIndex, (String) pValueObject);
} else if (pValueObject instanceof Boolean) {
pStatement.setBoolean(piIndex, ((Boolean) pValueObject).booleanValue());
} else if (pValueObject instanceof Byte) {
pStatement.setByte(piIndex, ((Byte) pValueObject).byteValue());
} else if (pValueObject instanceof Short) {
pStatement.setShort(piIndex, ((Short) pValueObject).shortValue());
} else if (pValueObject instanceof Integer) {
pStatement.setInt(piIndex, ((Integer) pValueObject).intValue());
} else if (pValueObject instanceof Long) {
pStatement.setLong(piIndex, ((Long) pValueObject).longValue());
} else if (pValueObject instanceof Float) {
pStatement.setFloat(piIndex, ((Float) pValueObject).floatValue());
} else if (pValueObject instanceof Double) {
pStatement.setDouble(piIndex, ((Double) pValueObject).doubleValue());
} else if (pValueObject instanceof BigDecimal) {
pStatement.setBigDecimal(piIndex, (BigDecimal) pValueObject);
} else if (pValueObject instanceof Date) {
pStatement.setDate(piIndex, (Date) pValueObject);
} else if (pValueObject instanceof Time) {
pStatement.setTime(piIndex, (Time) pValueObject);
} else if (pValueObject instanceof Timestamp) {
pStatement.setTimestamp(piIndex, (Timestamp) pValueObject);
} else {
pStatement.setObject(piIndex, pValueObject);
}
}
/**
* 根据传入的结果集返回结果集的元数据，以列名为键以列类型为值的map对象
* @param rs
* @return
* @throws SQLException
*/
private Map<String,Integer> getMetaData(ResultSet rs) throws SQLException{
Map<String,Integer> map = new HashMap<String,Integer>();
ResultSetMetaData metaData = rs.getMetaData();
int numberOfColumns = metaData.getColumnCount();
for(int column = 0; column < numberOfColumns; column++) {
String columnName = metaData.getColumnLabel(column+1);
int colunmType = metaData.getColumnType(column+1);
columnName = columnName.toLowerCase();
map.put(columnName, colunmType);
}
return map;
}
/**
* 将结果集封装为以列名存储的map对象
* @param rs
* @param metaDataMap元数据集合
* @return
* @throws Exception
*/
private Map<String,Object> setData(ResultSet rs,Map<String,Integer> metaDataMap) throws Exception {
Map<String,Object> map = new HashMap<String,Object>();
for (String columnName : metaDataMap.keySet()) {
int columnType = metaDataMap.get(columnName);
Object object = rs.getObject(columnName);
if(object==null){
map.put(columnName, null);
continue;
}
//以下并为对所有的数据类型做处理，未特殊处理的数据类型将以object的形式存储。
switch (columnType) {
case java.sql.Types.VARCHAR:
map.put(columnName, object);
break;
case java.sql.Types.DATE:
map.put(columnName, DateUtil.format(object.toString()));
break;
case java.sql.Types.TIMESTAMP:
map.put(columnName, DateUtil.format(object.toString()));
break;
case java.sql.Types.TIME:
map.put(columnName, DateUtil.format(object.toString()));
break;
case java.sql.Types.CLOB:
try{
if(object!=null){
Clob clob = (Clob)object;
long length = clob.length();
map.put(columnName, clob.getSubString(1L, (int)length));
}
}catch(Exception e){
LogUtil.error(this.getClass(), e,"将字段值从clob转换为字符串时出错@!");
}
break;
case java.sql.Types.BLOB:
map.put(columnName, "");
break;
default:
map.put(columnName, object);
break;
}
}
return map;
}
}