JAVA_and_PLSQL ( 33 )

Examples WRAP UP / Q&A

copyright 2004
all rights reserved www.rhinosystemsinc.com
3
Getting Started with Java

Must install a JDK & IDE
– JDK (Java Developers Kit) from Sun http://java.sun.com/j2se/1.4/index.html – IDE (Integrated Developers Environment)
1
INTRODUCTION
Joel A. Thompson 15 years of industry experience Consultant: Architecture, Software Engineering and Project Manager Java and Oracle expert
4
Getting Started with Oracle

Server Install Oracle (Steps)
– Download/Install from technet.oracle.com – Create the database, use Oracle's Database Configuration Assistant. – Make sure network listeners are started – typically your instance will be available on port 1525 – use Oracle's Net Configuration Assistant, to configure a "Listener".
copyright 2004
all rights reserved www.rhinosystemsinc.com
12
Issues Regarding Example 2
Good – Created the preparedStatement once at initialization time & then binding values in function. Loader routine purpose? – still worse performance than a SQL batch processing. Does not check to see if account exists already. GOOD – uses sequence number generation from oracle
copyright 2004
all rights reserved www.rhinosystemsinc.com
10
Issues Regarding Example 1
Selecting from entire table is generally a bad idea. (unless small "lookup table"). One call for the Query, and 2nd call to create the account.

FUNCTION
– Client Side calls a PL/SQL function in Oracle. PL/SQL is already compiled and loaded in Oracle. – Function will return a value

PROCEDURE
– Client calls a PL/SQL procedure – Similar to function, but does not return values.
copyright 2004
all rights reserved www.rhinosystemsinc.com
9
ADHOC: Java Client Example 1

SQL embedded in call from Java Client
// EXAMPLE OF SIMPLE SELECT STATEMENT Statement stmt=connection.createStatement(); // QUERY THE ENTIRE TABLE String sSQL="select ACCOUNT_ID,SSNO,FNAME,LNAME,PHONE from PERSON order by account_id"; // GET THE RESULT SET AND PROCESS IT. ResultSet rs=stmt.executeQuery(sSQL); while(rs.next()) { nPERSON_ID=rs.getInt(1); sSSNO=rs.getString(2); …/// check to see if matches with new account you'd like to add. } // IF ACCOUNT DOESN'T ALREADY EXIST, THEN ADD IT HERE: stmt.executeUpdate("insert into PERSON(ACCOUNT_ID,SSNO,FNAME,LNAME) values(account_id_seq.nextval," + "'" + sSSNO + "'," + "'" + sFNAME + "'," +"'" + sLNAME + "'");
all rights reserved www.rhinosystemsinc.com 8
copyright 2004
Ways to call PL/SQL

ADHOC
– Send the PL/SQL block of code from the client java program to the server for processing.
6
What is PL/SQL?

A programming language
– If/then/else – Loops – Function calls – Transactional – Procedural
Feature rich Performance Gain Oracle only

copyright 2004
all rights reserved www.rhinosystemsinc.com
11
ADHOC: Java Client Example 2

Prepared Statement/Bind from Java Client
public void initializeOnce() { // THE SQL INSERT TO BE MADE. String sQueryNotify="insert into PERSON (PERSON_ID,SSNO,FNAME,LNAME)" + " values (person_id_seq.nextval,?,?,?)"; // CREATE A PREPARED STATEMENT, BASED ON THE ABOVE SQL m_psInsertPerson=conn.prepareStatement(sQueryNotify); } public void insertNewPerson(String SSNO,String FNAME,String LNAME) { // BIND THE PARAMETER - REUSE THE ALREADY CREATED PREPARED STATEMENT m_psInsertPerson.setString(1, SSNO); m_psInsertPerson.setString(2, FNAME); m_psInsertPerson.setString(3, LNAME); // EXECUTE THE INSERT m_psInsertPerson.executeUpdate(); … return; }

copyright 2004 all rights reserved www.rhinosystemsinc.com 7
Some Good Reasons to use PL/SQL



Basically you'd like to do some logic on the server side in one call to the database Transaction support within the PL/SQL Temporary table queries/inserts to filter data further before returning result set Take advantage of server side resources while processing you PL/SQL – send message out through Oracle's Advanced Queuing. Distributed Computing – update another database… You want to abstract your database layer into Oracle, such that you can change the table's or columns around without affecting the client program.
copyright 2004
all rights re百度文库erved www.rhinosystemsinc.com
5
Client Server Java/PLSQL Architecture
copyright 2004
all rights reserved www.rhinosystemsinc.com

copyright 2004
all rights reserved www.rhinosystemsinc.com
2
OVERVIEW
Basics of PLSQL Optimizing RDBMS Calls from JAVA

Return Values & Cursors Logic processing on Server, ARRAY inserts And more …
JAVA & PL/SQL
For Oracle RDBMS (8i & 9i)
By Joel A. Thompson (joel@rhinosystemsinc.com) www.rhinosystemsinc.com 05/2004
copyright 2004
all rights reserved www.rhinosystemsinc.com

copyright 2004 all rights reserved www.rhinosystemsinc.com 13
ADHOC: Java Client Example 3

Client Requirement
– Copy the $ORACLE_HOME/jdbc/lib/classes12.jar to your client side's CLASSPATH. – Connect string example (thin driver) – requires no client side libraries: jdbc:oracle:thin:joel/welcome@localhost:1521:UCDBA
• Oracle’s JDeveloper (technet.oracle.com) • IntelliJ: (www.intellij.net) • Textpad: http://www.textpad.com
copyright 2004
all rights reserved www.rhinosystemsinc.com