IN-clause in HQL or Java Persistence Query Language

For JPA, it should work fine:

String jpql = "from A where name in (:names)";
Query q = em.createQuery(jpql);
q.setParameter("names", l);

For Hibernate's, you'll need to use the setParameterList:

String hql = "from A where name in (:names)";
Query q = s.createQuery(hql);
q.setParameterList("names", l);

Hibernate call store procedure

In Hibernate, there are three approaches to call a database store procedure.
1. Native SQL – createSQLQuery

You can use createSQLQuery() to call a store procedure directly.

Query query = session.createSQLQuery(

	"CALL GetStocks(:stockCode)")

	.addEntity(Stock.class)

	.setParameter("stockCode", "7277");

 

List result = query.list();

for(int i=0; i	Stock stock = (Stock)result.get(i);

	System.out.println(stock.getStockCode());

}




2. NamedNativeQuery in annotation

Declare your store procedure inside the @NamedNativeQueries annotation.




//Stock.java

...

@NamedNativeQueries({

	@NamedNativeQuery(

	name = "callStockStoreProcedure",

	query = "CALL GetStocks(:stockCode)",

	resultClass = Stock.class

	)

})

@Entity

@Table(name = "stock")

public class Stock implements java.io.Serializable {

...





Call it with getNamedQuery().




Query query = session.getNamedQuery("callStockStoreProcedure")

	.setParameter("stockCode", "7277");

List result = query.list();

for(int i=0; i	Stock stock = (Stock)result.get(i);

	System.out.println(stock.getStockCode());

}





3. sql-query in XML mapping file



Declare your store procedure inside the “sql-query” tag.


<!-- Stock.hbm.xml -->

...

<hibernate-mapping>

    <class name="com.mkyong.common.Stock" table="stock" ...>

        <id name="stockId" type="java.lang.Integer">

            <column name="STOCK_ID" />

            <generator class="identity" />

        </id>

        <property name="stockCode" type="string">

            <column name="STOCK_CODE" length="10" not-null="true" unique="true" />

        </property>

        ...

    </class>

 

    <sql-query name="callStockStoreProcedure">

	<return alias="stock" class="com.mkyong.common.Stock"/>

	<![CDATA[CALL GetStocks(:stockCode)]]>

    </sql-query>

 

</hibernate-mapping>





Call it with getNamedQuery().




Query query = session.getNamedQuery("callStockStoreProcedure")

	.setParameter("stockCode", "7277");

List result = query.list();

for(int i=0; i	Stock stock = (Stock)result.get(i);

	System.out.println(stock.getStockCode());

}





Conclusion



The above three approaches are doing the same thing, call a store procedure in database. There are not much big different between the three approaches, which method you choose is depend on your personal prefer.



Ref : link1

How to call a stored procedure with Hibernate ?

Supposing you have the following Oracle Stored procedure:

CREATE FUNCTION agenda RETURN SYS_REFCURSOR
AS 
    my_cursor SYS_REFCURSOR;
BEGIN
    OPEN my_cursor FOR
        SELECT id,person_name,person_surname,person_address
        FROM Person;
    RETURN  my_cursor;
 END;

Invoking it from Hibernate requires mapping the Stored Procedure in the Person class.

<sql-query name="SP_agenda" callable="true">
    <return alias="ev" class="Person">
        <return-property name="id" column="id"/>
        <return-property name="name" column="person_name"/>
        <return-property name="surname" column="person_surname"/>
        <return-property name="address" column="person_address"/>
    </return>
    { ? = call agenda() }
</sql-query>

Then, you can execute the procedure just like a normal Query:

Query query = session.getNamedQuery("SP_agenda");
List results = query.list();

How To Solve org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional on

When we implemented transactions using autowiring and TxManager in the spring config xml file, we ended up in having the well known org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here.

I was googling a bit on "No Hibernate Session bound to thread". Emphatically, I ended up with innumerous answers in several sites on the Internet. Springsource forum is not exception to this! Abundant conversations have been happening on this topic all around!!!

But there were a few best practices that I was able to compile from the chit-chats happening across the Internet. I thought of sharing the same here so that it would help some one some day in the near future!

1. First and foremost, configure the transaction manager properly : Use @Transaction annotation near your code and have a transactionManager bean in your spring config file.
2. when integration a transaction manager, do not use hibernate.current_session_context_class and hibernate.transaction_factory_class in the hibernate properties unless you have proper reasons to
   
3. Never call sessionFactory.openSession()
4. Use ApplicationContext rather than using BeanFactory
5. Have single dao instances running across the Application
6. Above all, most importantly, ensure that the current application context has the component scan for the beans where you have added the @transactional annotations. as per spring documentation's @ Trasactional usage guidelines,
   

only looks for @Transactional on beans in the same application context it is defined in. This means that, if you put in a WebApplicationContext for a DispatcherServlet, it only checks for @Transactional beans in your controllers, and not your services. See Section 15.2, “The DispatcherServlet” for more information.

Point # 6 was the culprit in our case and that was then resolved! So the lessson was You are likely to get "No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here" if you do not adhere to any of the above pointers.
PArticularly, if you are going to have the applicationContext.xml with the TxManager and the servletname-servlet.xml with the component scan annotations, then you are more likely to get this "No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here" exception since the TxManager's transaction holds good for the current application context only (which in this case does not have the bean definitions for the service or the daos where you have the @ Trasactional annotations)

If you find the information pretty helpful, I would really be happy if you would keep me posted via the comments form displayed under this article! If you had wanted some other information related to the same topic, I would suggest you to drop a note to me using the comments form for that would help me in getting back to you with the details you are in need of!

Ref : link

Spring and Hibernate’s getCurrentSession()

If you are using Spring to wrap a Hibernate SessionFactory and you are not using Spring-managed transactions, you may run into an issue. The reason is that Spring by default will wrap Hibernate’s SessionFactory implementation and delegate to its own transactional version. If you are just using the simple ThreadLocal-based session-per-request functionality, then when you attempt to open the Session, you will get an IllegalStateException thrown, with the error message "No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here". This happens because Spring’s SessionFactoryUtils checks if the Session is bound to Spring’s transactional support, and by default throws an error if it is not.

The solution to this is to set the property


<property name="exposeTransactionAwareSessionFactory"><value>false</value></property>

in the Spring config. This will return the “raw” SessionFactory instead of the proxied one. A snippet of code from AbstractSessionFactoryBean shows where the check is done:

 
/**
   * Wrap the given SessionFactory with a transaction-aware proxy, if demanded.
   * @param rawSf the raw SessionFactory as built by buildSessionFactory()
   * @return the SessionFactory reference to expose
   * @see #buildSessionFactory()
   * @see #getTransactionAwareSessionFactoryProxy
   */
  protected SessionFactory wrapSessionFactoryIfNecessary(SessionFactory rawSf) {
    if (isExposeTransactionAwareSessionFactory()) {
       return getTransactionAwareSessionFactoryProxy(rawSf);
    }
    else {
      return rawSf;
    }
  }

A sample Spring config is shown below.


<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:hsql://localhost:9001"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="exposeTransactionAwareSessionFactory"><value>false</value></property>
<property name="annotatedPackages">
<list>
<value>uk.co.researchkitchen.hibernate</value>
</list>
</property>
<property name="annotatedClasses">
<list>
<value>uk.co.researchkitchen.hibernate.Product</value>
<value>uk.co.researchkitchen.hibernate.ProductDescription</value>
</list>
</property>
<property name="hibernateProperties">
<value>
hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=create
hibernate.current_session_context_class=thread
</value>
</property>
</bean>

</beans>