Spring JDBC framework

JDBC Framework Overview

When using a normal JDBC database, it can be cumbersome to write unnecessary code to handle exceptions, open and close database connections, and so on. But the Spring JDBC framework is responsible for all the low-level details, from opening the connection, preparing and executing SQL statements, handling exceptions, handling transactions, and finally closing the connection.

So when you get the data from the database, what you do is define the connection parameters, specify the SQL statements to execute, and do the work required for each iteration.

Spring JDBC provides several methods and different classes and interfaces corresponding to the database. I 'll show you the classic and most popular ways to use the JdbcTemplate class framework. This is a central framework class that manages all database traffic and exception handling.

JdbcTemplate class

The JdbcTemplate class performs SQL queries, update statements, and stored procedure calls, executes iterative result sets, and extracts return parameter values. It also catches JDBC exceptions and transforms them into generic classes defined in the org.springframework.dao package, more information, and an exception hierarchy.

An instance of the JdbcTemplate class is thread-safe. So you can configure a single instance of JdbcTemplate and then securely inject this shared reference into multiple DAOs.

It is common practice to use the JdbcTemplate class by configuring the data source in your Spring profile, then injecting the shared data source bean dependency into the DAO class and creating JdbcTemplate in the set-up function of the data source.

Configure the data source

We create a database table Student in database TEST. Suppose you're using a MySQL database, and if you're using a different database, you can change your DDL and the corresponding SQL queries.

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

Now we need to provide a data source to JdbcTemplate, so it can configure itself to get database access. You can configure the data source in an XML file, one of which looks like this:

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

Data Access Objects (DAOs)

DAOs represent data access objects that are commonly used for database interactions. DAOs provide a way to read and write data to the database, and they should display this functionality through an interface that the rest of the application will access.

In Spring, Data Access Objects (DAOs) support the easy use of data access technologies such as JDBC, Hibernate, JPA, or JDO in a unified way.

Execute the SQL statement

Let's look at how to use SQL and jdbcTemplate objects to perform CRUD (create, read, update, and delete) operations in a database table.

Query an integer type:

String SQL = "select count(*) from Student";
int rowCount = jdbcTemplateObject.queryForInt( SQL );

Query a long type:

String SQL = "select count(*) from Student";
long rowCount = jdbcTemplateObject.queryForLong( SQL );

A simple query that uses binding variables:

String SQL = "select age from Student where id = ?";
int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

Query string:

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

Query and return an object:

String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL, 
                  new Object[]{10}, 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;
   }
}

Query and return 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;
   }
}

Insert a row into the table:

String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 11} );

Update a row in the table:

String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 10} );

Remove a row from the table:

String SQL = "delete Student where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{20} );

Execute the DDL statement

You can use the execute(.) method in jdbcTemplate to execute any SQL statement or DDL statement. Here's an example of creating a table using a CREATE statement:

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

Spring JDBC framework example

Based on these concepts, let's look at some important examples to help you understand the use of JDBC frameworks in Spring: