Hibernate, proper way to modify multiple tables in one transaction

Question

For a project, in which we are using Hibernate, we have a one DAO per table pattern. In each DAO call we create a transaction and commit after it finish executing.

Now, we are in need of updating two tables in the same transaction. The case is quite common, we need to emit a sales check and then reduce the quantity of the object sold from our stock. Of course, this must all be done in the same transaction.

So, my question is, which is the proper way to modify multiple tables in one single transaction. Here is an example of one of our DAO's. As you can see, each DAO is used only to handle one table.

We haven't implement either the billing or the stock DAO. We would like to know which is the proper way to implement our need. Any help, would be great.

@Service
public class StoreDaoImplementation implements StoreDao {

// We are gonna use a session-per-request pattern, for each data access object (dao).
// In order to use it, we need an session factory that will provide us with sessions for each request.
private SessionFactory factory;

public StoreDaoImplementation() {
    try{
        factory = new Configuration().configure().buildSessionFactory();
    }catch(Exception e){
        e.printStackTrace();
    }
}

/**
 * {@inheritDoc}
 */
@Override
public Tienda findById(String storeId) {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    try  {
        return session.get(Tienda.class, storeId);
    } catch (Exception e) {
        return null;
    } finally {
        tx.commit();
    }
}

/**
 * {@inheritDoc}
 */
@Override
public void insert(Tienda tienda) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    session.persist(tienda);
    tx.commit();
}

/**
 * {@inheritDoc}
 */
@Override
public void delete(Tienda tienda) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    session.delete(tienda);
    tx.commit();
}

/**
 * {@inheritDoc}
 */
@Override
public void update(Tienda tienda) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    session.merge(tienda);
    tx.commit();
}

/**
 * {@inheritDoc}
 */
@Override
public void updateSupervisor(List storeIds, String supervisorId) throws Exception {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    Query query = session.createQuery("UPDATE Tienda SET supervisor.idEmpleadoPk = :supervisorId WHERE idTiendaPk IN (:storeIds)");
    query.setParameter("supervisorId", supervisorId);
    query.setParameter("storeIds", storeIds);
    query.executeUpdate();
    tx.commit();
}

/**
 * {@inheritDoc}
 */
@Override
public List getAllStores(Integer offset,
                                 Integer rowNumber,
                                 String franchise,
                                 String country,
                                 Boolean state,
                                 String storeName,
                                 Integer orderBy,
                                 Integer orderType) {
    // Always use getCurrentSession, instead of openSession.
    // Only in very odd cases, should the latter be used, then we must remember to close our session.
    // For more information, read the following docs.
    // http://docs.jboss.org/hibernate/core/3.3/reference/en/html/transactions.html#transactions-basics-uow
    Session session = factory.getCurrentSession();
    // All hibernate transactions must be executed in an active transaction.
    Transaction tx = session.beginTransaction();
    try {
        setSessionFilter(session, franchise, country, state, storeName);
        // NOTE: In this query I am using join fetch. This is due a Hibernate bug, that won't allow
        // setting the fetch type in the mapping file. What this does, is that instead of doing multiple selects
        // for each join, it just simply does a big join in the main query.
        // Much faster if you are working with a remote server.
        String hql = "from Tienda T join fetch T.supervisor join fetch T.franquiciaFK join fetch T.pais";
        switch ((orderBy != null) ? orderBy : 4) {
            case 0:
                hql += " order by T.franquiciaFK.franquiciaPk";
                break;
            case 1:
                hql += " order by T.pais.paisPk";
                break;
            case 2:
                hql += " order by T.provincia";
                break;
            case 3:
                hql += " order by T.supervisor.idEmpleadoPk";
                break;
            default:
                hql += " order by T.franquiciaFK.orden";
                break;
        }
        switch ((orderType != null) ? orderType : 0) {
            case 0:
                hql += " asc";
                break;
            case 1:
                hql += " desc";
                break;
        }
        Query query = session.createQuery(hql);
        query.setFirstResult(offset);
        query.setMaxResults(rowNumber-1);
        return query.list();
    } catch (Exception e) {
        return null;
    } finally {
        tx.commit();
    }
}

/**
 * {@inheritDoc}
 */
@Override
public List getStoresBySupervisor(String supervisorId) {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    try {
        // NOTE: In this query I am using join fetch. This is due a Hibernate bug, that won't allow
        // setting the fetch type in the mapping file. What this does, is that instead of doing multiple selects
        // for each join, it just simply does a big join in the main query.
        // Much faster if you are working with a remote server.
        Query query = session.createQuery("from Tienda T join fetch T.supervisor join fetch T.franquiciaFK join fetch T.pais where T.supervisor.idEmpleadoPk = :supervisorId");
        query.setParameter("supervisorId", supervisorId);
        return query.list();
    } catch (Exception e) {
        return null;
    } finally {
        tx.commit();
    }
}

/**
 * {@inheritDoc}
 */
@Override
public Integer countAllStores(String franchise, String country, Boolean state, String storeName) {
    Session session = factory.getCurrentSession();
    Transaction tx = session.beginTransaction();
    try {
        // Check that the filters are not null.
        setSessionFilter(session, franchise, country, state, storeName);
        Query query = session.createQuery("select count(*) from Tienda");
        return ((Long) query.iterate().next()).intValue();
    } catch (Exception e) {
        return null;
    } finally {
        tx.commit();
    }
}

/**
 * Given that we already know the all filters we can use in our stores' queries,
 * we can make a method to configure them.
 * @param session Actual session that will query the DB.
 * @param franchise Franchise filter. Only returns those store of the specified franchise.
 * @param country Country filter. Only returns those store of the specified country.
 * @param state State filter. Only returns those stores of the specified state.
 */
private void setSessionFilter(Session session, String franchise, String country, Boolean state, String name) {
    if(franchise != null && !franchise.isEmpty()) {
        session.enableFilter("storeFranchiseFilter").setParameter("franchiseFilter", franchise);
    }
    if(country != null && !country.isEmpty()) {
        session.enableFilter("storeCountryFilter").setParameter("countryFilter", country);
    }
    if(state != null) {
        session.enableFilter("storeStateFilter").setParameter("stateFilter", state);
    }
    if(name != null && !name.isEmpty()) {
        session.enableFilter("storeNameFilter").setParameter("nameFilter", "%"+name+"%");
    }
}
}

Vishrant · Accepted Answer

You can use Transactional annotation, it gives more control like rollback on exception, and working with multiple transactions.

public interface BillingService {

    public BillingDAO getBalance();

}

@Service(value = "billingService")
@Transactional("transactionManager")
public class BillingServiceImpl implements BillingService {

    @Autowired
    private SessionFactory sessionFactory;

    @Override
    // Transactional // you can have method level transaction manager, which can be different from one method to another
    public BillingDAO getBalance(long id) {
        return sessionFactory.getCurrentSession().get(BillingDAO.class, id);
    }

}

public interface StockService {

    public StockDAO getStock();

}

@Service(value = "stockService")
@Transactional("transactionManager")
public class StockServiceImpl implements StockService {

    @Autowired
    private SessionFactory sessionFactory;

    @Autowired
    private BillingService billingService;

    @Override
    // Transactional
    public StockDAO getStock(long id) {

        // if you want to use billing related changes, use billing server which is autowired
        BillingDAO billingDAO = billingService.getBalance(id);

        return sessionFactory.getCurrentSession().get(StockDAO.class, billingDAO.getStockId());
    }

}

@Configuration
@EnableTransactionManagement
public class DatabaseConfig {

    @Autowired
    private ApplicationContext appContext;

    @Autowired
    private ApplicationProperties applicationProperties;

    @Bean
    public HikariDataSource getDataSource() {
        HikariDataSource dataSource = new HikariDataSource();

        dataSource
            .setDataSourceClassName(applicationProperties.getHibernateDatasource());
        dataSource.addDataSourceProperty("databaseName", applicationProperties.getRdbmsDatabase());
        dataSource.addDataSourceProperty("portNumber", applicationProperties.getRdbmsPort());
        dataSource.addDataSourceProperty("serverName", applicationProperties.getRdbmsServer());
        dataSource.addDataSourceProperty("user", applicationProperties.getRdbmsUser());
        dataSource.addDataSourceProperty("password", applicationProperties.getRdbmsPassword());

        return dataSource;
    }

    @Bean("transactionManager")
    public HibernateTransactionManager transactionManager() {
        HibernateTransactionManager manager = new HibernateTransactionManager();
        manager.setSessionFactory(hibernate5SessionFactoryBean().getObject());
        return manager;
    }

    @Bean(name = "sessionFactory")
    public LocalSessionFactoryBean hibernate5SessionFactoryBean() {
        LocalSessionFactoryBean localSessionFactoryBean = new LocalSessionFactoryBean();
        localSessionFactoryBean.setDataSource(appContext
                .getBean(HikariDataSource.class));
        localSessionFactoryBean.setAnnotatedClasses(BillingDAO.class);

        Properties properties = new Properties();

        // properties.put("hibernate.current_session_context_class","thread");
        // // because I am using Spring, it will take care of session context
        /*
         * 
         * Spring will by default set its own CurrentSessionContext
         * implementation (the SpringSessionContext), however if you set it
         * yourself this will not be the case. Basically breaking proper
         * transaction integration.
         * 
         * Ref:
         * https://stackoverflow.com/questions/18832889/spring-transactions-and-hibernate-current-session-context-class
         */
        properties.put("hibernate.dialect",
                applicationProperties.getHibernateDialect());

        properties.put("hibernate.hbm2ddl.auto", applicationProperties.getHibernateHbm2ddlAuto());
        properties.put("hibernate.show_sql", applicationProperties.getShowSql());
        // properties.put("hibernate.hbm2ddl.import_files",
        // "/resource/default_data.sql"); // this will execute only
        // when hbm2ddl.auto is set to "create" or "create-drop"
        // properties.put("connection.autocommit", "true");

        localSessionFactoryBean.setHibernateProperties(properties);
        return localSessionFactoryBean;
    }
}

Hibernate, proper way to modify multiple tables in one transaction

Answers (1)

Related Questions