- Create user MYAPP_POOL with minimum privileges in the database:
connect sys@orcl as sysdba
create user myapp_pool identified by oracle4;
grant create session to myapp_pool;
- Allow MYAPP_USER to connect through the proxy user MYAPP_POOL:
alter user myapp_user grant connect through myapp_pool authenticated using distinguished name;
- Start JDeveloper
- Create a new Empty Project called myapp
- Include your database's JDBC library (e.g. D:\ora102db\jdbc\lib\ojdbc14.jar) in the project libraries
- Create a class called dbhelper:
package myapp;
import java.sql.*;
import oracle.jdbc.pool.OracleOCIConnectionPool;
public class dbhelper {
public static String doQuery(Connection conn, String query) {
if (conn == null) {
return "No connection! ";
}
StringBuffer sb = new StringBuffer();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
for (int t_count = 0; ; t_count++ ) {
if (rs.next()) {
sb.append (
rs.getString(1)
+ " : "
+ rs.getString(2)
+ "\n"
);
}
else {
break;
}
}
rs.close();
stmt.close();
}
catch (Exception e) {
sb.append("Query: SQL Exception: " + e.toString());
}
return sb.toString();
}
public static void showQueryResult (Connection conn, String query) {
System.out.print(doQuery(conn, query));
}
public static String getSessionInfoDetail (Connection conn, String prompt, String key) {
return doQuery (
conn
, "select '" + prompt + "', sys_context('userenv', '" + key + "') from dual"
);
}
public static String getSessionInfo (Connection conn) {
return getSessionInfoDetail(conn, "Session User", "session_user")
+ getSessionInfoDetail(conn, "Proxy User", "proxy_user")
+ getSessionInfoDetail(conn, "External Name", "external_name")
+ getSessionInfoDetail(conn, "Enterprise Identity", "enterprise_identity")
+ getSessionInfoDetail(conn, "Session ID", "sessionid")
+ getSessionInfoDetail(conn, "SID", "sid")
;
}
public static void showSessionInfo (Connection conn) {
System.out.print(getSessionInfo(conn));
}
public static void closeConnection (Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (Exception ignored) {}
}
}
public static void closeConnectionPool (OracleOCIConnectionPool cp) {
if (cp != null) {
try {
cp.close();
} catch (Exception ignored) {}
}
}
}
- Create a class called proxyTest:
package myapp;
import java.sql.*;
import java.util.Properties;
import oracle.jdbc.pool.OracleOCIConnectionPool;
public class proxyTest
{
public static void main(String[] args)
{
OracleOCIConnectionPool cp = null;
Connection conn = null;
try {
cp = new OracleOCIConnectionPool();
cp.setURL (
"jdbc:oracle:oci:@"
+ "(DESCRIPTION="
+ "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))"
+ "(CONNECT_DATA=(SERVICE_NAME=orcl.disney.com))"
+ ")"
);
cp.setUser("myapp_pool");
cp.setPassword("oracle4");
Properties prop = new Properties();
prop.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "3");
prop.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "20");
prop.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1");
cp.setPoolConfig(prop);
Properties userProp = new Properties();
userProp.put (
OracleOCIConnectionPool.PROXY_DISTINGUISHED_NAME
, "cn=batman,cn=Users,dc=disney,dc=com"
);
conn = cp.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_DISTINGUISHED_NAME, userProp);
}
catch (Exception e) {
System.out.println("Connection error: " + e);
}
dbhelper.showQueryResult(conn, "select id, name from mydata order by 1");
dbhelper.showSessionInfo(conn);
dbhelper.closeConnection(conn);
dbhelper.closeConnectionPool(cp);
}
}
- Run the proxyTest class by pressing F11 –
the result should be something like this:
1 : first
2 : second
Session User : MYAPP_USER
Proxy User : MYAPP_POOL
External Name : cn=batman,cn=Users,dc=disney,dc=com
Enterprise Identity : cn=batman,cn=Users,dc=disney,dc=com
Session ID : 1459
Database SID : 366
- Install a
standalone OC4J – See
"Installing Oracle Application Server 10g Release 3 (10.1.3) on Windows XP".
- Create a Managed Data Source called MyappDS
that uses a Connection Pool called MyappCP
that connects to the database as MYAPP_POOL
- Add a Deployment Profile for WAR called myapp to your JDeveloper project,
with J2EE Web Context Root set to /myapp
- Create a servlet class called myservlet
with servlet mapping /myservlet:
package myapp;
import java.sql.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Properties;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;
import oracle.jdbc.*;
public class myservlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
PrintWriter out = response.getWriter();
out.println("<html><head><title>myservlet</title></head><body>");
DataSource ds = null;
OracleConnection conn = null;
try {
InitialContext ic = new InitialContext();
ds = (DataSource)ic.lookup("jdbc/MyappDS");
conn = (OracleConnection)ds.getConnection();
Properties prop = new Properties();
prop.put (
OracleConnection.PROXY_DISTINGUISHED_NAME
, "cn=batman,cn=Users,dc=disney,dc=com"
);
conn.openProxySession(OracleConnection.PROXYTYPE_DISTINGUISHED_NAME, prop);
}
catch (Exception e) {
out.println(e.toString());
}
out.print (
"<pre>"
+ dbhelper.doQuery(conn, "select id, name from mydata order by 1")
+ dbhelper.getSessionInfo(conn)
+ "</pre>"
);
try {
conn.close(OracleConnection.PROXY_SESSION);
}
catch (Exception e) {
out.println(e.toString());
}
dbhelper.closeConnection(conn);
out.println("</body></html>");
out.close();
}
}
- Right-click on the Deployment Profile (myapp.deploy) and select Deploy to EAR file
(or use the wizard to create a connection and deploy directly to OC4J)
- Deploy myapp.ear to your OC4J installation
- Go to http://localhost:8888/myapp/myservlet –
the output should be similar to that of the proxyTest class (see above)