Spring handler interceptor concepts....

Sometimes it is required to perform a task before displaying a webpage to the client.
Suppose you want to log the request in a table for tracking purpose or you have a common authorization mechanism to validate the user request.
Again you want to do something after the request has been processed. 
To do this kind of activites what you can use is a handler interceptor feature given by spring. 
I won't be repeating the configuration stuff here. You can find it in springs documentation itself.I will mostly concentrate on the 3 methods that should be overridden when you implement Intereceptor interface of spring.

1. preHandle :

  This is the part of block that will be executed before the request is passed to the controller. So you can put all the code you need to execute before starting the processing of request. 
Remember you can only return a boolean value from this method. 
If you return true then only the controller will be executed.

2.postHandle:

This is the part of code that will be executed when the controller has finished its job but the view is not yet rendered.
  

3.afterCompletion:

This is the part of code that gets executed after the response has been despatched :) . So you can do other tracking works / DB logging here if you want and it won't affect the response time of your request because this code will be executed only when the client has already received or the response is on its way to the client.

One most important thing to understand here is  when you return false from preHandle , postHandle and afterCompletion blocks are not executed at all. So you cannot completely rely on the thing that postHandle will be executed.
Sure thing is preHandle will be executed always no matter what happens. 

 
You can see an example of spring handler interceptor here

missing keyword in sql insert script containing MERGE statement...

When we deliver a project we also deliver the sql scripts which are necessaryto run our project. Once I had faced a weird error in the scripts delivered.
What we do in our project is we save the sql queries that are used in different places in the code in DB instead of having them inside the code. It is because when you need to update a query you don't need to rebuild your application. You just need to update a column in the DB and thats it. Your new query would reflect in your code. I will elaborate the rpocess in some other post later.
The important here is that the error we got during execution of the scripts. We have a table which has all the queries required for the application. in this table we have unique names for each queries and values in the other column. So to populate this table the insert script would look like :

insert into table_name(query_name,query_value) values ('name_for_the_query','actual_sql_query');

In the previous post i ave explained the MERGE statement. It is largely used in our application. So this merge query also was needed to be inserted in the DB. So it looked like:

insert into query_table(name,value) values ('merge query','merge into xxx using dual on (x=?)
when not matched then insert (x,x,x)
--One blank newline was here
when matched then update.....');

This caused the error when it was run as a script with other queries were also executed with this as a whole insertion into the table.
When the above script was run locally in my DB server using oracle sqldeveloper it did not throw any error.
But somehow in the client's DB it was throwing an error.
Then i redelivered the script removing this blank line and it worked.. :)
Hope you won't have any trouble in future with this type of error.
I don't remember the exact error code or description but if i get to know it i will update the exact error code.
Lastly my advice is always when you try to insert a query as a string in insert or update script in sql do not enter any blank line for your reading ease. ;)

Oracle Insert or Update in single Query…

Oracle Insert or Update in single Query…

Often a situation comes in applications where you dont know whether a row was already present in DB or not, if it was present then update it otherwise insert the new row.

Suppose you will do a batch update and in this batch of data you have multiple items. If you want to update your DB with the latest data then you can do one of the followings:

•     Hit DB to find which data is not there and filter in your code to differentiate between which item should use 'Insert' and which items should  be 'Update'. But in this method the performance will be slower because you have to hit the DB twice for this and you will also filter data in your code that will cost you quite some time.  
• There are other solutions like you can use a stored proc to do the work etc.But m suggestion would be to use oracle "MERGE" statement.

Suppose you have a table called EMP_MASTER(id,name,phone).

It has ID as the primary key.

Now you got a bunch of employee records andd in one shot you want your table to be updated according to the new record.

Suppose earlier the table looked like this :

EMP_MASTER
---------------------------------------
id   -    name   -    phone
--------------------------------
001 - John - 3434353
002 - Mike - 312121

Now in the batch of records you have got (002,Mike,9898),(003,Lucy,2312).

Here number 002 was already present in the database. So if you do a blind update then the new record would throw an exception/or will be neglected based on your query.

So you can use the below MERGE statement:

MERGE INTO EMP_MASTER e USING dual ON (e.id=e.id and e.id=? )
WHEN MATCHED THEN UPDATE SET name=? , phone=?
WHEN NOT MATCHED THEN INSERT (id,name,phone)
    VALUES ( ?,?,? );

It is blazing fast even with a scale of 4000+ records.

I came across this problem a few months back and i had to google a lot to find a suitable solution. I use the above mentioned statement and it helps a lot.

Limitations:-
•    In the batch itself if you have two records with same primary key value and both were previously not present in DB then this statement might throw an exception when used with any framework's batch update statement. Because both the data with duplicate primary key would be tried to insert in single shot so it will throw an Integrity constraint violation exception.

•    You cannot use any key that is used in the 'ON' clause as part of the update statement.

Apart from these the statement is a sweeet for developers who face this problem.

Njoy \m/ :)