Java中的结果集转换器 [英] Resultset convertor in java
本文介绍了Java中的结果集转换器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
JDBC 中是否可以将结果集内容直接获取到List中.我需要类似下面的通用内容.
Is there any way in JDBC to get resultset contents directly into List. I need something generic like below.
不用说只需要SELECT查询(因为我的应用程序是用于报告目的的)
List<Customer> blockedCustomerList = executeQuery(sql, Customer.class);
我写了这样的东西，它工作正常，但与此同时我无法获取BLOB和CLOB值.欢迎任何建议.
I wrote something like this, it working fine but with this i am unable to get BLOB and CLOB values. Any suggestions are welcome.
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ResultSetConverter {
/**
* Generates DB column and java Domain class property mapping
*
* @param pInputClassName
* java Domain class, fully qualified name(package+java class)
* @return
* Map<String,ColumnMappingDTO>, mapping of DB column and Java Doamin class property
* @throws Exception
* if pInputClassName is not loaded properly
*/
private static Map<String,ColumnMappingDTO> initColumnMappings(String pInputClassName) throws Exception{
Class mClassObj=null;
Field[] mFieldsArr=null;
Annotation[] mAnnotations =null;
ColumnMappingDTO vColumnMappingDTO=null;
javax.persistence.Column myAnnotation=null;
Map<String,ColumnMappingDTO> outMappingDetails =new HashMap<String, ColumnMappingDTO>();
try{
//try loading Domain Object Class
mClassObj=Class.forName(pInputClassName);
//get fields in class
mFieldsArr=mClassObj.getDeclaredFields();
for(Field vField:mFieldsArr){
mAnnotations=vField.getDeclaredAnnotations();
for(Annotation vAnnotation:mAnnotations){
if(vAnnotation instanceof javax.persistence.Column){
vColumnMappingDTO = new ColumnMappingDTO();
myAnnotation= (javax.persistence.Column) vAnnotation;
vColumnMappingDTO.setPropertyName(vField.getName());
vColumnMappingDTO.setColumnName(myAnnotation.name());
outMappingDetails.put(vColumnMappingDTO.getColumnName(), vColumnMappingDTO);
}
}
}
}catch(ClassNotFoundException e){
System.out.println("caught Exception in initColumnMappings() as "+e);
throw e;
}catch(Exception ex){
System.out.println("caught Exception in initColumnMappings() as "+ex);
throw ex;
}
return outMappingDetails;
}
/**
* Executes sql passed inform of PreparedStatement inPstmt and generated List genere as String pDomainClassName
*
* @param inPstmt
* Sql to be executed inform of PreparedStatement
* @param pDomainClassName
* fully qualified Class name of DTO
* @return
* Object, later to be type casted to List<pDomainClassName>
* @throws Exception
* When Mapping is not missing or done wrong
*
*/
public static Object executeQuery(PreparedStatement inPstmt,String pDomainClassName)throws SQLException,Exception{
ResultSet mRSet=null;
ResultSetMetaData mRsetMt=null;
ColumnValueDTO mColumnValueDTO =null;
List<String> mColumnNamesList =new ArrayList<String>();
List<ColumnValueDTO> mColumnValuesList=null;
List<Object> outResultList =new ArrayList<Object>();
Map<String,ColumnMappingDTO> mMappingDetailsMap =null;
String[] mArrColumnValues=null;
String mColumnName=null;
int mColumnCount=-1;
try{
//generate DB Column and Domain Class property mapping
mMappingDetailsMap=initColumnMappings(pDomainClassName);
//execute sql
mRSet=inPstmt.executeQuery();
if(mRSet!=null){
//get ResultSetMetaData
mRsetMt=mRSet.getMetaData();
if(mRsetMt!=null){
mColumnCount =mRsetMt.getColumnCount();
mArrColumnValues= new String[mColumnCount];
//generate SELECT columns list
for(int i=0;i<mColumnCount;i++){
mColumnName=mRsetMt.getColumnName(i+1);
mColumnNamesList.add(mColumnName);
}
}
while(mRSet.next()){
mColumnValuesList =new ArrayList<ColumnValueDTO>();
for(String columnHeader:mColumnNamesList){
mColumnValueDTO= new ColumnValueDTO();
if(mMappingDetailsMap.get(columnHeader)!=null){
mColumnValueDTO.setPropertyName(mMappingDetailsMap.get(columnHeader).getPropertyName());
mColumnValueDTO.setPropertyValue(mRSet.getString(mMappingDetailsMap.get(columnHeader).getColumnName()));
mColumnValuesList.add(mColumnValueDTO);
}
}
//
Object domainObj=createDomainObject(mColumnValuesList,pDomainClassName);
//Add Object to out List
outResultList.add(domainObj);
}
}
}catch(Exception ex){
System.out.println(" caught in executeQuery() "+ex);
throw ex;
}finally{
//release resources
try {
mRSet.close();
} catch (SQLException e) {
System.out.println(" caught in Exception while closing ResultSet "+e);
throw e;
}
}
return outResultList;
}
private static Object createDomainObject(List<ColumnValueDTO> columnValuesList,String vDoaminClass) throws Exception{
Class domainClassObj=null;
Object domainObj=null;
Field domainDataField =null;
Annotation[] annotations=null;
try{
domainClassObj= Class.forName(vDoaminClass);
domainObj=domainClassObj.newInstance();
for(ColumnValueDTO columnDTO:columnValuesList){
if(columnDTO!=null){
domainDataField = domainClassObj.getDeclaredField(columnDTO.getPropertyName());
domainDataField.setAccessible(true);
annotations = domainDataField.getDeclaredAnnotations();
for(Annotation annotation : annotations){
if(annotation instanceof javax.persistence.Column){
@SuppressWarnings("unused")
javax.persistence.Column myAnnotation = (javax.persistence.Column) annotation;
}
}
domainDataField.set(domainObj,getValueByType(columnDTO.getPropertyValue(),domainDataField.getGenericType().toString(),domainDataField.getType()));
}
}
}catch(ClassNotFoundException cnfe){
System.out.println(" Caught ClassNotFoundException in createDomainObject() "+cnfe);
throw cnfe;
}catch(IllegalAccessException iae){
System.out.println(" Caught IllegalAccessException in createDomainObject() "+iae);
throw iae;
}catch(InstantiationException ie){
System.out.println(" Caught InstantiationException in createDomainObject() "+ie);
throw ie;
}catch(SecurityException se){
System.out.println(" Caught SecurityException in createDomainObject() "+se);
throw se;
}catch(NoSuchFieldException nfe){
System.out.println(" Caught NoSuchFieldException in createDomainObject() "+nfe);
throw nfe;
}catch(Exception e){
System.out.println(" Caught Exception in createDomainObject() "+e);
throw e;
}
return domainObj;
}
@SuppressWarnings("deprecation")
private static Object getValueByType(String value, String type,Type domainFieldType) throws Exception{
Object retvalue=null;
try{
if(value!=null){
if(domainFieldType.equals(Integer.TYPE)){
retvalue=new Integer(value);
}else if(domainFieldType.equals(Double.TYPE)){
retvalue=new Double(value);
}else if(domainFieldType.equals(Float.TYPE)){
retvalue=new Float(value);
}else if(domainFieldType.equals(Character.TYPE)){
retvalue=new Character(value.charAt(0));
}else if(domainFieldType.equals(Short.TYPE)){
retvalue=new Short(value);
}else if(domainFieldType.equals(Long.TYPE)){
retvalue=new Long(value);
}else if(type.equals(java.sql.Timestamp.class)){
retvalue=java.sql.Timestamp.valueOf(value);
}else if(domainFieldType.equals(java.sql.Date.class)){
retvalue= java.sql.Date.valueOf(value);
}else if(domainFieldType.equals(String.class)){
retvalue=new String(value);
}
}
}catch(Exception ex){
System.out.println(" Caught Exception in getValueByType() "+ex);
throw ex;
}
return retvalue;
}
}
Mappin DTO类:
Mappin DTO class :
public class ColumnMappingDTO {
private String columnName;
private String propertyName;
private String dataType;
/**
* @return the columnName
*/
public String getColumnName() {
return columnName;
}
/**
* @param columnName the columnName to set
*/
public void setColumnName(String columnName) {
this.columnName = columnName;
}
/**
* @return the dataType
*/
public String getDataType() {
return dataType;
}
/**
* @param dataType the dataType to set
*/
public void setDataType(String dataType) {
this.dataType = dataType;
}
/**
* @return the propertyName
*/
public String getPropertyName() {
return propertyName;
}
/**
* @param propertyName the propertyName to set
*/
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
public String toString(){
return "Database Column :: "+this.columnName+" Java Property :: "+this.propertyName+" Java Datatype :: "+this.dataType;
}
}
列值类:
import java.io.Serializable;
public class ColumnValueDTO implements Serializable {
private String propertyName;
private String propertyValue;
private static final long serialVersionUID = -4915109169715618102L;
/**
* @return the propertyName
*/
public String getPropertyName() {
return propertyName;
}
/**
* @param propertyName the propertyName to set
*/
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
/**
* @return the propertyValue
*/
public String getPropertyValue() {
return propertyValue;
}
/**
* @param propertyValue the propertyValue to set
*/
public void setPropertyValue(String propertyValue) {
this.propertyValue = propertyValue;
}
public String toString(){
return "Property :: "+this.propertyName+" Value :: "+this.propertyValue;
}
}
推荐答案
在您的示例中，您有方法
In your example you have a method
public static Object executeQuery(PreparedStatement inPstmt,String pDomainClassName)throws SQLException,Exception{
List<Object> outResultList =new ArrayList<Object>();
// the body of method
return outResultList;
}
方法声明返回对象是可悲的，但是在实现中返回一个List.但是要获得所需的内容，可以使用通用方法.
It is strage that method declaration return object but in implementation returns a List. But to get what you want you can use a generic method.
public static <T> List<T> executeQuery(PreparedStatement inPstmt,Class<T> type) throws Exception{
return (List<T>) executeQuery(inPstmt, type.getName()); //Here you will get a warning.
}
-
请注意，在您的代码中您几乎没有遇到任何问题.值解析器消耗资源，并且在比较字符串和类型时无法正常工作.并且在使用前声明对象时，代码的可重复性很难.
Note that in your code you have few issues. The value parser is resource consuming and do not work properly as you compare string and type. and the redability of the code is hard as you declare the object before usage.
/**
* Generates DB column and java Domain class property mapping
*
* @param pInputClassName
* java Domain class, fully qualified name(package+java class)
* @return
* Map<String,ColumnMappingDTO>, mapping of DB column and Java Doamin class property
* @throws Exception
* if pInputClassName is not loaded properly
*/
private static Map<String, ColumnMappingDTO> initColumnMappings(String pInputClassName) throws Exception {
Map<String, ColumnMappingDTO> outMappingDetails = new HashMap<String, ColumnMappingDTO>();
try {
for (Field vField : getFields(pInputClassName)) {
for (Annotation vAnnotation : vField.getDeclaredAnnotations()) {
if (vAnnotation instanceof Column) {
ColumnMappingDTO vColumnMappingDTO = createColumnMapping(vField, (Column) vAnnotation);
outMappingDetails.put(vColumnMappingDTO.getColumnName(), vColumnMappingDTO);
}
}
}
} catch (Exception ex) {
System.out.println("caught Exception in initColumnMappings() as " + ex);
throw ex;
}
return outMappingDetails;
}
/**
* @param vField
* @param vAnnotation
* @return
*/
private static ColumnMappingDTO createColumnMapping(Field vField, Column vAnnotation) {
ColumnMappingDTO vColumnMappingDTO = new ColumnMappingDTO();
vColumnMappingDTO.setPropertyName(vField.getName());
vColumnMappingDTO.setColumnName(vAnnotation.name());
return vColumnMappingDTO;
}
/**
* @param pInputClassName
* @return
* @throws ClassNotFoundException
*/
private static Field[] getFields(String pInputClassName) throws ClassNotFoundException {
//try loading Domain Object Class
return Class.forName(pInputClassName).getDeclaredFields();
}
/**
* Executes sql passed inform of PreparedStatement inPstmt and generated List genere as String pDomainClassName
*
* @param inPstmt
* Sql to be executed inform of PreparedStatement
* @param pDomainClassName
* fully qualified Class name of DTO
* @return
* Object, later to be type casted to List<pDomainClassName>
* @throws Exception
* When Mapping is not missing or done wrong
*
*/
public static Object executeQuery(PreparedStatement inPstmt, String pDomainClassName) throws SQLException, Exception {
return executeQuery(inPstmt, Class.forName(pDomainClassName));
}
public static <T> List<T> executeQuery(PreparedStatement inPstmt, Class<T> domainClass) throws SQLException, Exception {
List<T> outResultList = new ArrayList<T>();
ResultSet mRSet = null;
try {
// generate DB Column and Domain Class property mapping
Map<String, ColumnMappingDTO> mMappingDetailsMap = initColumnMappings(domainClass.getName());
// execute sql
mRSet = inPstmt.executeQuery();
if (mRSet == null) {
return Collections.EMPTY_LIST;
}
List<String> mColumnNamesList = getColumnNameList(mRSet);
while (mRSet.next()) {
List<ColumnValueDTO> mColumnValuesList = new ArrayList<ColumnValueDTO>();
for (String columnHeader : mColumnNamesList) {
if (mMappingDetailsMap.containsKey(columnHeader)) {
ColumnValueDTO mColumnValueDTO = new ColumnValueDTO();
mColumnValueDTO.setPropertyName(mMappingDetailsMap.get(columnHeader).getPropertyName());
mColumnValueDTO.setPropertyValue(mRSet.getString(mMappingDetailsMap.get(columnHeader).getColumnName()));
mColumnValuesList.add(mColumnValueDTO);
}
}
//
T domainObj = createDomainObject(mColumnValuesList, domainClass);
// Add Object to out List
outResultList.add(domainObj);
}
} catch (Exception ex) {
System.out.println(" caught in executeQuery() " + ex);
throw ex;
} finally {
// release resources
try {
mRSet.close();
} catch (SQLException e) {
System.out.println(" caught in Exception while closing ResultSet " + e);
throw e;
}
}
return outResultList;
}
/**
* @param mColumnNamesList
* @param mRSet
* @return
* @throws SQLException
*/
private static List<String> getColumnNameList(ResultSet mRSet) throws SQLException {
ResultSetMetaData mRsetMt = mRSet.getMetaData();
if(mRsetMt == null){
return Collections.EMPTY_LIST;
}
List<String> mColumnNamesList = new ArrayList<String>();
// generate SELECT columns list
for (int i = 0; i < mRsetMt.getColumnCount(); i++) {
mColumnNamesList.add(mRsetMt.getColumnName(i + 1));
}
return mColumnNamesList;
}
private static Object createDomainObject(List<ColumnValueDTO> columnValuesList, String vDoaminClass) throws Exception {
Class<?> domainClassObj = Class.forName(vDoaminClass);
return createDomainObject(columnValuesList, domainClassObj);
}
private static <T> T createDomainObject(List<ColumnValueDTO> columnValuesList, Class<T> domainClassObj) throws Exception {
T domainObj = null;
try {
domainObj = domainClassObj.newInstance();
for (ColumnValueDTO columnDTO : columnValuesList) {
if (columnDTO == null) {
continue;
}
Field domainDataField = domainClassObj.getDeclaredField(columnDTO.getPropertyName());
domainDataField.setAccessible(true);
Object valueByType = parseValueByType(columnDTO.getPropertyValue(), domainDataField.getType());
domainDataField.set(domainObj, valueByType);
}
} catch (Exception e) {
System.out.println(" Caught " + e.getClass().getSimpleName() + " in createDomainObject() " + e);
throw e;
}
return domainObj;
}
private static Object parseValueByType(String value, Type type) throws Exception{
if(value == null) {
return null;
}
try{
if(Integer.TYPE.equals(type)){
return Integer.parseInt(value);
}
if(Double.TYPE.equals(type)){
return Double.parseDouble(value);
}
if(Float.TYPE.equals(type)){
return Float.parseFloat(value);
}
if(Short.TYPE.equals(type)) {
return Short.parseShort(value);
}
if(Long.TYPE.equals(type)) {
return Long.parseLong(value);
}
if(java.sql.Timestamp.class.equals(type)) {
return java.sql.Timestamp.valueOf(value);
}
if(java.sql.Date.class.equals(type)) {
return java.sql.Date.valueOf(value);
}
if(String.class.equals(type)) {
return value;
}
if(Character.TYPE.equals(type)) {
if(value.length() == 1) {
return value.charAt(0);
}
if(value.length() == 0) {
return '\0';
}
throw new IllegalStateException("");
}
}catch(Exception ex){
System.out.println(" Caught Exception in getValueByType() "+ex);
throw ex;
}
throw new IllegalArgumentException("Could not find the resolver for type " + type);
}
public static class ColumnValueDTO implements Serializable {
private String propertyName;
private String propertyValue;
private static final long serialVersionUID = -4915109169715618102L;
/**
* @return the propertyName
*/
public String getPropertyName() {
return propertyName;
}
/**
* @param propertyName the propertyName to set
*/
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
/**
* @return the propertyValue
*/
public String getPropertyValue() {
return propertyValue;
}
/**
* @param propertyValue the propertyValue to set
*/
public void setPropertyValue(String propertyValue) {
this.propertyValue = propertyValue;
}
public String toString(){
return "Property :: "+this.propertyName+" Value :: "+this.propertyValue;
}
}
public static class ColumnMappingDTO {
private String columnName;
private String propertyName;
private String dataType;
/**
* @return the columnName
*/
public String getColumnName() {
return columnName;
}
/**
* @param columnName the columnName to set
*/
public void setColumnName(String columnName) {
this.columnName = columnName;
}
/**
* @return the dataType
*/
public String getDataType() {
return dataType;
}
/**
* @param dataType the dataType to set
*/
public void setDataType(String dataType) {
this.dataType = dataType;
}
/**
* @return the propertyName
*/
public String getPropertyName() {
return propertyName;
}
/**
* @param propertyName the propertyName to set
*/
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
public String toString(){
return "Database Column :: "+this.columnName+" Java Property :: "+this.propertyName+" Java Datatype :: "+this.dataType;
}
}
private static class Column {
/**
* @return
*/
public String name() {
// TODO Auto-generated method stub
return null;
}
}
这篇关于Java中的结果集转换器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
