Java developers make the database work for you

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, Web. Services 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 te pda rt, u inse lete , de 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 p is ers t @Entity @Table(name = "EMP") public class Employee … @Column(name=“sal”) private Double salary Before Insert trigger: sal=… Employees @Return. Insert //Ecl. Lnk @Generated (value= Generation. Time. INSERT) // Hibernate

The far reaches of SQL vs the limit(itation)s of JPQL • Many Java ORM-frameworks – including JPA via Hibernate or Eclipse. Link – 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 = 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 Result. Set while rs. next { • And return it to the application …} to fetch the rows from or s r • Cursor == JDBC cu Stored Result. Set 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 Ins DM tead O L tri f gge r EMAIL_TYPE * * EMAIL_ ADDRESSES

The Hollywood Principle: Query Result. Set Change Notification Cache

Cache Refresh triggered by DB Da R tab egis No ase. C ter tifi ca han tio ge n S BM ener D R e l List ils c a a r O Jav deta s e k nt o e v v in ith e w 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

htt http p htt RESTful PL/SQL API exposed through dbms_epg p RESTful architecture

Database informing and leveraging the middle tier JEE Application Server Web Application Web Service ? ing the s u s l l ca HTTP TP package T UTL_H 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 Result. Sets 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 ’ Q&A • Send me an email: lucas. jellema@amis. nl • 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: – lucas. jellema@amis. nl