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

▶
java in the database
SMART_READER_LITE
LIVE PREVIEW

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

Jun 12, 2023 30 likes •307 views

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

slide-1
SLIDE 1

Java In the Database

Rick Hillegas Apache Derby

August 28, 2006

slide-2
SLIDE 2

General Overview

  • Derby Overview
  • User Code in the Database
  • SQL Support
  • Demo Overview
  • Demo SQL
slide-3
SLIDE 3

Derby Overview

  • Lightweight, 0-admin, pure Java database
  • Follows ANSI-SQL and JDBC standards
  • Usage: embedded or client/server
slide-4
SLIDE 4

User Code in the Database

  • Integrity
  • Performance
  • Integration
slide-5
SLIDE 5

User Code in the Database: Integrity

  • Push business logic close to data
  • Enforce business rules in one place
slide-6
SLIDE 6

User Code in the Database: Performance

  • Filter out noise early on
  • Reduce query execution time
  • Reduce network traffic
slide-7
SLIDE 7

User Code in the Database: Integration

  • Re-use existing freeware libraries
  • Join with external data
slide-8
SLIDE 8

SQL Support: ANSI SQL Support

  • User-written functions
  • User-written procedures
  • [User-written aggregates]
  • [User-written function tables]
  • Various languages, including SQL/PSM
slide-9
SLIDE 9

SQL Support: SQL/PSM

  • Procedural language
  • Vendor-specific dialects and extensions
slide-10
SLIDE 10

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

slide-11
SLIDE 11

SQL Support: Some Databases that Run User-written Java Code

  • Oracle
  • DB2
  • Sybase
  • Postgres
  • Derby
slide-12
SLIDE 12

SQL Support: Derby vs. Other Databases

  • User code runs on same VM as SQL byte

code

  • No process/thread switching
slide-13
SLIDE 13

Demo Overview: Concepts

  • Educational testing application
  • Students, Schools, Tests, Questions, Takings
slide-14
SLIDE 14

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

slide-15
SLIDE 15

Demo Overview: Functions

  • weighQuestion
  • scoreQuestion
  • computeAge
  • getMedianTestScore
slide-16
SLIDE 16

Demo Overview: Procedures

  • ScoreTestTaking
slide-17
SLIDE 17

Demo Overview: Triggers

  • Compute score when Student finishes taking

Test

slide-18
SLIDE 18

Demo Overview: Actions

  • Create schema objects
  • Load user-written code into database
  • Populate tables
  • Make Students take tests
  • Pose queries
slide-19
SLIDE 19

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', )

slide-20
SLIDE 20

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

slide-21
SLIDE 21

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'

slide-22
SLIDE 22

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'

slide-23
SLIDE 23

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'

slide-24
SLIDE 24

Demo SQL: Create a Procedure-invoking Trigger

  • From

com.scores.data.Database.createSchema() create trigger ScoreTestWhenDone

after update of takingDate

  • n TestTaking

referencing new as testTakingRow for each row mode db2sql call ScoreTestTaking( testTakingRow.takingID )

slide-25
SLIDE 25

Demo SQL: Firing a User-written Procedure from a Trigger

  • From com.scores.data.Data.finishTest()

update TestTaking set takingDate = ? where takingID = ?

slide-26
SLIDE 26

Demo SQL: Faking a User-written Aggregate

  • Invokes freeware from Jakarta commons

jarball

  • See

com.scores.proc.Functions.getMedianTestScore()

slide-27
SLIDE 27

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 )

  • rder by st.lastName, st.firstName