RetroBase - an analogue of Retrofit for database queries

Many developers use the Retrofit library in their projects, which allows you to turn the HTTP API into a java interface. This is very convenient, as it allows you to get rid of excess code and use it very easily. You just need to create an interface and hang a few annotations.

Recently, I was developing an Android application that needed to make database queries through the JDBC driver. Then I got the idea to create something like Retrofit just for database queries. This is how RetroBase appeared, which I will tell you now.


This article in English. This article is in English.
')
In order for the interface and annotations to become working code, Annotation Processing is required, which opens up truly tremendous possibilities for automating the writing of the same type of code. And in combination with JavaPoet, the process of generating java-code becomes convenient and simple.

On Habré, as well as on the Internet, there are several good articles on this topic, so it’s easy to deal with Annotation Processing, and the necessary JavaPoet manual fits into its README.md .

The basis of RetroBase is two annotations DBInterface and DBQuery along with DBAnnotationProcessor , which does all the work. Using DBInterface , an interface with database DBQuery methods is marked, and DBQuery marks the methods themselves. Methods can have parameters that will be used in a SQL query. For example:

 @DBInterface(url = SpendDB.URL, login = SpendDB.USER_NAME, password = SpendDB.PASSWORD) @DBInterfaceRx public interface SpendDB { String USER_NAME = "postgres"; String PASSWORD = "1234"; String URL = "jdbc:postgresql://192.168.1.26:5432/spend"; @DBMakeRx(modelClassName = "com.qwert2603.retrobase_example.DataBaseRecord") @DBQuery("SELECT * from spend_test") ResultSet getAllRecords(); @DBMakeRx @DBQuery("DELETE FROM spend_test WHERE id = ?") void deleteRecord(int id) throws SQLException; } 

The most interesting thing happens in DBAnnotationProcessor , where the generation of the class that implements the interface is carried out, the generated class will have the name *_* + Impl interface_name *_* + Impl :

 TypeSpec.Builder newTypeBuilder = TypeSpec.classBuilder(dbInterfaceClass.getSimpleName() + GENERATED_FILENAME_SUFFIX) .addSuperinterface(TypeName.get(dbInterfaceClass.asType())) .addField(mConnection) .addMethod(waitInit) .addModifiers(Modifier.PUBLIC, Modifier.FINAL); 

After that a connection to the database is created:

 FieldSpec mConnection = FieldSpec.builder(Connection.class, "mConnection", Modifier.PRIVATE) .initializer("null") .build(); 

A PreparedStatement is also created for each request:

 FieldSpec fieldSpec = FieldSpec .builder(PreparedStatement.class, executableElement.getSimpleName().toString() + PREPARED_STATEMENT_SUFFIX) .addModifiers(Modifier.PRIVATE) .initializer("null") .build(); 

... and the implementation of the method for this query:

 MethodSpec.Builder methodBuilder = MethodSpec.methodBuilder(executableElement.getSimpleName().toString()) .addAnnotation(Override.class) .addModifiers(Modifier.PUBLIC) .returns(returnTypeName); 

This takes into account the type of the return value of the method. It can be either void if the SQL query is an INSERT, DELETE, or UPDATE. Or ResultSet , if the SQL query is a SELECT.

It also checks whether the method can throw a SQLException . If it can, they will be dropped from the implementation of the method. And if not - caught and displayed in stderr .

All parameters of the annotated method are added to the overriding method, and an expression is generated for each parameter that allows you to transfer the value of the parameter to the PreparedStatement :

 insertRecord_PreparedStatement.setString(1, kind); 

Of course, the number and types of method parameters must match the parameters of the query transmitted using the DBQuery annotation.

After the file has been generated, it is recorded using Annotation Processing:

 JavaFileObject sourceFile = processingEnv.getFiler().createSourceFile(filename); Writer writer = sourceFile.openWriter(); writer.write(javaFile.toString()); 

Rx it!

Of course, it is convenient to get ResultSet , defining only the interface. And it would be even more convenient to take advantage of the popular RxJava and get Observable . In addition, it will make it easy to solve the problem with the execution of queries in another thread.

For this, DBMakeRxAnnotationProcessor was created along with DBInterfaceRx and DBMakeRx , which allow you to create a class with wrapper methods. You could already see the application of these annotations in the example above. The created class will have the name *_* + Rx interface_name *_* + Rx , and will also have an open constructor that accepts an interface object annotated with DBInterfaceRx , to which it will redirect requests, returning results in a reactive style.

All that is needed is to add the DBMakeRx annotation to the method and pass the model class name to it. The generated wrapper method will return an Observable<* *> . In this case, the class name of the model can not be defined. In this case, the generated method will return io.reactivex.Completable , which is convenient for INSERT, DELETE or UPDATE SQL queries that do not require returning a result.

For example, for the methods of the ResultSet getAllRecords(); interface ResultSet getAllRecords(); and void deleteRecord(int id) throws SQLException; From the example above, the following wrapper methods will be generated:

  public io.reactivex.Observable<com.qwert2603.retrobase_example.DataBaseRecord> getAllRecords() { return Observable.generate(() -> mDB.getAllRecords(), (resultSet, objectEmitter) -> { if (resultSet.next()) { objectEmitter.onNext(new com.qwert2603.retrobase_example.DataBaseRecord(resultSet)); } else { objectEmitter.onComplete(); } } , ResultSet::close); } public Completable deleteRecord(int id) { return Completable.fromAction(() -> mDB.deleteRecord(id)); } 

Here mDB is an interface object annotated by DBInterfaceRx that was passed to the constructor.

As can be seen from the generated method, we will need to create model class objects from the ResultSet , so the model class must have an open constructor that accepts ResultSet .

Naturally, the parameters of the generated method will correspond exactly to the parameters of the method that is called:

  public Completable insertRecord(String kind, int value, Date date) { ... mDB.insertRecord(kind, value, date); ... } 

All exceptions that occur during the execution of a request are passed to Subscriber as it should be in Rx.

An example of using all of the above can be as follows:

  private SpendDB mSpendDB = new SpendDBImpl(); private SpendDBRx mSpendDBRx = new SpendDBRx(mSpendDB); public Single<List<DataBaseRecord>> getAllRecords() { return mSpendDBRx.getAllRecords() .toList() .compose(applySchedulers()); } 

And if you want to replace new SpendDBImpl(); or new SpendDBRx(mSpendDB); to perform tests, you can use the popular Dagger .

On github you can find source codes with comments , as well as a working example of this small library.

The purpose of this article was to show how useful Annotation Processing can be to get rid of writing the same type of code. And, I hope, you may have new ideas on using this tool in your projects.

UPD. 1: Thanks to the comments in the comments, subscriber unsubscribe check was added in RX wrapper methods. (RetroBase version 1.0.4)

UPD. 2: if you need to execute an INSERT query and get the id of the created record, get the number of rows changed as a result of an UPDATE query, or find out the id records that were deleted by the DELETE query, you can add the returning id construct to the end of the SQL query and get the ResultSet with the required id

 @DBMakeRx(modelClassName = "com.qwert2603.spenddemo.model.Id") @DBQuery("UPDATE spend_test SET kind=?, value=?, date=? WHERE id=? returning id") ResultSet updateRecord(String kind, int value, Date date, int id) throws SQLException; 

As you can see from the code, it is also possible to use the DBMakeRx annotation to get Observable<*id*> . For you need to create a model class that gets the Id from the ResultSet and pass it to the modelClassName parameter of the modelClassName annotation. The class of the model containing Id may look as follows:

 public class Id { private int mId; public Id(ResultSet resultSet) throws SQLException { mId = resultSet.getInt(1); } public int getId() { return mId; } public void setId(int id) { mId = id; } } 

UPD. 3: in the version of RertoBase 1.1, a check of java.sql.Connection#isValid(0) added to automatically create a new connection to the database in case of an error (for example, the connection is lost).

UPD. 4: in version RertoBase 1.2, all Rx code is generated for version RxJava 2.x. An example of the generated methods you saw above. Also in this way the problem of backpressure was solved, which was indicated in the comments.