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.)
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.
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
Data Access and Transaction in Spring Bootcamp
By Pulkit Pushkarna
Data Access and Transaction in Spring Bootcamp
- 1,037