My musings on BizTalk!!

 

Vendor : You can use this “CANNON” to fire moving objects and its extremely powerful

Customer : I only need a “STRAW” to drink juice !!

Vendor : Well you can disable the features in a “CANNON” and use it as “STRAW”

Customer : In that case I might as well use a “MISSLE” or a “ROCKET” !!

Accessing SSL enabled web site using Apache HttpClient

I was trying to access a secure web site using Apache HttpClient API. However, it was failing giving me the following exception

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

In apache HttpClient website, it states that “HttpClient provides full support for HTTP over Secure Sockets Layer (SSL) or IETF Transport Layer Security (TLS) protocols by leveraging the Java Secure Socket Extension (JSSE).“

I tried their example of accessing a secure website and it worked. However the website I was trying to access is still failing.

After debugging the problem I came across a good article. In the article, the author mentioned that the above exception comes in the following case “when trying to open an SSL connection to a host using JSSE. What this usually means is that the server is using a test certificate (possibly generated using keytool) rather than a certificate from a well known commercial Certification Authority such as Verisign or GoDaddy. Web browsers display warning dialogs in this case, but since JSSE cannot assume an interactive user is present it just throws an exception by default.“

That was not exactly my case. I was not getting a warning from the web browser. The web site I was trying to access was using a commercial certificate but it was not a very well known. It was from a regional authority and not an international authority. Any key store comes with a default set of certificates from well known authorities.

So, I ran the program mentioned in the article. A file called jssecacerts was generated which includes the certificate. I have placed the file in the JAVA_HOME\jre\lib\security directory. Finally I was able to access the secure web site successfully.

Transaction optimization for Read Only Transaction: Spring ReadOnly attribute

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)
	{
		daoStep.getData(x);

	}	

	public void getNoReadOnlyData(int x)
	{
		daoStep.getData(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++)
		{
			service.getNoReadOnlyData(1);
			service.getReadOnlyData(1);
		}

		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");
		service.getReadOnlyData(1);
		//watch.stop();
		watch.start("Second Task");
		service.getReadOnlyData(2);
		watch.stop();
		watch.start("Third Task");
		service.getReadOnlyData(3);
		watch.stop();
		watch.start("Four Task");
		service.getReadOnlyData(4);
		watch.stop();
		watch.start("Five Task");
		service.getReadOnlyData(5);
		watch.stop();
		watch.start("Six Task");
		service.getReadOnlyData(6);
		watch.stop();
		//System.out.println(watch.prettyPrint());
		//System.out.println(watch.shortSummary());
		readOnlyTime = readOnlyTime+watch.getTotalTimeMillis();
		}

		for (int i = 0; i < runTime; i++)
		{

		StopWatch watch1 = new StopWatch("No-Read-Only-Data");
		//watch.start("First Task");
		service.getNoReadOnlyData(1);
		//watch.stop();
		watch1.start("Second Task");
		service.getNoReadOnlyData(2);
		watch1.stop();
		watch1.start("Third Task");
		service.getNoReadOnlyData(3);
		watch1.stop();
		watch1.start("Four Task");
		service.getNoReadOnlyData(4);
		watch1.stop();
		watch1.start("Five Task");
		service.getNoReadOnlyData(5);
		watch1.stop();
		watch1.start("Six Task");
		service.getNoReadOnlyData(6);
		watch1.stop();
		//System.out.println(watch1.prettyPrint());
		//System.out.println(watch1.shortSummary());
		noReadOnlyTime = noReadOnlyTime+watch1.getTotalTimeMillis();

		}

		System.out.println("Read Only : "+(readOnlyTime/runTime));
		System.out.println("No Read Only : "+(noReadOnlyTime/runTime));

	}

}

Finally the Spring XML configuration file

<?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns="http://www.springframework.org/schema/beans"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns:context="http://www.springframework.org/schema/context"
		xsi:schemaLocation="http://www.springframework.org/schema/beans
							http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

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

	<bean id="daoStep" class="com.tx.app.dao.DAOStep">
		<property name="dataSource" ref="faceDataSource"/>
	</bean>

	<bean id="serviceStep" class="com.tx.app.service.ServiceStep">
		<property name="daoStep"  ref="daoStep"/>
	</bean>

	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    	<property name="dataSource" ref="faceDataSource"/>
    </bean>

	<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">
			<props>
				<prop key="getReadOnly*">PROPAGATION_REQUIRED,readOnly</prop>
				<prop key="getNoReadOnly*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>    

</beans>

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

CREATE TABLE TEST
(VAL1 INT,
VAL2 INT) engine=innodb;

CREATE INDEX TEST_INDEX on TEST(VAL1);

OUTPUT
———-
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!!!

Understanding Spring Transaction management with Checked and Unchecked exceptions

I have been using Spring TX Management for quite sometime until I recently came upon an article describing the common pitfalls that occur in Transaction Strategies. Although many cases were covered, I would like to describe the common one that is seen in most code.

I am going to explain it with the help of a sample example. In the example we shall put together a sample Service which in turn calls a DAO. The service will call two methods from the DAO. It is only a trivial Service-DAO example.

First the DAO class : DAOStep.java

package com.tx.app.dao;

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

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

public class DAOStep extends  NamedParameterJdbcDaoSupport implements IDAOStep
{

	public void updateData(int val1 , int val2)
	{

		final String insertIntoTest = "UPDATE TEST SET VAL1= ? where VAL2= ? ";

		getJdbcTemplate().update(insertIntoTest,
				new Object[]{
							val1,
							val2
							},
				new int[]{
						 Types.INTEGER,
						 Types.INTEGER,
					});

	}

	public void insertData(int val1 , int val2)
	{

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

		getJdbcTemplate().update(insertIntoTest,
				new Object[]{
							val1,
							val2
							},
				new int[]{
						 Types.INTEGER,
						 Types.INTEGER,
					});

	}

}

Next 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 doStepRuntimeException()
	{
		daoStep.insertData(2, 2);
		daoStep.updateData(-2, 2);
		throw new MyRuntimeException();

	}

	public void doStepCheckedException() throws MyCheckedException
	{
		daoStep.insertData(2, 2);
		daoStep.updateData(-2, 2);
		throw new MyCheckedException();

	}	

	public IDAOStep getDaoStep() {
		return daoStep;
	}

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

}

As you can see the service throws two exceptions, one a checked exception(MyCheckedException) and second a runtime exception (MyRuntimeException).

Next the Runtime Exception class : MyRuntimeException.java

package com.tx.app.exception;

public class MyRuntimeException extends RuntimeException
{

}

Next the Checked Exception class: MyCheckedException.java

package com.tx.app.exception;

public class MyCheckedException extends Exception
{

}

And the main class to bootstrap the application

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

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");
		/*Method One*/
		service.doStepRuntimeException();
		/*Method Two*/
		service.doStepCheckedException();

	}

}

Finally the Spring XML context file : context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns="http://www.springframework.org/schema/beans"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns:context="http://www.springframework.org/schema/context"
		xsi:schemaLocation="http://www.springframework.org/schema/beans
							http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

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

	<bean id="daoStep" class="com.tx.app.dao.DAOStep">
		<property name="dataSource" ref="faceDataSource"/>
	</bean>

	<bean id="serviceStep" class="com.tx.app.service.ServiceStep">
		<property name="daoStep"  ref="daoStep"/>
	</bean>

	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    	<property name="dataSource" ref="faceDataSource"/>
    </bean>

	<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">
			<props>
				<prop key="doStepRuntimeException*">PROPAGATION_REQUIRED</prop>
				<prop key="doStepCheckedException*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>    

</beans>

The Table that needs to be there in some database is TEST. I am using MySQL database and following is the script used to create the TEST table

CREATE TABLE TEST
(VAL1 INT,
VAL2 INT) engine=innodb

Question

As you can see the Transaction attribute for the doStepRuntimeException and doStepCheckedException are both PROPAGATION_REQUIRED. What do you think would be the outcome when the Main class is executed once with Method 1 and once with Method 2? Would the records be inserted as well as updated in the database?

Explanation

The answer is follows.

Method 1)
The whole transaction is rolled back as RuntimeException is thrown and the current transaction is Rolled back

Method 2)
The whole transaction is committed as a checked exception (Exception) is thrown and the current transaction is Commited.

The correct way to rollback both the transactions would be to alter the XML file as follows

<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">
			<props>
				<prop key="doStepRuntimeException*">PROPAGATION_REQUIRED,-MyRuntimeException</prop>
				<prop key="doStepCheckedException*">PROPAGATION_REQUIRED,-MyCheckedException</prop>
			</props>
		</property>
	</bean>  

However, if you want to rollback all checked exceptions you can use -Exception instead of -MyCheckedException. The default behaviour is for Unchecked exception the current transaction is rolled back and for Checked exception the current transaction is commited.

Import and export IBM WebSphere MQ Components

IBM WebSphere provides a good utility called saveqmgr that enables you to save all the objects, such as queues, channels, etc, defined in a either local or remote queue manager to a file. You can then easily modify the file and use it to alter the definition of the same or another queue manager.

The saveqmgr is very easy to use. All what you need to do is

• Download the utility from IBM For windows you just need to unzip the file preferably in IBM WebSsphere bin directory
• To export the queue manager objects use the following command saveqmgr.exe -m qmName > outputFile
• To import the queue manager objects ensure that queue manager is running and then use the following command runmqsc qmName < outputFile

Easy Start with Apache Camel

Few days back I have started looking at Apache Camel Framework. It is an excellent routing framework that can perform many amazing functionalities in just few lines of code.However, due to the lack of documentations more complex code might get harder to implement. And as a beginner you might get confused in to which message header and body is been routed, what exception occurred in which endpoint, and which endpoint is been processed right now.

Apache camel provides a very useful utility which is “tracer” that can really helps you monitoring and debugging your code. It logs everything you need to know about the messages been routed.

Reference:
[1] http://activemq.apache.org/camel/
[2] http://activemq.apache.org/camel/tracer.html

Design v/s Debugging

I work on different projects which require me to perform very small, small and medium sized tasks which comprise a monolithic project just as any IT project out there would be. So what’s the big deal then?

I have seen people who design their work on paper be it very small or large and those who get into action right away. I fall into the latter group. I consider this to be a feather in my hat. This habit or skill, when I look at it now, are attributes that have creped into my nature of work partly due to my supervising body which wants results Yesterday and not Now or Tomorrow.

Until recently, I started noticing one of my colleagues who would jolt down even the slightest details of the task to perform before getting into action. Then with one steady stroke, as that of a master painter, she would complete her activities with no room for error. Integrating these modules would be a piece of cake.

On the contrary, I would have partly completed my activity with its main pieces in place and the results would also be there, feeling so proud of what I have achieved. In an IT project a partly completed project is equivalent to an uncompleted one. Integrating with these modules would be a tight rope walk. Endless debugging would follow during this integration process.

No more is this a feather in my hat, rather a scar on my face. I can’t emphasize enough the importance of Designing to the smallest details on paper before you start to act on your task. I am now trying by best to unlearn this behaviour. Hope I succeed. You might get a noticeable advantage in the beginning, however in the long run it’s always the tortoise than wins the race and not the hare.

Emirates Identity Card – Download Offline Application Form

This is just ease the download of the Offline Desktop application. It took me some time to download it from the website. So I have downloaded it myself and hosted it on this page. Step to follow to get the Software Application for Emirates Identity Card.

Please check the pre-requisites before downloading

1) Try to get it from EIDA website http://www.emiratesid.ae/html/english2/Offline.html

2)Right Click HERE and Save file to the file system

3)Rename file(pre-registration.jpg)  to pre-registration.msi

4)Double click and Install this file

5)Run the file

Emirates Identity Card – Boon or Bane

Today I had one of the worst experiences with the abuse of technology. For the past couple of weeks I have been rigorously trying to apply for the Emirates Identity Card which has been mandated by Emirates Identity Authority of United Arab Emirates. I have to say it’s a truly futuristic and highly commendable venture from the authorities. Every professional expatriate is supposed to have one. I foresee in the near future it will be integrated with all banks and form the basis of a Social Security System there by ensuring a nation wide credit system and much more. This initiative was a very good long term strategy which unfortunately lacked proper planning.

However my experiences in trying to procure an EID card have been quite bitter and painful in the royal way. I have been trying for the past few weeks to apply through their official website and fill the pre-registration form but in vain. You could get a cup of tea while the first page loads up (which is usual for high traffic sites). If it’s your lucky day and the page loads up by the time you get your cup of tea then definitely the time is 12:30 AM early in the morning.

The whole registration process comprises four pages. Now I fill the first page and try to submit, which gives me a validation check saying “Please choose your city & and street”. But again these are only optional fields. Then I notice that I was using the most popular browser in the world, FireFox(My mistake). So I switched to Internet Explorer (which accepts any junk.. thanks Microsoft) and the form submits to the second page. Voila, you get a page not found error. Submit again, once, twice then thrice. Luckily I get it now.

On the second page, you are requested to enter you residence permit number. Now this is a number of the format XXX/XXXX/XXXXXXX. However, by the time you enter the ‘/’ the whole content in the text field just disappears into thin air. Was this suggestion given by David Copperfield or David Blane? Hats off to whoever thought of this.

I would rather not elaborate on the third page. This is going to be an adventurous trip for the layman. So all the best and try and see if you know which part of the country you are in (If at all you find it).

Fourth page asks for your qualification. Now when you move on to your profession, it starts to get confusing again. There are tons of professions, and I being a Computer Engineer made me fall in none. Finally I had to satisfy my self with just “Professional” and as a job description, I key in “Sr. Software Engineer”.

Needless to say, once I complete all the four pages and reach the last Application Form preview section, I see that the details I have entered so far is not what I see on the last page. I see a totally new Occupation, someone else mother’s name is there, my company name is now “Sr. Software Engineer” and the rest I better leave it to all of you to figure it out. Don’t worry, its OK says the Call Center Executive. Don’t trust me, then give them a call and see!!!

If my memory is not wrong, this intricate system has been in place for more than a year now. Mind it; this is only the registration system we have been talking about.

You have only seen the tip of the iceberg still. The appointment system is something I still was not able to conquer so far. Hence I gave in and went to one of the centers. Needless to mention the bitter encounters I had with the Emirates Post counter staff, after a long treacherous trip I reached the venue and guess what I see!!! There is ONE kiosk machine (It’s a Touch sensitive enabled device wrapped over a normal computer) with half of its machinery outside for the convenience of the Security Staff so that it can be restarted at ease. At first I though it was my responsibility to restart the machine when it was my turn (If I could make it). Fortunately the Security Staff felt it their privileged responsibility to restart the machine (Yes restart the Computer and you can see….”Windows is Restarting…”) after each Appointment request was generated. That’s right after each request was generated they would restart the Kiosk Machine. Just some information to help those who will be going to get the Appointment from the Kiosk machine: Once you have filled in your mobile number you will be asked to call a standard mobile number something that starts with 0501062… Don’t be surprised when you hear the voice message “The mobile number you are calling is currently switched off…..!!!!” Its alright. Now that’s the intelligent system telling you with its ESP (Extra Sensory Perception) to call the Security Staff and ask him to restart the Kiosk Machine.

Fortunately I have got an appointment date now and am waiting for more action. More on that later. Until then, I shall cap my pen!!!

Sending Parameters to JasperReport

In many different forums, I have seen posts regarding how to send parameters to JasperReport. If you are still looking for an answer, you can definitely find it over here.

I am assuming that you are familiar with JasperReport and the JRXML template.

JasperReport allows you to pass parameters to the report during runtime. In order to do so you need to:

 

• Declare the parameter in your report template (jrxml) as follow

  <parameter name="itemName" class="java.lang.String"/>

  You can set a default value for the parameter in case it is not been passed. In order to do so, you can use the child element defaultValueExpression. 

• You can then refer to the parameter that you have declared in your template using the syntax “$P{PARAMETER NAME}”
• To refer to the parameter declared earlier in a text field use the follow syntax

  <textFieldExpression>
  $P{ itemName }
  </textFieldExpression>

  The text field will display the value of this parameter when the report is filled.

• To pass the parameter declared earlier to a query, use the following syntax

  <queryString>
  <![CDATA[
  SELECT * FROM product WHERE itemName = $P{ itemName } ORDER BY inventoryDate
  ]]>
  </queryString>

• To dynamically modify portions of the SQL query or to pass the entire SQL query as a parameter the syntax differs a little. BE CAREFUL. The ! character is needed. See the following example

  <queryString>
  <![CDATA[
  SELECT * FROM $P!{MyTable} ORDER BY $P!{OrderByClause}
  ]]>
  </queryString>

The parameters here are used to pass the table name in the FROM clause and the column names in the ORDER BY clause. Note that you cannot use the normal parameters (i.e. $P{}) to dynamically modify your query.

Please note that the reporting engine first deals with the $P!{} parameter references by using their values to obtain the final form of the SQL query, and only after that it transforms the rest of the $P{} parameters.

 

 Reference: The Definitive Guide to JasperReports by Teodor Danciu and Lucian Chirita