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

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。