Umetxoak Ikusirik Lorea Ezin Bizirik Arantzak Kendu Nahi Dizkio Bizi Berri Bat Eman (Loretxoa - Benito Lertxundi)

EJP (or Easy Java Persistence) is a powerful and easy to use relational database persistence API for Java. It has no need for mapping Annotations or XML configuration, and there is no need to extend any classes or implement any interfaces. In it is site we can find the following statement: "EJP is, by far, the easiest persistence API available for Java."

From my point of view EJP is a mixture of some features between myBatis and Spring Jdbc Template Row Mapper.

An example of EJP from its website:

	public static void main(String[] args) {
	DatabaseManager dbm = DatabaseManager.getDatabaseManager(...);
	dbm.saveObject(new Customer("Smith", "John"));
	Customer customer;
	if ((customer = dbm.loadObject(new Customer("Smith"))) != null) {
	customer.getSupport().add(new Support(...));
	dbm.saveObject(customer);
	}
	Collection<Customer> list = new ArrayList<Customer>();
	list = dbm.loadObjects(list, Customer.class);
	...
	}

view raw EJP.java hosted with ❤ by GitHub

and Customer and Support classes are simple POJOs without annotations or any special tag, and no configuration file.

What I am going to explain in this post is how integrate EJP with Spring.

First of all, we are going to create an interface which defines all permitted operations:

	public interface EjpJdbcOperations {
	Database newDatabase();
	JdbcOperations getJdbcOperations();
	int deleteObject(Object object);
	int deleteObject(Object object, String externalClauses, Object... externalClausesParameters);
	<T> List<T> executeQuery(final String sql, final RowMapper<T> rowMapper);
	int executeUpdate(String sql);
	int executeUpdate(String sql, List keys);
	void loadAssociations(Object object);
	<T>List<T> parameterizedQuery(String sql, RowMapper<T> rowMapper, Object ... parameters);
	int parameterizedUpdate(String sql, List keys, Object ... parameters);
	int parameterizedUpdate(String sql, Object ... parameters);
	<T> List<T> queryForObject(Class<T> cs, RowMapper<T> rowMapper);
	<T> List<T> queryForObject(Class<T> cs, String externalClauses, RowMapper<T> rowMapper, Object... externalClausesParameters);
	<T> List<T> queryForObject(T object, RowMapper<T> rowMapper);
	<T> List<T> queryForObject(T object, String externalClauses, RowMapper<T> rowMapper, Object... externalClausesParameters);
	int queryForInt(String sql);
	int saveObject(Object object);
	int saveObject(Object object, String externalClauses, Object... externalClausesParameters);
	<T> int[] batchUpdate(List<T> objectList);
	}

view raw EjpJdbcOperations.java hosted with ❤ by GitHub

This is an example of common operations provided by EJP. Methods that should be clarified are:

• newDatabase method is used for returning a Database object. This object is a facade that implements all operations supported by EJP. It is the main class of EJP project with permission of DatabaseManager class.
• loadAssociations are responsible (as its name suggests) to load an object associations.
• queryForObject methods execute a "query by example" queries.
• queryForInt is a method used for queries that return numerical results like count(*), avg(...), ...

EjpJdbcTemplate is the implementation of previous interface, and makes use of JdbcTemplate class as core.

Getting a new Database:

	private Database newDatabase(Connection connection) {
	return new Database(databaseName, connection);
	}

view raw EjpJdbcTemplate.java hosted with ❤ by GitHub

Remember that Database object is the core of EJP, and is required to execute all EJP operations. This method creates one Database with given Connection. databaseName is not mandatory, but for no reason is a constructor attribute. A null can be passed without any problem. In this implementation database name is a class attribute.

Deleting:

	public int deleteObject(final Object object) {
	int rowsAffected = jdbcTemplate.execute(new ConnectionCallback<Integer>() {
	public Integer doInConnection(Connection connection) throws SQLException,
	DataAccessException {
	Database database = newDatabase(connection);
	try {
	return database.deleteObject(object);
	} catch (DatabaseException e) {
	throw new SQLException(e.getMessage(), e.getCause());
	} finally {
	closeDatabase(database);
	}
	}
	});
	return rowsAffected;
	}

view raw EjpJdbcTemplate.java hosted with ❤ by GitHub

No secret in deleting an object. Using ConnectionCallback, connection is provided, and Database object is created.

Loading Associations:

	public void loadAssociations(final Object object) {
	jdbcTemplate.execute(new ConnectionCallback<Class<Void>>() {
	public Class<Void> doInConnection(Connection connection) throws SQLException,
	DataAccessException {
	Database database = newDatabase(connection);
	try {
	database.loadAssociations(object);
	return Void.TYPE;
	} catch (DatabaseException e) {
	throw new SQLException(e.getMessage(), e.getCause());
	} finally {
	closeDatabase(database);
	}
	}
	});
	}

view raw EjpJdbcTemplate.java hosted with ❤ by GitHub

If you want to load explicitly all associations of an object, load associations method must be called. The only integration difficult is that database.loadAssociations returns void, and ConnectionCallback should always return a result. In this case void.TYPE is used.

Executing Queries:

	public <T> List<T> executeQuery(final String sql, final RowMapper<T> rowMapper) {
	List<T> results = jdbcTemplate.execute(new ConnectionCallback<List<T>>() {
	public List<T> doInConnection(Connection con) throws SQLException,
	DataAccessException {
	RowMapperResultSetExtractor<T> extractor =
	new RowMapperResultSetExtractor<T>(rowMapper);
	Database database = newDatabase(con);
	try {
	ResultSet resultSet = database.executeQuery(sql).getResultSet();
	List<T> list = extractor.extractData(resultSet);
	JdbcUtils.closeResultSet(resultSet);
	return list;
	} catch (DatabaseException e) {
	throw new SQLException(e.getMessage(), e.getCause());
	} finally {
	closeDatabase(database);
	}
	}
	});
	return results;
	}

view raw EjpJdbcTemplate.java hosted with ❤ by GitHub

In this case most important line is where Database executeQuery method is called. This method returns a class of type Result. This class is a wrapper of ResultSet class but also can deal directly with beans (implicit wrapping), instead of getting one-by-one each parameter. But for following JdbcTemplate strategy and use a row mapper, ResultSet is used for mapping results.

Querying for Int:

	public int queryForInt(final String sql) {
	int results = jdbcTemplate.execute(new ConnectionCallback<Integer>() {
	public Integer doInConnection(Connection con) throws SQLException,
	DataAccessException {
	Database database = newDatabase(con);
	try {
	ResultSet resultSet = database.executeQuery(sql).getResultSet();
	if(resultSet.next()) {
	RowMapper<Integer> singleColumnRowMapper = getSingleColumnRowMapper(Integer.class);
	return singleColumnRowMapper.mapRow(resultSet, 0);
	} else {
	throw new IncorrectResultSizeDataAccessException(1);
	}
	} catch (DatabaseException e) {
	throw new SQLException(e.getMessage(), e.getCause());
	} finally {
	closeDatabase(database);
	}
	}
	});
	return results;
	}

view raw EjpJdbcTemplate.java hosted with ❤ by GitHub

See that follows the same schema as executeQuery, but instead of using RowMapperResultSetExtractor, a SingleColumnRowMapper is used. This mapper is used when the query result returns a single result.

Now I am going to show how to use this template into a DAO implementation.

	@Transactional
	@Repository
	public class EjpCustomerDao implements CustomerDao {
	@Autowired
	private EjpJdbcTemplate template;
	public Customer findCustomerById(Long id) {
	List<Customer> customers = this.template.queryForObject(Customer.class, "where :id = ?", BeanPropertyRowMapper.newInstance(Customer.class), id);
	if(customers.size() == 1) {
	return customers.get(0);
	} else {
	throw new IncorrectResultSizeDataAccessException(1, customers.size());
	}
	}
	public void saveCustomer(Customer customer) {
	this.template.saveObject(customer);
	}
	public void deleteCustomer(Customer customer) {
	this.template.deleteObject(customer);
	}
	public int countCustomers() {
	String query = "SELECT COUNT(*) FROM Customer";
	return this.template.queryForInt(query);
	}
	public List<Customer> findAllCustomersOrderedByName() {
	return this.template.queryForObject(Customer.class, "ORDER BY first_name",BeanPropertyRowMapper.newInstance(Customer.class));
	}
	public List<Customer> findCustomerByExample(Customer customer) {
	return this.template.queryForObject(customer, BeanPropertyRowMapper.newInstance(Customer.class));
	}
	public void addBatch(List<Customer> customers) {
	this.template.batchUpdate(customers);
	}
	public List<Customer> findAllCustomers() {
	return this.template.queryForObject(Customer.class, BeanPropertyRowMapper.newInstance(Customer.class));
	}
	}

view raw EjpCustomerDao hosted with ❤ by GitHub

Not much secret in this DAO. Only two methods requires special review findAllCustomersOrderedByName() and findCustomerByExample()

The first one:

this.template.queryForObject(Customer.class, "ORDER BY first_name",BeanPropertyRowMapper.newInstance(Customer.class)) 

 is converted by EJP to SELECT * FROM Customer ORDER BY first_name.

The second one:

this.template.queryForObject(customer, BeanPropertyRowMapper.newInstance(Customer.class)) 

instead of receiving a Class it receives an instance itself to execute the query. In case that first name field was set to alex, the equivalent SQL query would be SELECT * FROM Customer WHERE first_name='alex'

Now I have explained a strategy to integrate EJP with Spring. Because I have followed same structure as Spring-Data Jdbc-Extension project, rather than create a "HelloWorld" project, I decided to fork Spring-Data project into my Github account, and uploaded the code as a new extension for the project. Moreover a Jira issued has been created with id DATAJDBC-11. If you want to watch full code and unit tests go to git@github.com:maggandalf/spring-data-jdbc-ext.git and review spring-data-jdbc-ext/spring-data-jdbc-core/src/main/java/org/springframework/data/jdbc/ejp directory.

I wish you have found this post useful.


Music: http://www.youtube.com/watch?v=PlEknDUSRc0&feature=fvwrel