JDBC通用查询经典实例，JDBC通用经典实例,import java.

JDBC通用查询经典实例，JDBC通用经典实例,import java.

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;        }    }