CREATE TABLE Student(
  ID  INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Marks(
  SID INT NOT NULL,
  MARKS INT NOT NULL,
  YEAR  INT NOT NULL
);

package com.yiibai;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
  /** 
  * This is the method to be used to initialize
  * database resources ie. connection.
  */
  public void setDataSource(DataSource ds);
  /** 
  * This is the method to be used to create
  * a record in the Student and Marks tables.
  */
  public void create(String name, Integer age, Integer marks, Integer year);
  /** 
  * This is the method to be used to list down
  * all the records from the Student and Marks tables.
  */
  public List<StudentMarks> listStudents();
}

package com.yiibai;

public class StudentMarks {
  private Integer age;
  private String name;
  private Integer id;
  private Integer marks;
  private Integer year;
  private Integer sid;

  public void setAge(Integer age) {
   this.age = age;
  }
  public Integer getAge() {
   return age;
  }

  public void setName(String name) {
   this.name = name;
  }
  public String getName() {
   return name;
  }

  public void setId(Integer id) {
   this.id = id;
  }
  public Integer getId() {
   return id;
  }
  public void setMarks(Integer marks) {
   this.marks = marks;
  }
  public Integer getMarks() {
   return marks;
  }

  public void setYear(Integer year) {
   this.year = year;
  }
  public Integer getYear() {
   return year;
  }

  public void setSid(Integer sid) {
   this.sid = sid;
  }
  public Integer getSid() {
   return sid;
  }
}

package com.yiibai;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMarksMapper implements RowMapper<StudentMarks> {
  public StudentMarks mapRow(ResultSet rs, int rowNum) throws SQLException {

   StudentMarks studentMarks = new StudentMarks();

   studentMarks.setId(rs.getInt("id"));
   studentMarks.setName(rs.getString("name"));
   studentMarks.setAge(rs.getInt("age"));
   studentMarks.setSid(rs.getInt("sid"));
   studentMarks.setMarks(rs.getInt("marks"));
   studentMarks.setYear(rs.getInt("year"));

   return studentMarks;
  }
}

package com.yiibai;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

public class StudentJDBCTemplate implements StudentDAO {
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplateObject;
  private PlatformTransactionManager transactionManager;

  public void setDataSource(DataSource dataSource) {
   this.dataSource = dataSource;
   this.jdbcTemplateObject = new JdbcTemplate(dataSource);
  }

  public void setTransactionManager(
   PlatformTransactionManager transactionManager) {
   this.transactionManager = transactionManager;
  }

  public void create(String name, Integer age, Integer marks, Integer year){

   TransactionDefinition def = new DefaultTransactionDefinition();
   TransactionStatus status = transactionManager.getTransaction(def);

   try {
     String SQL1 = "insert into Student (name, age) values (?, ?)";
     jdbcTemplateObject.update( SQL1, name, age);

     // Get the latest student id to be used in Marks table
     String SQL2 = "select max(id) from Student";
     int sid = jdbcTemplateObject.queryForInt( SQL2 );

     String SQL3 = "insert into Marks(sid, marks, year) " + 
            "values (?, ?, ?)";
     jdbcTemplateObject.update( SQL3, sid, marks, year);

     System.out.println("Created Name = " + name + ", Age = " + age);
     transactionManager.commit(status);
   } catch (DataAccessException e) {
     System.out.println("Error in creating record, rolling back");
     transactionManager.rollback(status);
     throw e;
   }
   return;
  }

  public List<StudentMarks> listStudents() {
   String SQL = "select * from Student, Marks where Student.id=Marks.sid";

   List <StudentMarks> studentMarks = jdbcTemplateObject.query(SQL, 
                     new StudentMarksMapper());
   return studentMarks;
  }
}

package com.yiibai;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.yiibai.StudentJDBCTemplate;

public class MainApp {
  public static void main(String[] args) {
   ApplicationContext context = 
       new ClassPathXmlApplicationContext("Beans.xml");

   StudentJDBCTemplate studentJDBCTemplate = 
   (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
   
   System.out.println("------Records creation--------" );
   studentJDBCTemplate.create("Zara", 11, 99, 2010);
   studentJDBCTemplate.create("Nuha", 20, 97, 2010);
   studentJDBCTemplate.create("Ayan", 25, 100, 2011);

   System.out.println("------Listing all the records--------" );
   List<StudentMarks> studentMarks = studentJDBCTemplate.listStudents();
   for (StudentMarks record : studentMarks) {
     System.out.print("ID : " + record.getId() );
     System.out.print(", Name : " + record.getName() );
     System.out.print(", Marks : " + record.getMarks());
     System.out.print(", Year : " + record.getYear());
     System.out.println(", Age : " + record.getAge());
   }
  }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans-3.0.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/TEST"/>
   <property name="username" value="root"/>
   <property name="password" value="password"/>
  </bean>

  <!-- Initialization for TransactionManager -->
  <bean id="transactionManager" 
   class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
   <property name="dataSource" ref="dataSource" />  
  </bean>

  <!-- Definition for studentJDBCTemplate bean -->
  <bean id="studentJDBCTemplate"
   class="com.yiibai.StudentJDBCTemplate">
   <property name="dataSource" ref="dataSource" />
   <property name="transactionManager" ref="transactionManager" />  
  </bean>
   
</beans>

------Records creation--------
Created Name = Zara, Age = 11
Created Name = Nuha, Age = 20
Created Name = Ayan, Age = 25
------Listing all the records--------
ID : 1, Name : Zara, Marks : 99, Year : 2010, Age : 11
ID : 2, Name : Nuha, Marks : 97, Year : 2010, Age : 20
ID : 3, Name : Ayan, Marks : 100, Year : 2011, Age : 25

CREATE TABLE Student(
  ID  INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Marks(
  SID INT NOT NULL,
  MARKS INT NOT NULL,
  YEAR  INT NOT NULL
);

package com.yiibai;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
  /** 
  * This is the method to be used to initialize
  * database resources ie. connection.
  */
  public void setDataSource(DataSource ds);
  /** 
  * This is the method to be used to create
  * a record in the Student and Marks tables.
  */
  public void create(String name, Integer age, Integer marks, Integer year);
  /** 
  * This is the method to be used to list down
  * all the records from the Student and Marks tables.
  */
  public List<StudentMarks> listStudents();
}

package com.yiibai;

public class StudentMarks {
  private Integer age;
  private String name;
  private Integer id;
  private Integer marks;
  private Integer year;
  private Integer sid;

  public void setAge(Integer age) {
   this.age = age;
  }
  public Integer getAge() {
   return age;
  }

  public void setName(String name) {
   this.name = name;
  }
  public String getName() {
   return name;
  }

  public void setId(Integer id) {
   this.id = id;
  }
  public Integer getId() {
   return id;
  }
  public void setMarks(Integer marks) {
   this.marks = marks;
  }
  public Integer getMarks() {
   return marks;
  }

  public void setYear(Integer year) {
   this.year = year;
  }
  public Integer getYear() {
   return year;
  }

  public void setSid(Integer sid) {
   this.sid = sid;
  }
  public Integer getSid() {
   return sid;
  }
}

package com.yiibai;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMarksMapper implements RowMapper<StudentMarks> {
  public StudentMarks mapRow(ResultSet rs, int rowNum) throws SQLException {

   StudentMarks studentMarks = new StudentMarks();

   studentMarks.setId(rs.getInt("id"));
   studentMarks.setName(rs.getString("name"));
   studentMarks.setAge(rs.getInt("age"));
   studentMarks.setSid(rs.getInt("sid"));
   studentMarks.setMarks(rs.getInt("marks"));
   studentMarks.setYear(rs.getInt("year"));

   return studentMarks;
  }
}

package com.yiibai;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

public class StudentJDBCTemplate implements StudentDAO{
  private JdbcTemplate jdbcTemplateObject;

  public void setDataSource(DataSource dataSource) {
   this.jdbcTemplateObject = new JdbcTemplate(dataSource);
  }

  public void create(String name, Integer age, Integer marks, Integer year){

   try {
     String SQL1 = "insert into Student (name, age) values (?, ?)";
     jdbcTemplateObject.update( SQL1, name, age);

     // Get the latest student id to be used in Marks table
     String SQL2 = "select max(id) from Student";
     int sid = jdbcTemplateObject.queryForInt( SQL2 );

     String SQL3 = "insert into Marks(sid, marks, year) " + 
            "values (?, ?, ?)";
     jdbcTemplateObject.update( SQL3, sid, marks, year);

     System.out.println("Created Name = " + name + ", Age = " + age);
     // to simulate the exception.
     throw new RuntimeException("simulate Error condition") ;
   } catch (DataAccessException e) {
     System.out.println("Error in creating record, rolling back");
     throw e;
   }
  }

  public List<StudentMarks> listStudents() {
   String SQL = "select * from Student, Marks where Student.id=Marks.sid";

   List <StudentMarks> studentMarks=jdbcTemplateObject.query(SQL, 
   new StudentMarksMapper());
   return studentMarks;
  }
}

package com.yiibai;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainApp {
  public static void main(String[] args) {
   ApplicationContext context = 
       new ClassPathXmlApplicationContext("Beans.xml");

   StudentDAO studentJDBCTemplate = 
   (StudentDAO)context.getBean("studentJDBCTemplate");
   
   System.out.println("------Records creation--------" );
   studentJDBCTemplate.create("Zara", 11, 99, 2010);
   studentJDBCTemplate.create("Nuha", 20, 97, 2010);
   studentJDBCTemplate.create("Ayan", 25, 100, 2011);

   System.out.println("------Listing all the records--------" );
   List<StudentMarks> studentMarks = studentJDBCTemplate.listStudents();
   for (StudentMarks record : studentMarks) {
     System.out.print("ID : " + record.getId() );
     System.out.print(", Name : " + record.getName() );
     System.out.print(", Marks : " + record.getMarks());
     System.out.print(", Year : " + record.getYear());
     System.out.println(", Age : " + record.getAge());
   }
  }
}

<?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:tx="http://www.springframework.org/schema/tx"
  xmlns:aop="http://www.springframework.org/schema/aop"
  xsi:schemaLocation="http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
  http://www.springframework.org/schema/tx
  http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
  http://www.springframework.org/schema/aop
  http://www.springframework.org/schema/aop/spring-aop-3.0.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/TEST"/>
   <property name="username" value="root"/>
   <property name="password" value="cohondob"/>
  </bean>
 
  <tx:advice id="txAdvice" transaction-manager="transactionManager">
   <tx:attributes>
   <tx:method name="create"/>
   </tx:attributes>
  </tx:advice>
 
  <aop:config>
   <aop:pointcut id="createOperation" 
   expression="execution(* com.yiibai.StudentJDBCTemplate.create(..))"/>
   <aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
  </aop:config>
 
  <!-- Initialization for TransactionManager -->
  <bean id="transactionManager"
  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
   <property name="dataSource" ref="dataSource" />  
  </bean>

  <!-- Definition for studentJDBCTemplate bean -->
  <bean id="studentJDBCTemplate" 
  class="com.yiibai.StudentJDBCTemplate">
   <property name="dataSource" ref="dataSource" /> 
  </bean>

</beans>

------Records creation--------
Created Name = Zara, Age = 11
Exception in thread "main" java.lang.RuntimeException: simulate Error condition