Escape ‘_’ in SQL and Java

We had a table for which, a column say ‘ENAME’, was allowed to have underscore(_). The same column was allowed to be searchable from Flex User-Interface. As underscore(_) was allowed to be used in the text-box, we ended up retrieving all the records from the table when the user entered only ‘_’ in the text-box and queried for results. This happens as underscore(_) has a special meaning in Oracle and is used as a wild-card character(for single character) in SQL parlance.

The solution to this problem was to escape the underscore(_) in SQL and treat it as just another character as opposed to a wild-card character and escape it in Oracle SQL. Following should be the SQL query:

SELECT * FROM EMPLOYEE WHERE ENAME like '%\\_%'  ESCAPE '\';

The same fix applies to even ‘%’ which is used as a wild-card character for multiple characters.

In Java, we had to do the following as we were appending the search-value as a query parameter in SQL.

String query="SELECT * FROM EMPLOYEE";

if(!"".equals(ename))
{
    query=query.concat(" WHERE ENAME like "+"'%"+ename.replaceAll("_", "\\\\_")+"%'  ESCAPE '\\' ");

}

Just apply the above fix and fail the QA from breaking our robust Java and Oracle code.

SQL Query For Pagination on Database Side

Recently, i was tasked with implementing pagination for a data-grid in my application on the server-side. The pagination which i choose, is enabled at the database end by using SQL. After a bit of google reasearch, i stumbled upon the following query which seems to fit the bill.

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

where
FIRST_ROWS(N) tells the optimizer, “Hey, I’m interested in getting the first rows, and I’ll get N of them as fast as possible.”
:MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
Courtesy : Ask Tom, Q & A

Supporting Oracle’s xmltype column using Java and Spring – Issues -Part 2

My adventure with Oracle’s XMLType and ojdc6.jar (Oracle Thin driver, Type 4, pure java implementation) continues from my previous post…..

I was advised to use ojdbc6.jar and xdb6.jar to deal with Oracle’s XMLType in tandem or be ready to run into problems. Even after using them, i ran into problems when my application’s build moved to a higher environment(QA) in SDLC.
1. The first issue which we faced is below:


Caused by: java.lang.NoClassDefFoundError: oracle/xml/parser/v2/XMLParseException
	at org.springframework.data.jdbc.support.oracle.OracleXmlTypeValue.setValue(OracleXmlTypeValue.java:106)
	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:270)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)

Caused by: java.lang.ClassNotFoundException: oracle.xml.parser.v2.XMLParseException
	at weblogic.utils.classloaders.GenericClassLoader.findLocalClass(GenericClassLoader.java:296)
	at weblogic.utils.classloaders.GenericClassLoader.findClass(GenericClassLoader.java:269)
	at weblogic.utils.classloaders.ChangeAwareClassLoader.findClass(ChangeAwareClassLoader.java:56)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:307)

The reason for this is that xmlparserv2.jar was missing as part of application build. Actually, this is supposed to be loaded by Weblogic/Websphere/JBOSS as part of configuration with Oracle 11g database. As this was missing, i included Oracle 10g’s xmlparserv2.jar unknowingly and deployed the application. Come insertion into XMLType column, it failed again with another error.

Note: For an enterprise Oracle 11g edition these jars will be located under {ORACLE_HOME}/oracle/produce/{VERSION_NUMBER}/lib/xmlparserrv2.jar (and a whole lot of other jars too)

2. The second error which we will face is below:


java.lang.NoClassDefFoundError: oracle/xml/binxml/BinXMLMetadataProvider
	at org.springframework.data.jdbc.support.oracle.OracleXmlTypeValue.setValue(OracleXmlTypeValue.java:106)
	at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:270)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
	at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:63)
............
Caused by: java.lang.ClassNotFoundException: oracle.xml.binxml.BinXMLMetadataProvider
	at weblogic.utils.classloaders.GenericClassLoader.findLocalClass(GenericClassLoader.java:296)
	at weblogic.utils.classloaders.GenericClassLoader.findClass(GenericClassLoader.java:269)
	at weblogic.utils.classloaders.ChangeAwareClassLoader.findClass(ChangeAwareClassLoader.java:56)

The reason for this is that Oracle has updated xmlparserv2.jar for Oracle 11g. But, how a developer is supposed to know it? There is neither any documentation for this nor a naming convention for the jar in question.When you compare the jar sizes, you will realize that the xmlparserv2.jar which comes with Oracle 11g is about 1350 KB compared to the old jar of size, 1183 KB. And, they have included a new package oracle.xml.binxml to deal with binary XML.

There are a whole set of jars which also have been modifed in Oracle 11g. i.e.,classgen.jar, xsqlserializers.jar, xschema.jar, xmlmesg.jar, ojcr.jar, etc

Courtesy: Sandeep Molath(simplyblogmadi.blogpost.com) for giving me the basic understanding to get started on this issue.

Supporting Oracle’s xmltype column using Java and Spring – Part 1

Oracle driver(ojdbc6.jar) is not compliant with JDBC 4.0 when it comes to the usage of Types.SQLXML. Hence, either, we will have to make use of Oracle’s proprietary oracle.xdb.XMLType. Or, we need to depend on Oracle SQL functions to convert the clob to xmltype. Let me outline, the approaches which are currently available.

There are three ways in which you can insert XMLType data into an Oracle table containing xmltype column from Java:

a. Inserting the data in terms of CLOB in java and then internally convert the clob to XML-Type using XML() in Oracle
Click here for more information.

b. Using Oracle’s proprietary oracle.xdb.XMLType.
Click here for more information.

c. Use Spring Data JDBC Extensions project if you are Spring fanboy.

We will discuss the third approach which is the simplest of the lot:

1. If you are using Java 6 and Spring 3 then just include spring-data-oracle-1.0.0.RC1.jar . In addition, dont forget to include xdb6.jar(can be downloaded from Oracle site) . This jar provides the XMLType support and should be used along-side ojdbc6.jar

2. Configure SimpleJdbcTemplate

3. Configure the SQLXmlHandler for Oracle:


<bean id="sqlXmlHandler"
class="org.springframework.data.jdbc.support.oracle.OracleXmlHandler"/>

4. In your respective DAO class inject simpleJdbcTemplate and sqlXmlHandler

5. Add the below code snippet to enable the conversion from String to xmltype


simpleJdbcTemplate.update(
"INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
id,
sqlXmlHandler.newSqlXmlValue(xml));