Archive for the ‘JDBC’ Category

This article is a small POC on how the readOnly attribute used in Spring Transaction strategy improves database access in case of transactions that involve only READing data from datasources.

First a small extract from the Spring reference documentation:

The TransactionDefinition interface specifies:
• Isolation: the degree of isolation this transaction has from the work of other transactions. For example, can this transaction see uncommitted writes from other transactions?
• Propagation: normally all code executed within a transaction scope will run in that transaction. However, there are several options specifying behavior if a transactional method is executed when a transaction context already exists: for example, simply continue running in the existing transaction (the common case); or suspending the existing transaction and creating a new transaction. Spring offers all of the transaction propagation options familiar from EJB CMT.
• Timeout: how long this transaction may run before timing out (and automatically being rolled back by the underlying transaction infrastructure).
• Read-only status: a read-only transaction does not modify any data. Read-only transactions can be a useful optimization in some cases (such as when using Hibernate).

As you can see the Read-only status seems to be silver bullet for transactions that involve only reading data. Following is a setup that I used to prove the point.

The setup included a Service method which in turn calls the DAO layer to access a table and ONLY retrieve records. The time required to access the data is calculated both WITH and WITHOUT the readOnly attribute. The time is calculated using Spring’s StopWatch utility class. However you can use your own timing methodologies to check out the outcome. The application is run for sometime to warm up the JVM followed by the actual estimation of the time. This is clear in the Main.java class

This test is run on the following configuration machine:

JDK: 1.5
MySQL: 5.0.26
Records in DB: 1,000,000 plus records
RAM: 1.5 GB
Processor: Pentium 1.8GHz

DAO Class : DAOStep.java

package com.tx.app.dao;

import java.sql.Types;
import java.util.Calendar;
import java.util.List;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class DAOStep extends  NamedParameterJdbcDaoSupport implements IDAOStep

	public void getData(int x) 

		final String insertIntoTest = "insert into TEST(VAL1,VAL2) values(?,?) ";

		List val=getJdbcTemplate().queryForList("select 1 from test where val1= ? order by 1 desc" , new Object[]{x},new int[]{Types.INTEGER});


This DAO class only queries a table name “TEST” with the following query

SELECT 1 from TEST where val1=? ORDER BY 1 DESC

Next is the Service Class : ServiceStep.java

package com.tx.app.service;

import com.tx.app.dao.IDAOStep;
import com.tx.app.exception.MyCheckedException;
import com.tx.app.exception.MyRuntimeException;

public class ServiceStep 

	private IDAOStep daoStep;
	public void getReadOnlyData(int x) 

	public void getNoReadOnlyData(int x) 

	public IDAOStep getDaoStep() {
		return daoStep;

	public void setDaoStep(IDAOStep daoStep) {
		this.daoStep = daoStep;

Next is the Main Class : Main.java

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.util.StopWatch;

import com.tx.app.dao.IDAOStep;
import com.tx.app.exception.MyCheckedException;
import com.tx.app.service.ServiceStep;

public class Main 


	public static void main(String[] args) throws MyCheckedException 
		ApplicationContext context = new ClassPathXmlApplicationContext(new String[]{"context.xml"});
		ServiceStep service = (ServiceStep)context.getBean("serviceStepTX");
/*Warm Up for the application and the JVM*/
		for (int i = 0; < 10; i++)


		long readOnlyTime =0;
		long noReadOnlyTime =0;
		int runTime =500;
		for (int i = 0; i < runTime; i++) 

		StopWatch watch = new StopWatch("Read-Only-Data");
		//watch.start("First Task");
		watch.start("Second Task");
		watch.start("Third Task");		
		watch.start("Four Task");		
		watch.start("Five Task");		
		watch.start("Six Task");		
		readOnlyTime = readOnlyTime+watch.getTotalTimeMillis();

		for (int i = 0; i < runTime; i++) 
		StopWatch watch1 = new StopWatch("No-Read-Only-Data");
		//watch.start("First Task");
		watch1.start("Second Task");
		watch1.start("Third Task");		
		watch1.start("Four Task");		
		watch1.start("Five Task");		
		watch1.start("Six Task");		
		noReadOnlyTime = noReadOnlyTime+watch1.getTotalTimeMillis();
		System.out.println("Read Only : "+(readOnlyTime/runTime));
		System.out.println("No Read Only : "+(noReadOnlyTime/runTime));

<strong>Finally the Spring XML configuration file</strong>

<?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns="http://www.springframework.org/schema/beans"

    <bean id="faceDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
   		<property name="driverClass" value="com.mysql.jdbc.Driver"/>
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/facedb"/>
		<property name="user" value="root"/>	
		<property name="password" value=""/>	
		<property name="initialPoolSize"><value>3</value></property>		
		<property name="minPoolSize"><value>5</value></property>	
		<property name="acquireIncrement"><value>5</value></property>		
        <property name="acquireRetryAttempts"><value>10</value></property>
        <property name="acquireRetryDelay"><value>1000</value></property>
        <property name="checkoutTimeout"><value>600000</value></property>
        <property name="maxPoolSize"><value>25</value></property>
        <property name="maxStatements"><value>200</value></property>
        <property name="maxStatementsPerConnection"><value>20</value></property>
        <property name="maxIdleTimeExcessConnections"><value>600</value></property>
	<bean id="daoStep" class="com.tx.app.dao.DAOStep">
		<property name="dataSource" ref="faceDataSource"/>
	<bean id="serviceStep" class="com.tx.app.service.ServiceStep">
		<property name="daoStep"  ref="daoStep"/>
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    	<property name="dataSource" ref="faceDataSource"/>

	<bean id="serviceStepTX" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
		<property name="transactionManager" ref="txManager"/>
		<property name="target" ref="serviceStep"/>
		<property name="proxyTargetClass" value="true"/>		
		<property name="transactionAttributes">
				<prop key="getReadOnly*">PROPAGATION_REQUIRED,readOnly</prop>	
				<prop key="getNoReadOnly*">PROPAGATION_REQUIRED</prop>				

Once Now create a table in the MySQL database with the following script

VAL2 INT) engine=innodb;



Once you run the Main.java class you will see an output similar to this.

Read Only : 15
No Read Only : 30

The interpretation of this output is that a SELECT on the table

With readOnly attribute took an average of 15 ms
Without readOnly attribute took an average of 30 ms

Pretty good I suppose!!!

Read Full Post »

Today we shall look into a nice feature on how to secure our JDBC connections. It’s generally a standard to make JDBC connections in java applications to connect to database. We mostly have web applications running in a web container. These web applications make JDBC connections to the databases to retrieve data for the applications. How many of us have really thought how secure these JDBC calls are. Well they aren’t actually. Like any other insecure network protocol call, so are these JDBC connections. However these JDBC connections can be very easily encrypted. Well this discussion is purely based on Oracle databases and wouldn’t work with other drivers. However, if any one finds out about other databases please let me know.

Before we go any further let us see some of the security threats we face when we use normal JDBC connections

  • Eavesdropping and Data Theft
  • Data Tampering
  • Falsifying User Identities
  • Password-Related Threats

Data flowing over the network is prone to network sniffers, be it insider or outside you company. Any one can pick up these data packets and tamper with them.

Imagine you send a query for transfer of balance for $100 from Account A to Account B. Someone could intercept this request and just play around with the zeroes (This is only hypothetical. I sure shall try this out).

Since a picture is worth a thousand words, let us look at a typical application environment.

Application Infrastructure

Application Infrastructure

I believe now it is clear as to how your JDBC connections can get insecure.

Now we shall look into how to get around these issues. To solve these security challenges we have two special features that can be used

  • Data Encryption
  • Data Integrity

In simple lay man terms, it means that your JDBC calls will be transparently encrypted using standard encryption algorithms, there by making your data transfer secure. Secondly your data will be hashed to form messages digests, there by preventing data tampering. So that’s it. Let us get into action.

In order to encrypt and hash the data, certain configuration has to be done both at the client end and the server end. We shall look into them one at a time.

Client Configuration

This is one the easiest steps. You will only have to tell your JDBC driver to encrypt and digest the message as and when you make JDBC calls. Lets looks at a sample code

package com.datel.secure;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.net.ano.*;

public class StartAction {

    // The machine on which the database resides
    public final static String hostName    = "";
    // The TNS listener port
    public final static String port  = "1521";
    // The database name (SID)
    public final static String databaseSID = "home";
    // The database User login
    public final static String userName    = "scott";
    // The database user password
    public final static String password    = "tiger";	 

    Connection connection;  

    public static void main(String[] args) {
		// TODO Auto-generated method stub
		new StartAction().makeDbConnection();

	 public void makeDbConnection(){

		      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

		      // Form the database connect string using the connection parameters
		      // as specified in ConnectionParams.java
		      String dbConnectString ="@"+StartAction.hostName+":"+

		      Properties prop = new Properties();

		      // Set the user name and password property
		      prop.put("user", StartAction.userName);
		      prop.put("password", StartAction.password);

		      int level = AnoServices.REQUIRED;

		      // Set the Client encryption level
		      prop.put("oracle.net.encryption_client", Service.getLevelString(level));

		      // Set the Client encryption selected list
		      prop.put("oracle.net.encryption_types_client", "(RC4_40)");

		      // Set the Client integrity level
		      prop.put("oracle.net.crypto_checksum_client", Service.getLevelString(level));

		      // Set the client integrity selected list
		      prop.put("oracle.net.crypto_checksum_types_client", "( MD5 )");

		      connection = DriverManager.getConnection("jdbc:oracle:thin:"+dbConnectString,prop);

		      if ( connection != null )

		    	  Statement stmt = connection.createStatement();
		    	  ResultSet rs;
		    	  rs = stmt.executeQuery("SELECT * from EMP"); 

		    	  while (rs.next()) {
		              String lastName = rs.getString("sal");
		              System.out.println(lastName + "\n");


		    } catch(SQLException ex){
		    	//Trap SQL errors


		    } catch(Exception ex){



  • The first thing that you would have noticed different is the client encryption level. I have set it to REQUIRED by setting the property oracle.net.encryption_client. This value signifies, encryption is required and mandated at the client end without which the connection would fail. It supports four different legal values. They are REJECTED, ACCEPTED, REQUESTED and REQUIRED. Similar configuration is required at the server end which we shall see in the server configuration. That means we can have 16 different combinations between client and server. I prefer to leave both the Client and the Server as REQUIRED, because it enforces the encryption security check and cannot be bypassed.
  • The next property that is set is oracle.net.encryption_types_client.This tells the JDBC driver to follow RC4_40 encryption standard. There are other encryption standards like DES and Triple DES. Possible values are RC4_256; RC4_128; RC4_56; RC4_40; 3DES112; 3DES168
  • The next property set is oracle.net.crypto_checksum_client. This tells the JDBC driver to generate checksums for the data and its values can also be REJECTED; ACCEPTED; REQUESTED; REQUIRED.
  • The last property that is set is oracle.net.crypto_checksum_types_client. This tells the JDBC diver to follow the MD5 hashing algorithm to form checksums.

If you were to run this code you would obviously get an error message which states that encryption is not enabled on the server. So let us step into the server configuration.

Server Configuration

On your database server access the Oracle Net Manager. You could find this at Start->Programs->OracleHome->Configuration and Migration Tools->Oracle Net Manger on your database server. Actually this Oracle utility does nothing special but writes to your SQLNET.ORA .You could directly edit this file or else use this utility. So fire up the application and you would see a screen such as this

Oracle Net Manager

Now click on the Profile properties which will bring up another screen. Choose Oracle Advance Security from the drop down list.

Set the encryption type to Required and Selected methods to RC4_40. If you recall this is the same encryption setting that was enabled on the client. The noticeable difference is the encryption seed. Set some hard difficult string in this field. This is used by oracle to encrypt the data in its algorithms. Next chose the integrity tab which fires up this screen

Here we select the similar configurations as the client. Checksum level is Required and the Selected method is MD5

Now go to File->Save Network Configuration. If you want to see where these properties are save go to you Oracle Home folder. Under Network->Admin there will be a file called SQLNET.ORA which would look something like follows

# sqlnet.ora Network Configuration File: c:\OraHome_1\NETWORK\ADMIN\sqlnet.ora

# Generated by Oracle configuration tools.





SQLNET.CRYPTO_SEED = somerandomlonghardstring



There are ways to check if your encryption is working or not. I shall give you a hint thought. You can just check your database trace files. It would log something as follows

[29- -2008 15:49:06:398] nau_adi: exit
[29- -2008 15:49:06:398] na_tns: authentication is not active
[29- -2008 15:49:06:398] na_tns: encryption is active, using RC4_40
[29- -2008 15:49:06:398] na_tns: crypto-checksumming is active, using MD5
[29- -2008 15:49:06:398] na_tns: exit

Now fire up your application and enjoy transparent network encryption.

Read Full Post »