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>

Exercise 1

(1) Create database springDemo with  user table with fields
          (a) username
          (b) password
           (c) name
          (d) age
           (e) dob
   (2) Insert few records inside user Tables
   (3) Use datasource with DriverManagerDataSource, dbcp2.BasicDataSource and SingleConnectionDataSource to print the records of user tables

 

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

Exercise 2

    (1) Use JdbcTemplate to get the count of users
    (2) Get name of the user by providing username to the             parametrized query
    (3) Insert one record using JdbcTemplate
    (4) Use QueryForMap to fetch the user details of the                   user
    (5) Use QueryForList to fetch records of all users
     (6) Use a rowmapper to get the User object when you               query for a user

 

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());
    }
}

Exercise 3

Integrate Hibernate with Spring and use hql   query to count the number of records in user table.

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.)

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 (XML)

<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.xml.datasource.UserServiceImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <tx:advice transaction-manager="txManager" id="txAdvice">
        <tx:attributes>
            <tx:method name="get*" read-only="true"/>
            <tx:method name="*"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="userServicePointcut" expression="execution(* samples.spring.transactions.xml.datasource.UserService.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="userServicePointcut"/>
    </aop:config>

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 (or in xml <tx:method/>)

  • propagation - Transaction propagation behavior. Default is REQUIRED.
  • isolation - Transaction isolation level. Default level is DEFAULT.
  • 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

Exercise 4

  1. Create User and UserProfile table and Domain
  2. Create UserService and UserProfileService with crud operations
  3. Each crud operation should mark @Transactional with appropriate transactional attribute as per method behaviour (i.e. if method is performing only read then it should be configured read-only=true)
  4. Demonstrate timeout / rollback-for/no-rollback-for properties

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.

Programmatic transaction management

  • Using the TransactionTemplate.
  • Using a PlatformTransactionManager implementation directly
TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager);

transactionTemplate.execute(new TransactionCallbackWithoutResult() {
    protected void doInTransactionWithoutResult(TransactionStatus status) {
        updateOperation1();
        updateOperation2();
    }
});
@Autowired
PlatformTransactionManager txManager;

DefaultTransactionDefinition def = new DefaultTransactionDefinition();
// explicitly setting the transaction name is something that can only be done programmatically
def.setName("SomeTxName");
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

TransactionStatus status = txManager.getTransaction(def);
try {
    // execute your business logic here
}
catch (MyException ex) {
    txManager.rollback(status);
    throw ex;
}
txManager.commit(status);

Exercise 5

Continue form exercise 4

  1. Invoke UserProfileService#save() from UserService#save()
  2. In UserProfileService#save() check if any property (profilePicture) is null then throw IllegalArgumentException and make sure no UserProfile will be saved but User must be save.
  3. create separate branch
  4. In UserService remove @Transaction and manage transactions via TransactionTemplate
  5. In UserProfileService changed @Transaction and manage transactions via PlatformTransactionManager.

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) {
          ...
    }

}

Data Access and Transaction in Spring

By Pulkit Pushkarna

Made with Slides.com

Data Access and Transaction in Spring

  • 1,772

More from Pulkit Pushkarna