Java by examples

As name suggest SPLIT means separate out something. Here we are talking about separating character, words from given string by specifying some special character.

Note: MySQL has limitation that It can't return table from function. That's why we have to create stored procedure of SPLIT Function in order to get value in tabular format.

DROP PROCEDURE IF EXISTS SP_SPLIT;
DELIMITER $$
CREATE PROCEDURE `SP_SPLIT`(
 IN StringValue LONGTEXT,
 IN DelimiterChar  VARCHAR(1))
BEGIN
 DECLARE INDX INT;
 DECLARE SLICE nvarchar(4000); 
 SET INDX = 1;
 
 DROP TEMPORARY TABLE IF EXISTS Results;
 CREATE TEMPORARY TABLE Results(Items VARCHAR(100));

 MyLoop: WHILE INDX != 0 DO
 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
 SET INDX = LOCATE(DelimiterChar,StringValue);
 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
 IF INDX !=0 THEN
  SET SLICE = LEFT(StringValue,INDX - 1);
 ELSE
  SET SLICE = StringValue;
 END IF;
 -- PUT THE ITEM INTO THE RESULTS SET
 INSERT INTO Results(Items) VALUES(SLICE);
 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
 SET StringValue = RIGHT(StringValue,LENGTH(StringValue) - INDX);
 -- BREAK OUT IF WE ARE DONE
 IF LENGTH(StringValue) = 0 THEN
  LEAVE MyLoop;
 END IF;
 END WHILE;
END

Step 1: Execute above Stored Procedure in your MySQL.

Step 2: Call Stored Procedure and select result.

CALL SP_SPLIT("HELLO,WORLD,JAVAQUERY,THIS IS LONG STRING. YOU CAN DO EVEN BETTER",",");
/* Results is table created in SP_SPLIT stored procedure. */
SELECT * FROM RESULTS;

To process further on your result read "MySQL: How to select data from Stored Procedure in to Stored Procedure"

Few days back I wrote an article about How To Call Stored Procedure In Hibernate. After following few migration process of stored procedure. I was struggling to select data from another stored procedure within stored procedure. Finally I came up with solution after Googling few pages and reading books.

TEMPORARY table in MySQL is our wildcard in this database programming. Well TEMPORARY tables should be used carefully or you'll face few problems in MySQL. We'll discuss more about TEMPORARY tables in MySQL in my next article.

STORED PROCEDURE 1
Stored Procedure 1 is where we want to return something after processing some of our inputs.

/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_CALL;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_CALL()
BEGIN
 /* Drop TEMPORARY table if exists. */
 DROP TEMPORARY TABLE IF EXISTS Results;
 /* Create TEMPORARY table */
 CREATE TEMPORARY TABLE Results(Firstname VARCHAR(200));
 
 /* Insert value in TEMPORARY table */
 INSERT INTO Results 
 SELECT Firstname FROM user_master WHERE uid = 1;
END $
DELIMITER ;

STORED PROCEDURE 2
Stored Procedure 2 is where you want to select output of Stored Procedure 1. Here you can access TEMPORARY table Results declared in SP_CALL()

/* Drop procedure existing procedure if exists */
DROP PROCEDURE IF EXISTS SP_SELECT;
DELIMITER $
/* Creating new Stored Procedure */
CREATE PROCEDURE SP_SELECT()
BEGIN
 /* Declare variable */
 DECLARE F_NAME VARCHAR(100);
 
 /* Call Stored Procedure */
 CALL SP_CALL();
 /* Select from TEMPORARY table declared in SP_CALL */
 SELECT Firstname INTO F_NAME FROM Results;
 
 /* Print value of variable */
 SELECT F_NAME;
END $
DELIMITER ;

Database Migration is very important process of product based application. You may find bunch of software to migrate table structure from MSSQL to MySQL. There are very few software (Not any with full conversation) available to migrate Stored Procedure, Functions and Views.

I did this before so better not to waste your precious time to search for tools. You have to manually convert all Stored Procedure, Functions and Views. I came up with some basic rules that may help you to convert all of above.


1. "DECLARE" a variable in MSSQL and MySQL. 
You may think is it really matter? Yes, It is. MSSQL and MySQL follows different rules for declaring variables.

• MSSQL: Variable name starts with '@'.
• MySQL: You can't use '@'.
• -------------------------------------------------------------------------
• MSSQL: You can declare variable anywhere in Stored Procedure, Functions.
• MySQL: You have to declare variable at the starting of Stored Procedure, Functions.

2. "ISNULL" in MSSQL is similar to "IFNULL" in MySQL.

3. Using "IF".

-- MSSQL
IF (condition)
BEGIN
     ......
END
ELSE IF (condition)
BEGIN
     ......
END
ELSE
BEGIN
     ......
END
--------------------------------
-- MySQL
IF (condition) THEN
     ......
ELSEIF (condition) THEN
     ......
ELSE
     ......
END IF;

4. "LEN" in MSSQL is similar to "LENGTH" in MySQL

5. Function in MSSQL and MySQL
You may wonder that MSSQL can return table from function but unfortunately MySQL can't. If you have function in MSSQL which returns table, then you've to convert that function in stored procedure in order to get table as return value. There ain't other option in MySQL. MySQL function can only return single value.

6. MySQL: How to select data from Stored Procedure in to Stored Procedure
Article posted at http://www.javaquery.com/2014/01/mysql-how-to-select-data-from-stored.html

7. Temporary tables in MySQL.

Its not written here because its very important part of conversion. We need to discuss it with proper examples. I'll write separate articles for it.

MSSQL to MySQL Guide to Migration Part - 2 is published at http://www.javaquery.com/2014/02/mssql-to-mysql-guide-to-migration-part-2.html

What is STORED PROCEDURE?
A stored procedure is a pre-compiled SQL subroutine used to perform multiple procedural operations.


What will we cover in this article?

• Sample MSSQL and MYSQL stored procedure.
• How to call MSSQL and MYSQL stored procedure.
• What should be taken care while executing stored procedure in Hibernate?

IF (OBJECT_ID('SP_MSSQL_HIBERNATE') IS NOT NULL)
  DROP PROCEDURE SP_MSSQL_HIBERNATE
GO
CREATE PROCEDURE SP_MSSQL_HIBERNATE
@PARAM1 INT,
@PARAM2 INT,
@PARAM3 VARCHAR(50)
AS 
BEGIN
 BEGIN TRANSACTION
 BEGIN TRY
    /* 
     * Uncomment below code to get custom row in hibernate.
     * ------------------------------------------------
     * DECLARE @X, @Y INT;
     * DECLARE @RESULT INT;
     * SET @RESULT = @X + @Y;
     * SELECT @RESULT AS RESULT, 'javaQuery' AS STRING_RESULT;
     */
 
    /* Your custom operation */ 
    UPDATE user_master SET Firstname = @PARAM3 WHERE UID = 1;
           
    /* Insert record */
    INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');
           
    /* Returns user object (row) */
    SELECT * FROM user_master WHERE UID = 1;

 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
          ROLLBACK TRANSACTION;
          PRINT @@ERROR
 END CATCH;
END

DROP PROCEDURE IF EXISTS SP_MYSQL_HIBERNATE;
DELIMITER $
CREATE PROCEDURE SP_MYSQL_HIBERNATE(IN PARAM1 INT, IN PARAM2 INT, IN PARAM3 VARCHAR(100))
BEGIN
 /*
  * Uncomment below code to get custom row in hibernate.
  * ------------------------------------------------
  * DECLARE X, Y INT DEFAULT 10;
  * DECLARE RESULT INT;
  * SET RESULT = X + Y;
  * SELECT RESULT AS RESULT, 'javaQuery' AS STRING_RESULT; 
  */

  /* Your custom operation */ 
  UPDATE user_master SET Firstname = PARAM3 WHERE UID = 1;
        
  /* Insert record */
  INSERT INTO user_master (Firstname, Lastname) VALUES('Jennifer ', 'Lawrence');

  /* Returns user object (row) */
  SELECT * FROM user_master WHERE UID = 1;
END $
DELIMITER ;

---

How to call MSSQL and MYSQL stored procedure in Hibernate?

/**
 * Create hibernate configuration.
 */
Configuration c = new Configuration();
c.configure("hibernate.cfg.xml");

/**
 * Open session and begin database transaction for database operation.
 */
SessionFactory sf = c.buildSessionFactory();
Session session = sf.openSession();
Transaction t = session.beginTransaction();

/**
 * Create SQL Query for Stored Procedure and pass required parameters.
 * MSSQL : EXEC Name_Of_Stored_Procedure :param1, :param2
 * MYSQL : CALL Name_Of_Stored_Procedure :param1, :param2
 * 
 * `.addEntity(User.class)` will map output result as per User bean.
 */

/**************************************************/
/* Call MSSQL Stored Procedure and MAP it to bean */
/* Un-comment the code                            */
/**************************************************/
/*Query callStoredProcedure_MSSQL = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3").addEntity(User.class);
callStoredProcedure_MSSQL.setInteger("param1", 10);
callStoredProcedure_MSSQL.setInteger("param2", 10);
callStoredProcedure_MSSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
/*List userList = callStoredProcedure_MSSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}*/


/**************************************************/
/* Call MYSQL Stored Procedure and MAP it to bean */
/**************************************************/
Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");

/* callStoredProcedure_MSSQL.list() will execute stored procedure and return the value */
List userList = callStoredProcedure_MYSQL.list();
if (userList != null && !userList.isEmpty()) {
 for(User user : userList){
  System.out.println("Firstname:"+user.getFirstname());
 }
}

/******************************************************************/
/* Process custom result of Stored Procedure                      */
/* Un-comment the code, This will be the same for MSSQL and MYSQL */
/******************************************************************/
/*Query callStoredProcedure_MYSQL = session.createSQLQuery("CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)");
callStoredProcedure_MYSQL.setInteger("param1", 10);
callStoredProcedure_MYSQL.setInteger("param2", 10);
callStoredProcedure_MYSQL.setString("param3", "javaQuery");*/

/* callStoredProcedure_MYSQL.list() will execute stored procedure and return the value */
/*List

Above code contains 3 portion for execution. Un-comment your required code and test it.

1. How to Execute MSSQL Stored Procedure in Hibernate (code is commented)
2. How to Call MYSQL Stored Procedure in Hibernate
3. How to Process custom result of Stored Procedure in Hibernate (code is commented)

I'm creating sample code for one of my article of hibernate and database. I got exception while calling Stored Procedure from java in hibernate.

So I googled and after trying different option found the solution and thought let me post it in proper way so you guys don't need to waste your time to check other options.

EXCEPTION

Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:643)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:506)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:979)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
 at org.hibernate.loader.Loader.doQuery(Loader.java:662)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
 at org.hibernate.loader.Loader.doList(Loader.java:2211)
 ... 7 more

CODE WITH ERROR

Query callStoredProcedure = session.createSQLQuery("EXEC SP_MSSQL_HIBERNATE (:param1, :param2, :param3)").addEntity(User.class);
callStoredProcedure.setInteger("param1", 10);
callStoredProcedure.setInteger("param2", 10);
callStoredProcedure.setString("param3", "javaQuery");
      
List userList = callStoredProcedure.list();

SOLUTION

• If you are calling MSSQL stored procedure then SQL query must be without Parentheses ( and ).
  e.g EXEC SP_MSSQL_HIBERNATE :param1, :param2, :param3
• If you are calling MYSQL stored procedure then SQL query must be with Parentheses ( and ).
  e.g CALL SP_MYSQL_HIBERNATE (:param1, :param2, :param3)

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As described above database index helps only for search operation. Insert, Update and Delete will suffer. Index should be created only selected fields based on your retrieval of data (Columns).

- Source (WikiPedia: http://en.wikipedia.org/wiki/Database_index)

Indexing is serious business for any large application or small application. Haphazard use of Indexing will create an overhead on database so be careful while dealing with index.

Today, There is nothing much to say. All I can say is you can bookmark this article for future use of these queries. It may help you to save your time rather then wasting time on Googling.

SHOW ALL INDEXES

/* Use `*` to check out other fields. */
SELECT distinct ind.name as IndexName,t.name as TableName
FROM 
sys.indexes ind 
INNER JOIN 
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN 
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
sys.tables t ON ind.object_id = t.object_id 
WHERE 
ind.is_primary_key = 0 
AND ind.is_unique_constraint = 0 
AND t.is_ms_shipped = 0
AND ind.is_unique IN(0, 1)

DROP ALL INDEXES "UNIQUE" and "NON UNIQUE"

DECLARE @query nvarchar(max);
SELECT @query = 
(SELECT distinct 'DROP INDEX '+ ind.name + ' ON ' + t.name +';'
FROM 
sys.indexes ind 
INNER JOIN 
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN 
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
sys.tables t ON ind.object_id = t.object_id 
WHERE 
ind.is_primary_key = 0  
AND ind.is_unique_constraint = 0 
AND t.is_ms_shipped = 0
AND ind.is_unique IN(0, 1)
for xml path(''));
EXEC sp_executesql @query 

In previous tutorial "Getting started with JACOB. An Example with Source Code" I gave you brief information about JACOB and how it works. In this tutorial we'll mainly focus on How you can create your own .DLL file for your customized system request. I created .cs (.dll) file to read system's encryption/decryption and signing certificates.

Technically speaking, Java guys take help of .Net guys around you. Don't waste time to learn another language. If you are geek then learn it. Lets roll...

Creating first .cs (.dll) file.

Step 1: Install Visual Studio on your Machine (Recommend 2010 or up).

Step 2: Create New Project > Class Library


Step 3: IDE will provide you one file called Class1.cs use or add new by Right click on Project > Add > New Item (Ctrl + Shift + A). Select Class and give filename. Here I named it math.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

/**
 * @author vicky.thakor
 */
namespace jacobJavaQuery
{
    public class math
    {
        public int add(int x, int y)
        {
            return x + y;
        }

        public int sub(int x, int y)
        {
            return x - y;
        }
    }
}

Step 4: Open up AssemblyInfo.cs file from project properties and set ComVisible value to true.


Step 5: Right click on Project and open up Properties > Build > Check Register for COM interop. Its important to follow all steps because one missing may lead to failed COM call from Java.

Step 6: You are done with .cs file now you need to create .dll file. Right click on Project and click on build. It'll create .dll and .tld file at <project>\bin\Debug

package javaQuery.jacob;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.LibraryLoader;
import com.jacob.com.Variant;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

/**
 * @author vicky.thakor
 * @date 28th December, 2013
 * http://sourceforge.net/projects/jacob-project/
 * First Program to understand how to use JACOB library
 */
public class JACOBCustomDLL {

    public static void main(String[] args) {
        /**
         * `System.getProperty("os.arch")`
         * It'll tell us on which platform Java Program is executing. Based on that we'll load respective DLL file.
         * Placed under same folder of program file(.java/.class).
         */
        String libFile = System.getProperty("os.arch").equals("amd64") ? "jacob-1.17-x64.dll" : "jacob-1.17-x86.dll";
        try {
            /* Read DLL file*/
            InputStream inputStream = jacobExample.class.getResourceAsStream(libFile);
            /**
             *  Step 1: Create temporary file under <%user.home%>\AppData\Local\Temp\jacob.dll 
             *  Step 2: Write contents of `inputStream` to that temporary file.
             */
            File temporaryDll = File.createTempFile("jacob", ".dll");
            FileOutputStream outputStream = new FileOutputStream(temporaryDll);
            byte[] array = new byte[8192];
            for (int i = inputStream.read(array); i != -1; i = inputStream.read(array)) {
                outputStream.write(array, 0, i);
            }
            outputStream.close();
            /**
             * `System.setProperty(LibraryLoader.JACOB_DLL_PATH, temporaryDll.getAbsolutePath());`
             * Set System property same like setting java home path in system.
             * 
             * `LibraryLoader.loadJacobLibrary();`
             * Load JACOB library in current System.
             */
            System.setProperty(LibraryLoader.JACOB_DLL_PATH, temporaryDll.getAbsolutePath());
            LibraryLoader.loadJacobLibrary();

            /**
             * Create ActiveXComponent using CLSID. You can also use program id here.
             * Next line(commented line/compProgramID) shows you how you can create ActiveXComponent using ProgramID.
             */
            ActiveXComponent compProgramID = new ActiveXComponent("jacobJavaQuery.math");

            System.out.println("The Library been loaded, and an activeX component been created");

            int arg1 = 100;
            int arg2 = 50;
            /**
             * Call .cs(.dll) function/method as follow
             */
            int result = Dispatch.call(compProgramID, "add", arg1, arg2).toInt();
            System.out.println("Result: " + result);

            /* Temporary file will be removed after terminating-closing-ending the application-program */
            temporaryDll.deleteOnExit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The JACOB Project
JACOB stands for JAva COM Bridge. The JACOB project started in 1999 by Dan Adler. It allows java programs to make native calls to system. JACOB supports on window x86 and x64 based native call.

Well its very useful library, however there are very few support article available on internet. We'll first understand basic java program that uses JACOB library and interacts with windows program that allows COM bridge support.

What JACOB actually do?
JACOB creates ActiveXObject of program identified using Program ID, Version independent Program ID or CLSID (Class ID aka Globally Unique Identifier) Read more about How to find CLSID or Program ID of program?

First Program With JACOB

package javaQuery.jacob;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.LibraryLoader;
import com.jacob.com.Variant;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

/**
 * @author vicky.thakor
 * @date 28th December, 2013
 * http://sourceforge.net/projects/jacob-project/
 * First Program to understand how to use JACOB library
 */
public class JACOBGettingStarted {

    public static void main(String[] args) {
        /**
         * `System.getProperty("os.arch")`
         * It'll tell us on which platform Java Program is executing. Based on that we'll load respective DLL file.
         * Placed under same folder of program file(.java/.class).
         */
        String libFile = System.getProperty("os.arch").equals("amd64") ? "jacob-1.17-x64.dll" : "jacob-1.17-x86.dll";
        try {
            /* Read DLL file*/
            InputStream inputStream = jacobExample.class.getResourceAsStream(libFile);
            /**
             *  Step 1: Create temporary file under <%user.home%>\AppData\Local\Temp\jacob.dll 
             *  Step 2: Write contents of `inputStream` to that temporary file.
             */
            File temporaryDll = File.createTempFile("jacob", ".dll");
            FileOutputStream outputStream = new FileOutputStream(temporaryDll);
            byte[] array = new byte[8192];
            for (int i = inputStream.read(array); i != -1; i = inputStream.read(array)) {
                outputStream.write(array, 0, i);
            }
            outputStream.close();
            /**
             * `System.setProperty(LibraryLoader.JACOB_DLL_PATH, temporaryDll.getAbsolutePath());`
             * Set System property same like setting java home path in system.
             * 
             * `LibraryLoader.loadJacobLibrary();`
             * Load JACOB library in current System.
             */
            System.setProperty(LibraryLoader.JACOB_DLL_PATH, temporaryDll.getAbsolutePath());
            LibraryLoader.loadJacobLibrary();

            /**
             * Create ActiveXComponent using CLSID. You can also use program id here.
             * Next line(commented line/compProgramID) shows you how you can create ActiveXComponent using ProgramID.
             */
            ActiveXComponent compCLSID = new ActiveXComponent("clsid:{00024500-0000-0000-C000-000000000046}");
            /*ActiveXComponent compProgramID = new ActiveXComponent("Excel.Application");*/

            System.out.println("The Library been loaded, and an activeX component been created");
            
            /**
             * This is function/method of Microsoft Excel to use it with COM bridge.
             * Excel methods and its use can be found on
             * http://msdn.microsoft.com/en-us/library/bb179167(v=office.12).aspx
             * 
             * Understand code:
             * 1. Make Excel visible
             * 2. Get workbook of excel object.
             * 3. Open 1test.xls1 file in excel
             */
            Dispatch.put(compCLSID, "Visible", new Variant(true));
            Dispatch workbook = compCLSID.getProperty("Workbooks").toDispatch();
            Dispatch.call(workbook, "Open", new Variant("D:\\test\\test.xls"));

            /* Temporary file will be removed after terminating-closing-ending the application-program */
            temporaryDll.deleteOnExit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Other Resources
1. JACOB Library
2. Microsoft Excel methods for COM bridge interaction
- For other Programs you've to Google it.

What's Next?
I'm gonna help you to learn How you can create your own DLL file for your customized system request and An applet example with source code. Well you've to wait for my next article. Subscribe to catch updates.

Other Resources:
Jacob + Visual Studio. Your custom .DLL to use in Java.

Its very common for .Net guys but for Java Developer its hard as rocket science. I'm one of 'em until dig into it. This article for those who don't know how to find CLSID, Program ID and Version independent program ID.

All you need is one tool called OLE/COM Object Viewer by Microsoft. Its provided with Visual Studio but you don't have to download visual studio just to get OLE/COM Object Viewer. Its is standalone graphical utility which can be downloaded from free of cost.

Download : http://www.softpedia.com/get/System/System-Miscellaneous/OLE-COM-Object-Viewer.shtml

Step 1: You know what to do. Install it.

Step 2: Object Classes > All Objects

What is Hibernate?
Hibernate is framework that provides object oriented interaction with database. Now a days  it is used widely to handle large database.

If you are working behind large application which uses hibernate for all of its transaction, I think you might need to configure query log and tune up your hibernate criteria.


Problem: JOIN is one creates massive overload in hibernate criteria. To get your desired output you always use JOIN in your criteria. You should be careful while using JOIN in any of your hibernate criteria or queries (HQL).  Single JOIN will load tons of database records and create overhead on your application.

Why you need to configure query log?
For development purpose it will be very useful to track down each query execution through hibernate. I'm not talking about hibernate.show_sql property. It'll only show you query with ? but won't print actual input and output of that query.

We'll configure log4j to get input query parameter and output values. You should turn off this mechanism at production server of will create IO overhead on server.

Step 1: Creating log4j.properties file. I already created one for you all you need to do is place it in your project.

log4j.logger.org.hibernate=INFO
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.hql.ast.AST=info
log4j.logger.org.hibernate.tool.hbm2ddl=warn
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.cache=info
log4j.logger.org.hibernate.jdbc=debug
log4j.rootLogger = DEBUG, FILE

# Define the file appender
log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender
# Set the DatePattern
log4j.appender.FILE.DatePattern='.'yyyy-MM-dd-HH-mm

# Set the append to false, should not overwrite
log4j.appender.FILE.Append=true

# Set the name of the file.
# It'll be created every minute with different filename(appnded with yyyy-MM-dd-HH-mm) and placed under log4j folder.
log4j.appender.FILE.File=log4j/QueryLog.log

# Define the layout for file appender
log4j.appender.FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.FILE.layout.conversionPattern= %d{HH:mm:ss} %-5p %c - %m%n

Step 2: Configure this log4j.properties file before you perform any database transaction (Select, Insert, Update, Delete). Note: Download log4j [http://www.findjar.com/jar/log4j/jars/log4j-1.2.15.jar.html]

import org.apache.log4j.PropertyConfigurator; // import required
/** 
  * In below code I placed log4j.properties as string. 
  * Its file path, I placed log4j.properties in root directory/default package. You need to change path. 
  * If you are working with web application place it in war folder for below code to work.
  */
PropertyConfigurator.configure("log4j.properties");

Output:

17:43:45 DEBUG org.hibernate.SQL - select this_.UID as UID0_0_, this_.FIRSTNAME as FIRSTNAME0_0_, this_.Lastname as Lastname0_0_ from user_master this_ where this_.UID=?
17:43:45 TRACE org.hibernate.type.IntegerType - binding '1' to parameter: 1
17:43:45 DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
17:43:45 TRACE org.hibernate.type.IntegerType - returning '1' as column: UID0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Vicky' as column: FIRSTNAME0_0_
17:43:45 TRACE org.hibernate.type.StringType - returning 'Thakor' as column: Lastname0_0_

As you can see it shows what is value of input parameter where this_.UID=? with binding '1' to parameter: 1 and return values of fired query.

Conclusion: We have to be very careful about what we are trying to fetch from database and what will be fetched by frameworks. So configure it in your application and tune it up!