- 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;
- }
- }
来源: http://www.phpxs.com/code/1001730/