Database - Atypon-OpenSource/wayf-cloud GitHub Wiki

Table of Contents

• Query Management
• Named Parameters
• Result Set Processing
• DBExecutor

Query Management

Overview

SQL values can clutter up the codebase if it's contained in DAOs. Additionally, if the statement is defined as a Java string, changing its value would require a recompilation. In order efficiently organize the SQL statements, they should be abstracted out to separate property files.

Implementation

Guice's property mapping is leveraged to wire individual SQL statements to the DAO methods that need them. Each DAO has it's own property file in src/main/resources/dao. Prefixing each property with the file name ensures property uniqueness. A full implementation for a DAO may look like:

src/main/resources/dao/user-dao-db.properties:

user.dao.db.create = \
INSERT INTO wayf.user (first_name, last_name, email, phone_number) \
    VALUES (:firstName, :lastName, :email, :phoneNumber);

user.dao.db.read = \
SELECT id,\
        first_name AS 'firstName', \
        last_name AS 'lastName', \
        email AS email, \
        phone_number AS 'phoneNumber', \
        created_date AS 'createdDate', \
        modified_date AS 'modifiedDate' \
    FROM wayf.user \
        WHERE id = :id;

WayfGuiceModule:

public class WayfGuiceModule extends AbstractModule {
    @Override
    protected void configure() {
        try {
            ClassLoader classLoader = Thread.currentThread().getContextClassLoader();

            Properties properties = new Properties();
            properties.load(classLoader.getResourceAsStream("dao/user-dao-db.properties"));
        }
    }
}

UserDaoDbImpl.java:

public class UserDaoDbImpl implements UserDao {
    private static final Logger LOG = LoggerFactory.getLogger(UserDaoDbImpl.class);

    @Inject
    @Named("user.dao.db.create")
    private String createSql;

    @Inject
    @Named("user.dao.db.read")
    private String readSql;

}

Here you can see that the standard Guice @Named annotation can be used to reference a property. Since each property name is unique, the system does need to be told what file to look for the property in.

Named Parameters

Overview

The database technology behind the WAYF cloud is MySQL. As MySQL is a relational database, it requires that data be accessed via SQL statements. These are stored and executed as Prepared Statements to avoid SQL inject attacks. A Prepared Statement may look something like:

INSERT INTO wayf.user (first_name, last_name, email) VALUES (?, ?, ?);

This pattern may prevent SQL inject but it is somewhat brittle in that it relies on the input parameters being in a specific order. In order to make the system more flexible, Named Parameters may be used:

INSERT INTO wayf.user (first_name, last_name, email) VALUES (:firstName, :lastName, :email);

This statement does require the input fields to be in any order as the names can be matched to determine the proper placement of values.

Implementation

The WAYF cloud's implementation of Named Parameter Prepared Statements is largely based on Spring JDBC named parameters. The implementation may be found com.atypon.wayf.database.QueryMapper. The important difference between the WAYFs QueryMapper and the vanilla Spring implementation is that the QueryMapper will reflect on a Java object to automatically map it's properties into the SQL statement.

For example, if we have a User class that looks like:

public class User {
    public String getFirstName(){ ... };
    public String getLastName(){ ... };
    public String getEmail(){ ... };
}

We only need to pass in a User object into QueryMapper.buildQueryArguments for the SQL query values to be automatically populated.

Additionally, the custom WAYF QueryMapper syntax also supports nesting. If we have the following two classes instead:

public class User {
    public String getFirstName(){ ... };
    public String getLastName(){ ... };
    public Contact getConact(){ ... };
}

public class Contact {
    public String getEmail(){ ... };
}

We can map the Contact's email in the query by addressing it as a child of User by dot notation. The query would look like:

INSERT INTO wayf.user (first_name, last_name, email) VALUES (:firstName, :lastName, :contact.email);

The call to QueryMapper.buildQueryArguments would be the same in that the SQL string and the User object were the only two arguments but the mapper would find the nested email value and plug it into the Query.

Result Set Processing

Overview

Once a SQL statement has been executed on the database server, it is likely to return results to the application server. In order to map the result set to a usable Java object, a mapping layer is required.

Implementation

The motivation behind the mapping logic was to have it match the syntax of the custom WAYF prepared statement logic. This means that a SELECT statement would look like:

SELECT id,
        first_name AS 'firstName', 
        last_name AS 'lastName', 
        email AS 'email'
    FROM wayf.user 
        WHERE id = :id;

To accomplish this, the custom functionality was introduced with two classes: com.atypon.wayf.database.NestedFieldRowMapper and com.atypon.wayf.database.NestedFieldBeanMapper.

NestedFieldRowMapper

The responsibility of the NestedFieldRowMapper is to process the database ResultSet into a map of key-value pairs. The keys would be the column label as defined in the query and the values are the deserialized Java types that correlate to the column type. For example, a TIMESTAMP column would be mapped to a java.sql.Timestamp object. Once the row was transformed to a map, it could then be sent to the NestedFieldBeanMapper to have these values translated to a Java POJO.

NestedFieldBeanMapper

In the same way that the PreparedStamenet allows for Java fields to reflectively match SQL columns on the way in, NestedFieldBeanMapper allows for the mapping to work on the way out. In order to get a POJO from a database query, simply label the database query return columns to the same as the field names. Additionally, nesting attributes with a dot notation will also create nested POJOs. For example, if the User and Contact objects looked like:

public class User {
    public String getFirstName(){ ... };
    public String getLastName(){ ... };
    public Contact getConact(){ ... };
}

public class Contact {
    public String getEmail(){ ... };
}

We could map the email field with the following query:

SELECT id,
        first_name AS 'firstName', 
        last_name AS 'lastName', 
        email AS 'contact.email'
    FROM wayf.user 
        WHERE id = :id;

If the type of a property is an interface or an abstract class, you must create an implementation of com.atypon.wayf.database.BeanFactory and configure the new implementation in Guice. For an example of how this is done, see com.atypon.wayf.database.AuthenticatableBeanFactory.

DB Executor

Overview

Combining all of the functionality outlined above is the com.atypon.wayf.database.DbExecutor. For day-to-day work, this is the only class a developer will need to interact with. The DbExecutor encapsulates all of the database functionality and adds some useful front-doors for selecting, selecting just one row, and updated the database.