1 / 50

Java developers: make the database work for you

Java developers: make the database work for you. Lucas Jellema AMIS. Java Applications & Database. Servlet , Applet, Swing Client, EJB, WebService , JSP, JSF, …. Cache. “NO SQL”. Plain JDBC. Ibatis , Spring. JPA (Hibernate). EJB (CMP). WS*. Data Grid. JDBC. RDBMS.

juliemoore
Download Presentation

Java developers: make the database work for you

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Java developers: make the database work for you Lucas Jellema AMIS

  2. Java Applications & Database Servlet, Applet, Swing Client, EJB, WebService, JSP, JSF, … Cache “NO SQL” Plain JDBC Ibatis, Spring JPA (Hibernate) EJB (CMP) WS* Data Grid JDBC RDBMS

  3. 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?

  4. 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

  5. “We could also do that in the database” • in the database? Huh? RDBMS ≈

  6. Stored Procedures • Stored Procedures executing procedural programming units • PL/SQL, Transact-SQL, SQL/PL, SPL, pl/perl, pl/php, … • Java Stored Procedures SQL

  7. 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

  8. Project: VP - Rich UI, Complex Data Manipulation JSF (Rich Faces) SEAM JPA (Hibernate) Oracle RDBMS

  9. Team & Design that combines strengths of all technologies… JSF (Rich Faces) • Ease and Elegance of Implementation • Functionality (in an affordable way) • Productivity • Performance SEAM JPA (Hibernate) Oracle RDBMS

  10. 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

  11. 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

  12. Sample Data Model:Departments & Employees

  13. Primary, Unique and ForeignKey Constraints • Definition in Database is Declarative • Implementation is optimized • Imagine the programming and performance cost of a middle tier based implementation

  14. RDBMS not always exclusively accessed through one Java API SOA, ESB, WebServices Database Batch Bulk Processes Standard Applications LegacyApplications Data Replication & Synchronization

  15. 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 Definitionsand Check Constraints

  16. Database Triggers – decorating Data Manipulation • Triggers execute before or after Insert, Update or Delete of database records insert, update, delete Before Insert trigger: sal=… Employees

  17. 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

  18. JPA refreshing entities after triggers have applied new values @Entity @Table(name = "EMP") public class Employee…@Column(name=“sal”) private Double salary @ReturnInsert //EclLnk @Generated (value=GenerationTime.INSERT) // Hibernate persist Before Insert trigger: sal=… Employees

  19. The far reaches of SQL vsthe 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

  20. 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

  21. Sub and Grandtotals with Rollup • Rollup instructs databaseto aggregate at every levelstarting from the right • deptno, job • deptno • (grand total) • Also see: • Cube • GroupingSets

  22. 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

  23. Analytical Functions - example

  24. Flashback Query select emp.*, dept.dname from emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) , deptwhere emp.deptno = dept.deptno

  25. 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

  26. Employee Version-history with Analytical and Flashback

  27. Trees

  28. Trees

  29. 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 ENAME MGR DEPTNO LEVEL --------------- ---------- ---------- ---------- ---------- 7839 KING 10 1 7698 BLAKE 7839 30 2 7499 ALLEN 7698 30 3 7900 JAMES 7698 30 3 7654 MARTIN 7698 30 3 7844 TURNER 7698 30 3 7521 WARD 7698 30 3 7782 CLARK 7839 10 2 7934 MILLER 7782 10 3

  30. 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

  31. The read-only cursor API • A Cursor is a reference to a query result set • Database can open a cursor for a SQL query • And return it to the application to fetch the rows from • Cursor == JDBCResultSet • A cursor can be nested: containdetails … JDBC ResultSet while rs.next { … } cursor Stored Procedure Departments SQL Employees

  32. Cursor for Master-Detail resultset Stored Procedure

  33. Using Complex Views for Hiding Legacy Data Models

  34. Providing a ‘business object’ API USERS • DML API: a View – aided by an Instead Of trigger • Insert of one new row inUSERS_VW (e.g. a JPApersist operation) can actually be four new records • USER, PERSON, EMAIL_TYPEEMAIL_ADDRESS USERS EMAIL_TYPE Instead Of DML trigger PERSONS EMAIL_ADDRESSES * * * *

  35. The Hollywood Principle: Query ResultSet Change Notification Cache

  36. Cache Refresh triggered by DB Oracle RDBMS invokes Java Listener with event details Cache Register DatabaseChangeNotification SQL query PL/SQL

  37. Reaching out from the database Database

  38. Email conversations

  39. Database receiving and sending emails – from people or applications

  40. RESTful resource navigation

  41. RESTful architecture http http http RESTful PL/SQL APIexposed through dbms_epg

  42. Database informing and leveraging the middle tier JEE Application Server Enterprise Service Bus Web Application Web Service ? HTTP calls using the UTL_HTTP package

  43. 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

  44. Summary & Conclusions • Databases can do much more than • Java applications can benefit! • Strike the right balance: • Leverage database forwhat it can do best • Make Java and Database work together in a smooth way

  45. Use the right tool for the job • Render HTML • Enforce ApplicationLogic • Handle User Interaction • Create graphics • Interact with Internet • (bulk) copy of data • Guard Uniqueness • (large) Sort or Aggregation • (complex) SQL • Enforce data rules

  46. 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

  47. 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

  48. 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

  49. Want to know more? Q&A • 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: lucas.jellema@amis.nl • Visit our blog: http://technology.amis.nl/blog

  50. 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

More Related