Starting with Spring DAO layer – Stored procedures/functions

Configuring Spring JDBC DAO-

Create a Java/or Dynamic Web project in eclipse.
Copy all the spring jar files from /dist/modules into your project’s lib folder and add these jar files to java build path.
Copy all the jar files (except commons-attributes-compiler.jar) from /lib/jakarta-commons into your project’s lib folder and add these jar files to java build path. (Without these libraries tomcat will complain the following Exception-
java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory)

Now you are all set to begin web development with Spring DAO layer.

Following I show how we can configure Datasource/and the beans that use datasource using applicationContext.xml. and also I will show you how we can call Database functions/stored procedures running on MS SQL Server.

Following is the applicationContext.xml, where you configure your beans, specify the database connection settings here in datasource bean definition.

Following is a simple java class that shows how to call a function and map input/output parameters -

Following is a simple java class that shows how to call a stored procedure and map input/output paremeters using a private inner class.

Following is a model object used by the above java class -

thats it..you have an application with DAO layer. You can use similiar approach in web applications, For standalone make sure you have applicationContext.xml in classpath. For web applications, have applicationContext.xml in WEB-INF folder and define a context parameter with name (contextConfigLocation) and value (/WEB-INF/applicationContext.xml)

Starting with GWT (Google Web ToolKit)

Eclipse provides a standard plugin for GWT.

1). Download the GWT plugin following the instructions on
http://code.google.com/eclipse/docs/install-eclipse-3.4.html

2). Create a new Web Application Project.

The Project gets configured ready for GWT development and it comes with a sample GreetingService service.

3). Clean build the Project and right click on the project and select Run As – Web Application. You will see the application running in Hosted browser.

You can make changes to your client code and verify the changes in hosted browser. You don’t need to restart the server as all the changes being hot-deployed to hosted server.

– more examples to come..check out soon..

SQL Server Tips

1). Get a list of values as a comma-seperated list

SELECT DISTINCT STUFF( ( SELECT ‘,’ + RTRIM(fieldName) FROM table1 where id = ‘id’
FOR XML
PATH(”)),1 ,1, ”)
FROM table1 where id = ‘id’

2). Get just the time from datetime field .e.g. 12:11 AM

select (CASE SUBSTRING(CONVERT(varchar(24), getDate(), 121), 12, 12)
when ’00:00:00.000′ then ”
else substring(convert(varchar(20), getDate(), 9), 13, 5) + ‘ ‘ + substring(convert(varchar(30), getDate(), 9), 25, 2)
end) AS ‘just_the_time’

Starting With Hibernate

Tools : Eclipse, SQL Server

1). Create a Dynamic Web project in Eclipse.
2). Copy the following jar files into project’s lib folder and project’s class path.

antlr-2.7.6.jar
asm.jar
asm-attrs.jar
cglib-2.1.3.jar
commons-collections-2.1.1.jar
commons-logging-1.1.jar
dom4j-1.6.1.jar
hibernate3.jar
javassist.jar
jta-1.1.jar

sqljdbc.jar (FOR MS SQL Server)

3). Create the Hibernate configuration file hibernate.cfg.xml as follows -

4). For example in above configuration we defined a hibernate mapping file status.hbm.xml which can look like -

5). Store all the configuration files in src/default package.

6). Create a HibernateUtil class for serving sessions utilizing sessionFactory as follows -

7). Create a DAO class

8). Create a java class for status.hbm.xml mapping as follows -

9) Now you are ready to use Hibernate using the above utility class HibernateUtil and the Hibernate configuration mappings.
Following is a sample program -

Removing Non-Alpha Numeric characters except period (.), dash (-), ampersand (&), pound (#), comma (,) and underscore (_) using Oracle REGEXP

We had a requirement to have the text fields to accept only alphanumeric and some special characters. I tried to use the Orcale Regular expression for this as follows -

SELECT trim(REGEXP_REPLACE(REGEXP_REPLACE(‘.-&,_#HELLDS GjkjkO87&%^^^^^^^^^^&**’,’[^\\_\\sa-zA-Z0-9\\&\\.\\,\\-]‘,’ ‘),’ *’,’ ‘)) FROM DUAL

REGEXP_REPLACE takes the actual string as first argument
pattern as the second argument
and the replacement text as the third argument

The first call to REGEXP_REPLACE replaces the unwanted characters to Spaces.
and second call replaces the multiple white spaces to single white space.
and lastly TRIM is applied to remove leading/ending white spaces.

SELECT trim(REGEXP_REPLACE(REGEXP_REPLACE(‘.-&,_#HELLDS GjkjkO87&%^^^^^^^^^^&**’,’[^\\_\\sa-zA-Z0-9\\&\\.\\,\\-]‘,’ ‘),’ *’,’ ‘)) FROM DUAL

Current Time in Millis in C#

In Java, we do System.currentTimeInMillis to get the current time in milliseconds. This returns a long value which can be used as an unique number. While developing .NET C# code, I tried to get the same, but I didn’t find any method/property that gives this value.
After some research I found that we can get the same value using the following code -

long millis = (DateTime.UtcNow-new DateTime (1970, 1,1)).TotalMilliseconds;