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.
