Back

DBResearch v0.3

Comments

This version is seen as a round off of this research project, as the initial goal as reached; Accessing database data via a interface of standardised queries. The higher layers use only the IDao Interface to query or update the database. The Dao classes also moved to the SimpleJdbcTemplate, but this did not result in complete type safety.

Evaluation

The code

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

The IDao has grown to include all supported public methods :

package dao;

import java.util.List;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import domainmodel.Model;

public interface IDao {
	public void setDataSource(DriverManagerDataSource ds);
	
	public <T extends Model> int selectCount(T model);
	
	public int selectCountAll();
	
	public <T extends Model> List<T> selectAll();
	
	public <T extends Model> List<T> select(T model);
	
	public <T extends Model> int delete(T model);
	
	public int delete(int id);
	
	public void deleteAll();
	
	public <T extends Model> int update(T modelOld, T modelNew);
	
	public <T extends Model> int update(int id, T model);
	
	public <T extends Model> int insert(T model);
	
	public <T extends Model> int insertGetKey(T model);	
}

DerbyDao :

package dao;

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;
	// optimization
	private static Pattern andPat      = Pattern.compile("AND AND ");
	private static Pattern endAndPat   = Pattern.compile("AND $");
	private static Pattern endCommaPat = Pattern.compile(", $");
	
	public  void setDataSource(DriverManagerDataSource ds) {
		_dataSource = ds;
	}

	// Two fixes for an imperfect query builder. To be buried asap.
	protected static String deAnd(String andString) {
		Matcher match1  = andPat.matcher(andString);
		andString = match1.replaceAll("AND ");
		Matcher match2  = endAndPat.matcher(andString);
		return match2.replaceFirst("");
	}
	
	protected static String deComma(String commaString) {
		Matcher endCommaMatch = endCommaPat.matcher(commaString);
		return endCommaMatch.replaceAll("");
	}

}

The PersonDao class, which implements the queries:

package dao;

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

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import test.DBToolShed;

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

public class PersonDao extends DerbyDao {
	protected  DriverManagerDataSource _dataSource;
	
	public  void setDataSource(DriverManagerDataSource ds) {
		_dataSource = DBToolShed.getRealDataSource();
	}
	
	@Override
	public int selectCountAll() {
		SimpleJdbcTemplate select = new SimpleJdbcTemplate(_dataSource);
		return select.queryForInt("SELECT COUNT(*) FROM person");
	}

	@Override
	public <T extends Model> int selectCount(T tPerson) {
		Person person = (Person) tPerson;
		SimpleJdbcTemplate jTempl = new SimpleJdbcTemplate(_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));
	}

	@Override
	public <T extends Model> List<T> select(T person) {
		SimpleJdbcTemplate jTempl = new SimpleJdbcTemplate(_dataSource);
		if (person == null || (((Person) person).getFirstName() == null && 
				((Person) person).getLastName() == null)) {
			return (List<T>) jTempl.query("SELECT * FROM person", new PersonRowMapper());
		}
		String selectClause = "SELECT * FROM person where ";
//		System.out.println(selectClause + clauseBuilder(person));
		return (List<T>) jTempl.query(selectClause + clauseBuilder((Person) person),
				new PersonRowMapper());
	}
	
	@Override
	public <T extends Model> List<T> selectAll() {
		SimpleJdbcTemplate select = new SimpleJdbcTemplate(_dataSource);
		return (List<T>) select.query("SELECT * FROM person ORDER BY id", new PersonRowMapper());
	}

	@Override
	public <T extends Model> int delete(T person) {
		if (person == null || (((Person) person).getFirstName() == null && 
				((Person) person).getLastName() == null)) {
			return 0;
		}
		String deleteClause = "DELETE FROM person WHERE ";
//		System.out.println(deleteClause + clauseBuilder(person));
		return new SimpleJdbcTemplate(_dataSource).update(deleteClause + clauseBuilder((Person) person));
	}

	@Override
	public int delete(int id) {
		String deleteClause = "DELETE FROM person WHERE id = ";
		return new SimpleJdbcTemplate(_dataSource).update(deleteClause + id);
	}

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

	@Override
	public <T extends Model> int update(T personOld, T personNew) {
		if (personOld == null || personNew == null) { return 0; }
		return (new SimpleJdbcTemplate(_dataSource).update("UPDATE person SET " + 
				setBuilder((Person) personNew) + 
				" WHERE " + clauseBuilder((Person) personOld)));
	}
	
	@Override
	public <T extends Model> int update(int id, T personNew) {
		if (personNew == null) { return 0; }
		String updateClause = "UPDATE person SET ";
//		System.out.println(updateClause + setBuilder(personNew) + " WHERE id = " + id);
		return (new SimpleJdbcTemplate(_dataSource).update(updateClause + 
				setBuilder((Person) personNew) + " WHERE id = " + id));
	}
	
	@Override
	public <T extends Model> int insert(T person) {
		new SimpleJdbcTemplate(_dataSource).update(object2SQLInsert((Person) person));
		return 0;
	}

	@Override
	public <T extends Model> int insertGetKey(T person) {
		if (Person.contentNull((Person) person)) { return 0; }
		String sql = object2SQLInsert((Person) person);
		// This is the basic JDBC implementation. The fancy Spring variant below is
		// not supported by Derby :-(
		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;
		
//		return new SimpleJdbcInsert(_dataSource).withTableName("Person").
//		usingColumns("FirstName", "LastName").
//		usingGeneratedKeyColumns("Id").
//		executeAndReturnKey(new BeanPropertySqlParameterSource((Person)person)).intValue();

	}

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

	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;
	}
}

The Person class. only the extends Model has been added:

package domainmodel;

public class Person extends Model {
	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 ));
	}
}

Dependencies

The following external libaries are used:


Last updated: 2008-11-11

email