DBResearch v0.2

After rereading the The Pragmatic Programmer, I realised this project is something between a prototype and tracer bullets :-).

For some Generics experiments I used Effective Java, 2nd Edition.

Comments

Version 0.2 and the second attempt 0.21 are detours. The 0.1 version had only one table which allowed for a neat dao stack of the IDao interface containing all method definitions for database queries and the DerbyDao class implementing them. If the methods could be generic enough, support for another table could be added elegantly. But alas this didn't work. Two approaches were tried:

Insert raw SQL into the methods. This could work for methods that have no return value or a simple one, like primitive or String. For more complex return values, like table rows, the return type should be specified, or a method per class should be specified. This would make the DerbyDao class quite large. But the main problem is that the SQL composition is now done by upper layers, not the dao layer. Quite unacceptable.
Insert objects into the methods. This looks better, as the SQL is now solely a dao problem. This would allow methods like public void insert(Person person). But by adding tables the methods still start multiplying. An attempt to make the interface simpler could be possible by making the table classes like Person subclasses from Table and Generify the methods to accept extensions of Table, wouldn't work for me. And it would hide the table specifics, but not make them go away.

This leads to the conclusion that a dao per view is probably the clearest solution. The table dao's could share a parent for the generic code, like connection logic, but using this parent as generic programming interface is not a option. So far all examples of JDBC programming with dao's I found have only one table...

Addendum: Since then the different dao files migrated to very similar method sets, using the target class as main argument. It might be doable after all with more generics, but that is for a later release.

The conclusion is that insight on the needed queries and how generic they can be made depends on the View and Controller layer. As creating them is quite some work, I will not attempt it for this research project, but start with the real stuff.

Evaluation

Most examples put the connection data in the xml file. I prefer it in an external configuration file, making it easy to change the database location at run-time.
As most sheds, the DBToolShed becomes filled with appropriate and not-appropriate tools. This has to be split up per package. If a package has a common superclass, this is the appropriate place. Otherwise a tidy shed is ok.
Lots of testcases are added, some even in TDD mode. This works very well, but assumes you have an idea on your direction. Usually a requisite for succesful programming...
The insertGetQuery method in PersonDao and AddressDao is still basic JDBC, not Spring.

The code

All in one jar: DbResearch_v0.22.jar The demo code is in the test package, but this is a subset of what is exercised with the JUnit tests.

Because only few methods are universal enough for overriding, IDao is sparsly populated:

package dao;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

public interface IDao {
	public void setDataSource(DriverManagerDataSource ds);
	
	public int selectCountAll();
	
	public <T> List<T> selectAll();
	
}

DerbyDao contains the few methods used by all model dao classes:

package dao;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

public abstract class DerbyDao implements IDao {
	protected  DriverManagerDataSource _dataSource;
	private    String     _propertyFile;
	private    Properties _props;
	
	public  void setDataSource(DriverManagerDataSource ds) {
		_dataSource = ds;
	}
	public void setPropertyFile(String propertyFile) {
		_propertyFile = propertyFile;
	}

	public DriverManagerDataSource getDataSource() {
		_props = new Properties();
		try {
//	        _props.load(new FileInputStream(_propertyFile));
	        _props.load(new FileInputStream("properties.properties"));
		} catch (IOException ioex) {
			ioex.printStackTrace();
		}
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(_props.getProperty("driverClass"));
		dataSource.setUrl(_props.getProperty("jdbcConnection"));
		dataSource.setUsername(_props.getProperty("userName"));
		dataSource.setPassword(_props.getProperty("password"));
		return dataSource;
	}
	
	protected static String deAnd(String andString) {
		Pattern andPat    = Pattern.compile("AND AND ");
		Pattern endAndPat = Pattern.compile("AND $");
		Matcher match1  = andPat.matcher(andString);
		andString = match1.replaceAll("AND ");
		Matcher match2  = endAndPat.matcher(andString);
		return match2.replaceFirst("");
	}
	
	protected static String deComma(String commaString) {
		Pattern endCommaPat = Pattern.compile(", $");
		Matcher endCommaMatch = endCommaPat.matcher(commaString);
		return endCommaMatch.replaceAll("");
	}
}

The previously separated Mapper and Extractor are fused in the Mapper. Here PersonMapper as example:

package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import domainmodel.Person;

public class PersonRowMapper implements ParameterizedRowMapper<Person> {

	@Override
	public Person mapRow(ResultSet rs, int line) throws SQLException {
		Person person = new Person();
		person.setId(rs.getInt("ID"));
		person.setFirstName(rs.getString("FIRSTNAME")); 
		person.setLastName(rs.getString("LASTNAME"));
//		person.setAddressRef(null);
		return person;
	}
}

The Person class is mainly unchanged. An elaborate null tested was needed for the SQL composition in PersonDao. As all data were Person, this looked like the right place:

package domainmodel;

public class Person {
	private int _id;
	private String  _firstName;
	private String  _lastName;

	public Person() {
		
	}
	public Person(String firstName, String lastName) {
		_firstName  = firstName;
		_lastName   = lastName;
	}

	public int getId() {
		return _id;
	}

	public String getFirstName() {
		return _firstName;
	}

	public String getLastName() {
		return _lastName;
	}

	public void setId(int id) {
		_id = id;
	}
	
	public void setFirstName(String firstName) {
		_firstName = firstName;
	}

	public void setLastName(String lastName) {
		_lastName = lastName;
	}

	public String toString() {
		return _firstName + ";" + _lastName;
	}
	
	public static boolean contentNull(Person person) {
		return (person == null || (person.getFirstName() == null &&
								   person.getLastName()  == null ));
	}
}

The PersonDao contain all methods/queries for the person table. The AddressDao class contains a similar set:

package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import test.DBToolShed;

import dao.mapper.PersonRowMapper;
import domainmodel.Person;

public class PersonDao extends DerbyDao {
	protected  DriverManagerDataSource _dataSource;
	protected  PersonDao _dd;
	
	public  void setDataSource(DriverManagerDataSource ds) {
//		_dataSource = ds;
		_dataSource = DBToolShed.getRealDataSource();
	}
	
	@Override
	public int selectCountAll() {
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return select.queryForInt("SELECT COUNT(*) FROM person");
	}
	public int selectCount(Person person) {
		JdbcTemplate jTempl = new JdbcTemplate(_dataSource);
		if (person == null || (person.getFirstName() == null && person.getLastName() == null)) {
			return jTempl.queryForInt("SELECT COUNT(*) FROM person");
		}
		String selectClause = "SELECT COUNT(*) FROM person where ";
		return jTempl.queryForInt(selectClause + clauseBuilder(person));
	}

	public List selectAll() {
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return select.query("SELECT * FROM person ORDER BY id", new PersonRowMapper());
	}

	public int delete(Person person) {
		if (person == null || (person.getFirstName() == null && person.getLastName() == null)) {
			return 0;
		}
		String deleteClause = "DELETE FROM person WHERE ";
//		System.out.println(deleteClause + clauseBuilder(person));
		return new JdbcTemplate(_dataSource).update(deleteClause + clauseBuilder(person));
	}

	public void deleteAll(){
		new JdbcTemplate(_dataSource).update(
				"DELETE from PERSON ");
	}

	public int update(Person personOld, Person personNew) {
		if (personOld == null || personNew == null) { return 0; }
//		String updateClause = "UPDATE person SET firstname = '" + personNew.getFirstName() + 
//		"', lastName = '" + personNew.getLastName() + "' WHERE ";
//		String updateClause = "UPDATE person SET ";
//		System.out.println("UPDATE person SET " + setBuilder(personNew) + " WHERE " + clauseBuilder(personOld));
		return (new JdbcTemplate(_dataSource).update("UPDATE person SET " + setBuilder(personNew) + 
				" WHERE " + clauseBuilder(personOld)));
	}
	
	public int update(int id, Person personNew) {
		if (personNew == null) { return 0; }
		String updateClause = "UPDATE person SET ";
//		String updateClause = "UPDATE person SET firstname = '" + personNew.getFirstName() + 
//		"', lastName = '" + personNew.getLastName() + "' WHERE id = " + id;
//		System.out.println(updateClause + setBuilder(personNew) + " WHERE id = " + id);
		return (new JdbcTemplate(_dataSource).update(updateClause + setBuilder(personNew) + " WHERE id = " + id));
	}
	
	public int insert(Person person) {
		new JdbcTemplate(_dataSource).update(object2SQLInsert(person));
		return 0;
	}

	public int insertGetKey(Person person) {
		if (Person.contentNull(person)) { return 0; }
		String sql = object2SQLInsert(person);
		// This is the basic JDBC implementation. The fancy Spring variants 
		//  want complicated stuff instead of a sql String.
		// SimpleJdbcInsert has methods:
		// public SimpleJdbcInsert withTableName(String tableName)
		// public KeyHolder executeAndReturnKeyHolder(Map<String,Object> args)
		//  
		int key = 0;
		try {
			PreparedStatement pstmt = 
				_dataSource.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.executeUpdate();
			ResultSet keys = pstmt.getGeneratedKeys();
		
			keys.next();
			key = keys.getInt(1);
			keys.close();
		} catch (Exception e) { e.printStackTrace(); }
		return key;
	}

	public static String object2SQLInsert(Person person) {
		return  "INSERT INTO person (FIRSTNAME, LASTNAME) " +
				"VALUES('" + person.getFirstName() + 
				"', '" + person.getLastName() + 
				"')";
	}

	public List<Person> select(Person person) {
		JdbcTemplate jTempl = new JdbcTemplate(_dataSource);
		if (person == null || (person.getFirstName() == null && person.getLastName() == null)) {
			return jTempl.query("SELECT * FROM person", new PersonRowMapper());
		}
		String selectClause = "SELECT * FROM person where ";
//		System.out.println(selectClause + clauseBuilder(person));
		return jTempl.query(selectClause + clauseBuilder(person),
				new PersonRowMapper());
	}
	
	private static String clauseBuilder(Person person) {
		String firstNameClause = "";
		String lastNameClause  = "";
		String andClause       = "";
		if (person.getFirstName() != null) {
			firstNameClause = "firstName ='" + person.getFirstName() + "'";
		}
		if (person.getLastName() != null) {
			lastNameClause = "lastName = '" + person.getLastName() + "'";
		}
		if (!firstNameClause.equals("") && !lastNameClause.equals("")) {
			andClause = " AND ";
		}
		return firstNameClause + andClause + lastNameClause;
	}
	
	private static String setBuilder(Person person) {
		String firstNameClause = "";
		String lastNameClause  = "";
		String commaClause     = "";
		if (person.getFirstName() != null) {
			firstNameClause = "firstName ='" + person.getFirstName() + "'";
		}
		if (person.getLastName() != null) {
			lastNameClause = "lastName = '" + person.getLastName() + "'";
		}
		if (!firstNameClause.equals("") && !lastNameClause.equals("")) {
			commaClause = " , ";
		}
		return firstNameClause + commaClause + lastNameClause;
	}
}

Finally the Person JUnit file:

package test;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.dbunit.DatabaseUnitException;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import dao.AddressDao;
import dao.PersonDao;
import domainmodel.Person;

public class PersonDaoTest {
	PersonDao _pDao;
	AddressDao	_aDao;
	
	@Before
	public void setUp() throws Exception {
		_pDao = new PersonDao();
		_aDao = new AddressDao();
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		Properties dbProps = DBToolShed.getProperties();
		Assert.assertNotNull("Property not set ", dbProps.getProperty("driverClass"));
		dataSource.setDriverClassName(dbProps.getProperty("driverClass"));
		dataSource.setUrl(dbProps.getProperty("jdbcConnection"));
		dataSource.setUsername(dbProps.getProperty("userName"));
		dataSource.setPassword(dbProps.getProperty("password"));
		_pDao.setDataSource(dataSource);
		_aDao.setDataSource(dataSource);
		_aDao.deleteAll();
		_pDao.deleteAll();
	}
	@After
	public void tearDown() throws Exception {
	}
	
	@Test
	public void test1_RecordCount() {
		_pDao.insert(new Person("Aap1", "Een"));
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCountAll());
		_pDao.insert(new Person("Aap2", "Twee"));
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCountAll());
		_pDao.delete(new Person("Aap2", "Twee"));
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCountAll());
		
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCount(new Person("Aap1", null)));
		Assert.assertEquals("Person record count mismatch", 0, _pDao.selectCount(new Person("NoAap", null)));
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCount(new Person(null, "Een")));
		Assert.assertEquals("Person record count mismatch", 0, _pDao.selectCount(new Person(null, "NoEen")));
	}
	@Test
	public void test2_Insert() {
		_pDao.insert(new Person("Aap1", "Een"));
		Assert.assertEquals("FirstName incorrect", "Aap1", 
				_pDao.select(new Person(null,  "Een")).get(0).getFirstName());
		Assert.assertEquals("LastName incorrect",  "Een",  
				_pDao.select(new Person("Aap1", null)).get(0).getLastName());
		Person p3 = new Person();
		p3.setFirstName("Aap3");
		p3.setLastName("Drie");
		_pDao.insert(p3);
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCountAll());
		Person p4 = new Person("Aap4", "Vier");
		_pDao.insert(p4);
		Assert.assertEquals("Person record count mismatch", 3, _pDao.selectCountAll());
	}
	@Test
	public void test3_Select() {
		_pDao.insert(new Person("Aap1", "Een"));
		_pDao.insert(new Person("Aap2", "Twee"));
		List persons = _pDao.selectAll();
		Assert.assertEquals("Record count mismatch", 2, persons.size());
		Assert.assertEquals("Field data mismatch", "Aap1", persons.get(0).getFirstName());
		Assert.assertEquals("Field data mismatch", "Een",  persons.get(0).getLastName());
		Assert.assertEquals("Field data mismatch", "Aap2", persons.get(1).getFirstName());
		Assert.assertEquals("Field data mismatch", "Twee", persons.get(1).getLastName());

		Assert.assertEquals("Person record count mismatch", 1, _pDao.select(new Person("Aap1", "Een")).size());
		Assert.assertEquals("Person record count mismatch", 1, _pDao.select(new Person("Aap1", null)).size());
		Assert.assertEquals("Person record count mismatch", 1, _pDao.select(new Person(null, "Een")).size());
		Assert.assertEquals("Person record count mismatch", 2, _pDao.select(new Person(null, null)).size());
		Assert.assertEquals("Person record count mismatch", 0, _pDao.select(new Person("Aap*", null)).size());
		Assert.assertEquals("Person record count mismatch", 0, _pDao.select(new Person("Aap?", null)).size());
	}
	@Test
	public void test4_Delete() throws   ClassNotFoundException, DatabaseUnitException, 
										IOException, SQLException {
		DBToolShed.fullDatabaseImport(new File("testData", "DDTest4DB.xml"));
		_pDao.delete(new Person("Aap1", "Een"));
//		_pDao.update("DELETE FROM person WHERE FirstName = 'Aap1'");
		Assert.assertEquals("Person record count mismatch", 3, _pDao.selectCountAll());
		_pDao.delete(new Person("Aap2", null));
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCountAll());
		_pDao.delete(new Person(null, null));
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCountAll());
		_pDao.delete(new Person(null, "Drie"));
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCountAll());
	}
	@Test
	public void test5_Update() throws   ClassNotFoundException, DatabaseUnitException, 
										IOException, SQLException {
		DBToolShed.fullDatabaseImport(new File("testData", "DDTest4DB.xml"));
		Person testPerson = new Person("Aap1Updated", "Twee");
		Assert.assertEquals("Update count mismatch", 1, _pDao.update(new Person("Aap1", "Een"), testPerson));
		Assert.assertEquals("Person record count mismatch", 1, _pDao.selectCount(testPerson));
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCount(new Person(null, "Twee")));
		Assert.assertEquals("Update count mismatch", 2, 
				_pDao.update(new Person(null, "Twee"), new Person(null, "TweeToo")));
		Assert.assertEquals("Person record count mismatch", 2, _pDao.selectCount(new Person(null, "TweeToo")));
		int id = _pDao.select(new Person(null, "TweeToo")).get(0).getId();
		Assert.assertEquals("Update count mismatch", 1, _pDao.update(id, new Person("Teun", "Twiet")));
	}
}

Dependencies

The following external libaries are used:

commons-dbcp-1.2.2.jar,
commons-logging-1.1.1.jar,
db-derby-10.4.2.0-bin.zip (plugin),
dbunit-2.3.0.jar
derby_core_plugin_10.4.2.zip (plugin),
Eclipse 3.4.1, Java 1.6
junit-4.4.jar (plugin),
org.apache.log4j_1.2.13.v200706111418.jar,
slf4j-api-1.5.3.jar (for dbunit)
slf4j-log4j12-1.5.3.jar (for dbunit)
spring-framework-2.5.5-with-dependencies.zip (versions before 2.5.4 are known to work incorrect with this code),

Last updated: 2008-10-31

email