title: Resultset convertor in java
body: <p>Is there any way in <strong>JDBC</strong> to get resultset contents directly into List. I need something generic like below.</p>
  
  <p><strong>Needless to say only required for SELECT queries(as my application is for reporting purpose)</strong></p>
  
  <pre><code>List&lt;Customer&gt; blockedCustomerList = executeQuery(sql, Customer.class);
  </code></pre>
  
  <p>I wrote something like this, it working fine but with this i am unable to get BLOB and CLOB values. Any suggestions are welcome.</p>
  
  <pre><code>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&lt;String,ColumnMappingDTO&gt;, mapping of DB column and Java Doamin class property
       * @throws Exception
       *                       if pInputClassName is not loaded properly
       */
      private static Map&lt;String,ColumnMappingDTO&gt; initColumnMappings(String pInputClassName) throws Exception{
  
          Class mClassObj=null;
          Field[] mFieldsArr=null;
          Annotation[] mAnnotations =null;
          ColumnMappingDTO vColumnMappingDTO=null;
          javax.persistence.Column myAnnotation=null;
  
          Map&lt;String,ColumnMappingDTO&gt; outMappingDetails =new HashMap&lt;String, ColumnMappingDTO&gt;();
  
          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&lt;pDomainClassName&gt;
       * @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&lt;String&gt; mColumnNamesList =new ArrayList&lt;String&gt;(); 
          List&lt;ColumnValueDTO&gt; mColumnValuesList=null;
          List&lt;Object&gt; outResultList =new ArrayList&lt;Object&gt;();
          Map&lt;String,ColumnMappingDTO&gt; 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&lt;mColumnCount;i++){
                          mColumnName=mRsetMt.getColumnName(i+1);
                          mColumnNamesList.add(mColumnName);
                      }
                  }               
  
                  while(mRSet.next()){
                      mColumnValuesList =new ArrayList&lt;ColumnValueDTO&gt;();
                      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&lt;ColumnValueDTO&gt; 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;
      }
  
  }
  </code></pre>
  
  <p>Mappin DTO class :</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Column value class :</p>
  
  <pre><code>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;
      }
  
  
  }
  </code></pre>