8 ms·

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 wr

• 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.
    • gabereiser 3y ago
      Jpa ID’s can be any serializable with a matching column type. If you’re going down to the JDBI level or doing jooq to get at your data, there’s a problem. I’ve found most of my issues with Jpa and Hibernate specifically are due to relationships and my table design. Sometimes just having the foreignkey and not the full relationship is the answer, sometimes writing a custom @Query is, but never has going down to the jdbc level been pleasurable. Even looking at your example above, it’s something I would flag in a code review as being not novel and not trivial and therefore complicates the code base unnecessarily. SynchronizedQuery can be simplified with the synchronized keyword in modern Java on the interface method of your repository. For a really large spring project with hundreds of tables, we abandoned entity relationships outside of FK’s and instead did our joins in @Query’s, named queries (views essentially), services (to join repositories) and some components to share cached results in-memory, across nodes. What kind of ID were you trying to use and did you create a generator for it? IdentifierGenerator interface
      • seadan83 3y ago
        > Even looking at your example above, it’s something I would flag in a code review as being not novel and not trivial and therefore complicates the code base unnecessarily. SynchronizedQuery can be simplified with the synchronized keyword in modern Java on the interface method of your repository. Apparently that lengthy bit of code is to specify the query space. That call to "hq.addSynchronizedEntityClass(Author.class);" is seemingly a critical part. I learned a bit though to consider whether that could be simply sychronized (I appreciate your response!) From that same blog post: >> The query space describes which entity classes your query references. Hibernate uses it to optimize the dirty check and flush operation that it has to perform before executing the query.. You can do that by unwrapping Hibernate’s SynchronizeableQuery from JPA’s Query interface and calling the addSynchronizedEntityClass method with a reference to your entity class. I agree that is not trivial and complicates the code base unnecessarily. For comparison (just putting this out there), the equivalent JDBI (with no concerns over dirty checks, flush operations, or query spaces) in nearly its entirety is : ``` List<Author> author = jdbi .withResultMapper(BeanFactoryMapper.of(Author.class)) .withHandle(handle -> handle.createQuery("select first, last from author") .mapTo(Author.class) .list()); @Data class Author { String first; String last; } ``` I particularly like how this moves so much knowledge into the query string and away from the java classes. The above could be a far more complex query with joins, aggregation functions, etc.. and no new java classes would be needed, just a longer query string. In JPA, there is no query string of course. In JPA, the complexity of a more complicated query is pushed to additional classes with data fields and annotations. This is just a different characteristic of how one scales with complexity vs the other. Whether ORM vs result-set-mapper is better depends on context. It's all about picking the right tool for the right job. There are difference in how they scale relative to needed queries, complexity of data model, it's very situational. As an analogy, I think ORM is like PHP - great for getting started and building some pretty big stuff very quickly. For a larger system I would not recommend PHP. I find JPA to be very hard to maintain once scaled to larger code bases with non-trivial relational data. > What kind of ID were you trying to use and did you create a generator for it? IdentifierGenerator interface It was a vanilla Long. The IdentifierGenerator suggestion is a good one, I think that might have solved potentially all of the problems we had with the PK values (there were others). I think the crux of that issue was the existing annotation worked, but then we also needed it to work for a very ancient version of Oracle where it no longer. The required annotations for the two different versions of Oracle were not compatible with one another.. just messy. The issue of 'sometimes eager fetch' vs 'sometimes lazy fetch' was a large problem at the same time. I think that was perhaps a difficult problem because an '@Eager' was added to a number of fields already so teasing these different scenarios apart for all of the existing queries involving a number of entities was going to be a very non-trivial process. ------------- I'm just pointing out there are trade-offs when going with an ORM vs a result-set mapper. Both are better than doing some direct JDBC work. Spring+JPA pushes you into an ORM philosophy, which still for sure has its place.