[
https://issues.apache.org/jira/browse/FINERACT-2022?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Adam Saghy updated FINERACT-2022:
---------------------------------
Fix Version/s: (was: 1.11.0)
> Type-safe native SQL queries
> ----------------------------
>
> Key: FINERACT-2022
> URL: https://issues.apache.org/jira/browse/FINERACT-2022
> Project: Apache Fineract
> Issue Type: Improvement
> Reporter: Aleksandar Vidakovic
> Priority: Major
> Labels: FIPS
>
> h2. Background and Motivation
> Fineract initially had only JPA as its storage mechanism. When we moved from
> Mifos to the Apache Software Foundation we had to give up Hibernate as the
> default/standard JPA implementation and used the Apache sister project
> OpenJPA. OpenJPA had a couple of performance and standard compliance related
> issues which lead some developers to use plain native SQL queries in their
> service implementations. Because MySQL was for a long time the only
> configured database in the development environment developers started to
> introduce database system dependent query constructs (e. g. date functions).
> This was one of the main reasons why Fineract got stuck for a long time with
> MySQL 5.7 and for a while we couldn't even upgrade to the latest MySQL 8
> version. Support for PostgreSQL was requested by the community repeatedly
> over the years, but couldn't be provided due to the these database dependency
> issues. Starting with Fineract 1.6.x we replaced OpenJPA with EclipseLink and
> added a thin abstraction layer for database system specific functions which
> allowed us to support PostgreSQL.
> Nevertheless, we still have a lot (and growing) number of SQL native queries
> that are provided as Strings. This makes refactoring at the least a very time
> consuming task. Our database compatibility support is very limited (only
> specific functions) and as we wrote the code ourselves we have an additional
> burden to maintain it. Supporting more database systems (SQL Server, Oracle)
> in this setup is not worth the effort.
> h2. Target Personas
> * developers
> * integrators
> * BaaS
> h2. Goals
> * introduce QueryDSL to generate code for type-safe SQL queries
> * use QueryDSL also for complex JPA queries
> * make sure all repository classes follow Spring Data best practices (aka
> remove homegrown boilerplate code and annotations as much as possible)
> * implement any unsupported features related to database queries as QueryDSL
> extensions/plugins
> * automatically generate most/all of the query related code
> * avoid/remove all custom "@Query" annotations from repository classes
> * avoid/remove all JdbcTemplate use; all queries need to be included
> * avoid SQL/OQL queries as much as possible by using Spring Data query
> function mapping
> h2. Non-Goals
> * replace all JPA related code with pure Spring Data JDBC and QueryDSL
> h2. Proposed API Changes
> h3. Spring Data JPA
> Generate OQL DSL with QueryDSL and replace all custom JPA queries (either in
> "@Query" annotations and/or in direct use of "EntityManager").
> h3. Spring Data JDBC
> Generate SQL DSL with QueryDSL and replace all (String based) SQL queries
> (either in "@Query" annotations and/or in direct use of "JdbcTemplate").
> h3. Business logic services
> Avoid all direct use of JdbcTemplate or EntityManager. Make sure all query
> related data is contained only in repository classes. Optional: enforce this
> as a architecture rule with ArchUnit.
> h2. Risks
> Effectively we'll have three different database abstraction mechanisms in
> place with with slightly different behavior:
> * Spring Data JPA
> * Spring Data JDBC
> * QueryDSL
> It might make sense to eliminate at least one of them in a later iteration.
> h2. ETA
> We have roughly 50 feature packages/modules with database related
> functionality. Some are larger than others; first migrations could take a bit
> longer before we establish a routine. A rough calculation would be 3-4 days
> per feature/module which results in 150-200 development days or about 6
> months to finish this task. It might make sense to introduce specific unit
> tests to ensure that we don't introduce regressions. This could add some
> overhead (maybe 1 day) to the initial estimate.
> h2. Diagrams
> TBD
> h4.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
