DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Shantanu has posted 4 posts at DZone. View Full User Profile

DB Queries in spring's application.xml.

08.13.2014
| 1296 views |
  • submit to reddit
Context XML (userDAO-queries.xml) part :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans		 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
						http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">
    
<util:map id="VALIDATE_USER_CREDENTIAL" key-type="java.lang.String" value-type="java.lang.String" >
    	<entry key="QUERY" >
    		 <value>SELECT	user_id,	 user_name,  user_password 
			FROM	ty_users 
			WHERE	user_name = ? AND user_password = ?
   		</value>
    	</entry> 
    </util:map>
</beans>




Java Code Part:

UserDAOImpl.java
public class UserDAOImpl implements UserDAO{
	
	private static final Logger logger = LoggerFactory.getLogger(UserDAOImpl.class);// logger
	
	DBQueries dbQuery = new DBQueriesImpl("userDAO-queries.xml");//
	DBConnection dbConn = ConnectionFactory.getInstance().getConnectionMySQL();
	
	@Override
	public List<User> authenitcUserDetails(String userName, String userPassword) {
		Map<String, String> queryMap = null;
		String query ;
		List<Map<String, Object>> mappedUserList = null;
		List<User> listUser = null;
		try{	
			listUser = new ArrayList<User>();
			queryMap = dbQuery.getQueryBeanFromFactory("VALIDATE_USER_CREDENTIAL"); //passing the query-name or Map Id.
			query =  dbQuery.getQuery(queryMap);//passing the query-map to get the query.

//rest of the code is common fetching code.						
			mappedUserList = dbConn.dbQueryRead(query, new Object[]{userName,userPassword});			
			if(mappedUserList.size()>0){
				listUser =  new QueryResultSetMapperImpl<User>().mapRersultSetToObject(mappedUserList, User.class);
			}
		}catch(Exception  ex){
			logger.error("fetchAllUser Error:: ", ex);
		}		
		return listUser;
	}
}








DBQueriesImpl.java
public class DBQueriesImpl implements DBQueries {
	
	private static final Logger logger = LoggerFactory.getLogger(DBQueriesImpl.class);
		
	private ApplicationContext queriesCtx ;
	
       /**
	 * @Desc: Loading the queryContext xml
	 * @param queryContext
	 */
	public DBQueriesImpl(String queryContext) {
		queriesCtx = new ClassPathXmlApplicationContext(queryContext);
	}
	
        /**
	 * @Desc: Reading from the loaded application context and getting the query-map, .  
	 */
	@Override
	public Map<String, String> getQueryBeanFromFactory(String beanId){
		Map<String, String> queryMap = null;
		if (queriesCtx != null && beanId != null) {
			queryMap = (Map<String, String>)queriesCtx.getBean(beanId);
		}
		return queryMap;
	}
	
       /**
	 * @Desc: Getting the exact query from the query-map, .  
	 */	
	@Override
	public String getQuery(Map<String, String> queryMap) {
		String query=null;
		try{
			if(queryMap.containsKey(QueryConstants.QUERY_NODE)){
				query = (String) queryMap.get(QueryConstants.QUERY_NODE);
				queryMap.remove(QueryConstants.QUERY_NODE);
			}else{
				throw new NoSuchFieldError();
			}
		}catch(Exception excp){
			excp.printStackTrace();
		}
		return query;
	}	
}

While solving one of my assignment, I realize the age long practice of writing db queries in java class file is really a big pain. They are difficult to read and understand. Further if you want to modify it again a lot of task.

Thus, to get rid of the pain, I put the db queries in spring’s context xml.

Later I realize using such injections using xml, its not only easier to maintain and understand also we can have module wise segregation of db queries in different <util:map> or in different "xyzDAO-queries.xml". Further its easier to add more customers to the existing application.

Please see the code snippet area for the context.xml called "userDAO-queries.xml". I've created Map <util:map/> with an id. In the Map I put the key as <entry key="QUERY" > and values as <value>.db queries goes here....</value> , which contains the query.

The java classes are DBQueriesImpl.java and UserDAOImpl.java for fetching the exact query from the "xyzDAO-queries.xml".

Please see the comments in the java class.

Comments

Gordon Steven replied on Mon, 2014/08/18 - 10:31pm

 Will this work with any databases? - Dr. Hicham Riba

Shantanu Sikdar replied on Tue, 2014/08/19 - 10:05am in response to: Gordon Steven

I tried two scenarios

1. I tried with DB2 and Oracle, but they are different set of SQL in a multiple database application. I used the same framework.

2. I also tried with MySQL and Oracle, but the SQL queries are very basic one.