Custom constraint violation error messages with TMDBC

January 12, 2010 at 9:52 pm | Posted in PostgreSQL, TMDBC | Leave a comment

One thing I’ve never seen is in database client libraries is good, boilerplate-free support for supplying ones own error messages for integrity constraint violations, so I’m pleased to have added it to TMDBC. The standard messages the database gives when a constraint is violated aren’t really useful to present to end-users as-is, constraint names would for example be just plain confusing to a lowly end-user. I’ve solved this with the option of providing a custom list of error messages, optionally containing the data that was given as arguments, for each pair of DML command (well, any SQL command really) and constraint (or not-null column). If a constraint is violated and a custom message is found it will be used in place of the one provided by the database.

Enabling it requires first that a specific exception is added to the method-mapped-to-SQL-command in question to signal that it may throw an exception due to a constraint violation. A generic method on an interface could then look like this:

 @SQL(paramNames={"emp.salary", "emp.name"},
         value=
            "INSERT INTO employee ( salary, name )\n" +
            "VALUES ( ?, ? )\n" +
            "RETURNING emp_id")
 int insertEmployee( Employee emp ) throws ConstraintViolationException;

ConstraintViolationException has two methods that provide the name and type of the violation, but it’s not neccesary to use them for anything. The exception will look up a properties file named constraint-violation.properies in the package the of class with the @SQL annotated method to see if there is an error message defined for it. If the “name” column of the “employee” table has a unique constraint named “employee_name_key” and the method above is in an interface named “Sample”, then this file could contain:

Sample.insertEmployee.employee_name_key=Cannot add a new employee with name '%2$s', one already exists with that name.

If employee_name_key is violated during invocation of that method then the detail message of the exception would contain that text, with the second parameter to the SQL command interpolated into the message. One can also provide only the name of the constraint to provide a default message whenever it is encountered in the given package, but those cannot reference the arguments given to the SQL command as above.

Since this uses Java properties files it’s easy to localize them. To provide error messages in my native language I’d use one of the ways TMDBC supports providing a error message locale, and create a constraint-violation_no_NO.properties file with the following:

Sample.insertEmployee.employee_name_key=Kan ikke legge til en ny ansatt med navn '%2$s', det finnes allerede en ansatt med det navnet.

And that’s pretty much all there is to it.

This is tagged PostgreSQL because…

Short answer: It’s PostgreSQL specific.

I haven’t found any interfaces that let’s me get the names and types of constraint violations, so for now I’m parsing the error messags I get from the database. Since so far TMDBC only supports PostgreSQL I’ve hard-coded a set of regexp’s to check the response and fish out the constraint name if there’s a match. A stable interface that will provide that, both for database independence and stability across versions, is one of the things I’d like to see in PostgreSQL, but this approach works for me today and would be necessary for current releases anyhow.

TMDBC: Extending the Java compiler to handle SQL

May 25, 2009 at 9:09 pm | Posted in PostgreSQL, TMDBC | Leave a comment

Some few years ago I was developing a database backed Java application and grew annoyed at how cumbersome interacting with a database really was in that language. I just wanted to interface with SQL using Java call semantics and native Java objects. I didn’t want to have to write dumb container objects copying the result set type in those cases where that was all I needed, and under no circumstance did I want write anything to copy data back and forth, neither code nor any kind of configuration to explain to some tool how to do that. I just wanted a simple, boilerplate-free and seamless way to run SQL commands from Java, kind of like running SQL from PL/pgSQL is.

Additionally, since I was working in Java I expected that everything would be statically checked. I expected types to be checked between the database and application and that all SQL in the application would be verified against the database without having to do anything such as set up tests or, again, write configuration or something. For example syntax errors in SQL commands in the application or passing a Java string to a date field in a SQL command should generate a compile time error.

So I started tinkering on my spare time, and ended up with an approach relying on the metaprogramming facilities in Java to simply take SQL commands and Java method signatures and figure out how to connect them. Others have used similar approaches too, in Java and other languages; those that follow Planet PostgreSQL might have noticed Aurynn Shaws Simpycity tool for Python, which operates in the same fashion despite being simpler.

The tool is accidentally named TMDBC, and consists of a compile time and runtime library. The compile time is technically implemented as a code generating Java 6/JSR 269 annotation processor, a consequence of which is that it’s invoked by the Java compiler when it detects the relevant annotations, and while the run time is actually optional you won’t have all the fun without it. It has been developed against PostgreSQL and only tested successfully against that.

The implementation still has a few rough edges, but it’s quite usable and since you can mix that approach and plain JDBC in the same transaction using it shouldn’t introduce any hurdles. I feel quite confident in that there aren’t any severe, outstanding issues.

Enough ranting, code here

Here’s an example. If the use-cases one were to satisfy required that one could fetch a single entry, a range of entries or a list of entries from a “employee” table then a complete example that satisfies everything I’ve mentioned could be:

package test;

import java.util.List;
import no.thormick.tmdbc.annotation.*;

 // Connection to development database, not production
@JDBC( "jdbc:postgresql://localhost/postgres?user=test&password=test" )
@DBAPI public interface MinimalDBI {
    // abstract class also works and is more flexible
    @SQL(
        "SELECT name, salary, emp_id\n" +
        "FROM   employee\n" +
        "WHERE  emp_id = ?")
    Employee getEmployee( int empId );

    @SQL(
        "SELECT name, salary, emp_id\n" +
        "FROM   employee\n" +
        "WHERE  emp_id BETWEEN ? AND ?")
    List<Employee> getEmployees( int empIdLow, int empIdHigh );

    @SQL(
        "SELECT name, salary, emp_id\n" +
        "FROM   employee\n" +
        "WHERE  emp_id = ANY ( ? )")
    List<Employee> getEmployees( int[] empIds );
}

And that’s everything, done, no configuration files anywhere. When this is compiled an implementation of this interface as well as the “Employee” class will be generated (unless you supply it, in which case it will be type-checked against the result set). If there’s a type error between a SQL command and Java method or if PostgreSQL finds something wrong with a SQL command, then a compile error will be signaled. To use it, the following would suffice (the connection handling here is just the simplest way to do it, other more transparent approaches are supported):

package test;

import java.sql.*;

public class Main {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            // This, on the other hand, is the production connection
            conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost/postgres?" +
                "user=test&password=test");
            // Handling the connection like this is just one way to do it
            MinimalDBI db = new MinimalDBIImpl(conn);

            System.out.println("One employee:");

            Employee employee = db.getEmployee(2);
            System.out.println("Employee "+ employee.getEmpId() +": "+
                               employee.getName() +", "+ employee.getSalary() );

            System.out.println("Two employees:");

            for(Employee emp : db.getEmployees(new int[]{1,3}) ){
                System.out.println( "Employee "+ emp.getEmpId() +": "+
                                    emp.getName() +", "+ emp.getSalary() );
            }
        } finally {
            if( conn != null )
                conn.close();
        }
    }
}

Note how the class “MinimalDBIImpl” is instantiated. This is the generated database interface class (the name can be set by passing an argument to the DBAPI annotation) and contains the glue between Java and SQL (or JDBC, to be precise.)

That example might produce output like the following:

One employee:
Employee 2: Bar, 60
Two employees:
Employee 1: Foo, 50
Employee 3: Bruce, 70

This example is very minimalistic. Using a interface for the @DBAPI class makes TMDBC use some (hopefully reasonable) defaults for the implementation, an abstract class can be used for more flexibility. Java collection types are used, which causes the results to be materialized, but there are runtime library classes that can be used instead to return connected objects, pretty much a cursor that returns JavaBeans. To use that instead just change the return-type in the method signatures from “List” to “ResultStatement“. Those can be used in a for each loop like above too.

The web page for the tool is here, and there’s also JavaDoc for the API. I’ve written a considerably more elaborate example on how to use TMDBC together with GlassFish to create a PostgreSQL backed Web Service, also demonstrating a different way to handle connections.

Have fun with it!

Blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.

Follow

Get every new post delivered to your Inbox.