Using SQL IN clause with Prepared Statement

Using IN clause in prepared statements is not straight forward fortunately, there is a hack available for this scenario.

Example: Get list of customers who are in east cost states.
SQL could be: SELECT * FROM CUSTOMERS WHERE CUST_STATUS='ACTIVE' AND STATE IN('NJ','NY','MA','NH')

prepared statement - "SELECT * FROM  CUSTOMERS WHERE CUST_STATUS=? AND STATE IN (LIST_STATES)"

List queryForList = jdbcTemplate.queryForList(
    StringUtils.replace(sqlString, "LIST_STATES",
    ("'"+ StringUtils.replace(functions,",", "','") + "'")),new Object[] {});

Configuring Tomcat to Use JNDI based Oracle Datasource

In this post, I will talk about configuring a JNDI based datasource (Oracle) on Tomcat server which is a common way to access your backend.

1. Copy required Oracle driver jar files to $CATALINA_HOME/common/lib

2. Define your data source in Context $CATALINA_HOME/conf/server.xml

<!-- Global JNDI resources -->
  <GlobalNamingResources>
      <Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" 

driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
              username="scott" password="tiger" maxActive="20" 

maxIdle="10" maxWait="-1"/> 

  </GlobalNamingResources> 

3. Add data source resource link in $CATALINA_HOME/conf/context.xml

<Context>

<Resource global="jdbc/myoracle" name="jdbc/myoracle" 

type="javax.sql.DataSource"/>

</Context>

That's it, now you are ready to use data source configured above 

from your application deployed on tomcat.