Adding New Database Objects

Introduction

A database object is a POJO (Plain Old Java Object) that is persisted in a single database table as a single row where each field of the POJO is mapped to a database column. It can be tedious to write JDBC CRUD (Create, Read, Update, Delete) operations without some kind of help. Therefore, we are using Spring's SimpleJdbcTemplate (see: Data access with JDBC ) for all CRUD operation on these database POJOs. Even with SimpleJdbcTemplate, a lot of boiler plate SQL is required for CRUD operations. To reduce the amount of work needed to add new database objects we created a generic DAO (Data Access Object) to do most of the tedious CRUD operations called DBOBasicDao (W/implementation: DBOBasicDaoImpl).

DBOBasicDao

The DBOBasicDao has methods for working with any database object that implements DatabaseObject<T>. Here are the current methods of this generic DAO:

• Create:

  public <T extends DatabaseObject<T>> T createNew(T toCreate) throws DatastoreException
  

• Batch Create:

  public <T extends DatabaseObject<T>> List<T> createBatch(List<T> batch) throws DatastoreException
  

• Read :

  public <T extends DatabaseObject<T>> T getObjectById(Class<? extends T> clazz, SqlParameterSource namedParameters) throws DatastoreException, NotFoundException;
  

• Update:

  public <T extends DatabaseObject<T>> boolean update(T toUpdate) throws DatastoreException;
  

• Delete:

  public <T extends DatabaseObject<T>> boolean deleteObjectById(Class<? extends T> clazz, SqlParameterSource namedParameters) throws DatastoreException;
  

Add a new DatabaseObject<T>

The interface DatabaseObject<T>, provides all of the mapping needed for the DAO to perform CRUD operations. The first step to adding a new database object is to implement this interface:

public interface DatabaseObject<T> {

	/**
	 * Get the database Mapping for this object.
	 * @return
	 */
	public TableMapping<T> getTableMapping();

}

An example is org.sagebionetworks.repo.model.dbo.persistence.DBONode in lib-jdomodels.

The following is the definition of TableMapping. Note: RowMapper<T> is defined by Spring (see:org.springframework.jdbc.core.RowMapper )

public interface TableMapping<T> extends RowMapper<T> {

	/**
	 * The name of the database table.
	 * @return
	 */
	public String getTableName();
	/**
	 * The name of the DDL file that defines this table.
	 * @return
	 */
	public String getDDLFileName();

	/**
	 * Maps field names to column names.
	 * @return
	 */
	public FieldColumn[] getFieldColumns();

	/**
	 * The class for <T>
	 * @return
	 */
	public Class<? extends T> getDBOClass();

}

Example of DatabaseObject implementation

Define the POJO

In this example, we are showing how the POJO DBONodeType is added to the system. DBONodeType is a simple class with two fields:

public class DBONodeType {

	private Short id;
	private String name;
}

The first field 'id' is the ID of the type and 'name' is the name of the type. We want to store this object in the database table as follows:

select * from node_type;

Would result in something like this:

Now that we know what database POJO looks like and how we want to map it to a database table we are ready to implement the DatabaseObject interface:

public class DBONodeType implements DatabaseObject<DBONodeType> {


	private static FieldColumn[] FIELDS = new FieldColumn[]{
			new FieldColumn("id", "ID", true),
			new FieldColumn("name", "NAME"),
	};

	@Override
	public TableMapping<DBONodeType> getTableMapping() {
		return new TableMapping<DBONodeType>(){
			// Map a result set to this object
			@Override
			public DBONodeType mapRow(ResultSet rs, int rowNum)	throws SQLException {
				DBONodeType result = new DBONodeType();
				result.setId(rs.getShort("ID"));
				result.setName(rs.getString("NAME"));
				return result;
			}

			@Override
			public String getTableName() {
				return "NODE_TYPE";
			}

			@Override
			public String getDDLFileName() {
				return "schema/NodeType-ddl.sql";
			}

			@Override
			public FieldColumn[] getFieldColumns() {
				return FIELDS;
			}

			@Override
			public Class<? extends DBONodeType> getDBOClass() {
				return DBONodeType.class;
			}} ;
	}

	private Short id;
	private String name;

	public Short getId() {
		return id;
	}

	public void setId(Short id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

Here is a description of the methods:

• mapRow - Creates a new DBONodeType object and populates its fields direct from a JDBC result set. This mapping is used to read an object from the database.
• getTableName - The name of the database table. This table name is used in the auto-generation of all SQL used for this object.

• getDDLFileName - The name of the DDL file used to create the NODE_TYPE table. This file must be on the classpath. In this example this is the contents of the "schema/NodeType-ddl.sql" file:

  CREATE TABLE `NODE_TYPE` (
    `ID` smallint(6) NOT NULL,
    `NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    PRIMARY KEY (`ID`)
  )
  

• getFieldColumns - This array maps fields to column names and identifies which columns are part of the primary key. In this example, the primary key is the 'ID' column indicated with the 'true' in:

  new FieldColumn("id", "ID", true),
  

• getDBOClass - This provides the class of the database object, in this case it is DBONodeType.class.

Register the POJO with DBOBasicDao

Once we have defined our database POJO the last thing we need to do is register the DBONodeType class with the DBOBasicDao . Registration is necessary because the DBOBasicDao must create any table that does not exist and it needs to auto-generate all CRUD SQL used for instances of our new class DBONodeType. We register the DBONodeType class by adding an instance of it to the DBOBasicDaoImpl bean file 'dbo-beans.spb.xml':

<bean id="dboBasicDao" class="org.sagebionetworks.repo.model.dbo.DBOBasicDaoImpl">
		<property name="databaseObjectRegister">
			<list>
				<bean class="org.sagebionetworks.repo.model.dbo.persistence.DBONodeType" />
				....
			</list>
		</property>
	</bean>

We are now ready to start testing our new database object:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:jdomodels-test-context.xml" })
public class DBONodeTypeTest {

	@Autowired
	DBOBasicDao dboBasicDao;

	private short id = 1000;

	@After
	public void after() throws DatastoreException{
		if(dboBasicDao != null){
			MapSqlParameterSource params = new MapSqlParameterSource();
			params.addValue("id", id);
			dboBasicDao.deleteObjectById(DBONodeType.class, params);
		}
	}
	@Test
	public void testCRUD() throws Exception{
		// Create a new type
		DBONodeType nodeType = new DBONodeType();
		nodeType.setId(id);
		nodeType.setName("FakeType");

		// Create it
		DBONodeType clone = dboBasicDao.createNew(nodeType);
		assertNotNull(clone);
		assertEquals(nodeType, clone);
		// Fetch it
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("id", id);
		clone = dboBasicDao.getObjectById(DBONodeType.class, params);
		assertNotNull(clone);
		assertEquals(nodeType.getId(), clone.getId());
		assertEquals(nodeType.getName(), clone.getName());
		// Delete it
		boolean result = dboBasicDao.deleteObjectById(DBONodeType.class,  params);
		assertTrue("Failed to delete the type created", result);

	}

}