Announcement Announcement Module
Collapse
No announcement yet.
Generic DAO and Generic Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generic DAO and Generic Stored Procedure

    Hi,

    I have been working on revamp of a web application which is going to be built on using the integration of Spring2.5.6+Hibernate3.2+JPA+JSF1.2. To access the database, a generic DAO has been defined. There are thousands of stored procedures in the application, and it is quite unsuitable to write an inner stored procedure class for each stored procedure in the database. So A generic Stored Procedure class has been defined. Following are the Generic Dao, and Generic Stored procedure classes that have been defined.

    Code:
    @Repository
    public class GenericDAOImpl<T, ID extends Serializable> implements IGenericDAO<T, ID>
    {
    	
    	private EntityManager entityManager;	
    
    	@PersistenceContext
    	public void setEntityManager(EntityManager entityManager)
    	{
    		this.entityManager = entityManager;
    		
    	}
    	
    	protected EntityManager em()
    	{
    		return entityManager;
    	}
    	
    	public T find(Class<T> type, ID id)
    	{		
    		return em().find(type, id);
    		
    	}
    	
    	@SuppressWarnings("unchecked")
    	public List<T> findAll(Class<T> type)
    	{				
    		return em().createQuery("select _it_ from " + type.getName() + " _it_").getResultList();
    	}
    	
    	public boolean remove(T entity)
    	{		
    		boolean deletedFlag = false;
    		
    		if ( entity != null )
    		{
    			em().remove(entity);
    			deletedFlag = true;
    		}
    		return deletedFlag;
    	}
    	
    	public T save(T entity)
    	{		
    		return em().merge(entity);
    	}
    	
    	public T update(T entity)
    	{		
    		return em().merge(entity);
    	}
    	
    	public boolean removeById(Class<?> type, ID id)
    	{
    		
    		boolean deleteFlag = false;
    		
    		Query query = em().createQuery("select id from " + type.getName() + " where id = ?").setParameter(1, id);
    		if ( query.getResultList().size() != 0 )
    		{
    			em().remove(em().getReference(type, id));
    			deleteFlag = true;
    		}
    		return deleteFlag;
    		
    	}
    	
    	@SuppressWarnings("unchecked")
    	public List findJPQL(String jpql, Object [] values)
    	{
    		System.out.println("values===" + values);
    		int paramCount = 1;
    		Query query = em().createQuery(jpql);
    		
    		for ( Object parametr : values )
    		{			
    			query.setParameter(paramCount, parametr);
    			paramCount++;
    		}
    		List resultList = query.getResultList();		
    		return resultList;
    		
    	}
    		
    	public Integer executeNativeQuery(String nativeQuery, T [] values)
    	{
    		
    		int paramCount = 1;
    		int returnValue = 0;
    		Query query = (Query) em().createNativeQuery(nativeQuery);
    		for ( T parametr : values )
    		{
    			
    			query.setParameter(paramCount, parametr);
    			paramCount++;
    		}
    		query.executeUpdate();
    		
    		return returnValue;
    		
    	}
    	
    	@SuppressWarnings("unchecked")
    	public List findByNativeQuery(String nativeQuery, Object [] values)
    	{
    		
    		int paramCount = 1;
    		Query query = em().createNativeQuery(nativeQuery);
    		
    		for ( Object parametr : values )
    		{
    			
    			query.setParameter(paramCount, parametr);
    			paramCount++;
    		}
    		return query.getResultList();
    		
    	}
    }

    And here is the Generic Stored Procedure Class


    Code:
    public class GenericStoredProcedure 
    {
    	private static EdocLogger log = new EdocLogger(GenericStoredProcedure.class);
    	private DataSource dataSource;
    
    	/**
    	 * @param dataSource
    	 *  
    	 */
    	public void setDataSource(DataSource dataSource) {
    		this.dataSource = dataSource;
    	}
    
    	/**
    	 * @return the dataSource
    	 */
    	public DataSource getDataSource() {
    		return dataSource;
    	}
    	
    	/**
    	 * This method requires LinkedHashMaps for inParams and outParams so that parameters can be set
    	 * in a sequence
    	 * @param StoredProcedureName
    	 * @param inParams
    	 * @param outParams
    	 * @return
    	 */
    	public Map executeSimpleProcedure(String procedureName, Map<String, Long> inParams,	Map<String, RowMapperContainer> outParams)
    	{
    		
    		InnerStoredProcedure innerStoredProcedure= new InnerStoredProcedure(dataSource, procedureName,inParams, outParams);
    		
    		return innerStoredProcedure.executeProcedure(inParams);
    	}
    	
    	/**************************************START OF INNER CLASS*************************************************/
    	/***********************************************************************************************************/		
    	/**
    	 * INNER CLASS 'InnerStoredProcedure' IS ONLY FOR THE USE OF OUTER CLASS GenericStoredProcedure
    	 * @author mijaz
    	 *
    	 */
    	private class InnerStoredProcedure extends StoredProcedure 
    	{
    		
    		public InnerStoredProcedure(DataSource ds, String SQL, Map<String, Long> inParams, Map<String, RowMapperContainer> outParams) 
    		{
    			setDataSource(ds);
    			// setFunction(true);
    			setSql(SQL);
    			configerParameters(inParams, outParams);
    			compile();
    		}
    		
    		/**
    		 * Configure the input and output parameters for the stored procedure
    		 * @param inParams
    		 * @param outputParamers
    		 */
    		public void configerParameters(Map<String, Long> inParams, Map<String, RowMapperContainer>outputParamers) 
    		{
    			if (inParams != null && inParams.size() > 0) 
    			{
    				// entrySet()
    				Iterator<String> inParamsIterator = inParams.keySet().iterator();
    				while (inParamsIterator.hasNext()) 
    				{
    					String key = inParamsIterator.next();
    					declareParameter(new SqlParameter(key, Types.INTEGER));
    				}
    
    			}
    			
    			Iterator <String> keySetIterator = outputParamers.keySet().iterator();
    			while (keySetIterator.hasNext()) 
    			{
    				String key = keySetIterator.next();
    
    				RowMapperContainer rowMapperContainer = (RowMapperContainer) outputParamers.get(key);
    				int sqlType = rowMapperContainer.getType();
    				if (sqlType == OracleTypes.CURSOR) 
    				{
    					declareParameter(new SqlOutParameter(key,OracleTypes.CURSOR, rowMapperContainer.getRowMapper()));
    				}
    				else if (sqlType == OracleTypes.VARCHAR) 
    				{
    					declareParameter(new SqlOutParameter(key,OracleTypes.VARCHAR));
    				}
    				else if (sqlType == OracleTypes.STRUCT) 
    				{
    					declareParameter(new SqlOutParameter(key,OracleTypes.STRUCT,rowMapperContainer.getTypeName(), rowMapperContainer.getSqlReturnType()));
    				}
    				else if (sqlType == OracleTypes.BIT) 
    				{
    					//TODO ...
    				}
    				else if (sqlType == OracleTypes.ARRAY) 
    				{
    					//TODO ...
    				}
    				
    			}
    
    		}
    
    		public Map executeProcedure(Map inputs) {
    
    			return execute(inputs);
    		}
    	}
    	/**************************************END OF INNER CLASS 'InnerStoredProcedure'**************************/
    	/*********************************************************************************************************/
    	
    }
    
    // END OF CLASS 'GenericStoredProcedure'

    Following code is being used to call the Generic Stored Procedure

    Code:
    public String testProcMultiRows()
    	{
    		
    		
    		GenericStoredProcedure genericStoredProcedure =(GenericStoredProcedure) SpringApplicationContext.getBean("genericStoredProcedure");
    		
    		String procedureName = "ABC_GS_PROCEDURE";
    		
    		Map <String, Long>inMap = new LinkedHashMap<String, Long>(2);
    		inMap.put("visitID", new Long(26990));		
    
    		Map <String, RowMapperContainer>outMap = new LinkedHashMap<String, RowMapperContainer>(3);
    
    		outMap.put("retString", new RowMapperContainer(OracleTypes.VARCHAR));
    		outMap.put("icd", new RowMapperContainer(OracleTypes.CURSOR, new GenericRowMapper()));
    		//outMap.put("cursor2", new RowMapperContainer(OracleTypes.CURSOR,new GenericRowMapper()));		
    		
    		
    		log.info("START CALL TO STORED PROCESURE { "+ procedureName+" }");
    		Map resultMap = genericStoredProcedure.executeSimpleProcedure(procedureName, inMap, outMap);
    		log.info("END CALL TO STORED PROCESURE { "+ procedureName+" }");
    		log.info("START CALL TO PRINT METHOD");
    		print(resultMap);
    		log.info("END CALL TO PRINT METHOD");
    		return null;
    	}
    	
    	@SuppressWarnings("unchecked")
    	public void print(Map resultMap)
    	{
    		List parentList = (List)resultMap.get("icd");
    		
    		Iterator parentIterator = parentList.iterator();
    		while(parentIterator.hasNext())
    		{
    			List icdList = (List)parentIterator.next();
    			Iterator icdIterator = icdList.iterator();
    			while(icdIterator.hasNext())
    			{
    				log.info("ICD: "+ icdIterator.next());
    			}
    		}
    	}

    I need your precious comments about the approach that I have been adopted to access the database through Generic DAO and Generic Stored Procedure. If this is the right approach, how can I improve it...?
    Last edited by ranaijazahmad; Nov 16th, 2009, 11:53 AM.
Working...
X