Java In the Database Rick Hillegas Apache Derby August 28, 2006 - PowerPoint PPT Presentation

Java In the Database Rick Hillegas Apache Derby August 28, 2006

General Overview ● Derby Overview ● User Code in the Database ● SQL Support ● Demo Overview ● Demo SQL

Derby Overview ● Lightweight, 0-admin, pure Java database ● Follows ANSI-SQL and JDBC standards ● Usage: embedded or client/server

User Code in the Database ● Integrity ● Performance ● Integration

User Code in the Database: Integrity ● Push business logic close to data ● Enforce business rules in one place

User Code in the Database: Performance ● Filter out noise early on ● Reduce query execution time ● Reduce network traffic

User Code in the Database: Integration ● Re-use existing freeware libraries ● Join with external data

SQL Support: ANSI SQL Support ● User-written functions ● User-written procedures ● [User-written aggregates] ● [User-written function tables] ● Various languages, including SQL/PSM

SQL Support: SQL/PSM ● Procedural language ● Vendor-specific dialects and extensions

SQL Support: Java vs. SQL/PSM ● More expressive ● Better exception handling ● Portable datatypes ● Large body of available freeware ● Better tool support, including debuggers ● Same code can run in client, middle tier, and server

SQL Support: Some Databases that Run User-written Java Code ● Oracle ● DB2 ● Sybase ● Postgres ● Derby

SQL Support: Derby vs. Other Databases ● User code runs on same VM as SQL byte code ● No process/thread switching

Demo Overview: Concepts ● Educational testing application ● Students, Schools, Tests, Questions, Takings

Demo Overview: Classpath ● You can just put the user-written, server-side code on the server classpath ● But this demo loads a user-written, server- side jar file into the database ● The demo also loads Jakarta commons- math-1.1.jar freeware into the database

Demo Overview: Functions ● weighQuestion ● scoreQuestion ● computeAge ● getMedianTestScore

Demo Overview: Procedures ● ScoreTestTaking

Demo Overview: Triggers ● Compute score when Student finishes taking Test

Demo Overview: Actions ● Create schema objects ● Load user-written code into database ● Populate tables ● Make Students take tests ● Pose queries

Demo SQL: Load Jar File ● From com.scores.data.Database.createSchema() call sqlj.install_jar ( '/sw/demo/scores/jars/scores-server.jar', 'APP.SCORES_SERVER', 0 )

Demo SQL: Wire-up a Classpath ● From com.scores.data.Database.createSchema() call syscs_util.syscs_set_database_property ( 'derby.database.classpath', 'APP.SCORES_SERVER:APP.APACHE_COMMONS_MATH' )

Demo SQL: Create Simple Function ● From com.scores.data.Database.createSchema() create function computeAge ( birthday date) returns int language java parameter style java no sql external name 'com.scores.proc.Functions.computeAge'

Demo SQL: Create Function that Reads Data ● From com.scores.data.Database.createSchema() create function getMedianTestScore ( testID int ) returns double language java parameter style java reads sql data external name 'com.scores.proc.Functions.getMedianTestScore'

Demo SQL: Create Procedure ● From com.scores.data.Database.createSchema() create procedure ScoreTestTaking ( in takingID int ) language java parameter style java modifies sql data external name 'com.scores.proc.Procedures.ScoreTestTaking'

Demo SQL: Create a Procedure-invoking Trigger ● From com.scores.data.Database.createSchema() create trigger ScoreTestWhenDone after update of takingDate on TestTaking referencing new as testTakingRow for each row mode db2sql call ScoreTestTaking( testTakingRow.takingID )

Demo SQL: Firing a User-written Procedure from a Trigger ● From com.scores.data.Data.finishTest() update TestTaking set takingDate = ? where takingID = ?

Demo SQL: Faking a User-written Aggregate ● Invokes freeware from Jakarta commons jarball ● See com.scores.proc.Functions.getMedianTestScore()

Demo SQL: Pushing Work into the Database ● From com.scores.proc.Procedures.ScoreTestTaking() select st.lastName, st.firstName from School sc, Student st, LastTaking lt, QuestionTaking qt, Question q where q.questionName = ? and sc.schoolID = ? and q.questionID = qt.questionID and sc.schoolID = st.schoolID and lt.testID = q.testID and lt.studentID = st.studentID and lt.takingID = qt.takingID and scoreQuestion( q.difficulty, q.numberOfChoices, q.correctChoice, qt.actualChoice ) < weighQuestion( q.difficulty ) order by st.lastName, st.firstName