The complex query VO

[jnmorgan]

Presented: a solution to the complex query ORM problem. The source is included in the first three responses.

The dilemma is complex queries that are the bane of middle ware. The choices are to either span multiple VOs or hand write a "View" VO. This post describes and provides a code set for writing custom VOs for the complex query.

The motivation is a reengineering effort to transform an old poorly architected web application to a Struts/Spring application with a common set of Dao and Vo. The plan is to extract the queries and place them into DaoImpls and generate VOs based on the selection clause in the SQL. Many of the queries require generic single table VOs however, there are 20 to 30 that span multiple tables. Hence the need for a custom query VO generator. This reengineering is accomplised as a tax on other projects that are proposed on the old code sections. This is the fastest way to get from JDBC infected code to an SOA base.

To deploy and use this code create the directory structure (assumed to be under some
path, workspace and project structure):

<workspace>/<project>\src\test\tools\vogenerator
<workspace>/<project>\bin
<workspace>/<project>\lib

(for separation of class and source files in eclipse make a bin at the same level as the src directory.)

Drop the two java files in the vogenerator directory.
Drop the spring config in the the default pkg (src) directory.

you will need the following jars placed in the lib:
commons-collections.jar
commons-dbcp.jar
commons-logging.jar
commons-pool.jar
log4j-1.2.8.jar
mysql-connector-java-3.1.12-bin.jar (or whatever flavor you're using)
spring.jar

If you are using Eclipse, simply open it to the workspace path.
File -> New -> Project -> select "Java Project" -> enter your project name.
Eclipse should recognize the existing structure, so select finish.
Add the jars to the classpath.

Finally, edit the spring config file to reflect your needs.

The VO will be generated based on the MetaData that is returned from the SQL. The generated VO will have all the variables described in the selection clause of the SQL with all the functionality to extract (RowMapper interface), get and set. The VO will have a filename that is the "Java-tized" DB table name.

In your project, assuming your DaoImpls extend JdbcDaoSupport, replace all the old jdbc code with:

String sql = "select <somthing from many tables> FROM <many tables> WHERE <blah, blah blah>
list = getJdbcTemplate().query(sql,myCustomGeneratedMulti TableSpanningRowMappingVo);

that's it ... 10 minutes to solution.

[jnmorgan]

package test.tools.vogenerator;

import java.io.File;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * To generate a VO set the parameters in the spring config file.  The name of the VO is 
 * the "Java-tised" tablename. 
 * The VOs will be written the the <path> + <pkg> directory. 
 * 
 * @author jmorgan
 *
 */
public class VoGenerator extends JdbcDaoSupport {
	private Logger logger = Logger.getLogger(VoGenerator.class);

	private File path;
	private String pkg;
	private boolean includeTryCatch = false;
    private boolean isStdVo = false;
	private List tablenames;
	private String sql;  
	private String stdVoSql = "select * from tablename where rownum < 2";
	private GenericVo genericVo;
	
	public void execute(){
	    if (isStdVo){
			for (Iterator it = tablenames.iterator(); it.hasNext(); ){
			    String tablename = (String) it.next(); 
			    String tmpSql = stdVoSql.replaceAll("tablename", tablename);
			    generate(tablename,tmpSql);
			}
	    }
	    else{
	    	String tablename = (String) tablenames.get(0); 
			generate(tablename,sql);
	    }
	}

	/* use this if you are having trouble with the datasource */
	private void generate(String tablename, String sql ){
		genericVo.setTableName(tablename);
		logger.info("sql: " + sql );
		getJdbcTemplate().query(sql,genericVo);
		genericVo.outputColNames(path, pkg, includeTryCatch);
	}

	private void testConnection(){
		try {
			java.sql.Connection conn = this.getDataSource().getConnection();
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			e.printStackTrace();
		}
	}

	public boolean isIncludeTryCatch() {
		return includeTryCatch;
	}

	public void setIncludeTryCatch(boolean includeTryCatch) {
		this.includeTryCatch = includeTryCatch;
	}

	public boolean isStdVo() {
		return isStdVo;
	}

	public void setIsStdVo(Boolean isStdVo) {
		this.isStdVo = isStdVo.booleanValue();
	}

	public File getPath() {
		return path;
	}

	public void setPath(File path) {
		this.path = path;
	}

	public String getPkg() {
		return pkg;
	}

	public void setPkg(String pkg) {
		this.pkg = pkg;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	public List getTablenames() {
		return tablenames;
	}

	public void setTablenames(List tablenames) {
		this.tablenames = tablenames;
	}

	public GenericVo getGenericVo() {
		return genericVo;
	}
	public void setGenericVo(GenericVo genericVo) {
		this.genericVo = genericVo;
	}
	
	public static void main (String[] args){
		ApplicationContext ctx = new ClassPathXmlApplicationContext(new String[]{"vogen-config.xml"}); 
		VoGenerator data = (VoGenerator)ctx.getBean("vogen");
		data.execute();
	}
}

[jnmorgan]

package test.tools.vogenerator;

import java.io.File;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeMap;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;

public class GenericVo implements RowMapper {
	

	private Object voUnderTest;
	Logger logger = Logger.getLogger(GenericVo.class);

	private Map colMap = new TreeMap();
	private Set nonNullableTypes;
	private String tablename;
	
	/**
	 * This was the original intent of the tool, insuring that the VO under test
	 * had getters and setters that reflected the types in the database.
	 */
	public Object getById(String pkName, Object pkValue,  Class voClass) throws Exception{
		logger.debug("in getById");
		voUnderTest = voClass.newInstance(); 
		List list = null;
		String classname = voClass.getName();
		String tablename = classname.substring(classname.lastIndexOf(".")+1);
		String sql = "SELECT * FROM " + tablename + " where " + pkName + " = '" + pkValue + "'";
		logger.debug("sql: " + sql);
		//list = getJdbcTemplate().query(sql, this);
		return list.get(0);
	}
	
	public void setTableName(String tablename){
		this.tablename = tablename.toUpperCase();
	}
	
	public void reset(){
		colMap.clear();
	}

	public Object mapRow(ResultSet rs, int rownum) throws SQLException {
		ResultSetMetaData metaData = rs.getMetaData();
		int colcnt = metaData.getColumnCount();
		for (int i = 1; i <= colcnt; i++){
			String colname = metaData.getColumnName(i).toUpperCase(); 
			String colclassname = metaData.getColumnClassName(i);
			int isnullable = metaData.isNullable(i);
			colMap.put(colname, colclassname);
			logger.debug(colname + "   " + colclassname + "  isNullable: " + isnullable);
		}
		return null;
	}

	public void outputColNames(File path, String pkg, boolean includeTryCatch){
		PrintWriter writer=null;
		try{
			String classname = makeName(tablename);
			File fullpath = new File(path,pkg.replace('.','/'));
			logger.info("fullpath: " + fullpath );
			fullpath.mkdirs();
			File file = new File(fullpath,classname + ".java");
			writer = new PrintWriter(file);
			writer.println("package " + pkg + ";");
			writer.println();
			writer.println("import java.sql.Date;");
			writer.println("import java.sql.ResultSet;");
			writer.println("import java.sql.Timestamp;");
			writer.println("import java.sql.SQLException;");
			writer.println("import java.math.BigDecimal;");
			writer.println("import java.io.Serializable;");
   			writer.println("import org.springframework.jdbc.core.support.JdbcDaoSupport;");
			writer.println("import org.springframework.jdbc.core.RowMapper;");
			writer.println();
			writer.println("public class " + classname + " extends JdbcDaoSupport implements Serializable, RowMapper {");
			writer.println();
			writer.flush();
			//print the Static Names
			writer.println("\tpublic static final String TABLENAME " + " = \"" + tablename + "\"; " );
			for (Iterator keys = colMap.keySet().iterator(); keys.hasNext();){
				String colname = (String)keys.next();
				writer.println("\tpublic static final String " + colname + " = \"" + colname + "\"; " );
			}
			writer.println();
			writer.flush();
			//print the private vars
			for (Iterator keys = colMap.keySet().iterator(); keys.hasNext();){
				String colname = (String)keys.next();
				String varname = makeVarName(colname);
				String coltype = (String)colMap.get(colname);
				coltype = coltype.substring(coltype.lastIndexOf(".")+1);
				//private BigDecimal something;
				writer.println("\tprivate " + coltype + " " + varname + ";" );
			}

			//print the private vars predicates
			writer.println();
			writer.flush();
			for (Iterator keys = colMap.keySet().iterator(); keys.hasNext();){
				String colname = makeVarName((String)keys.next());
				//private BigDecimal something;
				writer.println("\tprivate boolean " + colname + "Set = false;" );
			}

			//print the getters and setters
			writer.println();
			writer.flush();
			for (Iterator keys = colMap.keySet().iterator(); keys.hasNext();){
				String colname = (String)keys.next();
				String methodName = makeName(colname);
				String varName = makeVarName(colname);
				String coltype = (String)colMap.get(colname);
				coltype = coltype.substring(coltype.lastIndexOf(".")+1);
				//private BigDecimal something;
				writer.println("\tpublic " + coltype + " get" + methodName + "(){ return this." + varName + ";}" );
				writer.println("\tpublic void set" + methodName + "( " + coltype + " " + varName +  "){ this." 
				               + varName + "=" + varName + "; " + varName + "Set = true; }" );
				writer.println("\tpublic boolean is" + methodName + "Set(){ return this." +  varName + "Set;}" );
				writer.println();
			}

			
			writer.flush();
			writer.println("\tpublic Object mapRow(ResultSet rs, int rownum) throws SQLException {");
			writer.println("\t\t"+classname + " vo = new " + classname + "();" );
			for (Iterator keys = colMap.keySet().iterator(); keys.hasNext();){
				String colname = (String)keys.next();
				String methodName = makeName(colname);
				String varName = makeVarName(colname);
				String coltype = (String)colMap.get(colname);
				String constantName = classname + "." + colname;
				coltype = coltype.substring(coltype.lastIndexOf(".")+1);
				writer.println("\t\ttry{");
				if (nonNullableTypes.contains(coltype)){
					writer.println("\t\t\tString tmp = rs.getString(" + constantName + ");" );
					writer.println("\t\t\tif (tmp != null){ " );
					writer.println("\t\t\t\tvo.set" + methodName + "( new " + coltype + "( tmp ));" );
					writer.println("\t\t\t}" );
				}else{
					writer.println("\t\t\tvo.set" + methodName + "( rs.get" + coltype + "(" + constantName + "));" );
				}
				writer.println("\t\t\t" + varName + "Set = true;");
				writer.println("\t\t}catch(SQLException e){}");
				
				
			}
			writer.println("\t\treturn vo;");
			writer.println("\t}");
			writer.println("}");
			writer.println();
			writer.flush();
		} catch(Exception e){
			e.printStackTrace();
		} finally{
			try{
				writer.flush();
				writer.close();
			}
			catch(Exception e1){}
		}
	}

	private String makeVarName(String rawName){
		StringTokenizer st = new StringTokenizer(rawName.toLowerCase(), "_");
		String name = st.nextToken();
		while (st.hasMoreTokens()){
			String segment = st.nextToken();
			String first = String.valueOf(segment.charAt(0)).toUpperCase();
			String rest = segment.substring(1);
			name = name.concat(first + rest);
			
		}
		return name;
	}
	
	private String makeName(String rawName){
		StringTokenizer st = new StringTokenizer(rawName.toLowerCase(), "_");
		String name =""; 
		while (st.hasMoreTokens()){
			String segment = st.nextToken();
			String first = String.valueOf(segment.charAt(0)).toUpperCase();
			String rest = segment.substring(1);
			name = name.concat(first + rest);
			
		}
		return name;
	}

	public Set getNonNullableTypes() {
		return nonNullableTypes;
	}

	public void setNonNullableTypes(Set nullTypeMap) {
		this.nonNullableTypes = nullTypeMap;
	}
}

[jnmorgan]

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

	<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/gunshopinventory" />
		<property name="username" value="jdbc" />
		<property name="password" value="asdfasdf" />
	</bean>

	<!-- The path where the output files will be placed -->
	<bean id="path" class="java.io.File">
		<constructor-arg type="java.lang.String" value="/vogen/vo/src" />
	</bean>
 
	<bean id="vogen" class="test.tools.vogenerator.VoGenerator">
		<property name="dataSource"><ref local="datasource"/></property>
		<!-- The path where the output files will be placed -->
		<property name="path" ><ref local="path" /></property>
		<!-- the package that will be written in the file -->
		<property name="pkg" value="com.mycorp.myapp.db.vo" />
		<!-- In standard VOs I place try catch statements around the  -->
		<!-- Sorry, but this will be implemented in the future  -->
		<property name="includeTryCatch" ><ref local="true" /></property>
		<property name="isStdVo" ><ref local="false" /></property>
		<property name="tablenames" >
			<list>
				<value>Get_PrintText_And_Comments</value>
			</list>
		</property>
		<property name="sql" >
			<value>
				SELECT i.ITEM_DESC_ID, id.item_desc_printtext, i.item_comments FROM item i, item_desc id  where i.item_desc_id = id.item_desc_id
			</value>
		</property>
		<property name="genericVo"><ref local="genericVo" /></property>
	</bean>


	<bean id="genericVo" class="test.tools.vogenerator.GenericVo" >
		<property name="nonNullableTypes">
			<set>
				<value>Integer</value>
				<value>Long</value>
				<value>BigDecimal</value>
			</set>
		</property>
	
	</bean>
	
	<bean id="true" class="java.lang.Boolean">
		<constructor-arg type="java.lang.String" value="TRUE" />
	</bean>
	<bean id="false" class="java.lang.Boolean">
		<constructor-arg type="java.lang.String" value="FALSE" />
	</bean>
</beans>

[jnmorgan]

package com.mycorp.myapp.db.vo;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.SQLException;
import java.math.BigDecimal;
import java.io.Serializable;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.RowMapper;

public class GetPrinttextAndComments extends JdbcDaoSupport implements Serializable, RowMapper {

	public static final String TABLENAME  = "GET_PRINTTEXT_AND_COMMENTS"; 
	public static final String ITEM_COMMENTS = "ITEM_COMMENTS"; 
	public static final String ITEM_DESC_ID = "ITEM_DESC_ID"; 
	public static final String ITEM_DESC_PRINTTEXT = "ITEM_DESC_PRINTTEXT"; 

	private String itemComments;
	private Integer itemDescId;
	private String itemDescPrinttext;

	private boolean itemCommentsSet = false;
	private boolean itemDescIdSet = false;
	private boolean itemDescPrinttextSet = false;

	public String getItemComments(){ return this.itemComments;}
	public void setItemComments( String itemComments){ this.itemComments=itemComments; itemCommentsSet = true; }
	public boolean isItemCommentsSet(){ return this.itemCommentsSet;}

	public Integer getItemDescId(){ return this.itemDescId;}
	public void setItemDescId( Integer itemDescId){ this.itemDescId=itemDescId; itemDescIdSet = true; }
	public boolean isItemDescIdSet(){ return this.itemDescIdSet;}

	public String getItemDescPrinttext(){ return this.itemDescPrinttext;}
	public void setItemDescPrinttext( String itemDescPrinttext){ this.itemDescPrinttext=itemDescPrinttext; itemDescPrinttextSet = true; }
	public boolean isItemDescPrinttextSet(){ return this.itemDescPrinttextSet;}

	public Object mapRow(ResultSet rs, int rownum) throws SQLException {
		GetPrinttextAndComments vo = new GetPrinttextAndComments();
		try{
			vo.setItemComments( rs.getString(GetPrinttextAndComments.ITEM_COMMENTS));
			itemCommentsSet = true;
		}catch(SQLException e){}
		try{
			String tmp = rs.getString(GetPrinttextAndComments.ITEM_DESC_ID);
			if (tmp != null){ 
				vo.setItemDescId( new Integer( tmp ));
			}
			itemDescIdSet = true;
		}catch(SQLException e){}
		try{
			vo.setItemDescPrinttext( rs.getString(GetPrinttextAndComments.ITEM_DESC_PRINTTEXT));
			itemDescPrinttextSet = true;
		}catch(SQLException e){}
		return vo;
	}
}

[tdev]

Maybe take a look at iBatis http://ibatis.apache.org/ and Spring support for iBatis too