Reform SQL-based approach in DAO

Introductory

I often in projects have to deal with frameworks for working with databases. Conceptually, these frameworks can be divided into 2 large classes:

• ORM- oriented
• SQL- oriented

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

A little review

Before you break, I suggest to remember, quite briefly, that there is . Who in the subject, feel free to skip the review.

And so, on the scene, representatives of the ORM- oriented approach:
')

• Hibernate
• Eclipselink
• Mybatis
• Jook

These representatives do their job in different ways. The latter, for example, DSL-way, offers to construct SQL queries using either the generated objects according to your database schema, or just strings. Others require the description of 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 instead - ORM thinking.

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

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

All of these solutions in one form or another are add-ons above the java.sql. * Package. And so these frameworks are cooler, the more they isolate the developer from him. However, using them, you have to think first in SQL categories, and then ORM.

I know about the existence and the third class of frameworks: generators. It is difficult for them to win a niche, because such solutions are usually written for a specific project and it is difficult for them to be universal. The idea 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 not very usual when you have to refine the generator of the DAO layer, instead of writing SQL queries or mapping.

What's wrong?

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

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

Much of the above made me ask: "And what should the framework be for you to like it?"

Declarative style

How? I think simple, so that he took and began to code. But what if it is serious? Declarative. Yes, I am a supporter of declarative style in such things, rather than imperative. What java comes to mind first when it comes to the declarative approach? Yes, just 2 things: annotations and interfaces . If these 2 substances are crossed and sent to the mainstream of the SQL-oriented solution, 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 under consideration. The idea itself is certainly not new, notes about a similar one I met in an IBM article somewhere in 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 mapping fields described?
3. How about something more complicated? And then these examples for 3 kopecks are already fed up.

I suggest in order to answer these questions and hike to reveal the possibilities of the framework.

Just proxy

>> 1. And who implements 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 to get access to the implementation is very simple, with the help of ... Yes, in other examples it is 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 to access the database, for example, the implementation of java.sql.Connection or javax.sql.DataSource or in general your object. 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 mapping fields described?

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

Examples

>> 3. What about something more complicated? And then these examples for 3 kopecks are already fed up.

But does it make any sense to bother with the SQL-92 parser, if the framework does not know how to do it, something more complicated? But to show everything in 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 java-code.

One of the few things that I never liked about SQL-based 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 is despite the fact that date checks will appear again when setting values ​​in PreparedStatement. And what is our friend offered? And it offers dynamic filters . The 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<Client> findClients(@TargetFilter("begin_date") Date beginDate, @TargetFilter("end_date") Date endDate); } 

And the point is that if the parameter value, for example, beginDate is null, then the associated SQL filter regTime> = :: begin_date will be cut 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, then the WHERE clause will not be in the final query. And note - in the code only the 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 the java code I’ll say that I’m not a fan of annotations myself, and a large number of them in most projects are just annoying. Therefore, I provided for an alternative defined by him - filters by object bean properties:

 //  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<Client> findClients(@TargetFilter ClientFilter period); } 

It turns out quite succinctly and clearly. Pro dynamic filters should be said separately that they support, or rather, they can be cut from subqueries and all predicates, excluding OVERLAPS and MATCH. I have never met the last ones in SQL living expressions, but there is a mention of them in the SQL-92 specification.

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

True, there is always one problem with static filters: 'How to react to the null parameter'? The quick answer seems to say - "Throw an exception, you will not be mistaken." But is it always necessary? Let's move on to the example of downloading clients with a certain status and check it out:

 public interface IClientDao { @TargetQuery(query = "SELECT id, name, state " + " FROM clients " + " WHERE state = :state", type = QT_SELECT, orm = Client.class) List<Client> 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 just such clients, non-statutory? The concept of SQL-92 parser saves again. It is enough to replace the filtering expression by status with ': state' with ': state ? ' as the framework engine modifies the WHERE section into the following view ' ... WHERE state IS NULL ' , if, of course, the input method is null.

About mapping

Filters in queries are nice, but in java solutions a lot of attention is paid to the mapping of the result of a SQL query on ORM objects and their binding and binding of the entities themselves. This is so much, look at what the JPA specification costs. Or, in your projects, look at shifting from ResultSet to domain objects or setting values ​​in PreparedStatement. Bulky, is not it? I chose the path, maybe less reliable and less elegant, but definitely - simple. Why go far when you can arrange a mapping right in the SQL query. This is the first thing that comes to mind?

Let's go straight to the examples. And here's the task, how to score the result of the query, if all the columns of the table differ from the ORM fields of the class, plus ORM has an embedded 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 realistic. The aesthetics of mepping is definitely lame, but still this option is closer to me than endless annotations or xml files. Yes, with the reliability of trouble, here and runtime and ORM refactoring issues of objects and not always the opportunity to take SQL and test in your favorite database client. But I will not say that this is a hopeless situation - tests save them from runtime and refactoring. To check the query in the database client 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, for example, for client_id it is necessary to preserve 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 query.

And last, a little business logic

Ideal dao, when one operation - one declarative method. And this is certainly good, but it is not always the case. In an application, you often need to get a hybrid or composite entity, when part of the data is generated by one SQL query, another part by a second SQL query, etc. Or make certain gluing, checking. Perhaps this is not the essence of Dao itself, but such manipulations usually take place and are isolated, but made public in order to be called from different parts of the program. But what should we do with interfaces, if it is nevertheless an urge 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 rotten, because it's 2017, but can I play on it? And what if you cross the declarative style adopted as the basis for the development of the DAO layer and default methods for business logic? And let's see what happens if you need to add an ORM object check 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, in addition to the stipulated one: sampling a limited number of columns, managing schemes, pagination (not for all is true), manipulating data ordering, reports, updating, inserting and deleting.

Conclusion

I don’t know, dear reader, whether I managed to bring something new into this filled world of SQL frameworks or not, to judge you. But I tried and was pleased 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 githaba, the link to which is already indicated in the chapter A little review in the list of SQL-oriented frameworks with the last line. Good luck.

Editorial staff

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