Architecture and Data Blog

On a recent project we had to connect to AWS Aurora postgres 10.6 version of the database in SSL mode using JDBC and Java 11 JRE. When the Aurora cluster is setup, we can force all connections to use SSL by using the options group settings (forceSSL=true), establishing secure connection from the application to the database is not as easy as it looks.

In all new development and sometimes during legacy codebase modernization, developers tend to add code quality checks and static analysis of codebase such as style checks, bug finders, cyclomatic complexity checking etc. into the CI/CD pipeline. When we inherit a codebase that has much PL/SQL and there is a desire to put the PL/SQL code base through the same types of code analysis, what options does a developer/dba have?

When using Groovy with Spring framework,  interacting with the database can be done using the Groovy.SQL class which provides a easy to use interface. When using Groovy.SQL, if we have a need to do transactions, we have the .withTransaction method that accepts a closure, to which we can pass in code to execute within the transaction. In our project since we were using spring already, using annotations to define transactions would be a great. Standard @Transactional annotations with Groovy.SQL will not work, since every place where the Groovy.SQL is used a new connection is acquired from the connection pool causing the database work to span multiple connections, which can result in dead-locks on the database. What we really want is that the database connection be the same across all invocations of Groovy.SQL with-in the same transaction started by the annotated method.

My latest project involves talking to MS-SQL Server using the JDBC driver and Java. While doing this we setup the database connection and had a simple SQL to get the first_name and last_name for a unique user_id from the application_user table in the database.

SELECT first_name,last_name
FROM application_user
WHERE user_id = ?

Given the above SQL, we did not think too much about performance as the user_id was indexed. The java code as below was used to run the SQL.

Consider this Hibernate mapping

@Column(name = "qReferenceId")
public Long getQReferenceId() {
return qReferenceId;
}

Where qReferenceId is data provided to our application via a external reference, we do not have a QReference Object or Table for FK references. When trying to query this object using DetachedQuery, this Simple expression was used.

public List<Movie> findByQReferenceId(Long id) {
     final SimpleExpression matchesId = Property.forName("qReferenceId").eq(id);
    DetachedCriteria criteria = DetachedCriteria.forClass(Movie.class);
    criteria = criteria.add(matchesId);
    List<Movie> movies = (List<Movie>) getHibernateTemplate().findByCriteria(criteria);
    return movies;
}

When running this method, I kept getting errors shown below.

Recently talking to someone about a persistence framework that they are using, this framework creates a rows in the database table with just Primary Key value and then later on updates the table row with values for other columns. Because of this framework, the tables cannot have any NOT-NULL constraints defined.

For example, the framework is doing

INSERT INTO customer (customerId, name, birthdate) VALUES (1,null,null);
UPDATE customer set name = 'FOO Name', birthdate = '12/12/1978' WHERE customerId = 1;

You cannot have NON-NULL constraint defined on name or birthdate column, since the INSERT statement would blow up, forcing you to change table design