java in the database - apache software...
TRANSCRIPT
![Page 1: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/1.jpg)
Java in the Database
Rick HillegasApache DerbySeptember 12, 2006
![Page 2: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/2.jpg)
Plug It!
● Dock a free database in your code● Dock your code to the database
![Page 3: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/3.jpg)
Sample Function public static int computeAge ( java.sql.Date date) { long interval = System.currentTimeMillis() - date.getTime(); return (int) (interval / MILLISECONDS_IN_YEAR); }
![Page 4: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/4.jpg)
General Overview
● Derby Overview● User Code in the Database● Demo
![Page 5: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/5.jpg)
Derby Overview
● Lightweight, 0-admin, pure Java database● Follows ANSI-SQL and JDBC standards● Usage: embedded or client/server
![Page 6: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/6.jpg)
EmbeddedJava Virtual Machine
Derby
SpreadsheetApplication
Embedded JDBC Driver
![Page 7: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/7.jpg)
Client/Server
Derby
DRDA
NetworkDriver
NetworkDriver
Network Server
BugTrackingApp Server
BugTrackingApp Server
![Page 8: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/8.jpg)
User Code
● Functions– Compute scalar result– Plug into query
● Procedures– Perform business operations– Plug into trigger or call from application
![Page 9: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/9.jpg)
Function Placementselect computeAge( birthday ), lastName
from Student
where computeAge( birthday ) > 5
order by computeAge( birthday ), lastName
select birthday, count(*)
from Student
group by birthday
having computeAge( birthday ) > 10
![Page 10: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/10.jpg)
Procedure Placement
call ScoreTestTaking( takingID )
create trigger ScoreTestWhenDone
after update of takingDate
on TestTaking
referencing new as testTakingRow
for each row mode db2sql
call ScoreTestTaking( testTakingRow.takingID )
![Page 11: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/11.jpg)
Triggered ProcedureUpdate table (client side)
Fires trigger (server-side)
Calls procedure (server-side)
Updates table again (server-side)Loops through many rows (server-side)
![Page 12: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/12.jpg)
Why?
● Integrity● Performance● Integration
![Page 13: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/13.jpg)
Integrity
● Push business logic close to data● Enforce business rules in one place
![Page 14: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/14.jpg)
Integrity Example
create table QuestionTaking
(
questionID int not null references Question( questionID ),
takingID int not null references TestTaking( takingID ),
actualChoice int not null,
unique( questionID, takingID ),
check ( vetChoice( actualChoice, questionID ) > 0 )
)
![Page 15: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/15.jpg)
Performance
● Filter out noise early on● Reduce query execution time● Reduce network traffic
![Page 16: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/16.jpg)
Query Tree
![Page 17: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/17.jpg)
Filtered Tree
![Page 18: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/18.jpg)
Integration
● Re-use existing freeware libraries● Join with external data
![Page 19: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/19.jpg)
ANSI SQL
● User-written functions● User-written procedures
![Page 20: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/20.jpg)
Why Java?
● Expressive● Good exception handling
![Page 21: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/21.jpg)
Why Java?
● Portable datatypes
![Page 22: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/22.jpg)
Why Java?
● Large body of available freeware● Off-the shelf tool support, including
debuggers
![Page 23: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/23.jpg)
Java Everywhere
● Same code can run in client, middle tier, and server
Client
Application Server
Database
ClientClient
![Page 24: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/24.jpg)
Supporting Databases
● Derby● Postgres● Oracle● DB2● Sybase
![Page 25: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/25.jpg)
Jars in Database
Database:1) Application Jar2) Jakarta math Jar
Client
![Page 26: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/26.jpg)
Derby References
● Developer's Guide– “Loading classes from a database”– “Derby server-side programming”
![Page 27: Java in the Database - Apache Software Foundationdb.apache.org/derby/binaries/JavaInTheDatabase-2006.pdf · Java in the Database Rick Hillegas Apache Derby September 12, 2006. Plug](https://reader030.vdocuments.us/reader030/viewer/2022021722/5c667dbb09d3f2e33b8c2c0a/html5/thumbnails/27.jpg)
Demo Concepts
● Educational testing application● Students, Schools, Tests, Questions, Takings