2

We are planning to use Spring Boot with JPA for our next project and I am wondering how much flexibility JPA gives in reality. If we start developing using a self-hosted PostgreSQL server and later want to change to Amazon RDS PostgreSQL or SQL Server, will we run into a lots of problems? On paper, JPA should serve as an abstraction layer, but If we start to use Database specific data types, views, functions/stored procedures we quickly lose the ability to change.

Generally what is the best practice? Use only JPA even if It means performance loss or dive hard into database programming with plSQL or T-SQL and if platform change is needed, rewrites of those functions needs to happen.

Another problem is the data types. PostgreSQL for example can be extended with ltree and it provides a good foundation for hierarchical data structures, but to use it, one need to extend JPA to use the ltree data type or solve every interaction with functions.

3

If we start developing using a self-hosted PostgreSQL server and later want to change to Amazon RDS PostgreSQL or SQL Server, will we run into lots of problems?

Not really. We recently migrated from Oracle 11g to Azure SQLServer and it just took us to change some Spring properties.

spring.datasource.url=jdbc:sqlserver://<host>:<port>;databaseName=<DB>
spring.datasource.username=<user>
spring.datasource.password=<pass>
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.database-platform=my.project.persistence.CustomSQLServer2012Dialect

We had to review some mappings too. For example, we had to change @Column(name = "READ") by @Column(name = "[READ]"). I guess that READ is a reserved word.

Look at the properties an pay attention to spring.jpa.database-platform=my.project.persistence.CustomSQLServer2012Dialect.

Spring Data JPA allows us to implement our dialects extending those provided by Hibernate. We usually want to take advantage of the built-in functions and DB server features. Think about these date or string functions that operate at DB level and make our life simpler.

Take a look to how we can incorporate these functions into JPA

public class PrestigeSQLServer2012Dialect extends SQLServer2012Dialect {

    /**
     * Default constructor
     */
    public PrestigeSQLServer2012Dialect() {
        super();
        registerFunction("convertStringToDate",
                new SQLFunctionTemplate(StandardBasicTypes.DATE, "CONVERT(DATETIME,?1,?2)"));

        // Current SQLServer Date's end of month day (without time)
        registerFunction("endOfCurrentMonth", new SQLFunctionTemplate(StandardBasicTypes.DATE, "EOMONTH(GETDATE())"));

        // Current SQLServer Date's end of month. Add or Remove
        registerFunction("endOfCurrentMonthAdd",
                new SQLFunctionTemplate(StandardBasicTypes.DATE, "EOMONTH(GETDATE(),?1)"));

        // Date's end of month
        registerFunction("endOfMonth", new SQLFunctionTemplate(StandardBasicTypes.DATE, "EOMONTH(?1)"));

    }}

Later we can use these functions with JPA queries

@Query("select kpi from KPIAccounts kpi where kpi.created between endOfCurrentMonthAdd(:months * -1) and endOfCurrentMonth()")
    Iterable<KPIAccounts> findKPIByMonthRangeBackforward(@Param("months") int months);

You will find Spring Data JPA to be very sophisticated and flexible.

Generally what is the best practice? Use only JPA even if It means performance loss or dive hard into database programming with pl SQL or T-SQL and if platform change is needed, rewrites of those functions needs to happen.

Depends totally on your requirements and needs. As I commented, we usually want to take profit of all the possible DB server features. If you find yourself needing full support of these functions and crafting complex queries, probably JPA won't help you. Right the opposite, it will generate unnecessary overhead. ORMs usually don't fit well in data-first domain. If that were the case, row mappers like JDBCTemplate or myBatis are better alternatives.

Performance could be one more concern to take into account because ORMs usually have lower performance than mappers.

If I were asked, I would not put the business into the DB. I didn't during the last decade and I didn't need it either. Placing logic into the DB tie business to the DB capabilities and constraint the way our system evolves. One more undesired drawback is vendor lock-in.

I find these strategies to make us loose cohesion. One more concern could be testing. I find hard to make unit testing of T-SQL or pl/SQL procedures.

Another problem is the data types. PostgreSQL, for example, can be extended with ltree and it provides a good foundation for hierarchical data structures, but to use it, one need to extend JPA to use the ltree data type or solve every interaction with functions.

As I commented above, if you find that the project relies heavily on the DB server features and capabilities, probably ORMs is not the proper tool for you. However, regarding this question (data types), I have found several references to how to map ltree structures with JPA.

I guess, the question here is Should we implement ORMs?

The only possible answer is depends on your needs and requirements. Before making a decision, invest time in researching and implementing tests of proof. Get familiar with various frameworks and find which one suits best your needs. Don't implement frameworks just because everyone else does. It's usually a mediocre argument that leads to mediocre solutions.

  • We did use JDBCTemplate before, but we would like a more productive workflow, manually building repositories takes too much time and with JPA we could lower the development time significantly for the rest api(s). One other thing we plan to use is Spring Data REST, which works well with JPA too. If I understand correctly, we can present views as repositories too, but I think our focus should be implementing as much as possible in java. Thank you for your detailed answer! – appl3r Jul 20 '17 at 13:11
  • if you plan to use Spring Data Repositories, be aware of the drawbacks. Customising repositories is quite complex and somewhat annoying. I'm right now trying to figure out how to do it and I'm starting to regret of using such facilities. But yes, for the simplest use cases, Spring Data is quite useful and productive. Good luck! – Laiv Jul 20 '17 at 13:14
0

On a good day you will have enough tests set up to run your build against any potential new database/environment to find out if it works as you expect. There are some good articles about how to build and test with Spring data.

I think its fairly common practice builds against an in-memory H2 database and run basic tests against that, but would also have an instance of my target database running on a CI server with more in-depth testing to test against real world deployment.

As far as taking advantage of specific database features such as the PostgreSQL ltree you mentioned, would it be possible to build just using standard data types, and maybe enhancing your model for specific databases? Also check whether Spring data/JDBC has support for these features, and how the database itself implements them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.