Spring Interview Questions - Basic To Advance - Part 5 Spring JDBC & Transaction Management

How would you achieve Transaction Management in Spring?
Spring framework provides transaction management support through Declarative Transaction Management as well as programmatic transaction management. Declarative transaction management is most widely used because it’s easy to use and works in most of the cases.

We use annotate a method with @Transactional annotation for Declarative transaction management. We need to configure transaction manager for the DataSource in the spring bean configuration file.

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

How to use Tomcat JNDI DataSource in Spring Web Application?
For using servlet container configured JNDI DataSource, we need to configure it in the spring bean configuration file and then inject it to spring beans as dependencies. Then we can use it with JdbcTemplate to perform database operations.

Sample configuration would be:
<beans:bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
     <beans:property name="jndiName" value="java:comp/env/jdbc/MyLocalDB"/>
</beans:bean>

What are the benefits of the Spring Framework’s transaction management?
The Spring Framework provides a consistent abstraction for transaction management that delivers the following benefits:

Provides a consistent programming model across different transaction APIs such as JTA, JDBC, Hibernate, JPA, and JDO.
Supports declarative transaction management.
Provides a simpler API for programmatic transaction management than a number of complex transaction APIs such as JTA.
Integrates very well with Spring’s various data access abstractions.
Spring resolves the disadvantages of global and local transactions. It enables application developers to use a consistent programming model in any environment. You write your code once, and it can benefit from different transaction management strategies in different environments. The Spring Framework provides both declarative and programmatic transaction management.

Spring JDBC Implementation
https://www.topjavatutorial.com/frameworks/spring/spring-jdbc/spring-jdbc-using-annotation-based-configuration/

JdbcTemplate for Insert, Update and Delete operations
String sql = "insert into employee(age,name) values(?,?)";
        jdbcTemplate.update(sql,new Object[]{emp.getAge(),emp.getName()});


JdbcTemplate for Querying data
JdbcTemplate offers various methods like query(), queryForInt(), queryForLong(),
queryForObject(), queryForList(), queryForMap(), and queryForRowSet() methods that we can use to query data.

String sql = "select * from employee where id = ?";
        Employee emp = jdbcTemplate.queryForObject(sql,new Object[]{id}, new RowMapper<Employee>(){

            public Employee mapRow(ResultSet rs, int rownum)
                    throws SQLException {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setAge(rs.getInt("age"));
                emp.setName(rs.getString("name"));
                return emp;
            }
         
        });

JdbcTemplate to execute DDL statements


    public void CreateEmployeeTable(){
        String sql = " CREATE TABLE Employee(ID INT PRIMARY KEY AUTO_INCREMENT, AGE INT,NAME VARCHAR(255)); ";
        jdbcTemplate.execute(sql);
    }

JDBCTeamplate Batch Update
https://examples.javacodegeeks.com/enterprise-java/spring/jdbc/spring-jdbctemplate-example/

Calling SP from Spring
https://javarevisited.blogspot.com/2013/04/spring-framework-tutorial-call-stored-procedures-from-java.html