example with spring jdbctemplate with transactions.

If you want to use aop of spring and do a transaction with database you can use @Transaction annotation.  this is how you do it.

Note that this works only with non MyISAM tables. MyISAM does not handle transactions.

Say there is a function you want to have the transaction. then you simply add the @Transaction annotation above the method.
public class BeverageModel{
private DriverManagerDataSource dataSource;
private JdbcTemplate jdbcTemplate;

public void setDataSource(DriverManagerDataSource dataSource){
this.dataSource = dataSource;
}

public DriverManagerDataSource getDataSource() {
return dataSource;
}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}



@Transactional
public int addBeverage(Beverage beverage){
getJdbcTemplate().update("INSERT INTO item (price, item_category) VALUES(?,?)",new Object[] { beverage.getPrice(), beverage.getItemCategory() });

int id = getJdbcTemplate().queryForInt( "SELECT last_insert_id()" );System.out.println(id);

return getJdbcTemplate().update("INSERT INTO beverage (id, name, quantity,size) VALUES(?,?,?,?)",new Object[] { id,beverage.getName(), beverage.getQuantity(),beverage.getSize() });

}
}

Now put the following in your bean configuration file
<?xml version="1.0" encoding="UTF-8"?>

<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:context="http://www.springframework.org/schema/context"xmlns:tx="http://www.springframework.org/schema/tx"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.1.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd ">

<!-- Initialization for data source -->

<bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/hotel_manager"/>
<property name="username" value="root" />
<property name="password" value="pass" />
</bean>

<!-- Definition for beverageModel bean -->
<bean id="beverageModel" class="mad.hotelmanagement.model.item.BeverageModel">
<property name="dataSource" ref="dataSource" />
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<context:annotation-config/>

<!-- Add this tag to enable annotations transactions -->
<tx:annotation-driven transaction-manager="txnManager" proxy-target-class="true" />

<bean id="txnManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="dataSource" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>

</beans>

Note that you have to edit the parameters so that the configuration match with your settings.
Now if you invoke the model class transaction method, you will notice that it will run perfectly.

If you did not use @Transactional annotation, then the method will use connections from different pools. That means the last_inset_id() will return 0 all the time.

So if last_inset_id()  does not return all the time and the method runs smoothly every time then, that is an indication of the transaction is happening without a problem.