All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • com.jquicker.persistent.rdb.util.ResultSetUtils Maven / Gradle / Ivy

    There is a newer version: 1.1.0
    Show newest version
    package com.jquicker.persistent.rdb.util;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.jquicker.commons.util.ObjectUtils;
import com.jquicker.model.BaseEntity;
import com.jquicker.model.ResultMap;

/**
 * java.sql.ResultSet工具类
 *
 * @author OL
 */
public class ResultSetUtils {

	/**
	 * 将ResultSet结果集转换成Map
    
	 * 列名与结果集数据分开存储，适用于处理大量数据的结果集
	 * 
	 * @param resultSet
	 * @param moreMeta
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static ResultMap convert(ResultSet resultSet, boolean moreMeta) {
		ResultMap map = new ResultMap();
		List fields = new ArrayList();
		List> fieldList = moreMeta ? new ArrayList>() : null;
		List> records = new ArrayList>();
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			for (int i = 1; i <= colCount; i++) {
				String name = metaData.getColumnLabel(i);
				fields.add(name);
				if (moreMeta) {
					ResultMap field = new ResultMap();
					field.put("name", name);
					field.put("size", metaData.getColumnDisplaySize(i));
					field.put("precision", metaData.getPrecision(i));
					field.put("scale", metaData.getScale(i));
					field.put("type", metaData.getColumnType(i));
					String typeName = metaData.getColumnTypeName(i);
					if (name.startsWith("lob_") && typeName.equalsIgnoreCase("varchar2"))
						field.put("typeName", "blob");
					else
						field.put("typeName", typeName);
					fieldList.add(field);
				}
			}
			while (resultSet.next()) {
				List record = new ArrayList();
				for (int i = 1; i <= colCount; i++) {
					record.add(resultSet.getObject(i));
					// record.add(getValue(rs, i));
				}
				records.add(record);
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		if (fieldList == null) {
			map.put("fs", fields);
		} else {
			map.put("fs", fieldList);
		}
		map.put("rs", records);
		return map;
	}

	/**
	 * 将ResultSet结果集转换成key-value形式的集合
    
	 * 适用于处理单行的结果集
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static ResultMap convertToMap(ResultSet resultSet) {
		ResultMap map = new ResultMap();
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					map.put(name, resultSet.getObject(i));
				}
				break;
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map.isEmpty() ? null : map;
	}
	
	public static  T convertTo(ResultSet resultSet, Class clazz) {
		T entity = null;
		try {
			entity = clazz.newInstance();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					entity.set(name, resultSet.getObject(i));
				}
				break;
			}
			entity.toString(); // 不激活一下，前端无法取到值 ??? TODO 
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return entity.isEmpty() ? null : entity;
	}
	
	/**
	 * 将ResultSet结果集转换成key-value形式的列表
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static List> convertToList(ResultSet resultSet) {
		List> list = new ArrayList>();
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				ResultMap map = new ResultMap();
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					map.put(name, resultSet.getObject(i));
				}
				list.add(map);
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list.isEmpty() ? null : list;
	}
	
	public static  List convertToList(ResultSet resultSet, Class clazz) {
		List list = new ArrayList();
		T entity = null;
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				entity = clazz.newInstance();
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					entity.set(name, resultSet.getObject(i));
				}
				list.add(entity);
				entity.toString(); // 不激活一下，前端无法取到值 ??? TODO 
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return list.isEmpty() ? null : list;
	}
	
	/**
	 * 将ResultSet结果集转换成List列表
    
	 * 若ResultSet结果集不是唯一列，取第一列
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static  List convertToSingleList(ResultSet resultSet, Class clazz) {
		List list = new ArrayList();
		try {
			while (resultSet.next()) {
				Object obj = resultSet.getObject(1);
				list.add((T) ObjectUtils.convert(obj, clazz));
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list.isEmpty() ? null : list;
	}
	
	public static Set> convertToSet(ResultSet resultSet) {
		Set> set = new HashSet>();
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				ResultMap map = new ResultMap();
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					map.put(name, resultSet.getObject(i));
				}
				set.add(map);
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set.isEmpty() ? null : set;
	}
	
	public static  Set convertToSet(ResultSet resultSet, Class clazz) {
		Set set = new HashSet();
		T entity = null;
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int colCount = metaData.getColumnCount();
			while (resultSet.next()) {
				entity = clazz.newInstance();
				for (int i = 1; i <= colCount; i++) {
					String name = metaData.getColumnLabel(i);
					entity.set(name, resultSet.getObject(i));
				}
				set.add(entity);
				entity.toString(); // 不激活一下，前端无法取到值 ??? TODO 
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return set.isEmpty() ? null : set;
	}
	
	/**
	 * 将ResultSet结果集转换成Set列表
    
	 * 若ResultSet结果集不是唯一列，取第一列
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static  Set convertToSingleSet(ResultSet resultSet, Class clazz) {
		Set set = new HashSet();
		try {
			Object obj = resultSet.getObject(1);
			set.add((T) ObjectUtils.convert(obj, clazz));
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set.isEmpty() ? null : set;
	}
	
	/**
	 * 将ResultSet结果集转换成唯一结果
    
	 * 若ResultSet结果集不唯一，取第一行第一列
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static Object convertToOne(ResultSet resultSet) {
		try {
			while (resultSet.next()) {
				return resultSet.getObject(1);
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 将ResultSet结果集转换成唯一结果
    
	 * 若ResultSet结果集不唯一，取第一行第一列
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 * @author OL
	 */
	public static  T convertToOne(ResultSet resultSet, Class clazz) {
		try {
			while (resultSet.next()) {
				Object obj = resultSet.getObject(1);
				return (T) ObjectUtils.convert(obj, clazz);
			}
			resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static String[] getColumns(ResultSet resultSet){
		try {
			ResultSetMetaData metaData = resultSet.getMetaData();
			int count = metaData.getColumnCount();
			String[] columns = new String[count];
			for (int i = 0; i < count; i++) {
				columns[i] = (metaData.getColumnLabel(i + 1));
			}
			resultSet.close();
			return columns;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Object getValue(ResultSet resultSet, int column) throws SQLException {
		Object obj = resultSet.getObject(column);
		if (obj != null) {
			int type = resultSet.getMetaData().getColumnType(column);
			switch (type) {
			case Types.BIT:
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.INTEGER:
			case Types.FLOAT:
			case Types.REAL:
			case Types.DOUBLE:
			case Types.NUMERIC:
			case Types.DECIMAL:
				double d = resultSet.getDouble(column);
				try {
					int i = (new Double(d)).intValue();
					BigDecimal b1 = new BigDecimal(d);
					BigDecimal b2 = new BigDecimal(i);
					int compare = b1.compareTo(b2);
					if (compare == 0)
						obj = i;
					else
						obj = d;
				} catch (Exception ex) {
					obj = d;
				}
				break;
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				obj = resultSet.getString(column);
				break;
			case Types.DATE:
			case Types.TIME:
				Timestamp timestamp = resultSet.getTimestamp(column);
				Date date = new Date(timestamp.getTime());
				obj = date;
				break;
			case Types.TIMESTAMP:
				obj = resultSet.getTimestamp(column);
				break;
			}
		}
		return obj;
	}

}
    
    
    
    

    




    
    
    © 2015 - 2024 Weber Informatics LLC | Privacy Policy