6 ms·

Spring DAL is really debatable. My view of it is that it is one of those 'fast early development' at the expense of maintenance. It's easy to write code, modify

Spring DAL is really debatable. My view of it is that it is one of those 'fast early development' at the expense of maintenance. It's easy to write code, modifying it later is difficult. Thus I feel this is an optimization to make writing easy code even easier. Therefore the pain of the Spring DAL is unexpectedly slow queries that are doing N+1 selects, having to translate SQL to JPA queries and back [+], dealing with the ORM issue that Spring models database rows and not database results, selects that retrieve far more data than they need, and the unnecessary situation of having to build a full object model to represent database (which is often unnecessary! If my only query aggregates 4 tables and returns two string values, there should be no need to model those 4 tables as full blown Java objects - I'm clearly a fan of things like MyBatis or JDBI for replacing the Spring DAL access. I've been embarking on that very effort at my job, so far replacing Spring JPA to JDBI has reduced a ton of code, was able to recently remove net 40 classes by replacing JPA with JDBI). For example, some queries are naturally best done as analytic queries (or window queries). I have no idea how to do that in JQL (JPA's query language). Moving those queries to the database side can come to the rescure, but it's still an issue.. Which makes me think that JPA makes the easy queries even easier, but the difficult queries nearly impossible (if not outright impossible). > Though micronauts is quickly approaching. I've been looking at micronaut quite a bit as well. Another big downside to springboot is that by default it wants around 100MB of RAM per instance. With 6~7 applications on one host, the expenditure for the memory is sizable. Micronaut has a significant leg up here. [+] DB Queries are often developed first by figuring out what the right query is. After that is done, it needs to be translated to JPA. If JPA eventually starts doing the wrong thing, or something slow - it then needs to be converted to SQL so you can do an explain plan. The latter is not that hard as you can enable a debug output to see what it is, but overall there are quite a few more steps here compared to: "develop your SQL query, next plop that into a string variable in your java code", vs "develop your SQL query, model the table relationships as java objects, build a JPA repository to run some queries against those tables" (in the happy case the latter is all that you need and is not bad, but when a query starts going slow, or starts fetching MB of data which you won't notice on local development but in production causes a p0...)

eweise 3y ago
Spring DAL is geared towards operational data stores where typically you are not joining four different tables. Its been a while but pretty sure you can just write the native SQL and map the results to a collection, bypassing mapping to objects. The best strategy I've found is to make the domain objects one-to-one with the tables so their is no joining or inheritance. Do any combining within the repository or application layers. This makes the DAL super simple.
- gabereiser 3y ago
  If you really really really have to, you can use @Query to define your own query for the repository interface method. This allows you to map, join, select, whatever you wish. The gripe is that if you don’t follow a specific wording convention, you must use @Query. JPA follows a specific convention - findByUsername or findBy (id) or findByPostAuthor(String name). There’s a whole section on how it derives sql statements from these methods.
  - seadan83 3y ago
    The magic naming in JPA CrudRepository can be pretty nice. Though, even resorting to @Query is not a panacea. Notably it breaks down for things like dynamic where clauses, dynamic joins - and you wind up using entity manager directly. This really good article [1] on how to use native queries has some good examples where the native query is very clean. Though, the article ends with this: ``` Query q = em.createNamedQuery("selectAuthorEntities"); SynchronizeableQuery hq = q.unwrap(SynchronizeableQuery.class); hq.addSynchronizedEntityClass(Author.class); List<Author> authors = (List<Author>) q.getResultList(); for (Author a : authors) { System.out.println("Author " + a.getFirstName() + " " + a.getLastName()); } ``` For a simple "select first, last from author", the above seems kinda insane. Further there are issues with the requirement that Author now be mutable. It gets even worse when it comes to the primary key and how that gets mapped. At work we have an ancient database where you cannot invoke a sequence within an insert statement. The ID generator for JPA throws a fit around this; it really wants to know the sequence and to use it. Hacking around JPA to not throw an error when we set the ID value directly is painful. JPA assumes that if an ID value is set, then your invocation of "entityManager.persist(entity)" will be an update. For us we have to set the ID value, and it very well is not an update operation that we wish to perform! Thus a simple task of inserting a new row and fetching it goes through different techniques, is required to have an object mapping, and breaks down suddenly if used in a different environment where the DB version changes (and of course, it is only production that uses this ancient database). For the use-case of the original developers this was fine, when looking at it now, with 4x the data and seeing that we do a N+1 query - the whole thing is a mess to work with as we have some join data that sometimes needs to be lazy fetched, and other times it does need to be eagerly fetched. Thus having duplicate DTOs to represent this situation because we need different annotations is a headache. So I've been facing with existing simple JPA entities that conditionally need to lazy fetch vs eager, and that's really painful. These are all tractable problems, but overall it would have been better/simpler for it all to have been pretty bare-bones SQL without any of the ORM layer. My snarky consolation is that at least those original developers got to save an hour or two when jamming this original product together in the first place. This is why I think SpringBoot + JPA are particularly good for toy demo projects, but something that is going to live for a decade and need serious changes and deal with serious changes in data scales - it's those difficult things that come later that are now requiring tons of time to implement. Some of those difficult things would be trivial to do had it all stayed as just SQL with mapping of results to objects. [1] https://thorben-janssen.com/jpa-native-queries/ https://thorben-janssen.com/jpa-native-queries/ -------------- Caveat, I am just saying it is debatable whether to use SpringBoot's DAL, or whether to avoid it and use something like JDBI instead (I strongly prefer the latter, and of course at work, that is what we're doing. The stuff that has been converted to JDBI performs really well because it fixes the lazy vs eager join problems, eliminates N+1 queries and always selects just exactly the right amount of data, and we can use advanced query structures that lets the database do the work (eg: case statements, analytic queries, the aggregation functions of DB, etc) - and it's been easy to test and has eliminated tons of needless JPA class mapping code that was inflexible for the growth of these applications). I've a lot of respect for the JPA repository stuff, but still just saying, it's debatable if that is a win. Usually the counter argument says "JPA is great stuff, and we can default to @Query or entity manager for native query - so how about we just give it a try and change it if it does not work?" At that point you become locked in and the painful parts are when you are 2 years down the road and have modify that system that of course uses JPA and mapped entities to the gills.

eweise 3y ago

Spring DAL is geared towards operational data stores where typically you are not joining four different tables. Its been a while but pretty sure you can just write the native SQL and map the results to a collection, bypassing mapping to objects. The best strategy I've found is to make the domain objects one-to-one with the tables so their is no joining or inheritance. Do any combining within the repository or application layers. This makes the DAL super simple.

gabereiser 3y ago
If you really really really have to, you can use @Query to define your own query for the repository interface method. This allows you to map, join, select, whatever you wish. The gripe is that if you don’t follow a specific wording convention, you must use @Query. JPA follows a specific convention - findByUsername or findBy (id) or findByPostAuthor(String name). There’s a whole section on how it derives sql statements from these methods.
- seadan83 3y ago
  The magic naming in JPA CrudRepository can be pretty nice. Though, even resorting to @Query is not a panacea. Notably it breaks down for things like dynamic where clauses, dynamic joins - and you wind up using entity manager directly. This really good article [1] on how to use native queries has some good examples where the native query is very clean. Though, the article ends with this: ``` Query q = em.createNamedQuery("selectAuthorEntities"); SynchronizeableQuery hq = q.unwrap(SynchronizeableQuery.class); hq.addSynchronizedEntityClass(Author.class); List<Author> authors = (List<Author>) q.getResultList(); for (Author a : authors) { System.out.println("Author " + a.getFirstName() + " " + a.getLastName()); } ``` For a simple "select first, last from author", the above seems kinda insane. Further there are issues with the requirement that Author now be mutable. It gets even worse when it comes to the primary key and how that gets mapped. At work we have an ancient database where you cannot invoke a sequence within an insert statement. The ID generator for JPA throws a fit around this; it really wants to know the sequence and to use it. Hacking around JPA to not throw an error when we set the ID value directly is painful. JPA assumes that if an ID value is set, then your invocation of "entityManager.persist(entity)" will be an update. For us we have to set the ID value, and it very well is not an update operation that we wish to perform! Thus a simple task of inserting a new row and fetching it goes through different techniques, is required to have an object mapping, and breaks down suddenly if used in a different environment where the DB version changes (and of course, it is only production that uses this ancient database). For the use-case of the original developers this was fine, when looking at it now, with 4x the data and seeing that we do a N+1 query - the whole thing is a mess to work with as we have some join data that sometimes needs to be lazy fetched, and other times it does need to be eagerly fetched. Thus having duplicate DTOs to represent this situation because we need different annotations is a headache. So I've been facing with existing simple JPA entities that conditionally need to lazy fetch vs eager, and that's really painful. These are all tractable problems, but overall it would have been better/simpler for it all to have been pretty bare-bones SQL without any of the ORM layer. My snarky consolation is that at least those original developers got to save an hour or two when jamming this original product together in the first place. This is why I think SpringBoot + JPA are particularly good for toy demo projects, but something that is going to live for a decade and need serious changes and deal with serious changes in data scales - it's those difficult things that come later that are now requiring tons of time to implement. Some of those difficult things would be trivial to do had it all stayed as just SQL with mapping of results to objects. [1] https://thorben-janssen.com/jpa-native-queries/ https://thorben-janssen.com/jpa-native-queries/ -------------- Caveat, I am just saying it is debatable whether to use SpringBoot's DAL, or whether to avoid it and use something like JDBI instead (I strongly prefer the latter, and of course at work, that is what we're doing. The stuff that has been converted to JDBI performs really well because it fixes the lazy vs eager join problems, eliminates N+1 queries and always selects just exactly the right amount of data, and we can use advanced query structures that lets the database do the work (eg: case statements, analytic queries, the aggregation functions of DB, etc) - and it's been easy to test and has eliminated tons of needless JPA class mapping code that was inflexible for the growth of these applications). I've a lot of respect for the JPA repository stuff, but still just saying, it's debatable if that is a win. Usually the counter argument says "JPA is great stuff, and we can default to @Query or entity manager for native query - so how about we just give it a try and change it if it does not work?" At that point you become locked in and the painful parts are when you are 2 years down the road and have modify that system that of course uses JPA and mapped entities to the gills.