Tomcat Resource Configuration for Oracle Database Connection

Tomcat has a Database Connection Pool mechanism that will work for most of the database including Oracle. The mechanism described in the tomcat web site will work for most situations. Using the above method will make the code not depend on a specific database.

However there are situations where the application code is dependent on Oracle database. One such example is when you use Oracle Stored procedures and you need to use Oracle Cursor as on output parameter. In such case you will have to cast the Statement to Oracle specific oracle.jdbc.OracleCallableStatement class to get the cursor.

However if you simply cast the java.sql.CallableStatement to oracle.jdbc.OracleCallableStatement you may notice that you get a class cast exception if you configured your DataSource using the above method.

To overcome the class cast exception, you need to configure the data source using a Oracle specific connection factory. The following example will server that purpose. The text in bold are the changes pertaining to the Oracle connection factory.

<Resource name="jdbc/OracleDS" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
user="DBUser"
password="xxxxxx"
driverClassName="oracle.jdbc.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your.db.host)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=serviceName)))"
maxActive="?"
maxIdle="?"
maxWait="-1"/>

spring apache commons configuration

I was surprised to find out that integrating the apache commons configuration with the spring framework is not trivial.  I was hoping that there will be one bean that we can use out of the box within the spring framework or the apache commons framework and we set that as a property in the bean and we can go on.  Unfortunately it was not that easy.   It takes a few steps to get this working.  Here are the steps that you need to follow to get this integration working:

1) Define the properties beans in spring xml file (typically ApplicationConfiguration.xml file)
 as follows: 

 <!-- Apache Commons Configuration Composite configuration -->
    <bean id="configurations"
        class="org.springmodules.commons.configuration.CommonsConfigurationFactoryBean">
        <property name="configurations">
            <list>
                <bean class="org.apache.commons.configuration.PropertiesConfiguration">
                    <constructor-arg type="java.net.URL"
                        value="classpath:myconfiguration.properties" /> 
                    <property name="reloadingStrategy">
                        <bean class="org.apache.commons.configuration.reloading.FileChangedReloadingStrategy"/>
                    </property>
                </bean>
                <bean class="org.apache.commons.configuration.SystemConfiguration"/>               
            </list>
        </property>
        <!-- define configuration as a set of spring resources -->
    </bean>
    <bean id="configuration" class="org.apache.commons.configuration.Configuration" factory-bean="&amp;configurations" factory-method="getConfigurations"/>

2) Define a bean with the getter and setter that returns/accepts an array of org.apache.commons.configuration.Configuration class.  To make it easier for the rest of the code to get the configuration easy, you may want to add a utility method in there to return the combined Configutaion.  Here is an example:

private Configuration configs[] ;
private ConfigurationBuilder cfgBuilder ;
    public Configuration[] getConfigs() {
        return configs;
    }

    public void setConfigs(Configuration[] configs) {
        this.configs = configs;
        cfgBuilder = new ConfigurationBuilder();
        for (Configuration cgf: configs) {
            cfgBuilder.addConfiguration(cgf);
        }
    }

    public Configuration getConfig() {
        return cfgBuilder.getConfiguration();
    }

3) Define the bean properties in the spring xml file.  I suggest, that you may want to define the above method in a base class of all your beans and define it as an abstract bean.  This will enable you to use the properties in all your beans without having to define the properties in every bean.  Here is an example:

  <bean id="baseActionBean"  class="com.my.company.BaseAction"  abstract="true" >
            <property name="configs" ref="configuration" />
        </bean>
         <bean id="logonClass" class="com.my.company.UsefulAction"  parent="baseActionBean">
         </bean>

4)  Add the necessary libraries if you do not have them already.  Here is the list of jars you will need.

commons-lang-*.jar
commons-configuration*.jar
spring-modules-jakarta-commons*.jar

 With these changes, you should be able to use the properties in your beans with a code as simple as getConfig().getString("propertyKey")

Deleting old log files based on size and time

There are times when the log files take up too much space in the system and bring the system to run out of space.  Fortunately there are few steps you can take to keep the log files under control for development servers.  Here I have listed a simple strategy:
1) Create a dedicated partition for the log files so if the log files grow out of space, that still does not crash the system by making no space left for your data and configuration files.
2) Keep all log files for all applications under a common directory dedicated for logs.  Replace the location of the log files within your application with symbolic links to the common logs folder or change the log configuration files to directly point them to the log directory.
3) Create a simple script to delete files that are older than certain number of days.
4) For files that does not get rolled over by the application, have a script that trims the head of the file by certain percentage of their size when the file size grows over a certain size limit.

If you follow these simple steps, your log files and directories will stay tidy and you still have logs left when you need them to debug issues.   Here is a sample script that you can use to cleanup old files and trim larger files.

logdir="/tmp/test"
trimAmt=50
tempFile=/tmp/trimedfile.$$
sizeLimit=+1G
fileAge= +30

cd $logdir

find . -mtime $fileAge -exec rm -f {} \;

for FILENAME in $(find . -size $sizeLimit -print )
do
    filesize=$(wc -l $FILENAME |  awk '{print $1}')
    trimsize=$(( $filesize -($filesize / $trimAmt)))
    echo " $FILENAME $filesize $trimsize "
    tail -$trimsize $FILENAME > /tmp/trimedfile.$$
    cat $tempFile > $FILENAME
    rm  $tempFile
done

Java URL connection timeout - default timeout might save you from hanging

Many times, when you make a url connection or any connection that works over tcp ip (ftp, http etc) protocol, it is possible that your client just hangs.  It is frustrating to debug this kind of issue because, you do not get any exception from your application except it just hangs.  This can be a problem especially in production environments where there are firewalls between every single component and it is hard to trace network traffic.   

If you are using sun jdk.1.4 or above there is a way you can prevent your code from just hanging.  Best of all, you do not even have to make a code change.  The sun jvm has a way to specify default timeout values for the net client.   All you have to do is just add the following java parameters to the command line that starts your application.  

-Dsun.net.client.defaultConnectTimeout=TimeoutInMiliSec -Dsun.net.client.defaultReadTimeout=TimeoutInMiliSec 

 
This will force the client to timeout and hopefully your application logs the exception that gives enough clue to debug the issue.   For more information about specifying network properties in java check out the Networking Properties guide from sun/oracle.

Exporting ClearQuest records to Excel

If you need to export clearquest records to excel, the easy option is to use CSV format.  You can do that by using the "Save result set to file" option and select a delimiter of your choice.  Hoqwever, if one of your exported column is a multi line text, this may not provide the results you expect when you open the file in excel.

In that case you may want to export the data as xml file.  Use the "Rational ClearQuest Client"  (not the windows client) and click on the "export query results" icon above the query results pane.  This will open a window where you can select the export format.   In this window select the xml format and you can get the results as an xml file. (You can find detailed instruction on how to import here) However, the xml file is not very friendly to read.   You can create a simple xsl file and add a single line to the xml file to use the xsl file you just created to make the file more reader friendly.

Here is a sample xsl file you can use: (You can download a copy from here)

<?xml version="1.0" encoding="ISO-8859-1"?
> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html> <body> <h2>My ClearQuest Results </h2> <table> <xsl:for-each select="exportedResults/columnNames"> <tr bgcolor="gray"> <xsl:for-each select="columnName"> <th><xsl:value-of select="."/> </th> </xsl:for-each> </tr> </xsl:for-each> <xsl:for-each select="exportedResults/records/record"> <tr> <xsl:for-each select="field"> <td><xsl:value-of select="."/></td> </xsl:for-each> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

You can add the following line to the generated xml so you tell the browser to use the xsl.  Make sure that the xsl and xml fiels are in the same directory.

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="cqTransform.xsl"?>
<!--Generated by ClearQuest Eclipse client  Mon Aug 23 14:43:15 EDT 2010-->

java urlconnection difference between jdk 1.4 vs 1.5

Recently, I ran in to an issue when trying to do some maintenance on an application that was last compiled using java 1.4 compiler.  After compiling with java 1.5 or 1.6 compiler, the application stopped working.  The part that was failing was when the application is making a URLConnection and posting a bytestream to the URL.  It appears that from the client's perspective it wrote the entire bytestream.  However, when the server is attempting to read the bytestream, it would get a EndOfFile exception.   After scratching my head for a day, I noticed that one of my colleague had the same application working on his machine.  The only difference is the URL he is using has a backslash at the end and mine did not.  I tried changing the url by adding a backslash at the end, and the application starts working.


I have not seen any official documentation regarding this difference in the behavior between the same code compiled by jdk 1.4 and the newer ones.  But, it seem to be the case as few others also experienced similar issue.

I will update this blog entry if I ever find more details on this issue.  Meanwhile, if you run in to this issue, you may want to try adding a backslash to the end of your url.

Pagination using sql

If you have a complex query that returns lot of rows from the database and you are displaying the results in a table,  you may want to add some pagination logic to your application.  However, most people add pagination to the application layer, but retrieve all the rows back from the database.  Java programming frameworks/API such as IBatis, JPA etc provides a mechanism to limit the number of rows retrieved or skip a number of rows.   However, the implementation of those framework is left behind the jdbc driver and often results in the data still retrieved to the application server and then discarded.   You could avoid this resource wastage by changing your query to limit the rows.  The following is an example of how you can do just that.

    select * from ( select rownum rnum, a.* from (
         select columns from your_complex_table order by someField )  a 
            where rownum <  #UPPER_LIMIT )     where rnum >= #LOWER_LIMIT

In this example, you can change the values of #UPPER_LIMIT and #LOWER_LIMIT as parameters to your query limit the results to just the rows you want to deal with.  If you are using a  iBATIS you can make your actual query (the one does fetches all the records) in to an sql fragment and reuse it for the paginated query and get row counts.   It also helps to get all the records in case of exporting all the data to some external format.  The following example shows how you can reuse the sql fragment within your sqlMapping.

<sql id="selectItems">
select columns from your_complex_table order by someField
</sql>
<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItems"/>
</select>
<select id="selectPaginated" resultClass="Item" parameterClass="map">
 select * from ( select rownum rnum, a.* from (
<include refid="selectItems"/>
    ) a  where rownum <  #UPPER_LIMIT )
   where rnum >= #LOWER_LIMIT
</select>