Generic MBean client for FMW JVM's (Coherence, WLS etc)

Having done some work with MBeans in OC4J 10.1.3.x I decided with the help of steve to create a query tool which allowed you to view the properties/attributes of any MBean from the command line. The JMX API makes it easy to write a generic tool so in the end I can query any JVM with an MBean Server using the tool. In short this is what I did using spring to ensure it was easy to setup with XML and of course run with ANT. Whats good about this is I can just query the MBeans I want to see rather then all of them. Early days but it does what I need it to do at this stage. Different output methods is what I want to do next, currently it just goes to standard out / console.

Step 1 - Define a connection Interface

package oracle.support.rda.server;

import java.util.Hashtable;

import javax.management.MBeanServerConnection;
import javax.management.remote.JMXServiceURL;

public interface ServerConnection 
{
  public void doConnection(String url) throws Exception;
  public void doConnection(JMXServiceURL jmxServiceURL, Hashtable env) throws Exception;
  public MBeanServerConnection getConnection();
  public boolean isConnected();
  public void close ();
}

Step 2 - Create an abstract class which implements the interface , pretty much does everything you need to connect to a MBean Server.

package oracle.support.rda.server;

import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.management.MBeanServerConnection;
import javax.management.remote.JMXConnector;
import javax.management.remote.JMXConnectorFactory;
import javax.management.remote.JMXServiceURL;


public abstract class ServerConnectionBase implements ServerConnection
{
  private JMXConnector jmxCon = null;
  private Logger logger = Logger.getLogger(this.getClass().getName());
  
  public ServerConnectionBase()
  {
  }

  public void doConnection(String url) throws Exception
  {
    logger.log(Level.INFO, "JMX Service URL = " + url);
    JMXServiceURL serviceURL = new JMXServiceURL(url);

    jmxCon = JMXConnectorFactory.connect(serviceURL);
  }

  public void doConnection(JMXServiceURL jmxServiceURL, Hashtable env) throws Exception
  {
    logger.log(Level.INFO, "Service URL Path = " + jmxServiceURL.getURLPath());
    jmxCon = JMXConnectorFactory.connect(jmxServiceURL, env);    
  }
  
  public MBeanServerConnection getConnection()
  {
    MBeanServerConnection mbs = null;

    if (jmxCon != null) 
    {
        try 
        {
            mbs = jmxCon.getMBeanServerConnection();
        } 
        catch (Throwable t) 
        {
            logger.log(Level.SEVERE,
                      "** FMW-RDA [ServerConnectionBase.getConnection] : Unable to retrieve MBeanServerConnection");
        }
    }

    return mbs;
  }

  public boolean isConnected()
  {
    boolean ret = false;
    if (jmxCon == null) 
    {
        return false;
    } 
    else 
    {
        try 
        {
            jmxCon.getConnectionId();
            return true;
        } 
        catch (Throwable t) 
        {
            // no need to do anything here
        }
    }

    return ret;
  }

  public void close()
  {
    if (jmxCon != null) 
    {
        try 
        {
            jmxCon.close();
            jmxCon = null;
        } 
        catch (Throwable t) 
        {
        }
    }
  }

}

Step 3 -Finally a class which connects to a WLS 11g instance

package oracle.support.rda.server.connections.wls;

import java.net.URL;

import java.util.Hashtable;
import java.util.Properties;
import java.util.logging.Logger;

import javax.management.remote.JMXConnectorFactory;

import javax.management.remote.JMXServiceURL;

import javax.naming.Context;

import oracle.support.rda.server.ServerConnectionBase;
import oracle.support.rda.server.connections.coherence.CohJMXConnection;

public class WLSJMXConnection extends ServerConnectionBase
{
  private static WLSJMXConnection instance = null;
  private Logger logger = Logger.getLogger(this.getClass().getName());
  private String serviceURL = null;
  
  static
  {
    try
    {
      instance = new WLSJMXConnection();
    }
    catch (Exception e)
    {
      throw new RuntimeException(e.getMessage(), e);
    }
  }
  
  private WLSJMXConnection() throws Exception
  {
    JMXServiceURL jmxServiceURL = null;
    Hashtable h = new Hashtable();
    
    if (instance == null)
    {
      Properties props = new Properties();
      
      URL url = ClassLoader.getSystemResource("server.properties");
      props.load(url.openStream());

      h.put(Context.SECURITY_PRINCIPAL, props.getProperty("wls.username"));
      h.put(Context.SECURITY_CREDENTIALS, props.getProperty("wls.password"));
      h.put(JMXConnectorFactory.PROTOCOL_PROVIDER_PACKAGES,
         "weblogic.management.remote");
      h.put("jmx.remote.x.request.waiting.timeout", new Long(10000));

      serviceURL = (String) props.getProperty("serviceurl");
      
      jmxServiceURL = 
        new JMXServiceURL(serviceURL);
      
    }
    
    super.doConnection(jmxServiceURL, h);
  }

  public static WLSJMXConnection getInstance() throws Exception
  { 
    return instance;
  }

  public String getServiceURL()
  {
    return serviceURL;
  }
}

So we would then use this connection in a spring XML file as follows using a Factory Class which defines the connections we wish to use. At the time of this post I had an OC4J 10.1.3.x, WLS 10.3.x and Coherence connection implementation classes which extend ServerConnectionBase.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

  <bean id="serverConnection" 
        class="oracle.support.rda.server.ConnectionFactory" 
        factory-method="getWLSConnection" 
        singleton="true">
  </bean> 

Step 4 - Define an interface which allows us to invoke queries against the MBeans and view there attributes

package oracle.support.rda.spring.queries;

import javax.management.MBeanServerConnection;

import oracle.support.rda.spring.exception.RDAQueryException;

public interface RDAQuery 
{
    public Object invoke(MBeanServerConnection mbs) throws RDAQueryException;
    public void setMBeanName(String mbeanName);
}

Step 5 - Define a implementation class for the query interface. The abstract class has been left out here but that's what does all the work of viewing the MBean attributes etc..

package oracle.support.rda.spring.queries;

import java.util.logging.Logger;

import javax.management.MBeanServerConnection;

import oracle.support.rda.spring.exception.RDAQueryException;

public class GenericQuery extends RDAQueryBase
{
  Logger logger = Logger.getLogger(this.getClass().getName());
  
  @Override
  public Object invoke(MBeanServerConnection mbs) throws RDAQueryException 
  {
      logger.info("Generic Query called");
      return super.invoke(mbs);
  }
}

Step 6 - Define the MBeans I wish to query as shown in the example below.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

  <bean id="serverConnection" 
        class="oracle.support.rda.server.ConnectionFactory" 
        factory-method="getWLSConnection" 
        singleton="true">
  </bean>
  
  <bean id="machineQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>com.bea:Name=machine1,Type=Machine</value>
    </property>
  </bean>

  <bean id="nodeMgrQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>com.bea:Name=machine1,Type=NodeManager,Machine=machine1</value>
    </property>
  </bean>
  
  <bean id="appleJVMQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>
        com.bea:ServerRuntime=apple,Name=apple,Type=JVMRuntime      
      </value>
    </property>
    <!--
    The following properties will not be displayed, giving you
    control over what information you show.
    -->
    <property name="nukedAttributes">
      <list>
        <value>ThreadStackDump</value>  
      </list>
    </property>
  </bean>
  
  <bean id="jdbcResourceQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>
        com.bea:Name=jdbc/scottDS,Type=JDBCSystemResource    
      </value>
    </property>
  </bean>

  <bean id="jdbcPropertiesQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>
        com.bea:Name=user,Type=weblogic.j2ee.descriptor.wl.JDBCPropertyBean,Parent=[pastest_dom]/JDBCSystemResources[jdbc/scottDS],Path=JDBCResource[jdbc/scottDS]/JDBCDriverParams/Properties/Properties[user]  
      </value>
    </property>
  </bean>

  <!--  
    This defines the Invoker object, which gets passed 
    a reference to the ServerConnection AND the list of queries
   -->
  <bean id="queryInvoker" class="oracle.support.rda.spring.invoker.QueryInvoker">
    <property name="serverConnection" ref="serverConnection"/>
    <property name="rdaQueries">
      <list>
        <ref bean="machineQuery"/>
        <ref bean="nodeMgrQuery"/>
        <ref bean="appleJVMQuery"/>
        <ref bean="jdbcResourceQuery"/>
        <ref bean="jdbcPropertiesQuery"/>
      </list>
    </property>
  </bean>    

  <!--  
   Bean used to list of MBeans available for use
   -->
  <bean id="showMBeans" class="oracle.support.rda.spring.invoker.MBeanViewer">
    <property name="serverConnection" ref="serverConnection"/>
  </bean> 
  
</beans> 

I provided the ability to remove properties you really don't wish to view for the MBean itself. The name nukedAttributes came from steve.

The queryInvoker bean is what actually invokes the queries themselves which takes a List of query beans themselves and uses the MBean Server Connection we defined at the start. That class is as follows

package oracle.support.rda.spring.invoker;
package oracle.support.rda.spring.invoker;

import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import oracle.support.rda.server.ServerConnection;
import oracle.support.rda.spring.exception.RDAQueryException;
import oracle.support.rda.spring.queries.RDAQuery;

@SuppressWarnings("unchecked")
public class QueryInvoker {

    private final Logger logger = Logger.getLogger(this.getClass().getName());
    private ServerConnection serverConnection;
    private List<RDAQuery> rdaQueries = new ArrayList<RDAQuery>();
    
    /**
     * @param rdaQueries the rdaQueries to set
     */
    public void setRdaQueries(List rdaQueries) 
    {
      System.out.println(rdaQueries.toString());
      this.rdaQueries = rdaQueries;
    }

    /**
     * @param mbeanServerConnection the mbeanServerConnection to set
     */
    public void setServerConnection(ServerConnection serverConnection) 
    {
      this.serverConnection = serverConnection;
    }

    public QueryInvoker() 
    {
      // TODO Auto-generated constructor stub
      logger.setLevel(Level.ALL);
    }
    
    public int getQueryCount() 
    {
      return rdaQueries.size();
    }
    
    public void run() 
    {
      for(RDAQuery query: rdaQueries) 
      {
          
        try 
        {
            System.out.println(query.invoke(serverConnection.getConnection()));    
        } 
        catch (RDAQueryException e) 
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
      }
    }
       
}

Output of this running is as follows against OC4J 10.1.3.x in an OPMN managed environment.

C:\jdev\ant-demos\11gFMW\fmw-rda>ant run-rda
Buildfile: build.xml

init:
    [mkdir] Created dir: C:\jdev\ant-demos\11gFMW\fmw-rda\dist
    [mkdir] Created dir: C:\jdev\ant-demos\11gFMW\fmw-rda\classes

compile:
    [javac] Compiling 17 source files to C:\jdev\ant-demos\11gFMW\fmw-rda\classes
    [javac] Note: Some input files use unchecked or unsafe operations.
    [javac] Note: Recompile with -Xlint:unchecked for details.
     [copy] Copying 1 file to C:\jdev\ant-demos\11gFMW\fmw-rda\classes

package:
      [jar] Building jar: C:\jdev\ant-demos\11gFMW\fmw-rda\dist\fmwrda.jar

run-rda:
     [java] 17/10/2010 8:17:48 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
     [java] INFO: Loading XML bean definitions from class path resource [query-beans.xml]
     [java] 17/10/2010 8:17:49 PM oracle.support.rda.server.ServerConnectionBase doConnection
     [java] INFO: Service URL Path = /opmn://beast.au.oracle.com:6003/home
     [java] [oracle.support.rda.spring.queries.GenericQuery@6401d98a, oracle.support.rda.spring.queries.GenericQuery@35712651]
     [java] Queries: 2
     [java] 17/10/2010 8:17:54 PM oracle.support.rda.spring.queries.GenericQuery invoke
     [java] INFO: Generic Query called
     [java]
     [java] [MBean: oc4j:j2eeType=ThreadPool,name=http,J2EEServer=standalone]
     [java] 17/10/2010 8:17:55 PM oracle.support.rda.spring.queries.GenericQuery invoke
     [java] INFO: Generic Query called
     [java]
     [java] *** Attributes ***
     [java]                             (int) minPoolSize                   : 0
     [java]                            (long) poolSize                      : 11
     [java]             ([Ljava.lang.String;) executingThreadNames          : com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@93
c4f1=Thread[JMSServer[beast.au.oracle.com:12601],5,HTTPThreadGroup], com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@1e2299d=Thr
ead[RMIClientConnectionThread-HTTPThreadGroup-10,5,HTTPThreadGroup], com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@d844a9=Thre
ad[RMIServer [/0.0.0.0:12401] count:1,5,HTTPThreadGroup], com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@aa3e6e=Thread[RMIServe
rConnectionThread-7,5,HTTPThreadGroup], com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@56cf01=Thread[RMICallHandler-41,5,HTTPTh
readGroup], com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@83e5f1=Thread[RMIServer [/0.0.0.0:12701] count:1,5,HTTPThreadGroup],
 com.evermind.util.ReleasableResourcePooledExecutor$MyWorker@3de6df=Thread[RMIClientConnectionThread-HTTPThreadGroup-8,5,HTTPThreadGroup], c
om.evermind.util.ReleasableResourcePooledExecutor$MyWorker@12cc81d=Thread[RMIServerConnectionThread-39,5,HTTPThreadGroup], com.evermind.util
.ReleasableResourcePooledExecutor$MyWorker@e834e4=Thread[RMIClientConnectionThread-RMICallHandler-21,5,HTTPThreadGroup], com.evermind.util.R
eleasableResourcePooledExecutor$MyWorker@1a04c26=Thread[RMIClientConnectionThread-RMICallHandler-4,5,HTTPThreadGroup], com.evermind.util.Rel
easableResourcePooledExecutor$MyWorker@ca22a=Thread[RMIServerConnectionThread-9,5,HTTPThreadGroup],
     [java]                (java.lang.String) name                          : http
     [java]                             (int) queueCapacity                 : 0
     [java]                (java.lang.String) objectName                    : oc4j:j2eeType=ThreadPool,name=http,J2EEServer=standalone
     [java]                             (int) maxPoolSize                   : 1024
     [java]                         (boolean) stateManageable               : false
     [java]                            (long) keepAliveTime                 : 600000
     [java]                         (boolean) eventProvider                 : false
     [java]     (javax.management.ObjectName) ObjectName                    : oc4j:j2eeType=ThreadPool,name=http,J2EEServer=standalone
     [java]                             (int) queueSize                     : 0
     [java]                         (boolean) statisticsProvider            : false
     [java]                         (boolean) debug                         : false
     [java]
     [java]
     [java] [MBean: oc4j:j2eeType=J2EEServer,name=standalone]
     [java]
     [java] *** Attributes ***
     [java]  ([Ljavax.management.ObjectName;) DeployedObjects               : [Ljavax.management.ObjectName;@30e34726
     [java]                (java.lang.String) serverBuildDate               : 090727
     [java]  ([Ljavax.management.ObjectName;) J2eeWebSites                  : [Ljavax.management.ObjectName;@1b980630
     [java]  ([Ljavax.management.ObjectName;) Resources                     : [Ljavax.management.ObjectName;@1b45e2d5
     [java]  ([Loracle.oc4j.admin.management.shared.SharedLibrary;) sharedLibraries               : [Loracle.oc4j.admin.management.shared.Sh
aredLibrary;@581de498
     [java]                             (int) state                         : 1
     [java]                         (boolean) dmsOn                         : true
     [java]             ([Ljava.lang.String;) deployedObjects               : oc4j:j2eeType=EJBModule,name="admin_ejb",J2EEApplication=syste
m,J2EEServer=standalone, oc4j:j2eeType=EJBModule,name="jmsrouter_ejb",J2EEApplication=default,J2EEServer=standalone, oc4j:j2eeType=EJBModule
,name="SessionEJB",J2EEApplication=SessionEJB,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=web,J2EEApplication=mapviewer,J2EEServer=s
tandalone, oc4j:j2eeType=WebModule,name=WebServices,J2EEApplication=WSRocks,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=petercrap,J2
EEApplication=petercrap,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=wsil-ias,J2EEApplication=WSIL-App,J2EEServer=standalone, oc4j:j2
eeType=WebModule,name=webapp1,J2EEApplication=testhtml,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=sunilcrap,J2EEApplication=sunilcr
ap,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=WebServices,J2EEApplication=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeTy
pe=WebModule,name=javasso-web,J2EEApplication=javasso,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=JMXSoapAdapter-web,J2EEApplication
=system,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=webapp,J2EEApplication=datatags,J2EEServer=standalone, oc4j:j2eeType=WebModule,n
ame=dms,J2EEApplication=system,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=defaultWebApp,J2EEApplication=default,J2EEServer=standalo
ne, oc4j:j2eeType=WebModule,name=SupportWar,J2EEApplication=Support,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=jmsrouter_web,J2EEAp
plication=default,J2EEServer=standalone, oc4j:j2eeType=WebModule,name=webapp1,J2EEApplication=secDemo,J2EEServer=standalone, oc4j:j2eeType=W
ebModule,name=ascontrol,J2EEApplication=ascontrol,J2EEServer=standalone, oc4j:j2eeType=ResourceAdapterModule,name=simpleOemsRA,J2EEApplicati
on=default,J2EEServer=standalone, oc4j:j2eeType=ResourceAdapterModule,name=OracleASjms,J2EEApplication=default,J2EEServer=standalone, oc4j:j
2eeType=J2EEApplication,name=system,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=WSIL-App,J2EEServer=standalone, oc4j:j2eeType=
J2EEApplication,name=petercrap,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=default,J2EEServer=standalone, oc4j:j2eeType=J2EEAp
plication,name=mapviewer,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=Support,J2EEServer=standalone, oc4j:j2eeType=J2EEApplicat
ion,name=secDemo,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=javasso,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name
=WSRocks,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=datatags,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=sunilc
rap,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,na
me=testhtml,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=SessionEJB,J2EEServer=standalone, oc4j:j2eeType=J2EEApplication,name=a
scontrol,J2EEServer=standalone,
     [java]     (javax.management.ObjectName) ObjectName                    : oc4j:j2eeType=J2EEServer,name=standalone
     [java]             ([Ljava.lang.String;) j2eeWebSites                  : oc4j:j2eeType=J2EEWebSite,name=default-web-site,J2EEServer=sta
ndalone,
     [java]                (java.lang.String) serverVendor                  : Oracle Corp.
     [java]                (java.lang.String) serverVersion                 : 10.1.3.5.0
     [java]                         (boolean) statisticsProvider            : false
     [java]                            (long) startTime                     : 1286851282887
     [java]                (java.lang.String) instanceName                  : home
     [java]                (java.lang.String) defaultRoutingId              : g_rt_id
     [java]  ([Ljavax.management.ObjectName;) JavaVMs                       : [Ljavax.management.ObjectName;@edc86eb
     [java]  ([Loracle.oc4j.admin.management.shared.InstalledLibrary;) installedLibraries            : [Loracle.oc4j.admin.management.shared
.InstalledLibrary;@6f7918f0
     [java]                (java.lang.String) oracleHome                    : /home/u01/app/oracle/product/1013AS_blue
     [java]                (java.lang.String) objectName                    : oc4j:j2eeType=J2EEServer,name=standalone
     [java]             ([Ljava.lang.String;) resources                     : oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-11
gr2",J2EEApplication=sunilcrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-11gr1",J2EEApplication=pet
ercrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-11gr1",J2EEApplication=AdrianTest-Project1-WS,J2EE
Server=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-11gr2",J2EEApplication=petercrap,J2EEServer=standalone, oc4j:
j2eeType=JDBCResource,name="jdev-connection-pool-srs-10g",J2EEApplication=sunilcrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="
jdev-connection-pool-coherence-11gr2",J2EEApplication=sunilcrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool
-scott-11gr2",J2EEApplication=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-11gr
1",J2EEApplication=sunilcrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="Example OCI Connection Pool",J2EEApplication=default,J2
EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-pas-11gr2",J2EEApplication=sunilcrap,J2EEServer=standalone, oc4j:
j2eeType=JDBCResource,name="jdev-connection-pool-srs-10g",J2EEApplication=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeType=JDBCRe
source,name="jdev-connection-pool-srs-10g",J2EEApplication=petercrap,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="oemsdbPool",J2E
EApplication=default,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-10g",J2EEApplication=sunilcrap,J2EES
erver=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-pas-11gr2",J2EEApplication=petercrap,J2EEServer=standalone, oc4j:j2e
eType=JDBCResource,name="jdev-connection-pool-pas-11gr2",J2EEApplication=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeType=JDBCRes
ource,name="jdev-connection-pool-scott-10g",J2EEApplication=AdrianTest-Project1-WS,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="s
cottPool",J2EEApplication=default,J2EEServer=standalone, oc4j:j2eeType=JDBCResource,name="jdev-connection-pool-scott-10g",J2EEApplication=pe
tercrap,J2EEServer=standalone, oc4j:j2eeType=JNDIResource,name="AdrianTest-Project1-WS",J2EEServer=standalone,applicationName=AdrianTest-Pro
ject1-WS, oc4j:j2eeType=JNDIResource,name="ascontrol",J2EEServer=standalone,applicationName=ascontrol, oc4j:j2eeType=JNDIResource,name="pete
rcrap",J2EEServer=standalone,applicationName=petercrap, oc4j:j2eeType=JNDIResource,name="javasso",J2EEServer=standalone,applicationName=java
sso, oc4j:j2eeType=JNDIResource,name="sunilcrap",J2EEServer=standalone,applicationName=sunilcrap, oc4j:j2eeType=JNDIResource,name="Support",
J2EEServer=standalone,applicationName=Support, oc4j:j2eeType=JNDIResource,name="datatags",J2EEServer=standalone,applicationName=datatags, oc
4j:j2eeType=JNDIResource,name="WSRocks",J2EEServer=standalone,applicationName=WSRocks, oc4j:j2eeType=JNDIResource,name="WSIL-App",J2EEServer
=standalone,applicationName=WSIL-App, oc4j:j2eeType=JNDIResource,name="secDemo",J2EEServer=standalone,applicationName=secDemo, oc4j:j2eeType
=JNDIResource,name="default",J2EEServer=standalone,applicationName=default, oc4j:j2eeType=JNDIResource,name="testhtml",J2EEServer=standalone
,applicationName=testhtml, oc4j:j2eeType=JNDIResource,name="mapviewer",J2EEServer=standalone,applicationName=mapviewer, oc4j:j2eeType=JNDIRe
source,name="SessionEJB",J2EEServer=standalone,applicationName=SessionEJB, oc4j:j2eeType=JTAResource,name="oc4j-tm",J2EEServer=standalone, o
c4j:j2eeType=JMSResource,name="JMS",J2EEServer=standalone, oc4j:j2eeType=JMSAdministratorResource,name="JMSAdministrator",J2EEServer=standal
one, oc4j:j2eeType=JCAResource,name=JCAResource,ResourceAdapter=OJMS RA,ResourceAdapterModule=simpleOemsRA,J2EEApplication=default,J2EEServe
r=standalone, oc4j:j2eeType=JCAResource,name=JCAResource,ResourceAdapter=OracleASjms,ResourceAdapterModule=OracleASjms,J2EEApplication=defau
lt,J2EEServer=standalone,
     [java]                         (boolean) stateManageable               : true
     [java]     (javax.management.ObjectName) defaultApplication            : oc4j:j2eeType=J2EEApplication,name=default,J2EEServer=standalo
ne
     [java]             ([Ljava.lang.String;) javaVMs                       : oc4j:j2eeType=JVM,name=single,J2EEServer=standalone,
     [java]                (java.lang.String) node                          : beast.au.oracle.com
     [java]                         (boolean) eventProvider                 : true
     [java]

BUILD SUCCESSFUL
Total time: 15 seconds
C:\jdev\ant-demos\11gFMW\fmw-rda>

If you wanted to create queries against a Coherence Server your query-beans.xml file would look like this. The idea here is to specifically query only the MBeans your interested in so you could easily tailor it to meet your needs such as monitoring a JDBC connection pool. The bean with id "showMBean" is used to view all available MBeans you can use.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>

  <!--  This creates a a basic ServerConnection object using no username/password -->
  <bean id="serverConnection"
        class="oracle.support.rda.coherence.ConnectionFactory"
        factory-method="getCoherenceConnection"
        singleton="true">
  </bean>
 
  <!--  This section defines the set of Queries to execute, add/remove easily!-->
  <bean id="clusterQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>Coherence:type=Cluster</value>
    </property>
    <!--
    The following properties will not be displayed, giving you
    control over what information you show.
    -->
    <property name="nukedAttributes">
      <list>
        <value>MembersDeparted</value> 
      </list>
    </property>
  </bean>

  <bean id="managementQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>Coherence:type=Management</value>
    </property>
  </bean>

  <bean id="reporterQuery" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>Coherence:type=Reporter</value>
    </property>
  </bean>

  <bean id="runtimeQuery1" class="oracle.support.rda.spring.queries.GenericQuery">
    <property name="MBeanName">
      <value>Coherence:type=Platform,Domain=java.lang,subType=Runtime,nodeId=1</value>
    </property>
    <property name="nukedAttributes">
      <list>
        <value>SystemProperties</value> 
      </list>
    </property>   
  </bean>
 
  <!-- 
    This defines the Invoker object, which gets passed
    a reference to the ServerConnection AND the list of queries
   -->
  <bean id="queryInvoker" class="oracle.support.rda.spring.invoker.QueryInvoker">
    <property name="serverConnection" ref="serverConnection"/>
    <property name="rdaQueries">
      <list>
        <ref bean="clusterQuery"/>
        <ref bean="managementQuery"/>
        <ref bean="reporterQuery"/>
        <ref bean="runtimeQuery1"/>
      </list>
    </property>
  </bean>  

  <!-- 
   Bean used to list of MBeans available for use
   -->
  <bean id="showMBeans" class="oracle.support.rda.spring.invoker.MBeanViewer">
    <property name="serverConnection" ref="serverConnection"/>
  </bean>
 
</beans>

Running Coherence Query/Command line clients in JDeveloper 11g

Always wanted to run the command line client and query client from JDeveloper but because JDeveloper does not allow you to specify a runnable class which belongs in a JAR file this is not possible. However thanks to the help of an internal employee I found you can do it as follows

The two clients are these two scripts below which use a class in coherence.jar as follows

coherence.cmd - com.tangosol.net.CacheFactory

query.cmd (New In Coherence 3.6) - com.tangosol.coherence.dslquery.QueryPlus

1. Create a new empty project in JDeveloper
2. Add 2 classes as follows for each client.

coherence.cmd - com.tangosol.net.CacheFactory

package pas.au.coherence.utils;

import com.tangosol.net.CacheFactory;

public class JdevCoherenceClient extends CacheFactory
{
  public JdevCoherenceClient()
  {
  }
}

query.cmd (New In Coherence 3.6) - com.tangosol.coherence.dslquery.QueryPlus

package pas.au.coherence.utils;

import com.tangosol.coherence.dslquery.QueryPlus;

public class JdevCoherenceQueryClient extends QueryPlus
{
  public JdevCoherenceQueryClient()
  {
  }
}

3.At a minimum if we use the default cache config file in coherence.jar we simply need to create a run/debug/profile for each client as follows. This is the example for the QueryPlus client where the config is called "CoherenceQueryClient"

Notice how I have set the JVM propery -Dtangosol.coherence.ttl=0 to avoid joining any clusters outside my own machine. Basically this will ensure no packets are sent from my machine. You would specify any other properties here for coherence such as -Dtangosol.coherence.cacheconfig if you have your own config file. Also the "Default Run target" is set to the class we created above which will then use the class we extended.

4. In that dialog select "Tool Settings" and ensure "Allow program Input" is checked as we need to type at the command prompt.

5. Now run your config select the Run icon in the toolbar and then selecting one of the 2 config you created.

Output in the log window would be as follows

Coherence - Continuous Query Caching

Coherence provides the ability to have a cache provide a feature that combines a query result with a continuous stream of related events to maintain an up-to-date query result in a real-time fashio. To demonstrate this here is a simple demo.

1. Create a very simple object which contains a single attribute "gender" for either male's or females.

package pas.au.coherence.querycaching;

public class DemoObject implements java.io.Serializable
{
  private String gender;

  public DemoObject ()
  {  
  }

  public DemoObject (String _gender)
  {  
   gender = _gender;
  }
  
  public String getGender() 
  {
    return gender;
  }

  public void setGender(String gender) 
  {
    this.gender = gender;
  }
  
}

2. Create a simple test class as follows.

package pas.au.coherence.querycaching;

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;
import com.tangosol.net.cache.ContinuousQueryCache;
import com.tangosol.util.Filter;
import com.tangosol.util.filter.EqualsFilter;

import java.util.logging.Level;
import java.util.logging.Logger;

public class ContinuousQueryCacheDemo
{
  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
  
  public ContinuousQueryCacheDemo()
  {
  }

  public void doLogMessage (String message)
  {
    logger.log (Level.INFO, message);  
  }
  
  public void run()
  {
    NamedCache test = CacheFactory.getCache("mycache");
    
    // put some data in
    test.put("1", new DemoObject("male"));
    test.put("2", new DemoObject("female"));
    test.put("3", new DemoObject("female"));
    test.put("4", new DemoObject("female"));
    test.put("5", new DemoObject("female"));
    test.put("6", new DemoObject("male"));
    
    doLogMessage("Size of mycache [mycache] = " + test.size());
    
    Filter filter = new EqualsFilter("getGender", "male");
    // Create Continuous Query Cache
    ContinuousQueryCache allMales =
      new ContinuousQueryCache(test, filter);
    
    doLogMessage("Created Query Cache with just MALES [allmales]");
    doLogMessage("Size of Continuous Query Caching [allMales] = " + allMales.size());
    
    // add another male entry to test cache
    test.put("7", new DemoObject("male"));
    
    doLogMessage("New MALE object added");
    
    // check if Continuous Query Caching has that entry added
    doLogMessage("Size of Continuous Query Cache [allMales] = " + allMales.size());
    
    doLogMessage("all done.."); 
  }
  
  public static void main(String[] args) 
  {
    ContinuousQueryCacheDemo test = new ContinuousQueryCacheDemo();
    test.run();
  }
}  

3.When you run this example you can see how adding objects to the named cache "mycache" it automatically maintains the ContinuousQueryCache locally on the client.

OUTPUT from an ANT client

....

     [java]   ThisMember=Member(Id=1, Timestamp=2010-09-27 09:36:06.633, Address=10.187.114.243:8088, MachineId=50163, L
ocation=machine:paslap-au,process:4176, Role=PasAuContinuousQueryCacheDemo)
     [java]   OldestMember=Member(Id=1, Timestamp=2010-09-27 09:36:06.633, Address=10.187.114.243:8088, MachineId=50163,
 Location=machine:paslap-au,process:4176, Role=PasAuContinuousQueryCacheDemo)
     [java]   ActualMemberSet=MemberSet(Size=1, BitSetCount=2
     [java]     Member(Id=1, Timestamp=2010-09-27 09:36:06.633, Address=10.187.114.243:8088, MachineId=50163, Location=m
achine:paslap-au,process:4176, Role=PasAuContinuousQueryCacheDemo)
     [java]     )
     [java]   RecycleMillis=1200000
     [java]   RecycleSet=MemberSet(Size=0, BitSetCount=0
     [java]     )
     [java]   )
     [java]
     [java] TcpRing{Connections=[]}
     [java] IpMonitor{AddressListSize=0}
     [java]
     [java] 2010-09-27 09:36:10.003/4.197 Oracle Coherence GE 3.6.0.0 (thread=Invocation:Management, member=1): Ser
vice Management joined the cluster with senior service member 1
     [java] 2010-09-27 09:36:10.174/4.368 Oracle Coherence GE 3.6.0.0 (thread=DistributedCache, member=1): Service
DistributedCache joined the cluster with senior service member 1
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: Size of mycache [mycache] = 6
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: Created Query Cache with just MALES [allmales]
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: Size of Continuous Query Caching [allMales] = 2
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: New MALE object added
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: Size of Continuous Query Cache [allMales] = 3
     [java] 27/09/2010 9:36:10 AM pas.au.coherence.querycaching.ContinuousQueryCacheDemo doLogMessage
     [java] INFO: all done..
     [java]


More info on this as follows

Oracle® Coherence Developer's Guide
Release 3.6
Part Number E15723-01
http://download.oracle.com/docs/cd/E15357_01/coh.360/e15723/api_continuousquery.htm

Display Cache Scheme from Coherence

Whenever I run coherence.cmd (windows) and I ask for a NamedCache using "cache pastest" for example it prints out the current scheme which I thought was handy for my own testing. After help from Patrick here is what your own code would like like to get that information yourself, if required.

 Note: I was using Coherence 3.6 but same code should work in 3.5 as well

1. Create a class as follows.

package pas.au.coherence.utils;

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;
import com.tangosol.net.DefaultConfigurableCacheFactory.CacheInfo;
import com.tangosol.net.DefaultConfigurableCacheFactory;

public class DisplaySchemeName 
{
    private static final String CACHE_NAME = "repl-pas";
  
    public DisplaySchemeName() 
    {
    }

    public static void main(String[] args) 
    {
      // TODO Auto-generated method stub
      NamedCache pastest = CacheFactory.getCache(CACHE_NAME);
      
      DefaultConfigurableCacheFactory factory = 
       (DefaultConfigurableCacheFactory) CacheFactory.getConfigurableCacheFactory();

      CacheInfo info = factory.findSchemeMapping(CACHE_NAME);
      
      System.out.println(String.valueOf(factory.resolveScheme(info)));
      System.out.println("all done..");
  
    }
}

2. Run it to verify it displays the cache scheme we are using as shown below.

....
....
TcpRing{Connections=[]}
IpMonitor{AddressListSize=0}

2010-09-20 12:43:17.259/4.898 Oracle Coherence GE 3.6.0.0 <D5> (thread=Invocation:Management, member=1): Service Management joined the cluster with senior service member 1
2010-09-20 12:43:17.463/5.102 Oracle Coherence GE 3.6.0.0 <D5> (thread=ReplicatedCache, member=1): Service ReplicatedCache joined the cluster with senior service member 1
<replicated-scheme>
  <scheme-name>example-replicated</scheme-name>
  <service-name>ReplicatedCache</service-name>
  <backing-map-scheme>
    <local-scheme>
      <scheme-ref>unlimited-backing-map</scheme-ref>
    </local-scheme>
  </backing-map-scheme>
  <autostart>true</autostart>
</replicated-scheme>
all done..
2010-09-20 12:43:17.510/5.149 Oracle Coherence GE 3.6.0.0 <D4> (thread=ShutdownHook, member=1): ShutdownHook: stopping cluster node
2010-09-20 12:43:17.510/5.149 Oracle Coherence GE 3.6.0.0 <D5> (thread=Cluster, member=1): Service Cluster left the cluster

Simple Java Stored procedure in JDeveloper 11g to Oracle 11g

The steps to create a Java Stored Procedure in JDeveloper 11g although similar to JDeveloper 10g have slightly changed. here is how to do this when deploying to a 11g R2 database. Couple of things to be aware of on the 11g RDBMS side.

Also now when you use a package the specification is created correctly to reference the correct package class name. In JDeveloper 10g you had to fix that manually.

1. Change your project J2SE version to JDK 1.5. can't use default 1.6 as Oracle JVM is not a 1.6 JVM,
it's a 1.5 JVM. By default JDeveloper 11g is using a 1.6 JDK which means the code you compile won't be able to be deployed to the Oracle JVM without switching to 1.5.

2. Create class as follows.

package pas.au.jsp;

public class DemoJSP
{
  public DemoJSP()
  {
  }
  
  public static String sayHello ()
  {
    return "Hello Pas";
  }
}

3. Right click on project node in navigator and select "New"
4. Select "Database Tier -> Database Files -> Load java and Java Stored procedures"
5. Click on "Ok"
6. Click on "Ok" again
7. Right click on profile storedProc1.dbexport and select "Add PLSQL Package"
8. Name it "HelloWorldPKG" and press ok
9. Right click on "HelloWorldPKG" and select "Add Stored procedure"
10. Select method sayHello and press OK
12. Right click on profile storedProc1 and select "Export to -> {your 11g database connection }"

JDeveloper Log window should show something as follows.

[12:00:54 PM] Invoking loadjava on connection 'scott-11gr2' with arguments:

[12:00:54 PM] -order -resolve -thin

[12:00:57 PM] Loadjava finished.

[12:00:58 PM] Executing SQL Statement:

[12:00:58 PM] CREATE OR REPLACE PACKAGE HELLOWORLDPKG

AUTHID CURRENT_USER AS FUNCTION sayHello RETURN VARCHAR2; END HELLOWORLDPKG;

[12:00:58 PM] Success.

[12:00:58 PM] Executing SQL Statement:

[12:00:58 PM] CREATE OR REPLACE PACKAGE BODY HELLOWORLDPKG AS FUNCTION sayHello RETURN VARCHAR2

AS LANGUAGE JAVA NAME 'pas.au.jsp.DemoJSP.sayHello() return java.lang.String'; END HELLOWORLDPKG;

[12:00:58 PM] Success.

[12:00:58 PM] Publishing finished.

[12:00:58 PM] ----  Stored procedure database export finished.  ----

13. From SQL*Plus invoke Java Stored procedure as follows.

d:\temp>sqlplus scott/tiger@linux11gr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 19 12:02:13 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@linux11gr2> select HELLOWORLDPKG.sayHello from dual;

SAYHELLO
-------------------------------------------------------------------------------

Hello Pas

SCOTT@linux11gr2>

Jetty 6.x UCP Data Source Setup

Here is how I setup Jetty 6.1 to use UCP (Universal Connection Pool) data source. Like tomcat it was a straight forward process. I have never used Jetty before so no doubt I may have put files in the wrong places but it still did work in the end.

1. Edit etc/jetty-plus.xml to add my data source config as follows.

<!-- Add a UCP DataSource -->
<New id="scott-oracle-ucp" class="org.mortbay.jetty.plus.naming.Resource">
  <Arg>jdbc/UCPPool</Arg>
  <Arg>
   <New class="oracle.ucp.jdbc.PoolDataSourceImpl">
    <Set name="connectionFactoryClassName">oracle.jdbc.pool.OracleDataSource</Set>
    <Set name="inactiveConnectionTimeout">20</Set>
    <Set name="user">scott</Set>
    <Set name="password">tiger</Set>
    <Set name="URL">jdbc:oracle:thin:@beast.au.oracle.com:1523/linux11gr2</Set>
    <Set name="minPoolSize">2</Set>
    <Set name="maxPoolSize">5</Set>
    <Set name="initialPoolSize">2</Set>
   </New>
  </Arg>
 </New> 

2. Copy ojdbc6.jar and ucp.jar into $JETTY_HOME\lib\ext directory. You can download those JAR files from here.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

Note: We started Jetty using JDK 1.6 so that we could use ojdbc6.jar. 

3. Start Jetty as follows ensuring we use our jetty-plus.xml file on the startup command line.

java -jar start.jar etc/jetty.xml etc/jetty-plus.xml

4.  Now simply deploy an application which will use the data source to ensure it is created. Then use JConsole to verify indeed your using UCP as shown below.

UTL_DBWS : Handling Web Service methods that accept XML Input parameters

Dealing with Web Services callouts using the UTL_DBWS package requires some thought when calling methods which accept XML data such as "org.w3c.dom.Element". In this example I show how to ensure the XML data within your XML file is correctly escaped prior to sending the request to the Web Service.

An example of UTL_DBWS package can be found on steve's blog here. That shows how to set it up at the database level in 11g.

Lets assume we have a Web Service deployed to OAS 10.1.3.x with a class as follows. Basic method which takes a org.w3c.dom.Element parameter for it's only service method testXML.

package pas.au.xml.ws;

import org.w3c.dom.Element;

public class XMLWebService
{
  public XMLWebService()
  {
  }
  
  public String testXML (Element xmlInput)
  {
    return "success";
  }
}

With UTL_DBWS installed we could simply write a PLSQL block as follows to call out Web Service method and the output clearly shows this works fine.

set serveroutput on size 100000
set linesize 130

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              myXML||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
        response.extract('//ns0:result/child::text()',
        'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Output

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>

The problem here is we can't just assume that the data which we pass into theWeb Service method is not a special character which in the XML world would require that it be escaped. The data we passed in our first test was a simple string such as "pas" as shown below.

myXML varchar2(100) := 'pas';

Lets assume we now want to pass data as follows by introducing a & character. The assumption here is we can't control what is passed to the method so it may be a special character which XML won't accept.

myXML varchar2(100) := 'pas&';

The result when running the PLSQL Block now is a runtime exception as follows.

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 29


No errors.
SCOTT@linux11gr2>

In fact if you try this outside of PLSQL / Oracle RDBMS itself, such as a J2SE client you would get a runtime exception as follows. The database error was a lot more useful then this.

java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to get header stream in saveChanges. SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.. java.io.IOException: SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.

So the solution around this is to use the package method dbms_xmlgen.CONVERT to convert the XML data into the escaped XML equivalent. So now our SQL is as follows

set serveroutput on size 100000
set linesize 130
call dbms_java.set_output(100000);

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas&';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  --add to convert XML with special characters correctly escaped
  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              dbms_xmlgen.CONVERT(myXML, dbms_xmlgen.ENTITY_ENCODE)||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
         response.extract('//ns0:result/child::text()',
          'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Note: I added a call to call dbms_java.set_output(100000); prior to running the BLOCK so we could see our REQUEST/RESPONSE XML to verify it has done what we needed it to do.

And the output now shows it works correctly.

Output

SCOTT@linux11gr2> @invoke_xmlws2.sql

Call completed.

Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@7e6efb86
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@917ab4b8
*** Created service: -1261915451 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c ***
ServiceProxy.get(-1261915451) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c
setProperty(javax.xml.rpc.soap.operation.style, document)
Preparing request
dbwsproxy.add.map: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:testXMLElement,null,http://pas.au.xml.ws/types/)
dbwsproxy.add.soap.element.namespace: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.element.node.child.0: 1, null
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:xmlInput,null,http://pas.au.xml.ws/types/)
dbwsproxy.element.node.child.0: 1, null
createElement(pas,null,null)
dbwsproxy.text.node.child.0: 3, pas&
request:
<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/">
<ns1:xmlInput>
<pas>pas&amp;</pas>
</ns1:xmlInput>
</ns1:testXMLElement>
response:
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">
<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>

Coherence Extend client from Oracle 11g RDBMS

Given coherence JAR file isn't that large I have always wanted a coherence client to run from the database itself by connecting to extend proxy client. Here is how I did this and what is required on the Oracle DB side to ensure you can run java code within the database to connect to a coherence extend proxy client.

Note: This was done with Oracle 11g r2 (11.2.0.1) RDBMS. The JVM in the DB is a 1.5 JVM version. So you won't be able to use JDK 1.6 for this.

1. First we create a new schema called COHERENCE in our database. The script to create this user is as follows.

drop user coherence cascade;

create user coherence identified by coherence
default tablespace users
temporary tablespace temp;

grant dba to coherence;

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*', 'read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','getClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','createClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.net.SocketPermission','*','listen,connect,resolve');

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*','read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','setFactory','');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'localhost:8088', 'listen,resolve' );

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override-dev.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/custom-mbeans.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

execute dbms_java.grant_permission('COHERENCE', 'SYS:java.security.SecurityPermission', 'getDomainCombiner', '' );

prompt
prompt COHERENCE user ready to roll
prompt
prompt all done..

As you can see I granted DBA to the user coherence only because I wanted to get this up and running quickly but in theory the DBA role should not be required. Also all those privileges are required in order to create a socket connection to the extend proxy client.

2. Load the following 3 JAR files into the database using the new user coherence. In order for the database to load/resolve coherence.jar it will require the log4j and sleepycat JAR files. I tried to avoid loading them as coherence outside of the database runs fine with just coherence.jar but inside the database it needed to compile/resolve the class files which meant it needed those 2 JAR files to do that. To me it's not a big issue as there quite small JAR files as well.

loadjava -u coherence/coherence -r -v -f -s -grant public log4j-1.2.15.jar

loadjava -u coherence/coherence -r -v -f -s -grant public je.jar

loadjava -u coherence/coherence -r -v -f -s -grant public coherence.jar

Note: There will be some errors resolving some of the classes but the main ones needed will resolve/compile fine so just ignore the errors. In fact a query as follow should show most of classes as VALID.

COHERENCE@linux11gr2> @check

STATUS    COUNT(*)
------- ----------
INVALID         11
VALID         3200


Java Name                                                    STATUS
------------------------------------------------------------ ----------
com/tangosol/util/internal/SunMiscCounter                    INVALID
com/tangosol/util/internal/SunMiscCounter$1                  INVALID
com/sleepycat/persist/model/ClassEnhancerTask                INVALID
org/apache/log4j/jmx/Agent                                   INVALID
com/tangosol/run/jca/CacheAdapter                            INVALID
com/tangosol/run/jca/CacheAdapter$CacheConnectionSpec        INVALID
com/tangosol/engarde/websphere/SecurityHelper                INVALID
com/tangosol/engarde/StubManagerHookup                       INVALID
com/tangosol/engarde/StubManager                             INVALID
com/tangosol/engarde/EjbRouter                               INVALID
com/tangosol/engarde/DebugStubManagerHookup                  INVALID

11 rows selected.

COHERENCE@linux11gr2> l
  1  select dbms_java.longname(object_name) "Java Name", status
  2  from user_objects
  3  where object_type like '%JAVA%'
  4* and status = 'INVALID'

3.Now we have a proxy client running on "papicell-au.au.oracle.com:9099" which is storage disabled but we have other cluster nodes which are storage enabled. In this setup the extend proxy client  is the node the database client will connect to and hence why we set it up with this privilege.

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

4. Now we are ready to actually create a Java Stored procedure which will connect to the extend proxy client and put/get data off the cache. The code for this is as follows and must be loaded into the database.

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;

public class CoherenceClient
{
  public CoherenceClient()
  {
  }
  
  public static void getData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    System.out.println("Key 1 = " + cache.get(1));
    
    CacheFactory.shutdown();
  }
  
  public static void putData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    cache.put(1, "Pas Apicella");
    
    CacheFactory.shutdown();   
  }
} 

5. The client cache config file which needs to be loaded into the database with the Java Class is as follows. You can see it connects to "papicell-au.au.oracle.com:9099" which is our extend proxy client host:port. This file is called "client-cache-config.xml" which is loaded into the database itself.

<?xml version="1.0"?>
<?xml version="1.0"?>

<!DOCTYPE cache-config SYSTEM "cache-config.dtd">

<cache-config>
  <caching-scheme-mapping>
    <cache-mapping>
      <cache-name>*</cache-name>
      <scheme-name>remote</scheme-name>
    </cache-mapping>
  </caching-scheme-mapping>
  <caching-schemes>
    <remote-cache-scheme>
      <scheme-name>remote</scheme-name>
      <initiator-config>
        <tcp-initiator>
          <remote-addresses>
            <socket-address>
              <address>papicell-au.au.oracle.com</address>
              <port>9099</port>
              <reusable>true</reusable>
            </socket-address>
          </remote-addresses>
        </tcp-initiator>
      </initiator-config>
    </remote-cache-scheme>
  </caching-schemes>
</cache-config>

6. The Java Stored procedure was created in JDeveloper 10g given we are using JDK 1.5 that made it easier to deploy from JDeveloper itself. The project is as follows. In this setup we simply exposed the Java Stored procedure static methods as 2 PLSQL procedures, but it would make sense to use a PLSQL package here rather then stand alone procedures.

7. You will see we reference the client cache config file as follows in our Java Stored procedure which is how it's done in the Oracle JVM.

System.setProperty("tangosol.coherence.cacheconfig", 

                               "jserver:/resource/schema/COHERENCE/client-cache-config.xml");

8. Finally we are ready to invoke our Java Stored Procedure which was loaded as follows. By creating a Java Stored procedure we are making it available in SQL terms. This means any client can invoke the coherence client , meaning even a PLSQL client if it wanted to or a .NET client. You simple call the stored procedures like any other procedures in the database. Basically when we test this we will use SQL*Plus to verify it works.

JDEV Log window which shows a successful deployment.

Invoking loadjava on connection 'coherence-11gr2' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE getData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.getData()';
Success.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE putData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.putData()';
Success.
Publishing finished.
----  Stored procedure deployment finished.  ----

9. Now we can write a very basic SQL file as follows which will put data onto the coherence cache and then retrieve it calling our 2 stored procedures.

set serveroutput on
call dbms_java.set_output(100000);

exec putData;
exec getData;

SQL*PLus Output

COHERENCE@linux11gr2> @run

Call completed.

2010-08-04 14:15:19.750/0.077 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:19.755/0.082 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:19.756/0.083 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Optional configuration
override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:19.758/0.085 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Optional configuration
override "/custom-mbeans.xml" is not specified
Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
2010-08-04 14:15:19.876/0.203 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:19.975/0.302 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:19.976/0.303 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:19.979/0.306 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
2010-08-04 14:15:20.013/0.340 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

2010-08-04 14:15:20.031/0.358 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:20.034/0.361 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:20.035/0.362 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Optional
configuration override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:20.036/0.363 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Optional
configuration override "/custom-mbeans.xml" is not specified
Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
2010-08-04 14:15:20.047/0.374 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:20.062/0.389 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:20.063/0.390 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:20.065/0.392 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
Key 1 = Pas Apicella
2010-08-04 14:15:20.082/0.409 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

COHERENCE@linux11gr2>

In this example we turn on debug output on the database side using DBMS_JAVA package to get console messages displayed when invoking the Java Stored procedure to verify indeed coherence is being used and what cache config file we are using. The fact that we now can access the coherence cluster from the database in SQL makes this worth the effort.

I also make sure I disconnect from the cluster once done in this simple example using "CacheFactory.shutdown();" in my Java Stored Procedures, assuming that database clients just need to read/put data from the cache and then there done.

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'
/

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

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>

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>

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