Implementing the Table Data Gateway (and Record Set) Pattern(s)

This tutorial assumes you did the Row Data Gateway tutorial, the Active Record tutorial, and the Data Mapper tutorial.

So we’ve reached the point where we have a Data Mapper in the Technical Services Layer, but leaving it in the Technical Services Layer introduces an upward dependency. (BAD!)

The truth is that the Data Mapper we’ve implemented is effectively stuck between the Domain Layer and the Technical Services Layer because it:

  • Manipulates Domain Model Objects (domain layer)
  • Uses SQL and has a dependency on the java.sql package (data source)

A UML diagram of the domain layer and the technical services layer including class Data Mapper which spans both layers and has a dependancy on a Domain Model object and on the Java SQL package

Can’t have that, so we need object(s) which will:

  1. Contain any and all SQL statements in the technical services layer (Table Data Gateway)
  2. Provide indirection between the Data Mapper and package java.sql (Record Set)

Writing a TDG

A TDG – like a RDG – contains methods for SQL operations, but they are scoped to a single table.

A UML diagram of a Table Data Gateway whose finder methods return Result Set objects from the Java SQL package

I won’t provide an example of a TDG, but I will provide an example of a Data Mapper which uses a TDG:

public class StudentDataMapper {
		
	/* ... */
	
	public static synchronized Student findByGuId(UUID uniqueID) throws DataMapperException {
		try {
			
			ResultSet rs = studentGateway.findByGuId(uniqueID);
			
			while(rs.next()) {
				UUID guid = UUID.fromString(rs.getString("guid"));
				String name = rs.getString("name");
				char grade = rs.getString("grade").charAt(0);
				int studentID = rs.getInt("studentID");
				
				Student student = new Student(guid);
				student.setName(name);
				student.setGrade(grade);
				student.setStudentId(studentID);
				
				return student;
			}
			return null;
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		} catch (SQLException e) {
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		}
	}
	
	public synchronized void update(Student student) throws DataMapperException {
		try {
			
			studentGateway.update(student.getGrade(), student.getStudentId(), student.getName(), student.getGuId());
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured saving Students to the data source.", e);
		}
	}
	
	public synchronized void insert(Student student) throws DataMapperException {
		try {
			
			studentGateway.insert(student.getGrade(), student.getStudentId(), student.getName(), student.getGuId());
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured saving Students to the data source.", e);
		}
	}
	
	public synchronized void delete(Student student) throws DataMapperException {
		try {
			
			studentGateway.delete(student.getGuId());
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured saving Students to the data source.", e);
		}
	}
}

The attentive student will note, however that there is still a dependency between our Data Mapper class and package java.sql in the findByGuId(…) method:

public static synchronized Student findByGuId(UUID uniqueID) throws DataMapperException {
		try {
			
			ResultSet rs = studentGateway.findByGuId(uniqueID);
			// that's a java.sql.ResultSet
			
			/* ... */
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		} catch (SQLException e) {
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		}
	}

The end result is still that the Data Mapper has a dependency on java.sql:

A UML diagram of a Data Mapper which has a dependancy on a Domain Model Object and a TDG as well as an undesired dependancy on the Java SQL package

To fix this, we use a Record Set; an in-memory representation of tabular data which provides indirection and reduces coupling with package java.sql.

Writing a Record Set

For our current purposes, our Record Set need only be an interface which provides the methods of java.sql.ResultSet used by our Data Mapper:

public interface RecordSet {
	/* 
	 * Looks like a java.sql.ResultSet,
	 * but isn't.
	 * */
	
	public boolean next() throws RecordSetException
	public java.lang.String getString(java.lang.String arg0) throws RecordSetException
	public boolean getBoolean(java.lang.String arg0) throws RecordSetException
	public byte getByte(java.lang.String arg0) throws RecordSetException
	public short getShort(java.lang.String arg0) throws RecordSetException
	public int getInt(java.lang.String arg0) throws RecordSetException
	public long getLong(java.lang.String arg0) throws RecordSetException
	public float getFloat(java.lang.String arg0) throws RecordSetException
	public double getDouble(java.lang.String arg0) throws RecordSetException
	public java.math.BigDecimal getBigDecimal(java.lang.String arg0, int arg1) throws RecordSetException
	public java.math.BigDecimal getBigDecimal(java.lang.String arg0) throws RecordSetException
	public boolean first() throws RecordSetException
	public boolean last() throws RecordSetException
	public boolean previous() throws RecordSetException;
	
}

… and we can change the Table Data Gateway finder methods to return RecordSet objects instead of java.sql.ResultSet objects:

A UML diagram of a Table Data Gateway whose finder methods return Record Set objects

Now our Data Mapper doesn’t have a dependency on java.sql.ResultSet:

public static synchronized Student findByGuId(UUID uniqueID) throws DataMapperException {
		try {
			
			RecordSet rs = StudentGateway.findByGuId(uniqueID); // No longer a java.sql.ResultSet
			
			while(rs.next()) {
				UUID guid = UUID.fromString(rs.getString("guid"));
				String name = rs.getString("name");
				char grade = rs.getString("grade").charAt(0);
				int studentID = rs.getInt("studentID");
				
				Student student = new Student(guid);
				student.setName(name);
				student.setGrade(grade);
				student.setStudentId(studentID);
				
				return student;
			}
			return null;
			
		} catch (StudentGatewayException e) {
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		} catch (RecordSetException e) { // previously handled java.sql.SQLException
			throw new DataMapperException("Error occured reading Students from the data source.", e);
		}
	}

… and all is well in our UML diagram:

A UML diagram of a Data Mapper which has dependancies on a Domain Model Object and the TDG as well as on Record Set

  • Tim

    Wow, many thanks for the series from raw data gateway to this. I was stuck trying to implement a separation from the HTTP REST API wrapped in a SDK (which in my opinion is like java.sql, except it’s asynchronous). I keep on thinking how I would maintain the flexibility to one day switch to another REST service. And I think your discussion is it! Is there anymore to this series of discussion in your web page? Is there anymore in Fowler’s book?

    • http://richard.jp.leguen.ca Richard JP Le Guen

      My web site doesn’t touch on this topic again – these were the last tutorials in this course and I wasn’t writing material for the course that follows.

      I recommend checking out Fowler’s book, and equally seeing if you can get ahold of the Master’s thesis “Enterprise Application Design Patterns: Improved and Applied” by Stuart Thiel from Concordia University that compliments the book well. The thesis should be available from here: users.encs.concordia.ca/~sthiel/thesis/Thesis_Stuart.doc

      However both the book and the thesis are higher-level, focusing on the overall architecture of a system – not just the technical services and data access layers – so they may contain more information than you need.

  • Kevin Sheppard

    Created 3 years ago and still helping people in 2016. Thank you for this series. I spent all week trying to understand and choose persistence techniques for a new project and this ironed out everything nicely.

    Thanks again!