Tuesday, 20 July 2010

How to Set v$session.program From a Universal Connection Pool (UCP)

The follow shows how you can ensure connections created from your Oracle Universal Connection Pool (UCP) clients can be uniquely identified using the Oracle JDBC driver property v$session.program. The property is an Oracle JDBC driver property so what we do here is the following.

1. Set the factory class to "oracle.jdbc.OracleDriver" using the method PoolDataSource.setConnectionFactoryClassName()
2. Use the PoolDataSource.setConnectionFactoryProperties() method to specify the driver properties that each Connection will use.

So here is a basic class showing how to set v$session.program and verify it did this correctly from SQL*Plus.


TestUCPJDBCProps.java

package demo;

import java.io.IOException;

import java.sql.Connection;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import oracle.ucp.UniversalConnectionPoolAdapter;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.admin.UniversalConnectionPoolManager;
import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;


public class TestUCPJDBCProps
{
  private PoolDataSource pds = null;
  private Properties props = new Properties();
  private UniversalConnectionPoolManager mgr = null;
  private String poolName = "PasUCPTest";
  
  public TestUCPJDBCProps() throws SQLException, UniversalConnectionPoolException
  {  
    mgr = UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager();
    
   // Create pool-enabled data source instance.
    pds = PoolDataSourceFactory.getPoolDataSource();
    // PoolDataSource and UCP configuration
    
    //set the connection properties on the data source and pool properties
    pds.setUser("scott");
    pds.setPassword("tiger");
    pds.setURL("jdbc:oracle:thin:@//beast.au.oracle.com:1523/linux11gr2");
    pds.setConnectionFactoryClassName("oracle.jdbc.OracleDriver");
    pds.setInitialPoolSize(2);
    pds.setMinPoolSize(2);
    pds.setMaxPoolSize(20);
    pds.setConnectionPoolName(poolName);
    props.put("v$session.program", "scott-ucp-j2seclient");
    pds.setConnectionFactoryProperties(props);
    
    mgr.createConnectionPool((UniversalConnectionPoolAdapter)pds);
    
    mgr.startConnectionPool(poolName);

  }

  public void run () throws SQLException, IOException
  {
    List connList = new ArrayList();
    
    for (int i = 0; i < 5 ;i++ ) 
    {
      //Get a database connection from the datasource. 
      Connection conn = pds.getConnection();
      System.out.println("Retrieved a connection from pool");
      connList.add(conn);
    }

    System.out.println("Press Enter to finish the demo -> ");
    System.in.read();
      
    // close all connections
    for (int j = 0; j < connList.size() ; j++) 
    {
      ((Connection)connList.get(j)).close();
    }
    
  }
  
  public void stopPool () throws UniversalConnectionPoolException
  {
    mgr.stopConnectionPool(poolName);  
  }
  
  public static void main(String[] args) throws IOException
  {
    System.out.println("Started UCP JDBC Property Test at " + new Date());
    TestUCPJDBCProps test;

    try
    {
      test = new TestUCPJDBCProps();
      test.run();
      test.stopPool();
    }
    catch (Exception e)
    {
      e.printStackTrace();
      System.exit(-1);
    }
    
    System.out.println("Ended UCP JDBC Property Test at " + new Date());
  }
} 

SQL*PLus Output

SCOTT@linux11gr2> @query-scott.sql

SCOTT sessions

USERNAME PROGRAM                   STATUS
-------- ------------------------- --------
SCOTT    sqlplus.exe               ACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE
SCOTT    scott-ucp-j2seclient      INACTIVE

6 rows selected.

SCOTT@linux11gr2>

The SQL for the query above was as follows.

set head on feedback on

set pages 999
set linesize 120

prompt
prompt SCOTT sessions

col machine format a25
col username format a15
col username format a8
col program format a25

select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/

Thursday, 15 July 2010

SQL Worksheet using JSF 2 / JSTL Result

Having used JSTL / JSP often enough I thought I would quickly try and get a Facelet page to use a JSTL Result object to provide a VERY basic SQL Worksheet demo which allowed the user to query the database and display the data regardless of what the query data was. Found a few gotchas on the Facelet / JSF 2.0 side which were handy to solve.

Problems

1. Using c:if for conditional output seemed to always result in FALSE when clearly that wasn't the case. There are a few options in JSF 2.0 world but the easiest was to now use ui:fragment as shown below.

<ui:fragment rendered="#{queryBean.rowcount > 0}">

2. As was the case with c:if , c:forEach didn't work for me either. I believe it had something to do with it being run when the component tree is being built. So now I will be using ui:repeat instead, which is close to identical but now use the attribute value instead of items.
<ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}"> 

So here is the Facelet page, Managed bean and a quick HTML screen show of how it worked.

Query.xhtml
<?xml version="1.0" encoding="UTF-8"?>
<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<ui:composition
      xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      template="/pages/templates/EmpTemplate.xhtml"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:c="http://java.sun.com/jsp/jstl/core">

    <f:metadata>
        <f:viewParam name="refresh" value="#{employeeBean.refresh}" />
        <f:viewParam name="deptno" value="#{employeeBean.deptno}" />
        <f:viewParam name="empno" value="#{employeeBean.empno}" />
    </f:metadata>

    <ui:define name="title">
        #{msg.browserheading}
    </ui:define>

    <ui:define name="header">
        <ui:include src="/pages/employees/header.xhtml" />
    </ui:define>

    <ui:define name="body">
        <b>Enter SQL query below without semi colon</b>
        <p />
        <h:message for="query" style="color: #336699"/>
        <h:form>
            <h:inputTextarea rows="8"
                             cols="100"
                             value="#{queryBean.query}"
                             required="true"
                             requiredMessage="You must an SQL select statement to execute"
                             validator="#{queryBean.validateQuery}"
                             id="query"/>
            <br />
            <h:commandButton value="Submit Query" action="#{queryBean.executeQuery}"/>
            <h:commandButton value="Clear" action="#{queryBean.clearScreen}"/>
            <p />
        </h:form>
        <p />
        <ui:fragment rendered="#{queryBean.rowcount > 0}">
            <i>Total of #{queryBean.rowcount} record(s) found</i>
            <p />
            <table border="1">
                <thead>
                  <tr>
                    <ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}">
                        <th class="heading">#{columnName}</th>
                    </ui:repeat>
                  </tr>
                </thead>
                <tbody>
                    <ui:repeat var="row" value="#{queryBean.queryData.rows}" varStatus="loop">
                        <tr class="${((loop.index % 2) == 0) ? 'even' : 'odd'}">
                          <ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}">
                            <td>#{row[columnName]}</td>
                          </ui:repeat>
                        </tr>
                    </ui:repeat>
                </tbody>
            </table>
            <p />
        </ui:fragment>
    </ui:define>
    
    <ui:define name="footer">
        <ui:include src="/pages/employees/footer.xhtml" />
    </ui:define>

</ui:composition>

QueryBean.java
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package oracle.jsf.demo.managedbeans;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.validator.ValidatorException;
import javax.servlet.jsp.jstl.sql.Result;
import oracle.jsf.demo.dao.emp.EmployeeService;

/**
 *
 * @author papicell
 */
@ManagedBean
public class QueryBean
{
    private Result queryData;
    private String query;
    private int rowcount;

    @ManagedProperty(value="#{employeeServiceImpl}")
    private EmployeeService service;

    public QueryBean()
    {
      rowcount = 0;
    }

    public String getQuery()
    {
        return query;
    }

    public void setQuery(String query)
    {
        this.query = query;
    }

    public Result getQueryData()
    {
        return queryData;
    }

    public void setQueryData(Result queryData)
    {
        this.queryData = queryData;
    }

    public EmployeeService getService()
    {
        return service;
    }

    public void setService(EmployeeService service)
    {
        this.service = service;
    }

    public int getRowcount()
    {
        return rowcount;
    }

    public void setRowcount(int rowcount)
    {
        this.rowcount = rowcount;
    }

    /*
     * Custom Validation method for query field
     */
    public void validateQuery(FacesContext context,
                                     UIComponent componentToValidate,
                                     Object value) throws ValidatorException
    {
        String statementSQL = ((String)value);
        if (!statementSQL.toLowerCase().startsWith("select"))
        {
            FacesMessage message =
                new FacesMessage("Not a valid SQL Select statement entered");
            throw new ValidatorException(message);
        }
    }

    public void executeQuery ()
    {
       queryData = service.executeQuery(getQuery());
       setRowcount(queryData.getRowCount());
    }

    public void clearScreen ()
    {
       queryData = null;
       setRowcount(0);
    }
} 

Browser Ouput

Wednesday, 14 July 2010

JSF 2 Handling Unexpected Runtime Errors

Note for myself:

1. Create a managed bean as follows.

 
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package oracle.jsf.demo.managedbeans;

import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.context.FacesContext;

/**
*
* @author papicell
*/
@ManagedBean
public class ErrorBean
{
private static final String BR = "n";

public String getStackTrace()
{
FacesContext context = FacesContext.getCurrentInstance();
Map map = context.getExternalContext().getRequestMap();
Throwable throwable = (Throwable) map.get("javax.servlet.error.exception");
StringBuilder builder = new StringBuilder();
builder.append(throwable.getMessage()).append(BR);

for (StackTraceElement element : throwable.getStackTrace())
{
builder.append(element).append(BR);
}

return builder.toString();
}

}
2. Define error page in web.xml which will catch all unknown exceptions.
 
<error-page>
<exception-type>java.lang.Exception</exception-type>
<location>/pages/main/error.jsf</location>
</error-page>

3. Define error page Facelet error.xhtml as follows
 
<?xml version="1.0" encoding="UTF-8"?>
<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<ui:composition
xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:ui="http://java.sun.com/jsf/facelets"
template="/pages/templates/EmpTemplate.xhtml"
xmlns:f="http://java.sun.com/jsf/core">

<ui:define name="title">
#{msg.browserheading}
</ui:define>

<ui:define name="header">
<h3 style="font-family: arial; font-variant: small-caps; color: #336699">
You have encountered a system error!!
</h3>
</ui:define>

<ui:define name="body">
The error message is:
<b>
#{requestScope['javax.servlet.error.message']}
</b>
<p />
<font color="RED">
Please show the system administator the error below.
</font>
<p />
<textarea rows="30" cols="100">
<h:outputText escape="false" value="#{errorBean.stackTrace}"/>
</textarea>
<p />
</ui:define>

<ui:define name="footer">
<ui:include src="/pages/employees/footer.xhtml" />
</ui:define>

</ui:composition>

Tuesday, 6 July 2010

Simple JSF 2 h:dataTable example using Result for the value attribute

With JSF 2 we can now use a JSTL Result (javax.servlet.jsp.jstl.sql.Result) , well perhaps you could with JSF 1.2 but seemed to be a JSF 2 new feature from what I could see. His a basic example on it. What I like about it is it completely takes the data off the JDBC ResultSet object and lets you work with it independently. Kinda like storing a JDBC ResultSet in an array of Objects but saves you having to define an object and populate it yourself. JSTL Result is easy and only a few lines of code required to use it.

1. Firstly create a basic class which returns a JSTL Result object from a query. In this example the Connection is retrieved from a data source within the container itself.


/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package pas.jsf2.fun.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import javax.sql.DataSource;

/**
*
* @author papicell
*/
public class JdbcUtil
{
private static Connection getJNDIConnection ()
{
Context ctx;
Connection conn = null;

try
{
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jdbc/scottDS");
conn = ds.getConnection();

}
catch (Exception ex)
{
Logger.getLogger(JdbcUtil.class.getName()).log(Level.SEVERE, null, ex);
}

return conn;
}

public static Result runQuery (String query, int maxrows) throws SQLException
{
Statement stmt = null;
ResultSet rset = null;
Result res = null;
Connection conn = null;

try
{
conn = getJNDIConnection();
stmt = conn.createStatement();
rset = stmt.executeQuery(query);

/*
* Convert the ResultSet to a
* Result object that can be used with JSTL/JSF tags
*/
if (maxrows == -1)
{
res = ResultSupport.toResult(rset);
}
else
{
res = ResultSupport.toResult(rset, maxrows);
}
}
finally
{
if (rset != null)
{
rset.close();
}

if (stmt != null)
{
stmt.close();
}

if (conn != null)
{
conn.close();
}
}

return res;
}

}

2. Create a Managed Bean as follows.
 
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package pas.jsf2.fun;

import java.sql.ResultSet;
import java.sql.SQLException;
import javax.faces.bean.ManagedBean;
import javax.servlet.jsp.jstl.sql.Result;
import pas.jsf2.fun.jdbc.JdbcUtil;
/**
*
* @author papicell
*/
@ManagedBean(name="JDBCEmpTable")
public class JDBCEmpTable
{
private Result empResultData;

public Result getEmpResultData() throws SQLException
{
populateEmpResultData();
return empResultData;
}

private void populateEmpResultData () throws SQLException
{
empResultData =
JdbcUtil.runQuery("select empno, ename, job from emp", -1);
}

}

3. Finally create the view Facelet page which will display the Result in a h:dataTable component.


<?xml version="1.0" encoding="UTF-8"?>
<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:f="http://java.sun.com/jsf/core">
<h:head>
<title>JSTL Result Emp Map Table Demo</title>
</h:head>
<h:body>
<h3 style="font-family: arial; font-variant: small-caps; color: #336699">
JSTL Result Emp Map Table Demo
</h3>
<h:dataTable var="row" value="#{JDBCEmpTable.empResultData}" border="1">
<h:column>
<f:facet name="header">#Empno</f:facet>
#{row.empno}
</h:column>
<h:column>
<f:facet name="header">Name</f:facet>
#{row.ename}
</h:column>
<h:column>
<f:facet name="header">Job</f:facet>
#{row.job}
</h:column>
</h:dataTable>
<p />
<h:link
outcome="index.jsp"
value="Return To Home" />
</h:body>
</html>

Monday, 5 July 2010

Oracle UCP with Java DB (Derby)

Creating some JSF 2.0 demos and was told if I could use Sun Java DB (derby) rather then Oracle for the back end database. Thought it would be a chance to use some other DB and was surprised with how easy it was to install, setup and create a database with. Here is how I ended up settting up a UCP Connection Pool against a Java Db (Derby) database.

1. Create the database itself

> java -jar %DERBY_HOME%\lib\derbyrun.jar ij
> connect 'jdbc:derby:firstdb;create=true';

2. Create an SQL file to use the classic DEPT/EMP tables.

SQL File to create classic DEPT/EMP Tables

 
drop table emp;
drop table dept;

AUTOCOMMIT OFF;

CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13));

ALTER TABLE DEPT
ADD CONSTRAINT DEPT_PK Primary Key (DEPTNO);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
HIREDATE DATE,
SAL INTEGER,
COMM INTEGER,
DEPTNO INTEGER);

ALTER TABLE EMP
ADD CONSTRAINT EMP_PK Primary Key (EMPNO);

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-21', 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

COMMIT;

ALTER TABLE EMP
ADD CONSTRAINT EMP_FK Foreign Key (DEPTNO)
REFERENCES DEPT (DEPTNO);

COMMIT;

Ant Build File Used to Load the Data

<?xml version="1.0"?>

<project default="buildschema" name="deptemp" basedir=".">

<!-- Set Properties -->
<property file="build.properties"/>

<!-- Targets -->

<target name="init">
<tstamp/>
<delete file="deptemp.out"/>
</target>

<target name="buildschema" depends="init">
<java classname="org.apache.derby.tools.ij"
output="deptemp.out"
failonerror="true"
dir="."
fork="true">
<classpath>
<pathelement path="${derby.home}/lib/derby.jar"/>
<pathelement path="${derby.home}/lib/derbytools.jar"/>
<pathelement path="${derby.home}/lib/derbyclient.jar"/>
</classpath>
<sysproperty key="ij.driver" value="org.apache.derby.jdbc.ClientDriver"/>
<sysproperty key="ij.database" value="${jdbcurl}"/>
<arg value="deptemp.sql"/>
</java>
</target>

</project>

Ant Properties File

# derby.home
#
# This property is only required if using ANT to run this demo. It will
# use the property to build a classpath required to run the utilities

derby.home=C:\\pas\\software\\derby\\10530\\JavaDB

# jdbcurl , assumes server network connection

jdbcurl=jdbc:derby://localhost:1527/firstdb;create=true

3. Start a network server which my clients will connect to to access the database

D:\jdev\derby\pas\databases>java -jar D:\jdev\derby\10530\JavaDB\lib\derbyrun.jar server start
2010-07-04 22:14:37.187 GMT : Security manager installed using the Basic server security policy.
2010-07-04 22:14:38.109 GMT : Apache Derby Network Server - 10.5.3.0 - (802917) started and ready to accept connections on port 15
27

4. Create a quick client to verify UCP using Java Db (Derby).

Note: Need to add ucp.jar and derbyclient.jar to the classpath
 
package pas.au.ucp.standalone;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

public class DerbyUCPTest
{
private PoolDataSource pds = null;

public DerbyUCPTest() throws SQLException
{
pds = PoolDataSourceFactory.getPoolDataSource();
pds.setURL("jdbc:derby://localhost:1527/firstdb");
pds.setConnectionFactoryClassName("org.apache.derby.jdbc.ClientDriver");
}

public void run () throws SQLException
{

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

try
{
conn = pds.getConnection();

System.out.println("Got Connection to DERBY database server from UCP Pool");

stmt = conn.createStatement();
rset = stmt.executeQuery("SELECT empno, ename from EMP");

while (rset.next())
{
System.out.println
(String.format("Empno#: %s, Ename: %s",
rset.getInt(1),
rset.getString(2)));
}
}
catch (SQLException se)
{
se.printStackTrace();
}
finally
{
if (rset != null)
{
rset.close();
}

if (stmt != null)
{
stmt.close();
}

if (conn != null)
{
conn.close();
}
}

}

public static void main(String[] args) throws SQLException
{
DerbyUCPTest derbyUCPTest = new DerbyUCPTest();
derbyUCPTest.run();

}
}

Output
-------

Got Connection to DERBY database server from UCP Pool
Empno#: 7369, Ename: SMITH
Empno#: 7499, Ename: ALLEN
Empno#: 7521, Ename: WARD
Empno#: 7566, Ename: JONES
Empno#: 7654, Ename: MARTIN
Empno#: 7698, Ename: BLAKE
Empno#: 7782, Ename: CLARK
Empno#: 7788, Ename: SCOTT
Empno#: 7839, Ename: KING
Empno#: 7844, Ename: TURNER
Empno#: 7876, Ename: ADAMS
Empno#: 7900, Ename: JAMES
Empno#: 7902, Ename: FORD
Empno#: 7934, Ename: MILLER