Spring JDBC Framework supports of all the low-level details like connection open, SQL statement preparation and execution of it, exceptions process, transactions handling and close the connection. Hence user just needs to define parameters of connection and give the SQL statement to be executed and do the task required.

JdbcTemplate Class:

The JdbcTemplate class executes SQL statements such as updates and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values and also catches JDBC exceptions and translates them to the generic, exception hierarchy defined in the org.springframework.dao package. JdbcTemplate class instances are threadsafe after configuration. By using JdbcTemplate class is to configure a DataSource in configuration file of Spring, and then dependency-inject that shared DataSource bean into classes of DAO, and the JdbcTemplate is created in the setter for the DataSource.

Data Source Configuring:

For example create a database table Customer in database.

CREATE TABLE Customer(
   CUS_ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   SALARY  INT NOT NULL,
   PRIMARY KEY (CUS_ID)
);

To pass a DataSource to the JdbcTemplate, we can configure the source in the XML file as shown below:

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

Data Access Object (DAO):

Data Access Object (DAO) which is used for database interaction which provides a means to read and write data to the database. And it makes easy to work with technologies like JDBC, JPA, Hibernate or JDO.

Executing SQL statements:

Operation on database tables using SQL and jdbcTemplate object. SQL statements like Create, Read, Update and Delete.

Integer Example:

String SQL = "select count(*) from Customer";
int Count = jdbcTemplateObject.queryForInt( SQL );

String Example:

String SQL = "select name from Customer where Cus_id = ?";
String sname = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);

Object Example:

String SQL = "select * from Customer";
Customer cust = jdbcTemplateObject.queryForObject(SQL, 
                  new Object[]{10}, new CustomerMapper());

public class CustomerMapper implements RowMapper {
   public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
      Customer cust = new Customer();
      cust.setID(rs.getInt("Cus_id"));
      cust.setName(rs.getString("cname"));
      cust.setAge(rs.getInt("Salary"));
      return student;
   }
}	

To insert a row:

String SQL = "insert into Customer (cname, Salary) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Suman", 30000} );

To delete a row:

String SQL = "delete Customer where Cus_id = ?";
jdbcTemplateObject.update( SQL, new Object[]{11} );

Executing DDL Statements:

From jdbcTemplate to execute statements of SQL by using method execute() method. Example: To create tables by using statement CREATE:

String SQL = "CREATE TABLE Customer( " +
   "CUS_ID   INT NOT NULL, " +
   "NAME VARCHAR(20) NOT NULL, " +
   "SALARY  INT NOT NULL, " +
   "PRIMARY KEY (CUS_ID));"
jdbcTemplateObject.execute( SQL );

Transaction Management

A database transaction is simultaneous actions which are grouped to form a single unit. It is RDBMS enterprise which makes sure consistency and data integrity. The data frameworks such as JDBC, Hibernate JPA, etc., the Spring Framework support for integrating with these frameworks. The key properties of transactions are:

ACID:

1. Atomicity: A transaction is atomic operations consist of series of action. The transaction atomicity makes sure that that either the action complete entirely or not affect at all.
2. Consistency: After all actions of a transaction have completed, the transaction is committed. Then the resources and data will be in a consistent state.
3. Isolation: by using the isolation user can hide uncommitted state changes from the outside, as failing transactions shouldn’t corrupt the system state.
4. Durability: After transaction has done, the results of it permanently change the system state, and before ending it, the state changes are recorded in a transaction log.

Difference between Local and Global Transactions:

Difference between Programmatic and Declarative:

Transaction management types are:

Spring Transaction Abstractions:  The key to the Spring transaction abstraction is defined by the org.springframework.transaction.PlatformTransactionManager interface, which is as follows:

public interface PlatformManager {
   TransactionStatus getTransaction(TransactionDefinition definition);
   throws TransactionException;
   void commit(TransactionStatus status) throws TransactionException;
   void rollback(TransactionStatus status) throws TransactionException;
}

Method & Description:

• void commit(TransactionStatus status):
  To commit the given transaction, according to its status.
• TransactionStatus getTransaction(TransactionDefinition definition):
  Returns a transaction which is currently active / can create a new one, according to the specified
• void rollback(TransactionStatus status):
  To perform a rollback action of the given transaction.
• String getName():
  To get the name of the transaction.
• boolean isReadOnly():
  Returns true if the transaction is read-only or else false.
• int getIsolationLevel():
  Returns the degree to which this transaction is isolated from the work of other transactions.
• int getPropagationBehavior():
  To get the propagation behavior.
• int getTimeout():
  To get the transaction completed time in seconds.

The possible values for isolation level:

• ISOLATION_DEFAULT:
  This is the default isolation level.
• ISOLATION_READ_UNCOMMITTED
  Indicates that dirty reads, non-repeatable reads and phantom reads can occur.
• ISOLATION_READ_COMMITTED:
  Indicates that dirty reads are prevented;
• ISOLATION_DEFAULT:
  The default isolation level.

The possible values for propagation types:

• TIMEOUT_DEFAULT:
  Use the default timeout of the underlying transaction system, or none if timeouts are not supported.
• PROPAGATION_MANDATORY:
  Support a present transaction;
• PROPAGATION_NEVER:
  Do not support a current transaction;
• PROPAGATION_NESTED:
  Execute within a nested transaction if a current transaction exists.
• PROPAGATION_REQUIRES_NEW
  Create a new transaction, suspending the present transaction if one exists.
• PROPAGATION_REQUIRED
  Support a current transaction; create a new one if none exists.

The interface TransactionStatus provides a way for transactional code to control transaction execution and query transaction status.

• boolean isCompleted():
  Returns true when the transaction is completed or else false.
• boolean isRollbackOnly():
  Returns true if the transaction has been marked as rollback-only or else false.
• boolean hasSavepoint():
  Returns true if the transaction internally carries a savepoint,or else false.
• void setRollbackOnly():
  To set the transaction rollback-only.
• boolean isNewTransaction():
  Returns true in case the current transaction is new.

Example:

private void update() {
	Connection con = ds.getConnection();
	Statement stmt = null;
	try {
		con.SetAutoCommit(false);
		stmt.createStatement();
		stmt.executeUpdate(“table1”);
		con.commit();
	} catch (Exception e) {
		con.rollback();
	} finally {
		databaseUtils.close(con.stmt);
	}
}