A Database Centric Approach to J2EE Application Development

1
A Database Centric Approach to J2EE Application
Development

• Where should code go Java or PL/SQL?

Toon Koppelaars Sr. IT Architect Centraal
Boekhuis Netherlands
2
Agenda

• Window-on-Data (WoD) Application
• Code Execution Environments
• WoD Application Code Classes
• Mapping Code Classes to Execution Environments
• A Few Examples
• Benefits of this Approach

3
Window-on-Data Application

• User enters search-criteria
• And hits a search button
• Server processes request
• Server returns data
• User interface displays data for user

4
Window-on-Data Application

• User navigates through displayed data
• And changes displayed values
• Hits a save button
• Server processes request
• And reports success (or failure)

5
Window-on-Data Application

• Core activities in WoD application
• Querying data
• Transacting data
• The underlying (relational) database design is
  the foundation of a WoD application
• The majority of business applications are WoD
  applications

6
Code Execution Environments (J2EE)
SQL PL/SQL in DBMS
Java in App Container
EJB
EJB
EJB
JavaScript in Browser
Application server
Swing
Java in JVM in OS
J
Servlet
Java in Web Container
D
Servlet
Applet
B
Java in JVM in Browser
JSP
HTML
JSP
C
DBMS
Browser
Web server
Middle Tier
Data Tier
Client Tier
7
WoD Application Code Classes
Three code classes
User Interface
Paint UI and respond to UI events
Business Logic (BL)
Retrieval and manipulation of data
Foundation database design including all data
integrity constraints
Data Logic (DL)
8
User Interface Code

• Creates User Interface (look)
• Responds to User Interface events
• Modifies the User Interface (feel)
• Will hold calls to business logic code
• Dependent upon result of call, User Interface
  will be modified
• Should not hold embedded DL or BL code
• Only calls to these types of code

9
Data Logic Code

• The database design has data integrity
  constraints
• This is not just PK, FK and CHECK
• At most one president
• A department that employs president or manager
  should also employ at least one administrator
• DL-code all code that exists for maintaining the
  integrity of the data held by the database design
• DBMS offers declarative constructs for some
• Most need development of custom code
• A lot of logic sits here not in BL-layer!

10
Business Logic Code

• BL-code whats left, the rest
• This is
• Code that composes executes queries
• retrieves and returns the data to caller
• Code that composes executes transactions
• communicates success/failure to caller
• By the way
• There is nothing OO-ish about BL code
• This is pure procedural code

11
(No Transcript)
12
Mapping Code Classes ? Execution Environments
13
DBMS is a S to M a DB(manage data store
query transact)
The Robust Database Data Integrity
Business Logic Transact Query
UI Logic Paint Respond
14
The Fat Database
De Robuuste Database Data Integriteit
Business Logic Transacties Queries
UI Logic Paint Respond
15
Mapping Code Classes ? Execution Environments
16
Design by Contract
De Robuuste Database Data Integriteit
Business Logic Transacties Queries
UI Logic Paint Respond
17
The Fat Database Queries

• Mr. Database, heres my current context
• Page x, search-criteria y, session s
• Give me all rows for this page
• Query view, use ref-cursor, use procedure out
  parameter (XML)
• Database ensures correct results
• By using the context given

18
The Fat Database Transactions

• Mr. Database heres my UI-row
• Insert
• Update
• Delete
• Tell me if it succeeds
• Database ensures correct table-row update(s)
• Reports error in case of some B/D logic failure
• Interface either SQL (viewinstead-of trigger) or
  procedure call

19
The Fat Database
SQL-based contract
Query
DML
Middle Tier
Tailor Made User Interface Views
Table Functions
Instead-Of Triggers
Data Tier
Business Logic
DML's
Queries
Database Design Data Logic
20
The Fat Database
Procedural contract
Procedure Calls
Middle Tier
Tailor Made User Interface Procedures
Data Tier
Business Logic
DML's
Queries
Database Design Data Logic
21
Demo Time

• Database Design
• With data integrity constraints
• Implementation with homegrown RuleGen framework
• Three code examples
• Transaction two UI service object approaches
• SQL based
• Procedural
• Report

22
Example EMP-DEPT
Employee works for Department Department is
managed by Employee
Empno Ename Job Born Hired Msal deptno
Deptno Dname Loc mgr
EMP
DEPT

• A department that employs the president or a
  manager should also employ an administrator
• You cannot manage more than two departments
• Department manager must work for a department
  he/she manages
• Department manager cannot be administrator or
  president
• At most one president allowed

DL
23
Demo Data Logic
Show the active DBMS
24
Example Transaction

• Insert a new department including the (new)
  department manager
• Also insert an ADMINISTRATOR, if the department
  manager is a MANAGER
• UI service object accepts values for
• Dept(deptno,dname,loc,mgr)
• Emp(empno,ename,job,born,hired,msal,deptno)
• Emp(empno,ename,job,born,hired,msal,deptno)

New DEPT
DEPTs manager
Optional ADMIN
25
UI Service Object View
Insert through this view
API calls
UI Tier
View V_NEW_DEPT (63)
Instead of insert trigger X_new_dept package
BL
DBMS Tier
DEPT EMP tables
DL
Constraints declarative RuleGen
26
UI Service Object View
Show the code
27
UI Service Object Stored Procedure
Call this stored procedure
API calls
UI Tier
Procedure P_INSERT_NEW_DEPT_XML (1)
BL
X_new_dept package
DBMS Tier
DEPT EMP tables
DL
Rules declarative RuleGen
28
UI Service Object Stored Procedure
Show the code
ltnew_deptgt
ltdnamegtAccountinglt/dnamegt
ltlocgtNYlt/locgt
ltename_mgrgtBrizzilt/ename_mgrgt
ltjob_mgrgtTRAINERlt/job_mgrgt
ltborn_mgrgt01-JAN-1965lt/born_mgrgt
ltmsal_mgrgt9600lt/msal_mgrgt
ltename_admin/gt
ltborn_admin/gt
ltmsal_admin/gt lt/new_deptgt
29
Example Query

• Perform ad-hoc query on table EMP or DEPT
• Syntax
• Select from ltsearch criteriongt
• emp
• dept
• Emp where jobmanager

30
UI Service Object Stored Procedure View
Call procedure with search criterium then select
from view
API calls
UI Tier
Procedure P_ADHOC_QUERY_CTX View V_ADHOC_QUERY
BL
Context Pipelined table function
DBMS Tier
DL
DEPT or EMP table
31
Benefits of this Approach

• Great performance!
• WoD logic implemented right next to the data
  itself
• If done right (efficient SQL, bind variables,
  ...) ? unparalleled scalability
• Maintainable, monitorable, tunable
• Oracle DBMS is extremely well instrumented
• Immune for du-jour MVC frameworks
• Majority of code sits in stable DBMS

32
Drawbacks of this Approach

• Too little declarative support for data integrity
  constraints
• Often DL-code is merged within BL-code
• Building a framework for this (in the DBMS tier)
  is very complex
• Huge opportunity for DBMS vendors
• You need PL/SQL skills
• You need relational database design skills
• (this might be an advantage)

33
In Conclusion

• In WoD applications users query and transact data
• A DBMS is designed to query and transact data
• Have evolved for over 20 years now
• Have become extremely efficient in this task
• On large data sets
• For many users simultaneously
• PL/SQL is designed to composeexecute queries and
  transactions ? business logic
• Use it!
• The database-centric mapping has really worked
  well for us

34
Questions Answers
A Database Centric Approach to J2EE Application
Development
Toon Koppelaars t.koppelaars_at_centraal.boekhuis.nl
http//web.inter.nl.net/users/T.Koppelaars
Thank you for your attention
35
(No Transcript)