Download
java developers make the database work for you n.
Skip this Video
Loading SlideShow in 5 Seconds..
Java developers: make the database work for you PowerPoint Presentation
Download Presentation
Java developers: make the database work for you

Java developers: make the database work for you

135 Vues Download Presentation
Télécharger la présentation

Java developers: make the database work for you

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