Data not retrieving after saving in database in hibernate 4

Question

I am using Hibernate 4 with Spring 4. I have created my own session factory and used Hibernate Transaction Manager. I have a problem while retrieving the data after saving. I am saving the data using ProcedureCall and in every method I am opening the session and closing the session. What is the problem? If I remove session.close() then it is working fine.

public Map<String, Object> savePurchaseOrderInvoiceDetail(String dataString, String order_no,String event, HttpSession hs) throws SQLException, ParseException {
    HibernateTransactionManager htmLocal = (HibernateTransactionManager) hs.getAttribute("HibernateTransactionManager");
    Session session = htmLocal.getSessionFactory().openSession();
    Transaction tx = getTransaction(session);
    ProcedureCall qry = session.createStoredProcedureCall("purchase_order_invoice_api");
    qry.registerParameter(0, String.class, ParameterMode.IN).bindValue(event);
    qry.registerParameter(1, String.class, ParameterMode.IN).bindValue(dataString);
    qry.registerParameter(2, String.class, ParameterMode.OUT);
    qry.registerParameter(3, String.class, ParameterMode.OUT);
    qry.registerParameter(4, Integer.class, ParameterMode.OUT);
    qry.registerParameter(5, String.class, ParameterMode.OUT);
    ProcedureOutputs output = qry.getOutputs();

    String msg = (String) output.getOutputParameterValue(2);
    String voucheNo=(String) output.getOutputParameterValue(3);
    int invoiceId=(int) output.getOutputParameterValue(4);
    String status=(String) output.getOutputParameterValue(5);

    Map<String, Object>map=new HashMap<String, Object>();
    map.put("msg", msg);
    map.put("voucherNo", voucheNo);
    map.put("lastInvoiceId", invoiceId);
    map.put("status", status);

    tx.commit();

    session.close();
    return map;
}

public Map<String, Object> getInvoiceDetails(String invoicedId,HttpSession hs) throws Exception{
    HibernateTransactionManager htmLocal = (HibernateTransactionManager) hs.getAttribute("HibernateTransactionManager");
    Session session = htmLocal.getSessionFactory().openSession();

    final Map<String, Object>map=new HashMap<String, Object>();

    String company=(String) hs.getAttribute("company");
    int invoiceIdInt=Integer.valueOf(invoicedId);


    String qry = "select inv.*,get_supplier_name(inv.Company,inv.Identity) AS CUSTOMER_NAME from invoice_tab inv";
    Query query = session.createSQLQuery(qry).addEntity(Invoice.class);
    query.setCacheable(false);

    List<Invoice> invoiceList = query.list();

    for (int i = 0; i < invoiceList.size(); i++) {
        Invoice invoiceObj=invoiceList.get(i);

        //Business logic
    }

    session.close();
    return map;
}

Answer 1

You are trying hard to to use Spring not to mention the fact that you are having a service (or maybe a repository) dependent on the fact that it is a web application. Both things are bad.

Add the @Transactional annotation to the class containing those methods and enable annotation driven transaction management. Instead of passing around the HttpSession simply inject your dependencies, in this case the SessionFactory.

Don't create sessions yourself use the current session, i.e sessionFactory.getCurrentSession() to obtain a transactional session.

@Service
@Transactional
public class YourService {

    private final SessionFactory sessionFactory;

    public YourService(SessionFactory sf) {
        this.sessionFactory=sf;
    }


    public Map<String, Object> savePurchaseOrderInvoiceDetail(String dataString, String order_no,String event) throws SQLException, ParseException {
        Session session = sessionFactory.getCurrentSession();
        ProcedureCall qry = session.createStoredProcedureCall("purchase_order_invoice_api");
        qry.registerParameter(0, String.class, ParameterMode.IN).bindValue(event);
        qry.registerParameter(1, String.class, ParameterMode.IN).bindValue(dataString);
        qry.registerParameter(2, String.class, ParameterMode.OUT);
        qry.registerParameter(3, String.class, ParameterMode.OUT);
        qry.registerParameter(4, Integer.class, ParameterMode.OUT);
        qry.registerParameter(5, String.class, ParameterMode.OUT);
        ProcedureOutputs output = qry.getOutputs();

        String msg = (String) output.getOutputParameterValue(2);
        String voucheNo=(String) output.getOutputParameterValue(3);
        int invoiceId=(int) output.getOutputParameterValue(4);
        String status=(String) output.getOutputParameterValue(5);

        Map<String, Object>map=new HashMap<String, Object>();
        map.put("msg", msg);
        map.put("voucherNo", voucheNo);
        map.put("lastInvoiceId", invoiceId);
        map.put("status", status);

        return map;
    }

    public Map<String, Object> getInvoiceDetails(int invoicedId, String company) throws Exception{
        Session session = sessionFactory.getCurrentSession();

        final Map<String, Object>map=new HashMap<String, Object>();

        String qry = "select inv.*,get_supplier_name(inv.Company,inv.Identity) AS CUSTOMER_NAME from invoice_tab inv";
        Query query = session.createSQLQuery(qry).addEntity(Invoice.class);
        query.setCacheable(false);

        List<Invoice> invoiceList = query.list();

        for (int i = 0; i < invoiceList.size(); i++) {
            Invoice invoiceObj=invoiceList.get(i);

            //Business logic
        }

        return map;
    }
}   

Something like the above.

What I don't really get is why you are even using hibernate as you are creating your own queries and don't use HQL or anything to get entities. The only thing you use hibernate for is mapping and that can be done with plain SQL also, adding hibernate to your project just for the mapping of your sql results is bit overkill in my book.