REVERT TO CONSOLE

At my latest client engagement I’ve become the king of batch applications. Small, stand-alone applications run via a scheduler or a set of shell scripts that perform a heavy amount of data-munging.

The problem with being assigned batch applications is that they’re never allocated much time in the project plan; in fact sometimes they’re not found in requirements at all and just seem to crop up mid way through the project.

For these types of applications, Hibernate can be rather bulky and time consuming. But it’s still nice to work in a more object-oriented environment than simple JDBC stuff. Enter Spring’s JDBCTemplate.

Here’s an example of how I’ve used it:

Here is the simple spring config that I used:

	
	

			oracle.jdbc.driver.OracleDriver
		

			jdbc:oracle:thin:@server:1521:mydb
		

			user
		

			password
		
	

	
		
			 
		
	

	

			
		
	

Start with a bean that describes the data you wish to populate:

import java.sql.Date;

public class MyBean {

	String sLibbyFileName, prisonAddress;
	Date sentenceDate;
	int kRoveAccountNumber;

	public int getKRoveAccountNumber() {
		return kRoveAccountNumber;
	}
	public void setKRoveAccountNumber(int roveAccountNumber) {
		kRoveAccountNumber = roveAccountNumber;
	}
	public String getPrisonAddress() {
		return prisonAddress;
	}
	public void setPrisonAddress(String prisonAddress) {
		this.prisonAddress = prisonAddress;
	}
	public Date getSentenceDate() {
		return sentenceDate;
	}
	public void setSentenceDate(Date sentenceDate) {
		this.sentenceDate = sentenceDate;
	}
	public String getSLibbyFileName() {
		return sLibbyFileName;
	}
	public void setSLibbyFileName(String libbyFileName) {
		sLibbyFileName = libbyFileName;
	}

}

Put together a select statement to retrieve the data:

SELECT ACCOUNT_NUMBER, ADDRESS, FILE_NAME, SENTENCE_DATE FROM FBI_SENTENCE_DATA;

And then create your class (I’m skipping the part where you retrieve the myListMaker bean from Spring:

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

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class MyBeanTemplate {

	private JdbcTemplate jdbcTemplate;

	public static final String MY_QUERY=
		"SELECT ACCOUNT_NUMBER, ADDRESS, FILE_NAME, SENTENCE_DATE FROM FBI_SENTENCE_DATA WHERE FILE_NAME = ? AND ADDRESS = ?";

	public List getListofBeans( String name, String address) {
		List myList = getJdbcTemplate().query(MY_QUERY,
				new Object[] { name, address },
				new int[]{ Types.CHAR, Types.CHAR}, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				MyBean bean = new MyBean();
				bean.setKRoveAccountNumber(rs.getInt("ACCOUNT_NUMBER"));
				bean.setPrisonAddress(rs.getString("ADDRESS"));
				bean.setSentenceDate(rs.getDate("SENTENCE_DATE"));
				bean.setSLibbyFileName(rs.getString("FILE_NAME"));
				return bean;
			}
		});
		return myList;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}

The coolest part of this is of course that you can call a database and retrieve a list of POJOs in one method call.

More links:

• This has been improved in Spring 2.
• More extensive documentation.