8. Spring JDBC Framework

 

Why we are not use plain/normal  JDBC ?

-  Using plain old JDBC for working with a database, it becomes complicated to use an unnecessary code for opening/closing database or handle exceptions etc.

Spring JDBC 

- The Spring JDBC framework is a part of the larger Spring Framework that provides comprehensive support for JDBC (Java Database Connectivity) operations. 

- JDBC is a standard Java API for database access, and the Spring JDBC framework simplifies the process of database interaction by handling common tasks, managing resources, and providing a higher level of abstraction.

- Spring JDBC gives several approaches and different classes to use it with the database. So, you just have to define the connection parameters. 

- One of the key components in Spring JDBC is the JdbcTemplate class. 

- It is a central framework class which manages all database communication such as executing SQL, updating statements etc. Also, it catches exceptions of JDBC and translates them to generic exception hierarchy defined in package org.framework.dao.

- The instances of JdbcTemplate are threaded safe once they are configured. Therefore, you can configure a single instance and then safely inject his into multiple DAOs. 

- A common practice that is done while using Jdbctemplate class is to configure DataSource in Spring config file. Then dependency-inject that into your DAO classes and the JdbcTemplate is created in setter for DataSource.

Steps

a. Configuration of DataSource

- we need to supply a DataSource to the JDBCTemplate  class so it can configure itself to get database access.

b. Data Access Object (DAO)

- Data Access Object also called DAO is commonly used for database interaction. It provides a means to read/write data to a database. They should use this functionality through an interface using which rest of application will access them.

- This DAO support in Spring makes it easy to work with technologies like JDBC, Hibernate etc which are used for data access.

c. Executing SQL Statements

Now you will see how to perform Create, Read, Update and Delete operations on database tables using SQL and JdbcTemplate object.

- Query for Integer

String SQL = "select count(*) from Student";

int rowCount = jdbcTemplateObject.queryForInt( SQL );

- Query for String

String SQL = "select name from Student where id = ?";

String name = jdbcTemplateObject.queryForObject(SQL, new object[]{10}, String.class);

- Querying and returning multiple objects

String SQL = "select * from Student";

List<Student> students = jdbcTemplateObject.query(

SQL, new StudentMapper());

public class StudentMapper implements RowMapper<Student> {

public Student mapRow(ResultSet rs, int rowNum) throws SQLException {

Student student = new Student();

student.setID(rs.getInt("id"));

student.setName(rs.getString("name"));

student.setAge(rs.getInt("age"));

return student;

}

}

- Inserting of a row into a table

String SQL = "insert into Student (name, age) values (?, ?)";

jdbcTemplateObject.update( SQL, new Object[]{"ABC", 41} );

- Updating a row into a table

String SQL = "update Student set name = ? where id = ?";

jdbcTemplateObject.update( SQL, new Object[]{"ABC", 41} )

- Deleting a row from a table

String SQL = "delete Student where id = ?";

jdbcTemplateObject.update( SQL, new Object[]{10} );

- d. Executing DDL Statements

You can use execute() from JdbcTemplate to execute any SQL or DDL statements. See the below example to use CREATE statement for table creation:

String SQL = "CREATE TABLE Student( " +

"ID INT NOT NULL AUTO_INCREMENT, " +

"NAME VARCHAR(20) NOT NULL, " +

"AGE INT NOT NULL, " +

"PRIMARY KEY (ID));"

jdbcTemplateObject.execute( SQL );

JdbcTemplate Class Overview:

1. Purpose:

   - JdbcTemplate is a central class in the Spring JDBC framework that manages database communication.

   - It handles tasks such as executing SQL queries, updating statements, and calling stored procedures.

   - Manages ResultSets, iterating over them, and extracting parameter values.

2. Exception Handling:

   - JdbcTemplate catches JDBC exceptions and translates them into a more informative exception hierarchy defined in the `org.springframework.dao` package.

   - This simplifies error handling, as application code doesn't need to deal with the intricacies of JDBC exceptions directly.

   - The Spring JDBC framework provides a consistent exception hierarchy, making it easier to handle database-related exceptions.

  

3. Thread Safety:

   - Instances of the JdbcTemplate class are thread-safe once configured.

   - This means you can create a single instance and share it across multiple Data Access Objects (DAOs) without worrying about concurrency issues.

4. Integration with DataSource:

   - A common practice is to configure a DataSource in the Spring configuration file. DataSource manages database connections.

   - The JdbcTemplate instance is then created by injecting the shared DataSource bean into DAO classes, often in the setter method for the DataSource.

5. DataSource:

   - The `DataSource` interface in Spring provides a standardized way to manage database connections. It helps in establishing a connection to the database and managing connection pooling, which can significantly improve application performance.

   - The DriverManagerDataSource class is an implementation of the standard DataSource interface that configures a plain JDBC driver through bean properties, and returns a new Connection every time.

6. JdbcTemplate:

   - The `JdbcTemplate` class is a central class in the Spring JDBC framework that simplifies database access by handling common tasks such as opening and closing connections, executing SQL queries, and handling exceptions.

   - It encapsulates the boilerplate code required for JDBC operations, reducing the amount of code developers need to write.

   - The `JdbcTemplate` class is typically configured with a `DataSource` bean, allowing it to manage database connections seamlessly.

7. Transaction Management:

   - Spring provides a powerful and flexible transaction management mechanism. You can use declarative transaction management using annotations or programmatic transaction management using the `TransactionTemplate` class.

   - Transactions can be configured at the method level, providing a fine-grained approach to manage database transactions.

8. RowMapper and ResultSetExtractor:

   - These interfaces help in mapping the result set of a SQL query to Java objects.

   - `RowMapper` is used to map a single row of a result set to a Java object, while `ResultSetExtractor` is more versatile and can be used to process the entire result set.

9. Batch Operations:

   - The Spring JDBC framework supports batch operations, allowing you to execute multiple SQL statements in a single batch for improved performance.

To use the Spring JDBC framework, you typically configure a `DataSource` bean, inject it into a `JdbcTemplate` or other relevant template classes, and use these templates to execute SQL queries and manage database interactions in a more streamlined and efficient manner. The framework promotes best practices and helps in writing cleaner, more maintainable database-related code in Java applications.