[Springframework-developer] Enhancement suggestion for Spring / JDBC module classes

[<Nic...@we...>]

I have a suggestion / enhancement for the Spring JDBC implementation of
using the JdbcDaoSupport  class.  It is an abstract class of it that
will do basic CRUD on a database.

I would like to get some input from the community to see if it may be
worth adding to spring to ease the maintenance for developers using the
JdbcDaoSupport / MappingSqlQuery classes.  With the current
implementation of JdbcDaoSupport, I found creating *a lot* of duplicate
code just to access the DB in general.  Note: This is by no means to
replace a ORM solution, but an extension to the Spring / JDBC option.

Here is a snippet of the code and what it does?  If necessary or
everyone thinks this may be a good cause, then I can submit it to JIRA
for later addition.....but I want to get some feedback.

Here is a snippet of the code that I have used....If necessary, I can
e-mail a concrete implementation of this as well.

/////////////////////////////////////////

package com.wellsfargo.framework.dao.jdbc;

import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.orm.ObjectRetrievalFailureException;

import com.wellsfargo.framework.util.SqlSyntaxUtil;

/**
 * Class goals: -Ease the maintenance for mapping a POJO to a jdbc
resultset for
 * INSERTS, UPDATES, DELETES, AND SELECTS.=20
 *=20
 * -Quick / Generic DB to POJO mapper using JDBC.
 * -The class will do the following:=20
 *      -generic CRUD operation on a table. INSERT, UPDATE, SELECT,
DELETE.=20
 *      -SELECT All from a table.=20
 *      -SELECT All and passing in a WHERE clause.=20
 *      -SELECT one object in database table based on the primary key.=20
 *      -Uses PreparedStatements to allow the DB to compile/optimise the
querys.
 *=20
 * -This class will not do the following:=20
 *      -Try not to re-invent any ORM solutions such as hibernate,
iBatis, JDO.=20
 *      -No extreme object inherited table structures. Use an ORM
solution.
 *=20
 * -Why use this:=20
 *     -When it doesn't make sense to use an ORM, but you need to use
 *      JDBC but to make the coding aspect a bit easier with the=20
 *      spring / jdbc implementation.
 *=20
 * @author Nick Neuberger
 */
public abstract class BasicJdbcDaoSupport extends JdbcDaoSupport {

	public BasicJdbcDaoSupport() {
		super();
	}

	public abstract String getTableName();

	public abstract String getPrimaryKey();

	public abstract String getFieldNamesWithoutPrimaryKey();

	/**
	 * This is used for the mapping of the resultset in the
BasicQuery class. It
	 * will create a new instance ie. new BlahObject() each time the
resultset
	 * is called.
	 *=20
	 * @return
	 */
	public abstract Class getPojoBean();

	/**
	 * This gets the concrete implementation of the MappingSqlQuery
class. This
	 * will be used to map the resultset to each domain object on
any retrieval
	 * desired. This class then perform basic retrievals for all
SELECT
	 * operations for the concrete classes.
	 *=20
	 * @return
	 */
	public abstract MappingSqlQuery getMappingSqlQuery();

	/**
	 * Gets all of the field names including the primary key for use
in SQL
	 * statements.
	 *=20
	 * @return
	 */
	public String getAllFieldNamesWithPrimaryKey() {
		return getPrimaryKey() + ", " +
getFieldNamesWithoutPrimaryKey();
	}

	/**
	 * Gets the SQL Statement for a SELECT all with no where class
appeneded.
	 *=20
	 * @return
	 */
	public String getSQLSelectAll() {
		return "SELECT " + getAllFieldNamesWithPrimaryKey() + "
FROM "
				+ getTableName();
	}

	/**
	 * Returns the SQL statement used by a SQL SELECT / WHERE
PRIMARYKEY =3D ?
	 *=20
	 * @return
	 */
	public String getSQLSelectByPrimaryKey() {
		return getSQLSelectAll() + " WHERE " + getPrimaryKey() +
" =3D ?";
	}

	/**
	 * Returns the SQL INSERT statement that will include the
primary key with it. This will add the number of question
	 * marks for the prepared statement based on the field count.
	 * @return
	 */
	public String getSQLInsertByPrimaryKey() {
		String sql =3D "INSERT INTO "
				+ getTableName()
				+ " ("
				+ getAllFieldNamesWithPrimaryKey()
				+ ") VALUES ("
				+ SqlSyntaxUtil
=09
.convertFieldsIntoQuestionCount(getAllFieldNamesWithPrimaryKey())
				+ ")";
		return sql;
	}

	/**
	 * Returns the SQL UPDATE statement that will include "all
field" in the update with a where clause
	 * based on the primary key of the table.
	 * @return
	 */
	public String getSQLUpdateByPrimaryKey() {
		String sql =3D "UPDATE "
				+ getTableName()
				+ " SET "
				+ SqlSyntaxUtil
=09
.convertFieldNamesToUpdateFieldNames(getFieldNamesWithoutPrimaryKey())
				+ " WHERE " + getPrimaryKey() + " =3D ?";
		return sql;
	}
=09
	/**
	 * Removes an object from the database passed in
	 *=20
	 * @param primarykey
	 *            field.
	 */
	public void removeObjectByPrimaryKey(Object lId) {
		getJdbcTemplate().update(
				"DELETE FROM " + getTableName() + "
WHERE " + getPrimaryKey()
						+ " =3D ?", new Object[] {
lId });
	}

	/**
	 * Performs a SELECT (ALL) FROM TABLE with no where clause.
	 *=20
	 * NOTE: Be careful on this operation. Basically this should be
used
	 * sparingly if your table is too big. This is not meant to
handle
	 * "thousands of records.
	 *=20
	 * @return
	 */
	public List getAll() {
		List list =3D null;
		MappingSqlQuery theMappingSqlQuery =3D
getMappingSqlQuery();

		// set the required stuff to run a Select All operation.
		theMappingSqlQuery.setDataSource(getDataSource());
		theMappingSqlQuery.setSql(getSQLSelectAll());
		theMappingSqlQuery.compile();
		list =3D theMappingSqlQuery.execute();

		return list;
	}

	/**
	 * Returns an object based on the primary key of the table.
	 *=20
	 * @param lId
	 * @return returns the pojo if found, if not, it will throw a
	 *         ObjectRetrievalFailureException
	 * @see org.springframework.orm.ObjectRetrievalFailureException
	 */
	public Object getObjectByPrimaryKey(Object lId) {
		Object object =3D getObjectByPrimaryKeyNoException(lId);
		if (object =3D=3D null) {
			throw new
ObjectRetrievalFailureException(getPojoBean(), lId);
		}
		return object;
	}

	/**
	 * Retrieve an Object by primary key. Doesn't throw an exception
if an empty
	 * list is found. Could be private or public....doesn't matter.
	 *=20
	 * @return Returns an empty object if it's not found.
	 */
	public Object getObjectByPrimaryKeyNoException(Object objectId)
{
		Object object =3D null;
		MappingSqlQuery theMappingSqlQuery =3D
getMappingSqlQuery();

		// set the required stuff to run a Select All operation.
		theMappingSqlQuery.setDataSource(getDataSource());
		theMappingSqlQuery.setSql(getSQLSelectByPrimaryKey());

		// add the params before the compile.
		if(objectId instanceof Long) {
			theMappingSqlQuery.declareParameter(new
SqlParameter(getPrimaryKey(), Types.INTEGER));
		}
		else {
			theMappingSqlQuery.declareParameter(new
SqlParameter(getPrimaryKey(), Types.VARCHAR));
		}

		theMappingSqlQuery.compile();

		// pass in the primary key id.
		List list =3D theMappingSqlQuery.execute(new Object[] {
objectId });
		if (!list.isEmpty()) {
			object =3D list.get(0);
		}

		return object;
	}

	/**
	 * Determines if the object is persisted or not.
	 *=20
	 * Used internally / externally to determine if an update or an
insert
	 * statement is called on an incoming save of an object.
	 *=20
	 * @param lId
	 * @return
	 */
	public boolean isObjectPersisted(Object objectId) {
		boolean bReturn =3D false;
		Object object =3D
getObjectByPrimaryKeyNoException(objectId);
		if (object !=3D null) {
			bReturn =3D true;
			logger.debug("Row / Object Found with Primary
Key of [" + objectId + "]");
		}
		else {
			logger.debug("Row / Object NOT Found with
Primary Key of [" + objectId + "]");
		}

		return bReturn;
	}

}

////////////////////////////////////////


Thanks,

Nick Neuberger