JDBC通用查詢經典實例
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;
}
}</pre>