Table Data Gateway Pattern

This pattern belongs to Data Source Architectural Patterns Catalog and this Catalog belongs to Patterns of Enterprise Application Architecture.

Intent

An object that acts as a Gateway to a database table. One instance handles all the rows in the table.
Here Gateway means an object that encapsulates access to an external system or resource.

Explanation

  • A Table Data Gateway holds all the SQL for accessing a single table or view: selects, inserts, updates, and deletes. Other code calls its methods for all interaction with the database.The idea of a table gateway is to provide a gateway class for each database table.
  • Mixing SQL in application logic can cause several problems so Table Data Gateway pattern separates out the SQL queries from code.

How It Works

A Table Data Gateway has a simple interface, usually consisting of several find methods to get data from the database and update, insert, and delete methods. Each method maps the input parameters into a SQL call and executes the SQL against a database connection. The Table Data Gateway is usually stateless, as its role is to push data back and forth.

Table Data Gateway pattern separates out the SQL queries from source code.

When to Use It

  • Table Data Gateway is probably the simplest database interface pattern to use, as it maps so nicely onto a database table or record type. It also makes a natural point to encapsulate the precise access logic of the data source. I use it least with Domain Model  because Data Mapper  gives a better isolation between the Domain Model  and the database
  • In order to use Table Data Gateway, you'll have one for each table in the database. For very simple cases, however, you can have a single Table Data Gateway that handles all methods for all tables.
  • One of the benefits of using a Table Data Gateway to encapsulate database access is that the same interface can work both for using SQL to manipulate the database and for using stored procedures.

Sample code

Let's create a Class Diagram for sample code of PersonGateway to demonstrate this pattern.

Step 1: Create a Domain model or POJO class.
public class Person{
    private int id;
    private String firstName;
    private String lastName;
    private String gender;
    private int age;
    // Getter and Setter methods
}
Step 2: Create a PersonGateway class which holds all the SQL for accessing a single table or view: selects, inserts, updates, and deletes. Other code calls its methods for all interaction with the database.
public class PersonGateWay{
    public RecordSet find(int id){
        // find person record by id.
    }
    public RecordSet findByFirstName(){
        // find person by first name.
    }
    public void update(String firstName, String lastName, String age){
        // Update person entity.
    }
    public void insert(String firstName, String lastName, String gender,String age){
         // insert person entity.
    }
    public void delete(int id){
        // Delete person record by id from database.
    }
}

References

https://www.martinfowler.com/eaaCatalog/tableDataGateway.html

Comments