Transcript Database - AMIS Technology Blog

Java developers:
make the database work for you
Lucas Jellema
AMIS
Java Applications & Database
Cache
“NO SQL”
Plain JDBC
Ibatis,
Spring
JPA
(Hibernate)
JDBC
RDBMS
EJB (CMP)
WS*
Data Grid
Position of Database
• It almost feels like “a necessary evil”
• Database is abstracted away as much as
possible
• It’s the persistent data store
– It does CRUD (Create, Retrieve, Update &
Delete)
• What else could there be to it?
Database (Vendor) Independence
• Applications should not depend on a specific
vendor’s database
– Only use common functionality (available in ‘all’)
– Do not leverage special features of any
database
• Abstract database away through frameworks
– Use generic and/or generated SQL
• Do as little as possible relating to the RDBMS
– … even if the organization happens to have
enterprise editions and specialized db veterans
“We could also do that in the
database”
• in the database? Huh?
RDBMS
≈
Stored Procedures
• Stored Procedures executing procedural
programming units
– PL/SQL, Transact-SQL, SQL/PL,
SPL, pl/perl, pl/php, …
– Java
Stored
Procedures
After the polarization (peak 2002)
pragmatism struck…
• EJB 2.x => JPA and EJB 3.0 (JEE 5)
• Consensus
– Leverage enterprise database for what it is
good at (you pay for it, may as well use it)
– Most applications will only be used on one
vendor’s database ever
– Even portable applications should still
leverage database strengths
• Through generic APIs with database specific
implementations
Project: VP - Rich UI,
Complex Data Manipulation
JSF (Rich
Faces)
SEAM
JPA (Hibernate)
Oracle
RDBMS
Team & Design that combines
strengths of all technologies…
-
Ease and Elegance of Implementation
Functionality (in an affordable way)
Productivity
Performance
JSF (Rich Faces)
SEAM
JPA (Hibernate)
Oracle RDBMS
Database Strengths
• Integrity
• Fine grained (data) security and auditing
• Data Retrieval
– joining tables together, leveraging indexes
– hierarchical, network-like traversals
– advanced analytics, historical queries, mining
• Aggregation and Sorting
• Complex & Massive Data Manipulation
Zooming in – using Oracle
•
•
•
•
Have to pick one
Largest market-share
Fairly representative (e.g. ANSI SQL)
The one I personally know best
Oracle RDBMS
Sample Data Model:
Departments & Employees
Primary, Unique and Foreign
Key Constraints
• Definition in Database is Declarative
• Implementation is optimized
• Imagine the programming and performance
cost of a middle tier based implementation
RDBMS not always exclusively
accessed through one Java API
SOA, ESB,
WebServices
Batch Bulk
Processes
Standard
Applications
Database
Data Replication
&
Synchronization
Legacy
Applications
Other data constraints
• Not Null
• Data Type:
– string, numeric, date (time), xml
– maximum length, integer/floating point
• Data Rules
– COMM < 0.3 * SAL
– COMM IS NULL or JOB = ‘SALESMAN’
– MGR != EMPNO
• Implemented using Column Definitions
and Check Constraints
Database Triggers – decorating
Data Manipulation
• Triggers execute before or after Insert,
Update or Delete of database records
Before Insert
trigger: sal=…
Employees
Purpose of triggers
• Set default values on new records
– if :new.job=‘SALESMAN’ then :new.comm = 1000
• Calculate & Derive values upon insert,
update or delete
• Notify third parties of data manipulation
• Perform complex validation on the data
changes applied by the transaction
– Per Department: Max Salary < 1.8 * Average
– Per Manager: #subordinates < 15
JPA refreshing entities after
triggers have applied new values
@Entity
@Table(name = "EMP")
public class Employee
…
@Column(name=“sal”)
private Double salary
Before Insert
trigger: sal=…
Employees
@ReturnInsert //EclLnk
@Generated (value=
GenerationTime.INSERT)
// Hibernate
The far reaches of SQL vs
the limit(itation)s of JPQL
• Many Java ORM-frameworks – including JPA
via Hibernate or EclipseLink – generate SQL
– Usually “database independent” SQL
– By and large only leveraging the common
functionality across databases
• As a consequence:
– Many Java applications do not exploit the
wealth of (the SQL of) their databases
– And use what they do leverage in a what is
possibly a suboptimal way
Aggregation & Rollup
• Data for reporting purposes can be prepared
by database queries
– Including aggregations
(max/min/avg/count/sum)
– and Sub Totals
– and Grand Total
– and String Aggregation
Sub and Grand
totals with Rollup
• Rollup instructs database
to aggregate at every level
starting from the right
– deptno, job
– deptno
– (grand total)
• Also see:
– Cube
– Grouping
Sets
Analytical Functions –
spreadsheet-style row processing
• Analytical Functions allow SQL queries to
perform inter-row comparison & aggregation
• For example: in a single query, for each
employee
– show salary rank in department and job
– show salary difference with colleague next
higher in rank (on the list per department)
– show average salary in the department
– show csv list of colleagues in department
Analytical Functions - example
Flashback Query
select emp.*
,
dept.dname
from
emp AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '1' DAY)
,
dept
where emp.deptno = dept.deptno
Flashback Versions
• Retrieve all states each record has been in
– Every transaction that touched a row left a version
of it
– Pseudocolumns: xid, operation, starttime, endtime
Employee Version-history with
Analytical and Flashback
Trees
Trees
Retrieving Hierarchical data sets
with single SQL statements
• Database has optimized algorithms
–
–
–
–
Starting at any node in the tree or network
Drilling down to the specified number of levels
Order siblings within parent
Indicate leaf and parent nodes; detect cycles
EMPID
--------------7839
7698
7499
7900
7654
7844
7521
7782
7934
ENAME
MGR
DEPTNO
LEVEL
---------- ---------- ---------- ---------KING
10
1
BLAKE
7839
30
2
ALLEN
7698
30
3
JAMES
7698
30
3
MARTIN
7698
30
3
TURNER
7698
30
3
WARD
7698
30
3
CLARK
7839
10
2
MILLER
7782
10
3
Encapsulate Database specific
SQL in a View API
• Views – for encapsulation of data model,
multi-table join, (advanced) SQL hiding,
authorization rules
– Note: a view looks like a table to the client
View
The read-only cursor API
• A Cursor is a reference to a query result set
• Database can open a cursor
JDBC
for a SQL query
ResultSet
while rs.next {
• And return it to the application
…}
to fetch the rows from
• Cursor == JDBC
Stored
ResultSet
Procedure
Departments
• A cursor can be
Employees
nested: contain
details …
Cursor for Master-Detail resultset
Stored
Procedure
Using Complex Views for Hiding
Legacy Data Models
Providing a ‘business object’ API
• DML API: a View – aided by an Instead Of trigger
USERS
• Insert of one new row in
USERS_VW (e.g. a JPA
persist operation) can actually
be four new records
– USER, PERSON,
EMAIL_TYPE
EMAIL_ADDRESS
USERS
*
*
PERSONS
EMAIL_TYPE
*
*
EMAIL_
ADDRESSES
The Hollywood Principle:
Query ResultSet Change Notification
Cache
Cache Refresh triggered by DB
Cache
SQL query
PL/SQL
Reaching out from the database
Database
Email conversations
Database receiving and sending
emails – from people or applications
RESTful resource navigation
RESTful architecture
RESTful PL/SQL API
exposed through dbms_epg
Database informing and
leveraging the middle tier
JEE Application Server
Web
Application
Web
Service
?
Enterprise
Service Bus
Other Database Features worth
investigating
•
•
•
•
•
•
•
•
•
•
•
•
•
Virtual Private Database & Fine Grained Authorization
XMLType, XMLDB & FTP/HTTP/WEBDAV server
Object Types and Collections
Data type Interval & Time Zone support
Fine Grained Auditing
System Triggers, for example “after logon”
(Global) Application Context
Autonomous Transaction
Advanced Queuing (& JMS interaction)
Creating advanced job execution schedules
Edition Based Redefinition (versioning of database objects)
Statistics and Data Mining
Virtual Columns
Summary & Conclusions
• Databases can do much more than
• Java applications can benefit!
• Strike the right balance:
– Leverage database for
what it can do best
• Make Java and Database work together in a
smooth way
Use the right tool for the job
• Render HTML
• Enforce Application
Logic
• Handle User
Interaction
• Create graphics
• Interact with Internet
• (bulk) copy of data
• Guard
Uniqueness
• (large) Sort or
Aggregation
• (complex) SQL
• Enforce data rules
Summary & Conclusions
•
•
•
•
•
Databases can do much more than
Java applications can benefit!
Strike the right balance:
Make Java and Database work together
Cater for ‘multiple database
consumers’
• Acquire expertise on your team
• Arrive at architectural design choices and
best development practices
Best Practices & Principles
• Prevent data to travel to the middle tier
unless it has to
– Performance (network and object
instantiation) & Resource Usage (memory)
• When data is on the middle tier: ensure it has
the required freshness
• Encapsulate database (specific) functionality
– NO SQL (in the middle tier)
– Decoupling and database (vendor) &
framework independence
Best Practices & Principles
• Use Views and Stored Procedures to create
APIs that encapsulate database functionality
– Note: the database brings constraints and
triggers to the party – weaved in like Aspects
– Cursors mapping to ResultSets allow retrieval
of nested data structures through simple calls
• Leverage the database for what it’s worth
• Include ‘database developer’ in your team
• Never be dogmatic
Want to know more?
• Have the sources for the demos
• Have this presentation presented & discussed at
your organization
• Learn about Java and the Database (Oracle)
• Inject (Oracle) Database expertise – in the context of
Java development - into your team
• Receive a paper with more details on ‘making the
database work for you & for
’
• Send me an email: [email protected]
• Visit our blog: http://technology.amis.nl/blog
Master Class
‘Java Developers make the
database work for you’
• Friday 17 December 2010
(AMIS, Nieuwegein):
– One day master class:
‘Java Developer make the database
work for you’
• For information and registration:
– [email protected]