Stored Procedure in Hibernate

//Calling Stored procedure from Hibernate

XXXX.hbm.xml
<sql-query name="del_logged_users" callable="true">
        {call del_logged_users()}
</sql-query>

//Java code to call the stored procedure
public void deleteAllUsersDAO() throws Exception {
        String sql = "{call del_logged_users()}";
        Object[] inputParams = {};
        try    {    
            QueryInput queryInput = PersistenceHelper.createQuery("del_logged_users");
            PersistenceHelper.executeNamedQuery(queryInput);
            
            DatabaseUtil dbUtil = new DatabaseUtil();   //See this class below
            dbUtil.setup(getDataSourceName());
            dbUtil.runCall(sql, inputParams);
        } catch (Exception e) {
            StringBuffer message = new StringBuffer("Error while updating data. Original error message: ").
                append(e.getMessage());
            LogHelper.error(CLASS_NAME, METHOD_NAME, message.toString());
        
            throw new InternalException(
                    BusinessServiceMessageConstants.INT_ERRCODE_JDBC_QUERY_ERROR,
                    message.toString(), e);
        }
}

//Supporting class DatabaseUtil.java
private static HashMap datasourceCache = null;

public void setup(String datasource Name) throws NamingException {
        try {
            synchronized (this) {
                if( datasourceCache == null ) {
                    datasourceCache = new HashMap();
                }
                datasource = (DataSource)datasourceCache.get(datasourceName);
                if( datasource == null ){
                    InitialContext context = new InitialContext();
                    datasource = (DataSource)context.lookup(datasourceName);
                    datasourceCache.put(datasourceName, datasource);
                }
            }   // end synchronized block
        }
        catch(NamingException e) {
            e.printStackTrace();
            //Rethrow the exception
            throw e;
        }
}

public void runCall(String sql, Object[] params) throws SQLException, PersistenceException, 
                                                               RequiredParameterException {
    CallableStatement statement = null;
    Connection connection = getConnection();    
    try {
        statement = connection.prepareCall(sql);
        if (params != null) {
            for (int i = 0; i < params.length; i++)    {
                Object par = params[i];
                if (par == null)
                    statement.setNull(i + 1, Types.VARCHAR);
                if (par instanceof String)
                    statement.setString(i + 1, (String) par);
                else if (par instanceof Long)
                    statement.setLong(i + 1, ((Long) par).longValue());
                else if (par instanceof Timestamp)
                    statement.setTimestamp(i + 1, (Timestamp) par);
                else if (par instanceof java.util.Date)
                    statement.setDate(i + 1, new java.sql.Date(((java.util.Date) par)
                            .getTime()));
                else if (par instanceof Integer)
                    statement.setInt(i + 1, ((Integer) par).intValue());
                else if (par instanceof java.math.BigDecimal)
                    statement.setLong(i + 1, ((java.math.BigDecimal) par).longValue());
                // st.setBigDecimal(i+1, (java.math.BigDecimal)par);
            }
        }
        statement.execute();
      //connection.commit(); 
    }
    catch (SQLException e) {
        throw e;
    }
    finally {
        try {            
            if ( statement != null ) {
                statement.close();
            }
            if ( connection != null ) {
                connection.close();
            }
        }
        catch (SQLException e) {        
        } 
    }
}

-----------------------------END----------------------------------------

Difference between JDBC and Hibernate

Difference between JDBC and Hibernate:

Dear reader,
This is a different type of blogs. Here you won't find usual answers for differences.
Let's see JDBC and HIBERNATE: in both cases developers have to write Database codes, the difference is 
only file types. In both cases expertise is needed if you write complex queries. But still there are
some major differences, I will put here:

1) In JDBC, Queries are database dependent. In Hibernate, queries are independent of underlying database.
Here we have vaious SQLDialects for each type of Databases. So JDBC supports only native Structured Query Language (SQL).
But Hibernate uses a powerful query language Hibernate Query Language (independent from type of database) that 
is expressed in a familiar SQL like syntax and includes full support for polymorphic queries. So You don't need to 
change the SQL scripts if you change databases.

2) With JDBC, it is developer’s responsibility to handle JDBC result set and convert it to Java objects through code 
to use this persistent data in application. So with JDBC, mapping between Java objects and database tables is done manually.  
Hibernate reduces lines of code by maintaining object-table mapping itself and returns result to application in form of Java 
objects. It relieves programmer from manual handling of persistent data, hence reducing the development time and maintenance cost.

3) With JDBC, caching is maintained by manual coding. Hibernate provides Caching as application frameworks.
Hibernate also provides features like Attaching/Detaching of objects, so that later you can merge it to Database.
Hibernate caches data at Level 1 and supports optional Level2 caching through third party software (ehcache,
TreeCache/JBoss cache etc.). So Caching is totally external to JDBC.


4) Concurrency is handled manually in JDBC, in JDBC there is no check about the data which is already updated.
This check has to be added by the developer. In Hibernate, we have locking systems. Hibernate enables developer 
to define version type field into application, due to this Hibernate updates version field of table every time 
relational tuple is updated in form of Java object to that table. So if two users retrieve same tuple and then 
modify it and one user saves this modified tuple to database, version is automatically updated for this tuple 
by Hibernate. When other user tries to save updated tuple to database then it does not allow saving it because 
this user does not have updated data. This is achieved by Optimistic-lock.

5) Under the hood, Hibernate uses JDBC only. However developers have DAOs and POJOs to use
any where in application because of hibernate while writing business logics. So it is very easy to make a 
cleaner seperation of Data Access Layer from Business logic layer.

6) Hibernate supports "Lazy loading" of objects which JDBC doesn't support. This feature enable us to get only 
the required object instead of fetching all the entities associated with that. It fetches them only on request
by specifying the join in the queries.

7) Get Dynamic_insert and Dynamic_Update by specifying attribute <class name="P" table="PN" 
dynamic-update="true" dynamic-insert="true"> in *.hbm.xml file so that only few columns gets updated instead of
whole table each time you update a record in DB. So Causing enhanced performance in Hibernate.

8) Hibernate has an extremely good attribute "mutable", by default it is true. If you make a class as "Immutable" means
"mutable=false", then– insert=allowed, delete=allowed, update=not allowed.

--------------------------------End--------------------------------------