annotation based spring-jdbc [DATAJDBC-82]

[spring-projects-issues]

cemo koc opened DATAJDBC-82 and commented

Almost all modules of spring evolved in last a few years in terms of simplicity and conciseness. We are against using heavy libraries such as JPA or other persistence API implementations. Spring JDBC module is simple and low level. However spring-jdbc module has not changed so much. It is working great but it can be considered a little bid old-fashioned. You have to duplicated a fair amount code to just query a table. I am not opening this issue in Spring Data project because I think that this must be part of Spring Framework.

What we would like to see is an annotation based approach. There is a project which is very similar to what I have in my mind. JDBI 1 has annotation based API and it is very convenient to use it.

public interface MyDAO
{
  @SqlUpdate("create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);
}

This piece of code can be more elegant even with ParameterResolvers. I will try to summarize the ideas in my mind which can greatly improve this module.

• Annotation based queries.
• Mapper annotation for bean mappers
• Batch query support
• Returning generated id from jdbc driver
• Map or List types support
• Container based return types such as Collections or Guava Optional or something else
• Parameter type support.

I can count other goodness of JDBI project. After having an annotation approach in Spring JDBC module, Oliver or someone else can add a module to Spring Data project which is based on Spring JDBC module but working with annotations such as Persistence API Annotations. Also Spring Roo project's auto finder can be part of this project as well.

I predict that the improvement I suggested above on JDBC module can create same effect of Spring MVC Annotation based Controller effect.

Hope that It will be part of Spring JDBC module.

---

Issue Links:

• DATAJDBC-116 Add SQL annotations like in JDBI
  ("is duplicated by")

4 votes, 11 watchers

[spring-projects-issues]

Juergen Hoeller commented

Ollie, what's your take on this proposal?

Juergen

[spring-projects-issues]

cemo koc commented

@Oliver,

I am considering this issue very crucial for v4.1. I think, as many others, that JPA based solutions are very heavy and also their learning curve is problematic. I am expecting a great traction by this issue as the effect created by annotation based request mappings or Java based programmatic configurations of Spring.

The developers of other frameworks claim that productivity with java frameworks on data layer is poor. You need to duplicate a fair amount codes. Or you need to know many other things as Persistence Api. However what we really want to do is accessing database by a Datasource.

@SqlUpdate("insert into something (id, name) values (:id, :name)")
void insert(int id, String name);

This little piece of code easily shows how this can easily be learned. You do not need to know another SQL derivative language, a framework or anything else. All you need to know is pure SQL.

There will be another winner, Spring-Boot project. I still do not believe a JPA based library can be considered lightweight currently. With this enhancement, Spring Boot will be more lightweight than ever.

Also, there is another question, Where this stuff should be in? If Spring will continue to have a spring-jdbc module, this should be part of this module. Spring data project can have an extension of this module which is based on Persistence API Annotations or new annotations.

[spring-projects-issues]

Oliver Drotbohm commented

While I certainly agree with you on the fact that JPA adds a layer of indirection over JDBC, I'm nur sure I necessarily agree on the implications you outline. Additional functionality definitely adds weight, but I'd argue that a layer of abstraction also adds simplification (otherwise it's bad abstraction). Also, your JDBC sample is probably as equally as complex as the JPA counterpart (depending on what prior knowledge you bring), and especially if it comes to the various flavors of joins, JDBC is far from less complex than JPA.

That aside, JDBC based data access definitely its place in the toolbox of Java developers, whatever the reasons to use it may be. Also, I think the content of the proposal here is valuable and worth implementing. However I don't think the Spring JDBC module is the right place for it. This is mostly due to the additional layer of abstraction that will be introduced. Beyond that, a lot of the code that will be helpful to implement stuff like this resides in Spring Data Commons. There's effectively no way Spring JDBC will be able to introduce a dependency on that module.

However, I think the stuff suggested here can be an interesting addition to the Spring Data JDBC module as it would effectively bring the module more on par with other modules providing the repository abstraction already. There's also a 3rd-party project taking a similar approach maintained by nurkiewicz. It might be worth investigating, whether it makes sense to join forces

[spring-projects-issues]

cemo koc commented

My example is easier than JPA counterparts which is requiring both JPA configuration and also JPQL knowledge. Even though native sql query is possible with JPA, It still require you to configure JPA.

I am aware of Tomasz Nurkiewicz's project but what I am suggesting here is different. I am suggesting a similar implementation of JDBI. SQL Object API of JDBI is focussing on only declarative programming. JDBI does not and will not fill gap betwen JPA annotations and JDBC as Tomasz's done. (By the way I really like spring-data-jdbc-generic-dao project). A few years ago I had filed an similar issue to Data Project as well.

Let me to summarise improvements in my mind clearly:

Spring JDBC:

• Add something like JDBI Sql Object.
• Add parameter conversion API support

Example: Guava's Optional. If it has data bind this data, If not bind null

@SqlUpdate("insert into something (id, birthday) values (:id, :userBirthday)")
 void insert(int id, Optional<Date> userBirthday);

In case a value, It will provide birthday here...

• Add parameter return type API support

@SqlQuery("select name from something where id = :id")
Optional<String> findNameById(@Bind("id") int id);

If the return value is not null, it will return Optional with this value. In other case It will Optional.absent()

• Add support for expansion such as List types.
• Support for returning Generated Id which is currently very ugly in Spring JDBC with KeyHolders

Shortly this module will bring many similarities as JDBI Object Api has. But It will not focus on bringing a common Repository DAO based on some annotations such Persistence API Annotations. It will not try to fill the gap between persistence mapping and

Spring Data JDBC module:

• Has dependency to Spring Data Commons and Spring JDBC
• Spring Data project can provide a DAO repository base on some annotations and use necessary parts from Spring Data Commons.
• Add an implementation similar to Spring ROO's finders. It can be based on again JPA annotations.

I am not considering myself as an expert for Spring Data project. Please forgive my ignorance If I say something wrong.

Thanks for your response

[spring-projects-issues]

Serkan ÖZAL commented

Hi,

I implemented an object mapping based "RowMapper" library for Spring's JDBC. Maybe it can be a part of this implementation

It's URL is https://github.com/serkan-ozal/spring-jdbc-roma-impl.

There are other lots of interesting features and these features can be customized with developer's extended classes. It has some new and unique features like conditional lazy, conditional lazy object loading and conditional field ignoring. In addition, it has custom expression language named RXEL (ROMA Expression Language).

Spring-JDBC-ROMA is a rowmapper extension for Spring-JDBC module. There is already a rowmapper named org.springframework.jdbc.core.BeanPropertyRowMappe r for binding resultset attributes to object. But it is reflection based and can cause performance problems as Spring developers said. However Spring-JDBC-ROMA is not reflection based and it is byte code generation (with CGLib and Javassist) based rowmapper. It generates rowmapper on the fly like implementing as manual so it has no performance overhead. It also supports object relations as lazy and eager. There are other lots of interesting features and these features can be customized with developer's extended classes.

Features:

• All primitive types, strings, enums, dates, clob, blob, collections and complex objects are supported.

• Writing your custom class (or type) based field generator factory, object creater, object processor, table name resolver, column name resolver implementations and customizable data source, schema, table names are supported.

• Writing your custom field based mapper, SQL based binder, expression language based binder and custom binder implementations are supported.

• Lazy or eager field accessing is supported. Lazy support can be configured as conditional and conditions can be provided as expression language or as custom lazy condition provider implementations. If lazy condition is not enable, specified field is not handled as lazy and set as eager while creating root entity.

• Loading lazy fields can be enable or disable at runtime dynamically by using key based, expression based and custom implementation based approaches. If lazy-load condition is not enable, specified lazy field is not loaded no matter field is configured as lazy.

• Ignoring fields can be enable or disable at runtime dynamically by using key based, expression based and custom implementation based approaches. This feature is very useful in some use-cases. For example, this feature can be used to ignore some fields while serializing to JSON at your specified controller and this behaviour doesn't effect other controllers.

• Writing field access definitions as REXL (ROMA Expression Language) or as compilable Java code in annotation. XML and properties file configuration support will be added soon.

[spring-projects-issues]

Alfred Brown commented

Regards, developers.
Do you know the Spring JDBC Data Repository project
https://github.com/nurkiewicz/spring-data-jdbc-repository
?
I think it is very useful and fills a gap missing between JDBC and Spring
Data repositories Core. People can benefit from a compromise on pure JDBC
and JPA.
I have post a message in
nurkiewicz/spring-data-jdbc-repository#9
related to this question.
Thanks in advance

[spring-projects-issues]

Alex Rader commented

Spring Data JPA has similar feature https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query
It will be consistent and very useful

[spring-projects-issues]

Jens Schauder commented

Marking this as "complete" might be a stretch, but with https://github.com/spring-projects/spring-data-jdbc we do now have a Repository abstraction based on JDBC available.

With https://jira.spring.io/browse/DATAJDBC-164 the first basic annotation support is there as well.

Please create separate issues for further improvement requests