Transcript Data Access Layers

Data Access Layers
Technion – Institute of Technology
236700
1
Overview
We’ll focus on
SQL-DAL today
2
Evolution
3
Relational DB
• SQL stands for Structured Query Language
• An external Domain Specific Language (DSL) for data retrieval
• First published at 1974 by Donald D. Chamberlin &
Raymond F. Boyce (IBM)
• Data is organized in Tables
‒ A table column is associated to a name and a type
‒ A table row holds the actual data
• Table may have relations between them:
Students
Courses
ID
Name
Age
Faculty
111111
Don
Quixote
31
CS
222222
Sancho
Panza
28
EE
ID
236700
236363
Name
Registration
Faculty
Soft.
Design
CS
DBMS
CS
SID
CID
111111
236700
111111
236363
222222
236700
4
SQL (basic) Queries
• The 4 basic queries are:




Select – Retrieves row(s) from a table(s)
Insert – Inserts data into a table(s)
Update – Updates existing row(s) in a table(s)
Delete – Deletes row(s) from a table
• Different SQL dialects
‒ Slight differences between implementations
‒ MySQL (Oracle), SQL Server (Microsoft),
DB2 (IBM), Derby (Apache), etc.
5
Java Database Connectivity (JDBC)
• An SPI developed by Sun Microsystems (rip),
as part of JDK 1.1 (1997)
• Intends to supply a unified DB access
‒ Strives to make the underlying SQL-DB platform
transparent (Implementation-wise)
‒ Not always possible, due to the syntax dialects
6
Java Database Connectivity (JDBC)
• Was a good start but:
‒ Developers basically needs to maintain two data
models: DB Tables + Code objects
‒ A lot of “translation” code is needed:
• Objects to SQL
• Queries results to Objects
‒ Abstraction level is very shallow
• Complicated error-path
• Queries may vary between DB implementations
‒ API induces complicated code !
• Hard to keep it DRY
7
JDBC - Demo
• Example
‒ Students / Courses
‒ Registration of students to courses
‒ Take aways
‒ Use try-with-resources block (Using in C#)
‒ Note resources are actually final
‒ ResultSet is a resource! (As well as Connection and Statement)
‒ Still lots of boilerplate / use patterns
‒ Preparing statements
‒ Deciphering the results
‒ Handling exceptions
‒ Be careful of different SQL dialects
‒ Join is hard
8
Things to lookout for
• Inheritance
‒ How do we maintain inheritance in Databases?
• Objects relations
‒ What happens if an object holds a list of objects?
Objects to Relational translation is hard
9
Things to lookout for (cont.)
• Entities Equality
‒ How can we say if a row’s entity is the same as a certain
Java object?
‒ Pointers cannot be used as entities identifiers in the DB!
• Example: Suppose multiple people with the same name exists.
how can we tell them apart?
• ID is not always available / makes sense
• In a purely Java world this is easy: we have pointer to their objects
• When DB are involved this is simply not true
p
u
b
l
i
c
b
o
o
l
e
a
n
i
s
O
l
d
e
s
t
W
i
s
e
s
t
(
P
e
o
p
l
e
p
e
o
p
l
e
)
{
r
e
t
u
r
n
p
e
o
p
l
e
.
g
e
t
O
l
d
e
s
t
(
)
=
=
p
e
o
p
l
e
.
g
e
t
W
i
s
e
s
t
(
)
;
/
/
O
K
}
p
u
b
l
i
c
b
o
o
l
e
a
n
i
s
O
l
d
e
s
t
W
i
s
e
s
t
(
D
B
d
b
)
{
r
e
t
u
r
n
d
b
.
g
e
t
O
l
d
e
s
t
(
)
=
=
d
b
.
g
e
t
W
i
s
e
s
t
(
)
;
/
/
B
a
d
}
10
Spring framework – JDBC
template
• A set of classes built on-top of JDBC to provide:
‒ Resource creation / release
‒ Better iteration over ResultSet
‒ Parameter resolution from objects
‒ Mapping rows to objects
11
Spring framework – JDBC
template
• A big step towards the right direction
• API is much better
‒ Less error handling
‒ Short & Simple code
‒ Framework does most of the “heavy lifting”:
• Parameters from Java Beans
• Java objects resolution using custom mappers
12
Spring - Demo
13
Spring framework – JDBC
template
• This is an example for an API evolution
‒ First version introduced JdbcTemplate
‒ Later, classes like
NamedParameterJdbcTemplate wrapped
JdbcTemplate but delegated most of the work to it
• But still:
‒ Queries may vary between different DB impl.
‒ Manual objects resolution
‒ Entity equality is still a concern
14
Object Relational Mapping
• ORM Frameworks will:
‒ Manage the DB structure
‒ Map Classes to Tables
‒ Map Objects to Rows
‒ Maintain at most one object in memory per DB row
• We will:
‒ Not worry about Inheritance
‒ Not worry about Entity Equality
‒ Write less code
‒ Write simpler code
15
Hibernate
• Provides a declarative way of mapping objects to their
corresponding data
• Defines an external DSL named HQL, which is similar to
SQL, only it deals with objects
‒ Meaning, instead of querying tables for rows, we now
query Classes for instances
‒ For example, getting all students born after 1985:
F
R
O
M
S
t
u
d
e
n
t
W
H
E
R
E
d
o
b
>
3
1
/
1
2
/
1
9
8
5
Student is a Java class
dob is a class member
of type Date
16
Hibernate
• Simple configuration via Annotations
• Specification via XML is also supported
• Java Beans defines tables
‒ Can also explicitly define (table/column names)
• Subclasses objects extends the tables
• Complex objects relations
‒ Objects which points to other objects
‒ Automatically maintains the relational tables
• No need to worry about SQL dialects
• As powerful as JDBC
17
Hibernate – Entity Equality
• Mapped objects now need to declare an ID member
• Hibernate resolves row-object identity
@Entity
@Table( name = “Books" )
public class Book {
@Id private int id;
private String name;
private int getId() {…}
private void setId(int id) {…}
private String getName() {…}
private void setName(String name) {…}
…
}
18
Hibernate – API
• Common actions are part of the API:
‒ Adding an object:
s
e
s
s
i
o
n
.
s
a
v
e
(
b
o
o
k
)
;
‒ Getting result objects as a list:
L
i
s
t
<
B
o
o
k
>
b
o
o
k
s
=
s
e
s
s
i
o
n
.
c
r
e
a
t
e
Q
u
e
r
y
(
"
f
r
o
m
B
o
o
k
"
)
.
l
i
s
t
(
)
;
• Fluent API
L
i
s
t
<
B
o
o
k
>
h
e
m
i
n
g
w
a
y
B
o
o
k
s
=
s
e
s
s
i
o
n
.
c
r
e
a
t
e
Q
u
e
r
y
(
"
f
r
o
m
B
o
o
k
w
h
e
r
e
a
u
t
h
o
r
=
?
"
)
.
s
e
t
S
t
r
i
n
g
(
0
,
“
E
.
H
e
m
i
n
g
w
a
y
"
)
.
l
i
s
t
(
)
;
19
Hibernate - Demo
20