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
