Reform SQL-oriented approach in DAO

Introductory

In projects, I often have to deal with database frameworks. Conceptually, these frameworks can be divided into 2 large classes:

• ORM- oriented
• SQL- oriented

Some of them are good, some are not very good. But subjectively, I can say: SQL-oriented are inferior in development to ORM-oriented. I emphasize in development, but not in opportunities. Although it will not work to change this balance, it is quite possible to offer an unusual look at the world of SQL-oriented approach. Who cares, welcome to kat

A bit of review

Before transgressing, I suggest recalling, quite briefly, what is . Who in the subject, feel free to skip the review.

And so, on stage, representatives of the ORM- oriented approach:

• Hibernate
• Eclipselink
• Mybatis
• Jooq

These representatives cope with their task in different ways. The latter, for example, DSL-way, suggests constructing SQL queries using either generated objects according to your database schema or just strings. Others require describing the correspondence between java objects and database tables. But not the point. All of them are united by one idea: to isolate the developer from writing SQL queries as much as possible, offering ORM thinking in return.

On the other hand, representatives of the SQL-oriented approach gathered:

• Spring Framework JDBC
• sql2o (really liked)
• Jdbi
• Reforms

All these solutions in one form or another are add-ons for the java.sql. * Package . And the more cool these frameworks are, the more they isolate the developer from him. Nevertheless, using them, you will have to think first with SQL categories, and then with ORM.

I know about the existence of a third class of frameworks: generators. It is difficult for them to win a niche, because such decisions are usually written for a specific project and it is difficult for them to be universal. The idea in them is the following: to generate the DAO layer completely, using the knowledge of a specific project, knowledge of the database and the specifics of business requirements. I met with such decisions twice. It is very unusual when you have to modify the generator of the DAO layer, instead of writing SQL queries or mapping.

What's wrong?

I deliberately did not give value judgments to this or that approach, ORM vs SQL vs Generators. Everyone decides for himself, in combination with the circumstances, what to choose. But here I am ready to offer a certain alternative both in stylistic expression and in conceptual expression for SQL-oriented ones. But first I’ll say that I don’t like it at the code level (performance, debug, etc. - I omit it) in existing solutions:

1. Certain verbosity to achieve simple things
2. Boilerplate, boilerplate, boilerplate ... and again the boilerplate
3. Lack of a point in the code where you can see sql-orm or orm-sql options
4. In one form or another, constructing an SQL query by filtering conditions
5. Much knowledge for using the framework API - learn about +100500 entities before breaking code

A lot of the above made me ask: 'What kind of framework should it be for you to like it?'

Declarative style

Which one? I think simple, so that he took and began to code. But seriously? Declarative. Yes, I am a supporter of declarative style in such things than imperative. What comes to mind first in java when it comes to a declarative approach? Yes, only 2 things: annotations and interfaces . If these 2 substances are crossed and directed into the channel of the SQL-oriented solution, then we get the following:

public interface IClientDao {
    @TargetQuery(query =
            "SELECT id, name, state " +
            "  FROM clients " +
            "  WHERE id = ?", type = QT_SELECT)
    Client findClient(long clientId);
}

This is a simple working example, the essence of which is to emphasize the idea of ​​a declarative style, which is reflected in the framework in question. The idea itself is certainly not new, I saw notes about something like this in IBM articles around 2006, and some of the frameworks already use this idea. But seeing such an example, I would reasonably ask a few questions:

1. And who implements the IClientDao contract and how to get access to the implementation?
2. And where is the mapping of the fields described?
3. What about something more complicated? And then these examples are already tired of 3 cents.

I propose, in order, to answer these questions and the campaign to reveal the capabilities of the framework.

Just proxy

>> 1. And who is implementing this contract and how to get access to the implementation?

The contract is implemented by the framework itself using the java.lang.reflect.Proxy tool and you do not need to implement it yourself for SQL purposes. And access to the implementation is very simple, with the help of ... Yes, by the way, it’s easier to show by example:

IClientDao clientDao = com.reforms.orm.OrmDao.createDao(connection, IClientDao.class);
Client client = clientDao.findClient(1L);

Where connection is an object for accessing the database, for example, an implementation of java.sql.Connection or javax.sql.DataSource or your object in general. Client is your ORM object and perhaps the only thing from the framework itself is the com.reforms.orm class. OrmDao , which will cover 98% of all your needs.

Concept

>> 2. And where is the mapping of the fields described?

As promised above, I will touch on 2 things: style and concept. To answer the second question, you need to talk about the concept. The message is that in order to propose a new one, a radical view of the solution is needed. What about an SQL-92 parser? When this thought first came to me, I threw it so far that I thought I would never meet her again. But how then to make the SQL-oriented framework convenient? Saw another add-on? Or do another helper to the helper of the framework? In my opinion, it is better to limit the supported set of SQL constructs - as a good compromise, and in return get something convenient, in my opinion. Mapping is based on the expression tree, after parsing the SQL query. In the example above, the column names are mapped to the ORM field names of the object one to one. Of course, the framework supports mapping and is more complex, but about it a little later.

Examples

>> 3. What about something more complicated? And then these examples are already tired of 3 cents.

But does it make sense to bother with the SQL-92 parser at all, if the framework is not able to do something more complicated? But to show everything in the examples without volume is not an easy task. Of course, I will show, though I will omit the table declarations in SQL and parts of the java code.

One of the few things that I have never liked in SQL-oriented solutions is the need to construct SQL queries. For example, when certain filtering criteria may or may not be specified. And you probably know this piece of code, or rather its simplified version:

    // где-то в DAO
    private String makeRegTimeFilter(Date beginDate, Date endDate) {
        StringBuilder filter = new StringBuilder();
        if (beginDate != null) {
            filter.append(" reg_time >= ?");
        }
        if (endDate != null) {
            if (filter.length() != 0) {
                filter.append(" AND");
            }
            filter.append(" reg_time < ?");
        }
        return filter.length() == 0 ? null : filter.toString();
    }

And I wrote this fragment exactly as I most often meet in old projects. And this despite the fact that date checks will appear again when setting values ​​in PreparedStatement. And what is offered by our friend? And he offers dynamic filters . Using an example is easier to understand, so let's look at finding customers for a certain interval:

public interface IClientDao {
    @TargetQuery(query =
            "SELECT id, name, state " +
            "  FROM clients " +
            "  WHERE regTime >= ::begin_date AND " +
            "        regTime < ::end_date", type = QT_SELECT, orm = Client.class)
    List findClients(@TargetFilter("begin_date") Date beginDate,
                             @TargetFilter("end_date") Date endDate);
}

And the bottom line is that if the parameter value, for example, beginDate is null, then the associated SQL filter regTime> = :: begin_date will be cut out from the final SQL query and in our case the following line will go to the database server:

SELECT id, name, state FROM clients WHERE regTime < ?

If both values ​​are null, the WHERE section will not be in the final query. And note - in the code there is only a declaration and no logic. In my opinion, but I really want to listen to others, this is a strong weapon and a strong side of the framework. According to java code, I will say that I myself am not a fan of annotations and a large number of them in most projects are simply annoying. Therefore, he provided an alternative that he defined - filters by the properties of the object's bean:

// Бойлерплейт get/set код вырезал. В своем файле.
public class ClientFilter {
    private Date beginDate;
    private Date endDate;
}
public interface IClientDao {
    @TargetQuery(query =
            "SELECT id, name, state " +
            "  FROM clients " +
            "  WHERE regTime >= ::begin_date AND " +
            "        regTime < ::end_date", type = QT_SELECT, orm = Client.class)
    List findClients(@TargetFilter ClientFilter period);
}

It turns out pretty concise and understandable. About dynamic filters, it’s worth separately saying that they support, or rather, they can be cut from subqueries and all predicates, excluding OVERLAPS and MATCH. I have never seen the latter in 'live' SQL statements, but there is a mention of them in the SQL-92 specification.

Of course, the framework also supports static, mandatory filters. And they have the same syntax as in HQL or SpringTemplate - ': named parameter' .

True, there is always one problem with static filters: 'How to respond to a null parameter'? The quick answer seems to say - "Throw an exception, you will not be mistaken." But is it always necessary? Let's go for example, loading clients with a certain status and check this:

public interface IClientDao {
    @TargetQuery(query =
            "SELECT id, name, state " +
            "  FROM clients " +
            "  WHERE state = :state", type = QT_SELECT, orm = Client.class)
    List findClients(@TargetFilter("state") ClientState state);
}

But the task is what to do if the client status in the database may be absent? The state column allows NULL values ​​and we need to search for exactly such clients, stateless? The concept of SQL-92 parser saves again. It is enough to replace the filter expression by status from ': state' to ': state ? ' as a framework engine modifies the WHERE section into the following form ' ... WHERE state IS NULL ' , unless, of course, null falls into the input of the method.

About mapping

Filters in queries is certainly good, but in java solutions much attention is paid to mapping the result of a SQL query to ORM objects and their binding and the binding of the entities themselves. There are so many, see what one JPA specification is worth. Or look in your projects at shifting from ResultSet into domain objects or setting values ​​in PreparedStatement. Cumbersome, isn't it? I chose the path, maybe less reliable and less elegant, but definitely simple. Why go far when you can arrange mapping directly in a SQL query. Is this the first thing that comes to mind?

Let's get right to the examples. And here's the problem, how to map the query result if all the columns of the table are different from the fields of the ORM class, plus ORM has a nested object?

public interface IClientDao {
    @TargetQuery(query =
            "SELECT cl.id AS client_id, " + // underscore_case -> camelCase преобразование
            "       cl.name AS clientName, " + // можно сразу в camelCase
            "       cl.state AS client_state, " + // any_type to enum преобразование автоматически поддерживается, если в enum имеется аннотация @TargetMethod
            "       cl.regDate AS t#regTime, " + // t# - это директива, что нам нужна и дата и время java.sql.Timestamp -> java.util.Date
            "       addr.addressId AS address.addressId, " + // обращение к вложенному объекту через точку, а как еще?
            "       addr.city AS address.refCity, " +
            "       addr.street AS address.refStreet " +
            "  FROM clients cl, addresses addr" +
            "  WHERE id = :client_id", // допускается указание именованного параметра для простого фильтра
            type = QT_SELECT)
    Client findClient(long clientId);
}

This example is more close to real conditions. The aesthetics of mapping are definitely lame, but still this option is closer to me than endless annotations or xml files. Yes, reliability is a trouble, here are runtime and ORM object refactoring issues and it’s not always possible to take SQL and test it in your favorite database client. But I will not say that this is a hopeless situation - tests save from runtime and refactoring. In order to check the request in the database client, you will have to “clean” it. Another point: What SQL query will go to the database server ? All AS sections will be cut from the request. If you need to save, for example, for client_id the value of cid as an alias, then you need to add a colon before this alias: cl.id AS cid: client_id and cid will live -> cl.id AS cid in the final request.

And last, a bit of business logic

Ideal Tao when one operation is one declarative method. And this is certainly good, but this is not always the case. In an application, you often need to get a hybrid or composite entity, when part of the data is formed by one SQL query, another part by the second SQL query, etc. Or make certain gluings, checks. Perhaps this is not the essence of the Tao itself, but such manipulations usually take place and they are isolated, but made public in order to call from different parts of the program. But what do we do with the interfaces, if it does itch to shove business logic into Tao? Unexpectedly for me and to the surprise of many developers, default methods appeared in java8. Cool? Yes, I know that the news is bad, because 2017 is in the yard, but do not play on it? But what if we cross the declarative style adopted as the basis for the development of the DAO layer and the default methods for business logic? And let's see what happens if you need to add an ORM check for an object to null and load data from another DAO:

public interface IClientDao {
    // этот метод уже видели
    @TargetQuery(query =
            "SELECT id, name, state " +
            "  FROM clients " +
            "  WHERE id = ?", type = QT_SELECT)
    Client findClient(long clientId);
    // получить дао внутри другого дао доступно из коробки
    IAddressDao getAddressDao();
    // метод с бизнес логикой
    default Client findClientAndCheck(long clientId, long addressId) throws Exception {
        Client client = findClient(clientId);
        if (client == null) {
            throw new Exception("Клиент с id '" + clientId + "' не найден");
        }
        // Здесь код может быть сколь угодно сложным, если нужно
        IAddressDao addressDao = getAddressDao();
        client.setAddress(addressDao.loadAddress(addressId));
        return client;
    }
}

I don’t know if I’m right, but I want to call it interface programming . Something like this. In fact, all that I wanted to tell. But this is definitely not all that the framework can do, apart from the stipulated one: sampling a limited number of columns, managing schemes, paginating (not for everything the truth), manipulating data ordering, reports, updating, launching and deleting.

Conclusion

I don’t know, dear reader, whether I managed to bring something new to this filled world of SQL frameworks or not, it's up to you to judge. But I tried and am happy with what I tried. I look forward to a critical look at the proposed approach and ideas, if any. The solution is available on the github, the link to which is already indicated in the chapter A little overview in the list of SQL-oriented frameworks last line. Good luck.

Editorial

N1. Added Client.class object type to @TargetQuery annotation for methods that return lists.