Java pl sql
Download
1 / 33

java pl - PowerPoint PPT Presentation


  • 265 Views
  • Updated On :

JAVA & PL/SQL. For Oracle RDBMS (8i & 9i) By Joel A. Thompson ([email protected]) www.rhinosystemsinc.com 05/2004. INTRODUCTION. Joel A. Thompson 15 years of industry experience Consultant: Architecture, Software Engineering and Project Manager Java and Oracle expert. OVERVIEW.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'java pl' - niveditha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Java pl sql l.jpg

JAVA & PL/SQL

For Oracle RDBMS (8i & 9i)

By Joel A. Thompson ([email protected])

www.rhinosystemsinc.com

05/2004

all rights reserved www.rhinosystemsinc.com


Introduction l.jpg
INTRODUCTION

  • Joel A. Thompson

  • 15 years of industry experience

  • Consultant: Architecture, Software Engineering and Project Manager

  • Java and Oracle expert

all rights reserved www.rhinosystemsinc.com


Overview l.jpg
OVERVIEW

  • Basics of PLSQL

  • Optimizing RDBMS Calls from JAVA

    Return Values & Cursors

    Logic processing on Server, ARRAY inserts

    And more …

  • Examples

  • WRAP UP / Q&A

all rights reserved www.rhinosystemsinc.com


Getting started with java l.jpg
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)

      • Oracle’s JDeveloper (technet.oracle.com)

      • IntelliJ: (www.intellij.net)

      • Textpad: http://www.textpad.com

all rights reserved www.rhinosystemsinc.com


Getting started with oracle l.jpg
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".

  • 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/[email protected]:1521:UCDBA

all rights reserved www.rhinosystemsinc.com


Client server java plsql architecture l.jpg
Client Server Java/PLSQL Architecture

all rights reserved www.rhinosystemsinc.com


What is pl sql l.jpg
What is PL/SQL?

  • A programming language

    • If/then/else

    • Loops

    • Function calls

    • Transactional

    • Procedural

  • Feature rich

  • Performance Gain

  • Oracle only

all rights reserved www.rhinosystemsinc.com


Some good reasons to use pl sql l.jpg
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.

all rights reserved www.rhinosystemsinc.com


Ways to call pl sql l.jpg
Ways to call PL/SQL

  • ADHOC

    • Send the PL/SQL block of code from the client java program to the server for processing.

  • 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.

all rights reserved www.rhinosystemsinc.com


Adhoc java client example 1 l.jpg
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


Issues regarding example 1 l.jpg
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.

all rights reserved www.rhinosystemsinc.com


Adhoc java client example 2 l.jpg
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;

    }

all rights reserved www.rhinosystemsinc.com


Issues regarding example 2 l.jpg
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

all rights reserved www.rhinosystemsinc.com


Adhoc java client example 3 l.jpg
ADHOC: Java Client Example 3

  • Query based on Prepared Statement

    { // IN CONSTRUCTOR or INITIALIZATION BLOCK

    // THE SQL QUERY TO BE MADE.

    String sQueryNotify="select email_author,notification,subject,s.name as name " +

    " from project p, status s " +

    " where p.id=? " +

    " and s.id=p.status_id";

    // CREATE A PREPARED STATEMENT, BASED ON THE ABOVE SQL

    m_psLookupProjectID=conn.prepareStatement(sQueryNotify);

    }

    public EmailInfo queryEmailInfo(int id)

    {

    // BIND THE PARAMETER

    m_psLookupProjectID.setInt(1,id); // REUSE THE ALREADY CREATED PREPARED STATEMENT

    // GET THE RESULT SET AND PROCESS IT.

    ResultSet rs=m_psLookupProjectID.executeQuery();

    return info;

    }

all rights reserved www.rhinosystemsinc.com


Adhoc pl sql example 4 l.jpg

1

2

3

4

5

6

7

ADHOC: PL/SQL Example 4

PreparedStatement ps=null;

sSQL=new String("declare" +

" l_id number:=0;" +

" ret number:=0;" +

" lssno varchar2(32):=?;" +

" error_msg varchar2(1026):=null;" +

" BEGIN" +

" select count(*) into ret from person where SSNO= lssno ;" +

" if(ret=0) then" +

" insert into person(id,ssno) values(seq_id.nextval, lssno) returning id into ret;" +

" end if;" +

" ?:=ret;" +

" EXCEPTION WHEN OTHERS THEN" +

" ?:=SQLERRM;" +

" END;");

ps =connection.prepareStatement(sbSQL.toString());

int indx=1;

ps.setString(indx++,SSNO);

int rettype=Types.INTEGER;

ps.registerOutParameter(indx++,rettype);

rettype=Types.VARCHAR;

ps.registerOutParameter(indx++,rettype);

ps.executeUpdate();

l_sError=ps.getString(2);

if(l_sError!=null)

{

System.err.println("PL/SQL Error: " + l_sError);

return;

}else

{

ret=ps.getInt(1);

System.out.println("New USERID: " + ret);

}

all rights reserved www.rhinosystemsinc.com


Analysis of example 4 l.jpg
Analysis of Example 4

  • Refer to labels in red from example.

  • 1)Notice that "DECLARE" begins our PL/SQL code. You don't need a DECLARE section, however, you can setup variables here by binding them to Java variables on your client side. Also notice the BEGIN statement, which is matched with the "END;" statement in #4.

DECLARE

BEGIN

END;

all rights reserved www.rhinosystemsinc.com


Analysis of example 417 l.jpg
Analysis of Example 4

  • Refer to labels in red from example.

  • 2)Test to see if the person exists before inserting into the table. (notice the RETURNING clause on the insert).

  • 3)If there are any exceptions that are thrown in the surrounding block, then execute this line of code – assign the ERROR MESSAGE (SQLERRM) to the return message

  • 4)The "END" tag is used to close the BEGIN - Also notice the usage of ";" after the END tag – also notice that we finished the string.

all rights reserved www.rhinosystemsinc.com


Analysis of example 418 l.jpg
Analysis of Example 4

  • Refer to labels in red from example.

  • 5)Use an existing SQL connection to create the callable statement – a prepared statement, used to bind input variables and output variables. Notice the index variable declared and assigned value of 1.

  • 6)Bind the SSNO string to the first occurance of a "?" in the PLSQL. (what will happen if SSNO string's length is greater than 32?).Next few lines we register the output variables. (NOTICE how they match up with the ?). One is of type INTEGER the other VARCHAR.

all rights reserved www.rhinosystemsinc.com


Analysis of example 419 l.jpg
Analysis of Example 4

  • Refer to labels in red from example.

  • 7)We executed the SQL statement, and now check for errors. If we find an error Message (ie not null), then we will report it, and return immediately. ELSE we'll continue and report the new userid (created from a sequence number in Oracle).

all rights reserved www.rhinosystemsinc.com


Issues from example 4 l.jpg
Issues from Example 4

  • Constructing SQL on the client side.

    • Need to recompile in order to change sql

    • Not very portable.

    • Not modular

  • Trade offs?

    • Plain SQL insert and catch duplicate key exception – need to make sure your Primary KEY or unique indices are setup properly.

all rights reserved www.rhinosystemsinc.com


Function procedure call example 5 l.jpg
FUNCTION/PROCEDURE CALL – Example 5

  • Call a function, that processes some data and returns a result set.

  • KEY THINGS TO NOTE:

    • Minimize SQL code on client side.

    • Ready for portability

      • Within Oracle – yes

      • Using other database – perhaps

      • SQL 92 syntax?

all rights reserved www.rhinosystemsinc.com


Example 5 syntax consideration l.jpg
Example 5 – Syntax consideration

  • JDBC SYNTAX For portability{?= call <procedure-name>[<arg1>,<arg2>, ...]} {call <procedure-name>[<arg1>,<arg2>, ...]}

  • ORACLE SYNTAX (not portable)BEGIN ?:=<procedure-name>[<arg1>,<arg2>, ...]; END;

all rights reserved www.rhinosystemsinc.com


Example 5 pl sql server code l.jpg
Example 5 – PL/SQL server code

  • Load the following into oracle once at install time

  • Declare function in a package (not necessary but good to do).CREATE OR REPLACE PACKAGE api_person_pkg AS TYPE T_REFCURSOR IS REF CURSOR; -- Define a cursor reference. FUNCTION getPeople (VARCHAR2) RETURN T_REFCURSOR;

    END api_person_pkg;/

all rights reserved www.rhinosystemsinc.com


Example 5 pl sql server code24 l.jpg

Create the body of the package/function

CREATE OR REPLACE PACKAGE BODY api_person_pkg AS

FUNCTION function getPeople(p_filter varchar2:=null) RETURN T_REFCURSOR

IS

v_ret_cursor API_PERSON_PKG.T_REFCURSOR;

BEGIN

if (p_filter is null) then

OPEN v_ret_cursor FOR

'select * from person';

else

OPEN v_ret_cursor FOR

'select * from person where username like ''%' || p_filter || '%''';

end if;

RETURN v_ret_cursor;

END;

END api_person_pkg;

/

Consider using global temporary table. Create the table at install time, then use the table to insert records into from different tables – the records are only visible to current session. On commit records go away.

Example 5 – PL/SQL server code

all rights reserved www.rhinosystemsinc.com


Example 5 plsql l.jpg

Test code to test the PL/SQL

SQL> set serveroutput on;

SQL> declare

v_ret_cursor API_PERSON_PKG.T_REFCURSOR;

v_id number; --PERSON ID

v_name varchar2(120); -- the username

begin

v_ret_cursor:=API_PERSON_PKG.getPeople('jthomps');

LOOP

FETCH v_ret_cursor into v_id ,v_name;

EXIT WHEN v_ret_cursor%NOTFOUND;

dbms_output.put_line('values retrieved =' || v_id || ',' ||

v_name);

END LOOP;

END;

/

Example 5 – PLSQL

all rights reserved www.rhinosystemsinc.com


Example 5 java client code l.jpg
Example 5 – java client code

  • Call a function to return a result set. try

    {

    String sql=new String("{ ? = call api_person_pkg.getPeople('joelt') }");

    // could have used "BEGIN ?:= api_person_pkg.getPeople('joelt'); END;"

    final int cursorRefType=oracle.jdbc.driver.OracleTypes.CURSOR;

    cs=m_Conn.prepareCall(sql);

    cs.registerOutParameter(1,cursorRefType);

    cs.execute();

    rs=(ResultSet)cs.getObject(1);

    int ColNameIndex=1;

    ResultSetMetaData rsmd=rs.getMetaData();

    String ColName1=rsmd.getColumnName(ColNameIndex++);

    String ColName2=rsmd.getColumnName(ColNameIndex);

    System.out.println(ColName1 + "," + ColName2);

    while(rs.next())

    {

    l_sPersonID=rs.getString(1);

    l_sUsername=rs.getString(2);

    System.out.println(l_sPersonID + "," + l_sUsername);

    }

    }catch(SQLException sqle)

    { // ...

    System.out.println("Error: " + sqle.getMessage());

    }finally

    {

    rs.close();

    cs.close();

    try{ m_Conn.close(); } catch(Exception e){}

    }

all rights reserved www.rhinosystemsinc.com


Example 6 objective l.jpg
Example 6 – objective

  • Java to insert/update record with stored procedure, showing how to create primary key & foreign key records using Oracle's sequences.

all rights reserved www.rhinosystemsinc.com


Example 6 create the str varray type l.jpg

You need to create a type to hold the array. This type will be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

create or replace TYPE str_varray AS VARRAY(10) OF VARCHAR2(5)

/

Example 6 – create the STR_VARRAY type

all rights reserved www.rhinosystemsinc.com


Example 6 stored procedure l.jpg

Create the stored procedure be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

PROCEDURE storePersonCar(p_username varchar2,p_Cars str_varray)AS

cnt number:=0;

id person.id%TYPE;

indx integer;

BEGIN

select count(*) into cnt from person where username=p_username;

BEGIN

if ( cnt = 0 ) then

insert into person(id,username) values(seq_person_id.nextval,p_username) returning id into id;

else

select id into id from person where username=p_username;

delete from cars where person_id=id;

end if;

indx:=p_Cars.FIRST;

while indx <= p_Cars.LAST LOOP

insert into cars values(id,p_Cars(indx));

indx:=p_Cars.next(indx);

END LOOP;

commit;

EXCEPTION WHEN OTHERS then

rollback;

END;

END;

/

Example 6 – stored procedure

all rights reserved www.rhinosystemsinc.com


Example 6 java code l.jpg

import java.sql.*; be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

import oracle.sql.*;

PreparedStatement ps =null;

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("JOEL.STR_VARRAY", m_Conn);

String sCar="";

String cars[]=new String[10];

for(int i=0;i<10;i++)

{

sCar="carX"+i; // CONTRIVE A NAME of a CAR

cars[i]=new String(sCar);

}

ARRAY array3 = new ARRAY (desc, m_Conn, cars);

String sql=new String("{call api_person_pkg.storePersonCar('joelt',?) }");

ps= m_Conn.prepareStatement(sql);

// Set the values to insert

((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(1, array3);

// Insert the new row

ps.executeUpdate();

m_Conn.commit();

Example 6 –Java Code

all rights reserved www.rhinosystemsinc.com


Example 6 java notes l.jpg
Example 6 –Java Notes be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

  • Make sure to include classes12.jar and nls_charset12.jar – both found in $ORACLE_HOME/jdbc/lib directory.

  • This example works with Oracle 9.2.0.1.0 on Windows XP.

  • Java version 1.4.2_03

all rights reserved www.rhinosystemsinc.com


Portability compatibility issues l.jpg
Portability & Compatibility issues be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

  • SQL Standards

  • JDBC standards

  • Other issues (dates, functions…etc.)

  • Portability from schema-to-schema

    • abstracting calls to database with PL/SQL layer

all rights reserved www.rhinosystemsinc.com


Wrap up l.jpg
WRAP-UP be referenced in the PL/SQL procedure as a parameter type, and in the Java Code.

  • QUESTIONS and Answers

  • MORE INFORMATION:

all rights reserved www.rhinosystemsinc.com


ad