Back

DBResearch v0.1

This version is still in transit from the single table example from Vogella to a multi table system.

Structure

The project is currently divided into four packages and some xml:

dao
The part that actually sends queries to the database
dao.mapper
The part that get the replies from the database and converts in into handsome classes like person. This is part of the Model.
domainmodel
The part that defines the model classes. Another part of the Model.
test
The remaining package acts as View and Controller in one. Actually it is just for demo and testing.
META-INF directory
The personBeans.xml wires the proper sub-class of dao.IDao into test.TestDao2, and defines the database info into this sub-class. All other interclass dependencies are hard-coded. In principle all class dependencies could be wired by Spring, but it would result in lots of xml glue, and this is not neccessary a good thing. Haven't found any real info on this yet.

Comments

Evaluating this version:

The code

All in one jar: DbResearch_v0.1.jar

The database interface IDao Interface:

package dao;

import java.util.List;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import domainmodel.Person;

public interface IDao {
//	public void setDataSource(DriverManagerDataSource ds);
	
	// methods without table knowledge
	public int selectCountAll(String tableName);
	public int getInt(String sql);
	public String getString(String sql);
	public int update(String sql);

	// methods with knowledge of the returned reference structure
	public <T> List<T> select(String sql);
	public <T> List<T> selectAll(String table);

	// obsolete methods for first demo TestDao
	@Deprecated
	void create(String firstName, String lastName);
	void deleteAll(String table);
}

The DerbyDao class, implementing the methods which are usable for all tables:

package dao;

import java.util.List;

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

public abstract class DerbyDao implements IDao {
	protected  DriverManagerDataSource _dataSource;
//	private static DerbyDao _dd;

	public  void setDataSource(DriverManagerDataSource ds) {
		_dataSource = ds;
	}
	
	public int selectCountAll(String tableName) {
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return select.queryForInt("select count(*) from " + tableName);
	}

	public int getInt(String sql) {
		return new JdbcTemplate(_dataSource).queryForInt(sql);
	}
	
	public String getString(String sql) {
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return (String)select.queryForObject(sql, String.class);
	}

	public abstract <T> List<T> select(String sql);

	public abstract <T> List<T> selectAll(String table);

	public int update(String sql) {
		return new JdbcTemplate(_dataSource).update(sql);
	}
	
//	public abstract void delete(String firstName, String lastName); 
	
	public void deleteAll(String table) {
		new JdbcTemplate(_dataSource).execute("DELETE from " + table);
	}
}

The PersonDao class, implementing the table specific methods:

package dao;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import dao.mapper.PersonRowMapper;

public class PersonDao extends DerbyDao {

	@Override
	public <T> List<T> select(String sql) {
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return select.query(sql, new PersonRowMapper());
	}
	@Override
	public <T> List<T> selectAll(String table){
		JdbcTemplate select = new JdbcTemplate(_dataSource);
		return select.query("select * from " + table, new PersonRowMapper());
	}

	public void create (String firstName, String lastName){
		JdbcTemplate insert = new JdbcTemplate(_dataSource);
		insert.update("INSERT INTO PERSON (FIRSTNAME, LASTNAME) VALUES(?,?)", 
				new Object[]{firstName, lastName});
	}

	public void delete(String firstName, String lastName){
		new JdbcTemplate(_dataSource).update(
				"DELETE from PERSON where FIRSTNAME= ? AND LASTNAME = ?", 
				new Object[]{firstName, lastName});
	}

}

The classes in the dao.mapper and domainmodel packages are not really changed from the Vogella original. I added the id member and used column names instead of indices to retrieve database fields.

The test package has the most changes. TestDao2 is a kind of manual test file created before the real unit tests. The Main method (should still work) is commented out as launching is now done via the Main class.

package test;

import java.util.List;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import dao.DerbyDao;
import dao.IDao;
import dao.PersonDao;
import domainmodel.Person;

public class TestDao2 {
	private IDao _dao;
	
//	public static void main(String[] args) {
////		DerbyDao dao = new DerbyDao();
//		DerbyDao dao = getDataSource();
//		TestDao2 td2 = new TestDao2();
//		td2.setDao(dao);
//		td2.run();
//	}	
	
	public void setDao(IDao dao) {
		_dao = dao;
	}
	
	public void run() {
		printPersons(_dao.<Person>selectAll("person"));
		System.out.println("Count: " + _dao.selectCountAll("person"));
		System.out.println("Delete all from PERSON");
		_dao.deleteAll("person");
		System.out.println("Count: " + _dao.selectCountAll("person"));

		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap1',  'Een'  )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap2',  'Twee' )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap3',  'Drie' )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap4',  'Vier' )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap5',  'Vijf' )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap6',  'Zes'  )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap7',  'Zeven')");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap8',  'Acht' )");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap9',  'Negen')");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap10', 'Tien' )");
		
//		System.out.println("Aap6, Zes " + (_dao.getId("Aap6", "Zes")));
		System.out.println("Aap5 " + _dao.getInt("select Id from person where firstname = 'Aap5'"));
		System.out.println("Lastname Aap9: " + 
				_dao.getString("SELECT lastname FROM person WHERE firstname = 'Aap9'"));
		
		printPersons(_dao.<Person>selectAll("person"));
		System.out.println("Count: " + _dao.selectCountAll("person"));
		
		_dao.update("DELETE FROM person WHERE firstName = 'Aap7' and lastName = 'Zeven'");
		System.out.println("Deleted: " + _dao.update("DELETE FROM person WHERE firstName = 'Aap4'") + " records");
		System.out.println("Updated: " + _dao.update("UPDATE person set firstName = 'Noot3' WHERE firstName = 'Aap3'") + " records");
//		_dao.deleteAll();
		printPersons(_dao.<Person>selectAll("person"));
		System.out.println("Count2: " + _dao.selectCountAll("person"));
		System.out.println("Count3: " + _dao.getInt("SELECT COUNT(*) FROM person"));
		
//		dao.selectCountAll("aap");
	}
	
	private static DerbyDao getDataSource() {
//		DerbyDao dd = DerbyDao.getDD();
		DerbyDao dd = new PersonDao();
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
		dataSource.setUrl("jdbc:derby:testdb;create=true");
		dataSource.setUsername("");
		dataSource.setPassword("");
		dd.setDataSource(dataSource);
		return dd;
	}
	
	public static void printPersons(List<Person> pList) {
		System.out.println("List size: " + pList.size());
		for (Person myPerson : pList) {
			printPerson(myPerson);
		}
	}
	public static void printPerson(Person myPerson) {
		if (myPerson != null) {
		    System.out.println(myPerson.getId() + " " 
		    				 + myPerson.getFirstName() + " " 
		    				 + myPerson.getLastName());
		}
	}
}

There is still the original TestDao class, but it uses methods which are too Person specific. So far it still works.

The class Main The current application launch point.

package test;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
	
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("META-INF/personBeans.xml");
		BeanFactory factory = (BeanFactory) context;
		// instantiate the test instance with the factory info 
		TestDao2 test = (TestDao2) factory.getBean("test");
		test.run();
	}
}

The wiring in personBeans.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<!-- In the properties tags, value= is used for Strings, 
     ref= is used to indicate the argument is a reference. value= is used for Strings -->

<!-- On invocation of the item 'dao' ('id=...') use the class defined with 'class=...'. -->
<bean id="dao" class="dao.PersonDao">
	<property name="dataSource" ref="dataSource" />
</bean>

<bean id="test" class="test.TestDao2">
	<property name="dao" ref="dao" />
</bean>

<!-- On invocation of the item 'dataSource' ('id=...'), use the class defined with 'class='.
     for all properties, call methods 'set<Name> with the argument defined with 'value='. -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver" />
	<property name="url"             value="jdbc:derby:testdb;create=true" />
	<property name="username"        value="" />
	<property name="password"        value="" />
</bean>

</beans>

The last class are the unit tests i DerbyDaoTest

package test;

import java.util.List;

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

import dao.DerbyDao;
import dao.PersonDao;
import domainmodel.Person;

public class DerbDaoTest {
	DerbyDao _dao;
	
	@Before
	public void setUp() throws Exception {
		_dao = new PersonDao();
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
		dataSource.setUrl("jdbc:derby:testdb;create=true");
		dataSource.setUsername("");
		dataSource.setPassword("");
		_dao.setDataSource(dataSource);
		
		_dao.deleteAll("person");
	}
	@After
	public void tearDown() throws Exception {
		_dao.deleteAll("person");
	}
	
	@Test
	public void testRecordCount() {
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap1', 'Een')");
		Assert.assertEquals("Person record count mismatch", 1, _dao.selectCountAll("person"));
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap2', 'Twee')");
		Assert.assertEquals("Person record count mismatch", 2, _dao.selectCountAll("person"));
		_dao.update("DELETE FROM person  WHERE firstName = 'Aap2'");
		Assert.assertEquals("Person record count mismatch", 1, _dao.selectCountAll("person"));
	}
	@Test
	public void testInsert() {
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap1', 'Een')");
		Assert.assertEquals("FirstName incorrect", "Aap1", _dao.getString("SELECT DISTINCT firstName from Person"));
		Assert.assertEquals("LastName incorrect", "Een", _dao.getString("SELECT DISTINCT lastName from Person"));
	}
	@Test
	public void testSelect() {
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap1', 'Een')");
		_dao.update("INSERT INTO person (FIRSTNAME, LASTNAME) VALUES('Aap2', 'Twee')");
		List<Person> persons = _dao.select("SELECT * FROM person ORDER BY id");
		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());
	}

}

To get it all to work, you need a database and a table Person. The database is created automatically, but the table isn't (yet). Start an SQL prompt (ij) and pase the following:

CREATE TABLE Person(
		ID int generated by default as identity (start with 1) not null,
		FIRSTNAME VARCHAR(20) NOT NULL,
		LASTNAME VARCHAR(20) NOT NULL,
		PRIMARY KEY (ID)
	);

Dependencies

The following external libaries are used:


Last updated: 2008-10-11

email