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