Data Access and Transaction in Spring

Agenda

1. Database Access without Spring
2. Controlling database connections
a. SingleConnectionDataSource
b. DriverManagerDataSource
c. Pooled DataSource
3. DAO support
4. Data access with JDBC Template
5. Implementing RowMapper
6. Object Relational Mapping (ORM) Data Access
7. Spring Transaction
8. Declarative transaction management
9. Using @Transactional
10. @Transactional settings

Agenda

11. Multiple Transaction Managers with @Transactional
12. Transaction propagation
13. Programmatic transaction management
a. Using the PlatformTransactionManager
14. Transaction bound event

JDBC without Spring

String driverName = "com.mysql.jdbc.Driver";
Class.forName(driverName).newInstance();
String connectionString = "jdbc:mysql://localhost:3306/springDemo";
String username = "root";
String password = "mysql";
Connection connection = (Connection) DriverManager.getConnection(connectionString,
username, password);
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM user");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
    System.out.println(resultSet.getString("name"));
}

create table user
(username VARCHAR(100), 
password VARCHAR(100), 
name VARCHAR(100), 
age INT, 
dob DATE)

insert into user values("spring","spring","spring",23,'1990-01-16');

Data Source Configuration

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

XML configuration

Data Source Configuration (Cont.)

Using data source for connection

@Service
public class UserDAO {

    @Autowired
    DataSource dataSource;

    void printUserNames() throws SQLException {
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM user");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString("name"));
        }
    }
}

Single connection datasource

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

Pooled connection

<bean class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="url" value="jdbc:mysql://localhost:3306/springDemo"/>            
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="username" value="root"/>
    <property name="password" value="mysql"/>
    <property name="initialSize" value="2"/>
     <property name="maxTotal" value="5"/>
</bean>

JdbcTemplate

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

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

XML configuration

@Repository
public class UserDao {

    @Autowired
    DataSource dataSource;
    @Autowired
    JdbcTemplate jdbcTemplate;

    void userCount() {
        String sql = "SELECT COUNT(*) FROM user";
        System.out.println(jdbcTemplate.queryForObject(sql, Integer.class));
    }
}

JdbcTemplate (Cond.)

Query with parameters using JdbcTemplate

@Repository
public class UserDAO {

    @Autowired
    JdbcTemplate jdbcTemplate;

    String getUserName() {
        String sql = "SELECT NAME FROM user WHERE username = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{"sunny"}, String.class);
    }
}

Insertion using JdbcTemplate


@Repository
public class UserDAO {

    @Autowired
    JdbcTemplate jdbcTemplate;

    void insertUser(User user) {
    String sql = "INSERT INTO user (username,password,name,age,dob)VALUES(?,?,?,?,?)";
         jdbcTemplate.update(sql, new Object[]{
         user.getUsername(), user.getPassword(), user.getName(), user.getAge(), user.getDob()
        });
    }
}

Query for Map

@Repository
public class UserDAO {

    @Autowired
    JdbcTemplate jdbcTemplate;

    void queryForMapDemo() {
        String sql = "SELECT * FROM user WHERE username = ?";
        System.out.println(jdbcTemplate.queryForMap(sql, new Object[]{"sunny"}));
    }
}

Query for List

@Repository
public class UserDAO {

    @Autowired
    JdbcTemplate jdbcTemplate;

    void queryForListDemo() {
        String sql = "SELECT * FROM user";
        System.out.println(jdbcTemplate.queryForList(sql));
    }
}

Using RowMapper

public class UserMapper implements RowMapper<User> {

    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setName(rs.getString("name"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setAge(rs.getInt("age"));
        user.setDob(rs.getDate("dob"));
        return user;
    }
}

@Repository
public class UserDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    User getUser() {
        String sql = "SELECT * FROM user WHERE username = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{"sunny"}, new UserMapper());
    }
}

Named Parameter Jdbc Template

@Repository
public class UserDao {

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    void namedParameterJdbcTemplateDemo() {
    void namedParameterJdbcTemplateDemo() {
        String sql = "SELECT * FROM user WHERE username = :id";
        SqlParameterSource parameterSource = new MapSqlParameterSource("id", "sunny");
        namedParameterJdbcTemplate.queryForObject(sql, parameterSource, new UserMapper());
    }
  }
}

XML config :

<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
     <constructor-arg ref="dataSource"/>
</bean>

Usage

Spring ORM

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="packagesToScan" value="com.spring.demo"/>
    <property name="hibernateProperties">
       <props>
            <prop key="dialect">org.hibernate.dialect.MySQLDialect</prop>
       </props>
    </property>
</bean>

@Repository
public class UserDAO {
@Autowired
    SessionFactory sessionFactoryBean;

    void sessionFactoryDemo() {
        String sql = "SELECT COUNT(*) FROM User";
        Query query = sessionFactoryBean.openSession().createQuery(sql);
        System.out.println(query.uniqueResult());
    }
}

Spring Transactions

Spring Framework provides a consistent abstraction for transaction management that delivers the following benefits:

Consistent programming model across different transaction APIs such as Java Transaction API (JTA), JDBC, Hibernate, Java Persistence API (JPA), and Java Data Objects (JDO).
Support for declarative transaction management.
Simpler API for programmatic transaction management than complex transaction APIs such as JTA.
Excellent integration with Spring’s data access abstractions.

Spring Transactions (Cont.)

org.springframework.transaction.PlatformTransactionManager

public interface PlatformTransactionManager {

    TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;

    void commit(TransactionStatus status) throws TransactionException;

    void rollback(TransactionStatus status) throws TransactionException;
}

Declarative transaction management

Declarative transaction management (Annotations)

<bean name="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/spring_advance"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>

        <property name="initialSize" value="1"/>
        <property name="maxTotal" value="2"/>
    </bean>

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

    <bean id="userService" class="samples.spring.transactions.annotaions.datasource.UserServiceImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <tx:annotation-driven transaction-manager="txManager"/>


@Transactional
public class UserService {

    @Transactional(readOnly = true)
    List<User> getUser(String name) { }
    @Transactional(readOnly = true)
    List<User> getUser(String name, int age) { }

    void addUser(String name, int age) { }
}

@Transactional Settings

propagation - Transaction propagation behavior. Default is REQUIRED.
read-only - Is this transaction read-only? Default is false.
timeout - Transaction timeout value (in seconds). Default is -1
rollback-for - Exception(s) that trigger rollback; comma-delimited.
no-rollback-for - Exception(s) that do not trigger rollback; comma-delimited.

Important notes

Multiple Transaction Managers with @Transactional

<bean name="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
       <!-- Datasource config -->
    </bean>

    <bean id="transactionManager1" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
        <qualifier value="t1"/>
    </bean>

    <bean id="transactionManager2" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
        <qualifier value="t2"/>
    </bean>

    <bean id="userService" class="samples.spring.transactions.annotaions.multiple.UserService">
    </bean>
    <tx:annotation-driven/>

@Service
public class UserService {

    @Transactional("t1")
    public void hello() {
        System.out.println("Hello from t1");
    }

    @Transactional("t2")
    public void hi() {
        System.out.println("Hello from t2");
    }
}

Transaction propagation

REQUIRED
REQUIRES_NEW
NESTED
MANDATORY
NEVER
NOT_SUPPORTED
SUPPORTS

Transaction propagation

REQUIRED

Transaction propagation

REQUIRED_NEW

Transaction propagation

NESTED

The NESTED behavior makes nested Spring transactions to use the same physical transaction but sets savepoints between nested invocations so inner transactions may also rollback independently of outer transactions

MANDATORY

The MANDATORY behavior states that an existing opened transaction must already exist

NEVER

! MANDATORY

Transaction propagation

NOT_SUPPORTED

The NOT_SUPPORTED behavior will execute outside of the scope of any transaction and pause if already exists

SUPPORTS

The SUPPORTS behavior will execute in the scope of a transaction if an opened transaction already exists. If there isn't an already opened transaction the method will execute anyway but in a non-transactional way.

Transaction bound event

Registering a regular event listener is done via the @EventListener annotation. If you need to bind it to the transaction use @TransactionalEventListener. The listener will be bound to the commit phase of the transaction by default

@Component
public class MyComponent {

    @TransactionalEventListener(phase = TransactionPhase.AFTER_COMPLETION)
    public void handleOrderCreatedEvent(CreationEvent<Order> creationEvent) {
          ...
    }

}

Github Link

git@github.com:pulkitpushkarna/spring-transaction-complete-demo.git