How to secure your REST API with Java Spring Security

Here is the best article I read on how to secure REST API and even make a full stateless architecture by using JWT token to store all user details.

Great, great, great work by Jerome Loisel from Octo Technologies.

https://octoperf.com/blog/2018/03/08/securing-rest-api-spring-security/

Enjoy the simplicity of this article. Jerome succeded to make something complex understandable.

Avoid select min/max with Oracle

I hate select max clauses with Oracle. My company used to write subqueries which such expressions. It's slow and unreadable. Conditions in subquery with upper table create deep dependencies and awfull links between table. It doesn't allow to reuse code.

That's why I investigate on a performant solution to avoid select max/min. A solution is "dense_rank first". It will help you to retreive only one line per group. It's difficult to explain. So, I made a sample on SQL Fiddle.

Schema is :

CREATE TABLE test (student, year, status, program) AS
SELECT 'john', 2010, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2011, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2012, 'passed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2013, 'failed', 'bba' FROM DUAL
UNION ALL SELECT 'john', 2014, 'unknown', 'bba' FROM DUAL
UNION ALL SELECT 'clara', 2011, 'failed', 'mstf' FROM DUAL
UNION ALL SELECT 'clara', 2012, 'passed', 'msmmd' FROM DUAL;

Sample request is :

SELECT
student,
min(year) keep (dense_rank FIRST ORDER BY YEAR desc) year,
min(status) keep (dense_rank FIRST ORDER BY YEAR desc) status,
min(program) keep (dense_rank FIRST ORDER BY YEAR desc) program
FROM test
GROUP BY student

And the result is :

STUDENT |YEAR |STATUS  |PROGRAM |
--------|-----|--------|--------|
clara   |2012 |passed  |msmmd   |
john    |2014 |unknown |bba     |

How to write request ?

This is the most important part of this post. In this sample, we want to get the status and program for the most recent academic year for each student. So, first of all, we have to GROUP BY student. Once we have partionned our data by student, we want to keep that contains the most recent academic year. That's why we make a keep (dense_rank FIRST ORDER BY YEAR desc).

So, don't forget :

1. Group your data by a criteria 
2. In each group, you will keep only the first row. That's why you have to define a order criteria
3. Of course, your "group by" criteria can not be used as "order" criteria

You can try it by yourself :

http://sqlfiddle.com/#!4/ba828/1/0


Enjoy!