Oracle WITH clause- details with simple explanations

Oracle 9.2 brought up a feature to write sub queries using WITH clause, formally known as Subquery factoring. Using this keyword, it is possible to define sub queries in a complex SQL query which can be reused several times.You might wonder why do we need a special keyword yet again and why we can't manage with the traditional sub query mechanism. This blog tries to answer these questions with simple, yet powerful examples.

In a nutshell, WITH clause give the following advantages for developers who deal with complex queries:

• It simplifies the complex query
• Improves readability of the query
• Helps to reuse a code segment without repeating unnecessarily
• Makes a developer's life easy when comes to debugging/ finding bugs in the query

Let's stat off with a simple example of this before look at the explanations of the syntax. Hope this query will light up the real advantages achieved through subquery factoring (WITH clause).

	WITH R1 AS
	(SELECT * FROM A JOIN B ON A.X = B.Y WHERE A.Z = 'abcd'
	) ,
	R2 AS
	(SELECT * FROM C WHERE D.X = 'pqrs' AND D.Y IN (SELECT Y FROM R1)
	) ,
	R3 AS
	(SELECT * FROM E JOIN R2 ON E.X = R2.Y WHERE E.Z = 'pqrs' AND R2.P ='bla bla'
	)
	SELECT R3.somthing,
	tab.something
	FROM R3,
	anotherTable tab
	WHERE THIS= 'THAT'
	ORDER BY R3.P

view raw with.sql hosted with ❤ by GitHub

For simplicity, I've used some A, B, bla bla. Hope it helps to get the message easily! As you can see, it looks nice to read and understand; It has simplified the complex query by just dividing it into pieces. Imagine how it would be with the traditional sub query?

Just take R2 for instance. If we try to get the output of it using the traditional sub queries, it'll look like

	SELECT *
	FROM C
	WHERE D.X = 'pqrs'
	AND D.Y IN
	(SELECT Y FROM A JOIN B ON A.X = B.Y WHERE A.Z = 'abcd'
	)

view raw R2.sql hosted with ❤ by GitHub

We know how simple this query is. Just imagine the size of the total query if we are writing some real complex query!

So let's see the syntax of this now. Hope you got it right already by going through the above simple example. Syntax is simple as follows. If you understand like a sentence in English, you'll never forget it!

	WITH <sub query name> AS (SELECT something FROM aTable)
	SELECT another thing from <sub query name>

view raw syntax1.sql hosted with ❤ by GitHub

Similarly, when you have multiple sub queries, it's like- let A AS this, B AS that. Then just write a select query to get your desired columns.

	WITH <sub query name1> AS (SELECT something FROM aTable),
	<sub query name2> AS (SELECT somethingElse FROM anotherTable)
	SELECT anotherThing from <sub query name2>

view raw syntax2.sql hosted with ❤ by GitHub

Pls note this will work with Oracle 9.2 or later versions only. Hope this helped you guys, will catch u with another interesting topic soon!

Add code snippets easily in your blogs using GitHub

It's obvious that there are numerous ways and tools available to include your code snippet in the blogs. Each having their own pros and cons. What do you want as a programmer who wants to simply share your knowledge? You will be interested to do this without much hassle and also to let the readers view and copy the code easily.

Here are the five easy steps to do this using GitHub:

1. Go to https://gist.github.com/
2. Put appropriate file name in the name this file... area. Make sure to put correct extension, so that GitHub will show proper syntax formatting.
3. Paste the code & click on Create Public Gist button
4. Copy the url from the Embed this gist area. Eg: <script src=\"https://gist.github.com/abcd.js\"></script>
5. Paste it simply in your blog. That's it guys!

SpringMVC application with Hibernate + Bootstrap + JUnit + Mockito + Form Validation

In this post, let's look at a basic, but technology wise rich SpringMVC application. Wondering why yet another tutorial in SpringMVC while enough available in net? The whole purpose of this post is to develop a simple application with required important technologies which could be understood by a beginner also!

In abstract, this sample application uses the following technologies. It's perfect to give a kick start in each technologies, so happy coding!

• Spring MVC 3.2
• Maven 3.1
• Hibernate 3.6.3
• Jetty-Maven 8.1.12
• Mockito 1.9.5
• JUnit 4.11
• Bootstrap 3.0
• MySQL

If you wish to start right now without spend much time in reading, you can go ahead and download the code to develop your application on top of it. This post includes the basics to get into the project. Await for posts with explaining the technologies used & configuring them in near future :)

Download the full project from GitHub

This is a basic customer management system where you can add, view & delete customer details.

• The JSP pages are using Bootstrap for CSS, which makes your life easy by giving best UI experience out of the box
• The full project is based on Maven, so all the advantages of Maven comes along- Easy dependency management, unit tested automated builds, so on. 
• Jetty-Maven plugin is used to deploy & run the application within seconds of time. 
• There is a sample unit test including Mocking objects used in this application, so it'll be a starter for people who are new to Mocking and Mockito. 
• Form validation is done through annotations and achieved by javax.validation API. Also it is done in a way to support Internationalization- so that it can be adapted to various languages and regions without engineering changes
• Annotation based Hibernate is used as ORM framework and HQL used to query the MySQL DB.

Table creation

	CREATE TABLE CUSTOMER
	( ID INT PRIMARY KEY AUTO_INCREMENT,
	FIRSTNAME VARCHAR(50),
	LASTNAME VARCHAR(50),
	EMAIL VARCHAR(30),
	TELEPHONE VARCHAR(20)
	);

view raw create_cutomer.sql hosted with ❤ by GitHub

According to the sample, you need to create a schema named customerMgr and run the above table script. Or else you can create a schema name at your choice and make sure to change it in the database.properties file. Similarly, change the database username, password in the same file.

Start the project

I love IntelliJ IDEA for it's best features, but it's possible to use any IDE as this is a Maven project. Just open the pom.xml file and Idea does the rest for you. Once imported, the following structure should be available without any errors after Maven finishes downloading necessary library files.

Changing the logging directory or name of the file

You can edit the log4j.xml in the resources directory if you wish to change the logging directory/ name of the file / the logging level.

	<?xml version="1.0" encoding="UTF-8" ?>
	<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
	<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
	<param name="Threshold" value="INFO"/>
	<param name="Target" value="System.out"/>
	<layout class="org.apache.log4j.PatternLayout">
	<param name="ConversionPattern" value="%d{HH:mm:ss,SSS}- %m%n"/>
	</layout>
	</appender>
	<appender name="info" class="org.apache.log4j.RollingFileAppender">
	<param name="Threshold" value="INFO"/>
	<param name="File" value="logs/customerMgr.log"/>
	<layout class="org.apache.log4j.PatternLayout">
	<param name="ConversionPattern" value="%d %-5p [%c{1}] %m %n"/>
	</layout>
	</appender>
	<root>
	<priority value="debug"/>
	<appender-ref ref="console"/>
	<appender-ref ref="info"/>
	</root>
	</log4j:configuration>

view raw log4j.xml hosted with ❤ by GitHub

Form Labeling and Validation errors

All the labels and validation error messages in this application are supporting internationalization. If you wish to change any of the labels/ Error messages, go ahead and change them in the messages_en.properties file under resources folder.

	label.firstname=First Name
	label.lastname=Last Name
	label.email=Email
	label.telephone=Telephone
	label.addcustomer=Add Customer
	#Validation Error Messages
	Size=the {0} field must be between {2} and {1} characters long
	Size.customer.firstname=FirstName must be between {2} and {1} characters
	Size.customer.lastname=LastName must be between {2} and {1} characters
	Size.customer.telephone=Your phone number must be between {2} and {1} digits
	Pattern.customer.telephone=Please enter valid numerical number!
	Email=Email address not valid
	Past=Date must be in the past
	NotEmpty=Field cannot be left blank
	NotNull=Field cannot be left blank

view raw messages_en.properties hosted with ❤ by GitHub

Here, labels can have any meaningful names at your selection. But the keys under validation error messages section has to follow some standards. There will be a separate post about validation, but for the time being, you need to know that this format should follow the below format:

{ValidationClass}.{modelObjectName}.{field}

Changing the Context Path of this application

It's easy to change the context path in jetty-maven-plugin section of the pom.xml file. Currently it is set to customerMgr and if required, you can change this. 

	<plugin>
	<groupId>org.mortbay.jetty</groupId>
	<artifactId>jetty-maven-plugin</artifactId>
	<version>${jetty.maven.plugin.version}</version>
	<configuration>
	<scanIntervalSeconds>5</scanIntervalSeconds>
	<webAppConfig>
	<contextPath>/customerMgr</contextPath>
	</webAppConfig>
	</configuration>
	</plugin>

view raw pom.xml hosted with ❤ by GitHub

Here, you can see another useful feature during development phase that the auto scanning for changes. Currently it is set for 5 seconds and while the Jetty is running if we do any changes in the source & recompile, it'll be detected automatically within 5s & applied without any hassle.

Running & testing the application 

To run the tests, use the command mvn clean or simply right click on tests phase under Maven projects tab & select Run.

To Run the application, use the command mvn jetty:run or simply right click on jetty:run under Maven projects tab & select Run.

Once successfully started the application, you can go to the page from he following url. Make sure to change the context path if you have changed in the pom file.

http://localhost:8080/customerMgr/home

Home page

Customer add form

Form with validation error

That's it! Hope you guys enjoyed and it was clear enough. Wait for the posts with more details of the technologies used in this application and the ways to configure them. Until then, bye!