An HTTP api for analytic queries
This post has been featured on This Week in Spring – November 8, 2016 and I’m so happy to be referred as “friend of the Spring community” 🙂
So, you have read through the previous parts of this series (1, 2 and 3) and you now have a Spring Boot project with jOOQ enabled, an Oracle Database with a simple schema containing musical data. What can you do with it?
Content
- An introductory example
- Window functions with jOOQ
- Common table expressions, window functions and Java expressions combined
- Summary and use cases
In the previous post I had a simple select, getting all artists from my library ordered by name. For queries like that I wouldn’t bother investigating into jOOQ. I have written extensively about my use of Hibernate and summarized my thoughts in this interview. In short: You can come very far with JPA / Hibernate, you can even create portable JPQL queries that support some analytics, as Thorben shows. Thorben also has a nice little tool that helps deciding wether JPA / Hibernate is the right tool for the job, checkout it out: Should you use JPA for your next project?
Imagine the following use cases based on this schema:
- I want a list of top n albums by a list of artists ordered by play count
- I want a list of artists and how many times I played a track by them cumulative by day
- Given a specific months, I want a top n list of tracks played in this month ordered by play count and also including the change in result to the previous month
You can try all examples yourself by cloning this repository: github.com/michael-simons/DOAG2016 and running those steps:
./mvnw docker:start ./mvnw clean package ./mvnw spring-boot:run |
Please read through the links at the top of this post to grok each step.
