arnaud.nauwynck@gmail.

com

Introduction to Db - Jdbc - JPA - SpringData

This document:
http://arnaud-nauwynck.github.io/docs/Intro-DB-Jdbc-
JPA-SpringData.pdf
(4) < Spring Data

Spring JPA
QueryDsl
< JPA API - JPQL
(3) < Hibernate/EclipseLink

Spring JDBC
< JDBC API
(2) < Driver Impl.

SQL Client Driver

(1) < DataBase
< B-Tree File
Spring JPA
QueryDsl

Spring JDBC
Structured Query Langage
SQL = DDL + DML
Oracle Sample DataBase
https://github.com/oracle/db-sample-schemas/
human_resources/hr_cre.sql
UML Employee - Department

* employees 0..1 dept

Employee Department
0..1 deptManager

0..1 manager
Example using PGAdmin3
SQL SELECT

Simple
(with WHERE clause)

With “JOIN .. ON”

(and GROUP BY)

With SubQueries
SQL DML
Detailed CRUD
CRUD =

Create INSERT into <Table..> (col1, col2, …)

VALUES (?0, ?1, 123, ..)
Read SELECT * from <Table..> where <expr..>

Update UPDATE col1=value1, col2=…

from <Table..> where <expr..>

Delete DELETE from <Table..>

where <expr..>
Emp-Dept CRUD Sample
Emp-Dept Queries
SQL Exercises
http://www.w3resource.com/sql-exercises/sql-subqueries-
exercises.php
Spring JPA
QueryDsl

Spring JDBC
SQL Merge (=Upsert)
Simple SELECT Query
[ With Col1, Col2 from Table1... ]

select /*+HINT */
Col1,
Col2 as prettyName,
function(col3,col4)
from Table1 alias1, Table2 alias2,
(inner/outer..) join Table3 alias3 on ..
where
Col1 = 123 - - Literal Value
and Col2 = ?0 - - Bind-Variable … ?0=123
and Col3 = Col4
and alias1.Col1 = alias2.Col2 – explicit JOIN with alias
[ limit .. firstRows .. cf also rownum ]
Order by Col2 asc, Col3 desc
Aggregate Query

select C1, C2, count(*), avg(Col3), min(Col4) ..

from Table ..
where
...
Group by C1, C2

Having ..

Order by ...
SQL Nested Queries

Example 2:
select .. from (select .. where ..) where .. not in (select ..where )
Analytical Query Functions
select …
analyticalFunc[first,last,nth,min,max,avg,..](..)
OVER (PARTITION BY ..)
from …
where ..
Hierarchical Queries
Exemple: clause “where” using Hierarchy
Advanced Bulk Update
with PL/SQL + Array

CREATE OR REPLACE PROCEDURE ..(a ARRAY) IS

CURSOR c IS select .. from TABLE(CAST(a ..))
BEGIN
OPEN c;
LOOP FETCH c BULK COLLECT INTO ...;
END
Spring JPA
QueryDsl

Spring JDBC
B-Tree = Balanced-Tree
(not only Binary Tree)
B-Tree

a self-balancing tree data structure

that keeps data sorted
and allows searches,
sequential access,
insertions, and deletions
in logarithmic time.
Sort/Compare Rows – Columns ?

(col1, col2, col3, col4, col5) <? (col1, col2, col3, col4, col5)

( 123, “Hello”, FALSE, +1e3, 'b' ) == ? ( 123, “Text2”, FALSE, +1e3, 'b' )
>?

Choose columns …
exemple:
Sort (col1)
Sort (col3,col5,col2)
Lexicographical Compare Rows

1 Row = N Columns

Compare row by Col1,Col2,ColK :

if (a.col1 < a.col1) => -1

else if (a.col1 > a.col1) => +1
else {
if (a.col2 < b.col2) … => ..
.. { … => 0
}}
B-Tree on (Col1,Col2...) = INDEX

Insert ROW = (ACID : Atomic) Insert Data

+ Insert Index1 + Insert Index2 + ….

PK INDEX : col (ID)

INDEX2 : (Col2,Col3,ID)

INDEX3 : (Col3,Col2,Col6)
Select .. where ID = ?

Execution Plan

Step 1: Index Lookup (Unique) by ID

Log(N) logical reads

=> get “rowId” (=address)

Step 2: table lookup by RowId (=O(1))

=> get other columns
Select .. where C2=? and C3=? ..
… with INDEX (C2,C3,otherC...)
Execution Plan

Step 1: Index Range Scan

lookup = Log(N) logical reads
+ scan non unique

=> foreach.. get “rowId” (=address)

Loop Step 2: table lookup by RowId (=O(1))

=> get other columns
Applicable Index(es) ?
for “.. where Col2=? And Col5=?”

PK INDEX : col (ID)

Applicable =
Allow Lexicographical
Top->Down Search
INDEX (Col2,Col6,Col5)

INDEX (Col2,Col5,Col6)

INDEX (Col5,Col2,Col7)

INDEX (Col1,Col2,Col5)
Select C3,C4 where C1=?,C2=?
With INDEX(C1,C2,..C3,C4)

Execution Plan

Step 1: Index Lookup by ID

Log(N) logical reads ...unique/scan

=> read Col3,Col4 value from INDEX

.. NO need table lookup by rowid

Optim = Index Coverage

Query Execution Engine
“Select ..
from ..
where .. Execute Query
col1='val1'
and col2=123“

Result : Tabular Data Format = “ResultSet”

Huge Result .. Server-Side “Cursor”
Begin “Execute” Query
Result = Partial Data
+ Cursor (= handle of server-side Partial Data+ Iterator)

Fetch next page

Fetch next page

Fetch next page

CLOSE CURSOR !!
SoftParse – HardParse …
PrepareQuery + BindVariable
Compute
First Seen ? Execution Plan
“Select .. HARD Parse
from ..
where ..
col1=?0 Put in Cache
and col2=?1“
BindVariable:
set(0, “val1”)
set(1, 1234) Already Seen ?
SOFT Parse
= create Cursor
Explain Execution Plan
Query / Prepared Query Execution
Select ..
Prepare (SQL)
from ..
where ..
col1=?0
and col2=?1 PreparedStatement

BindVariable: ExecuteQuery
set(0, “val1”)
set(1, 1234)

ResultSet (page1)

Next row Fetch next page

get col1, get col2 …
next row
...
… = JDBC API Explained
Spring JPA
QueryDsl

Spring JDBC
import java.sql.*;

DataSource Connection PreparedStatement ResultSet

Sample Jdbc (Test with Rollback)
Refactored (1/2)
Refactored (2/2)
“Template” + Callback
Code to
run with Cx
+ rollback

Common
Template
Framework

.. similar to
Spring
Template
Data Transfer Object for CRUD
CRUD 1 / 4 : SELECT
Jdbc is As Verbose as Easy ...

Is it DRY ?

D don't
R repeat
Y yourself
CRUD 2 / 4 : INSERT
CRUD 3 / 4 : UPDATE (All columns)
UPDATE with Versioning
CRUD 4 / 4 : DELETE
Check my Foreign Keys …

Looks the simplest code to DELETE ? …

It is the hardest to execute safely !!

Because you must clear

all incoming Foreign Keys to this PK first
And What About INSERT...
Chicken & Egg
How to ?

INSERT CHICKEN .. FK to EGG ID (not exist yet)

INSERT EGG .. FK to CHICKEN ID (not exist yet)

Solution 1/
INSERT nextval(..)… CHICKEN … NULL FK) => newChickenID
INSERT nextval(..)… EGG … CHICKEN_ID => newEggID
UPDATE CHICKEN set EGG_ID = newEggID

Solution 2/ … use Database Deferred Constraint Check

Select nextval(..) => newChickenID
Select nextval(..) => newEggId
INSERT … CHICKEN … newChickenID –-- no exist yet .. deferred check!
INSERT … EGG … newEggID
Spring JPA
QueryDsl

Spring JDBC
javax.sql.DataSource
(remark: javax.* not java.* )

DataSource Connection
javax.sql.XADataSource
Connection

XAResource

XADataSource XAConnection
java.sql.Connection (1/3)
Connection Statement

PreparedStatement

CallableStatement

create*
Statement

create*
Data
java.sql.Connection (2/3)
Connection

TransactionManager SavePoint

transaction
java.sql.Connection (3/3)
DatabaseMetaData

Connection

metadata
java.sql.PreparedStatement (1/2)
Statement ResultSetMetaData

PreparedStatement ResultSet

execute*

metadata

Set parameters (cf next)

PreparedStatement (2/2)
ParameterMetaData

PreparedStatement

metadata

clear

set*
java.sql.Statement
Connection Statement ResultSet
java.sql.CallableStatement
Statement

PreparedStatement

CallableStatement
java.sql.ResultSet (1/2)
ResultSetMetaData

ResultSet
java.sql.ResultSet (2/2)
import java.sql.*; (full)
DatabaseMetaData ParameterMetaData ResultSetMetaData

DataSource Connection Statement ResultSet

PreparedStatement

Driver CallableStatement

XAResource

XADataSource XAConnection
Jdbc Database App
DataSource Code Sample

Using
explicit Transaction
+ commit/rollback

Using try-close

Rule for ALL Resources :

If You Open It => You Close It
H2 DataSource HelloWorld
Postgresql Hello
PreparedStatement Sample
Spring JPA
QueryDsl

Spring JDBC
Spring-Jdbc Database App
Springboot config/application.yml
Springboot JDBC… “JUST work”
Springboot Jdbc main()

Start Spring
+ inject DataSource

Pooled DataSource injected

Springboot JUnit

Pooled DataSource
DataSource injected
in injected
Start Spring
+ inject DataSource
SpringBoot … explicit
DataSourceAutoConfiguration
Explicit @Bean
(example for Multi DataSources)
(Reminder) DataSource
Try-Finally Close
Using
explicit Transaction
+ commit/rollback

Using try-close

Rule for ALL Resources :

If You Open It => You Close It
Same using Template Spring-Jdbc
PooledConnection + Transaction
Thread-12
= Thread-Locked : reuse

Conn

XA ..

XA commit/rollback
=> connection commit/rollback
THEN repool
(Reminder) try-finally Connection
+ try-finally Statement
JdbcTemplate(dataSource)
Run Jdbc App
Springboot built-in supports JTA
@Transactional
@Transactional JUST Works
Spring JPA
QueryDsl

Spring JDBC
JPA (with springboot data)
EntityManager (1/2)
EntityManager (2/2)
What is an “Entity” ?

A class with @Entity

And an @Id
Javax.persistence.* Annotations
How many java.persistence.*
Annotations ?
$ cd src/main/java/javax/persistence
$ find . -name \*.java -exec grep -H '@Retention' {} \;

$ find . -name \*.java -exec grep -H '@Retention' {} \; \

| cut -d: -f1 | sed 's|\./\(.*\)\.java|\1|g' | wc -l

90
Sufficient @Annotations to know?

@Entity @Inheritance
Sub-
Classes
Base @Id @DiscriminatorColumn
@GeneratedValue
@SequenceGenerator @Table
@Version Custom @Column

For relationships @ManyToOne

FK (*) → (1) PK
PK (*) ← (*) FK @OneToMany @JoinTable
@JoinColumn
Why putting @Column & @Table ?
@Id with Sequence Generator

SQL:
CREATE SEQUENCE employees_seq INCREMENT BY 10;

Detailed JPA:
@Version ?
whenever overwriting modified value without reading
=>

version=1

(5):
(1) (2):
REDO fetch
Get data version:1
+ update

Wait... (6):
(3):
Save
Mofify + Save (on version 1)
(on version=2)
OK => increment version=2
OK
(4):
Mofify + Save (on version 1?)
=> OptimisticLockException .. version=2 !
@Entity Employee-Department

Database table “EMPLOYEE” Database table “DEPARTMENT”

id (PK), version, first_name, last_name, …. id (PK), version, name,
department_id (FK department.id) department_manager_id (FK employee.id)
manager_id (FK employee.id)
For real with @Column ...
FindById with JPA
CRUD with JPA
Dynamic Query
( java.persistence.CriteriaBuilder )
Dynamic Query
using Bind-Variables
Dynamic Query …
String type checking?

Which one is correct???

Discover it by Exception on PROD !

cb.get(“addr”) // column name in Database

cb.get(“address”) // field name in java
cb.get(“adress”) // with a Typo
cb.get(“city_id”).get(“name”) // after refactoring db schema
Generated *_ class MetaModel
+ Compile Type Check

Generate
Real MetaModel ..
(Real? … see javac processor)
Pom.xml MetaModel plugin
(example for eclipselink)
DynamicCriteria using MetaModel
Search Parameters in Criteria class
(also called “Specification”)
Cascading Setters with “return this”
for Fluent API
springboot data

Spring JPA
QueryDsl

Spring JDBC
Pom.xml QueryDsl plugin
QueryDsl Generated Q* class
similar but richer than *_ class
QueryDsl
++More Fluent than JPA
QueryDsl + Bind-Variables !
(not a clean API for parameters!!)
QueryDsl Predicate (no bind-var!)
springboot data

Spring JPA
QueryDsl

Spring JDBC
Repository
extends JpaRepository
Repository with extra Finders
findBy XXX And YYY
Small + Custom + Almost Complete
(see also next for QueryDsl)

By naming convention .. Equivalent to:

“Select * from EMPLOYEE where email=?”

Extends JpaRepository
built-in CRUD …
findAll, by Page, save, delete...
(no update, use Setters)
Sample Code
using springboot-data Repository
Springboot @JPA configuration

springboot dark magic

not even 1 line .. all optional !!!

Useless equivalent
2 implicit lines

Optional
for debug (see next)
Springboot hibernate… “JUST work”

CRUD …
select * from EMPLOYEE where …
insert into EMPLOYEE (..) values (..)
When/How/Where are my
“create Table ()” ???
Tables are created/updated at startup

Detected H2 Database SQL langage

Also create PK/FK indexes

How??

Which call JPA..

→ Hibernate...
→ JDBC

Call springboot-data
JpaRepository

You code
calls a generated
Proxy..
JPA Dyn Criteria + Spring-Data
= Specification
( != querydsl Predicate !)
Spring-Data + Specification..
Sample spring-data Specification
Better.. QueryDsl + Spring-Data
AngularJS + Springboot
In Jhipster … you have 100% springboot on server-side
… with Code Generator
And also Hipe code on client-side : Html / Css + AngularJS + ..
Java is Hipe
Make Jar nor WAR – NO PHP
NO NodeJS on server

20 years of Java
Just The Beginning

Its HIPE ...because of springboot

& open-source & java community
Conclusion
Conclusion

Only a (not so short) introduction to

Databases < JDBC < JPA < Spring-Data

This document:
http://arnaud-nauwynck.github.io/docs/Intro-Db-Jdbc-
JPA-SpringData.pdf