How to expose a SQL query using Oracle AS Jpublisher

How to expose a SQL query using Oracle AS Jpublisher

This can be perform using the JPublisher utility available in Oracle Application server.

Below are the sequence of step and a example on how to expose a SQL statement as webservice.

1) Create a Oracle Object Type using the below SQL in the oracle DB.

create or replace type address as object
 (
 street varchar2(50),
 city varchar2(30),
 state varchar2(2),
 zip varchar2(10),
 map member function get_address return varchar2 )
not final;

2) create the oracle objects body.

create or replace type body address
as
map member function get_address
return varchar2
l_address varchar2(200);
begin
l_address :=
street || ' ' ||
city || ' ' ||
state || ' ' ||
zip;
return l_address;
end;
end;

3) Create a oracle table that hold the objects in a table.

create table address_table of address

4) Now insert data into the above created table using the PLSQL code.

declare
l_address address;
begin
l_address := address(
'Mountain View', 'CA', '94055' );
insert into address_table values ( l_address );
commit;
dbms_output.put_line ( l_address.get_address() );
end;

5) now create a Java wrapper class using the Oracle JPublisher using the below command.

jpub -user=USERNAME/PASSWORD -methods=none -builtintypes=jdbc -numbertypes=objectjdbc -usertypes=jdbc -sql address:Address:MyAddress -package=sample.jpubs

THis will create a Java Interface class called MyAddress. This class belongs to the object Sample.jpubs

6) We will now add the method called getAddress() to the generated file MyAddress.java,
which in turn invokes the get_address() method of the address object type:
public String getAddress( Connection connection )
throws SQLException
{
String getAddressStmt =
"begin ? := " + getSQLTypeName()+".get_address( ? ); end;";
CallableStatement cstmt = null;
try
{
cstmt = connection.prepareCall ( getAddressStmt );
cstmt.registerOutParameter ( 1, OracleTypes.VARCHAR );
// pass the second parameter corresponding to the
// implicit parameter "self".
cstmt.setObject( 2, this );
cstmt.execute();
String address = (String) cstmt.getObject( 1 );
return address;
}
finally
{
JDBCUtil.close ( cstmt );
}
}
We need to import the classes book.util.JDBCUtil and java.sql.CallableStatement
for the MyAddress class after adding the previous method, in order to compile the file
MyAddress.java.
We pass a Connection object in the wrapper method getAddress(). This is required to
execute the object’s get_address() method after connecting to the database.

import java.util.Map;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import book.util.JDBCUtil;
import book.ch10.jpub.MyAddress;
public class DemoSQLData
{
public static void main(String[] args) throws Exception
{
Connection connection = null;
try
{
connection = JDBCUtil.getConnection(
"benchmark", "benchmark", "ora10g" );
Map myMap = connection.getTypeMap();

myMap.put ( "BENCHMARK.ADDRESS",
Class.forName( MyAddress.class.getName() ) );
_demoSelect( connection );
finally
{
JDBCUtil.close ( connection );
}
}

private static void _demoSelect( Connection connection )
throws SQLException
{
PreparedStatement pstmt = null;
ResultSet rset = null;
try
{
String selectStmt = "select value(a) from address_table a";
pstmt = connection.prepareStatement ( selectStmt );
rset = pstmt.executeQuery();
while ( rset.next() )
{
MyAddress address = (MyAddress) rset.getObject(1);
System.out.println( address.getAddress( connection ) );
}
}
finally
{
JDBCUtil.close( rset );
JDBCUtil.close( pstmt );
}
}

This complete the creation of the Java Wrapper class which we can use in normal Java web service which exposes SQL statement
select * from  address_table