[Spring] 8 - Introduction to JDBC

Problem with traditional JDBC

• Results in redundant, error prone code

• Leads to poor exception handling

With Spring JDBC Framework, we just define connection parameters and specify the SQL statement to be executed and do the required work for each iteration while fetching data from the database.

---

Spring’s Jdbc Template

• It is one of most popular approach defined in the org.springframework.dao package

• Configuration
• Query execution
• Working with result sets
• Exception handling

---

JdbcTemplate Class

• Instances of the JdbcTemplate class are threadsafe once configured. So you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs. 

• A common practice when using the JdbcTemplate class is to configure a DataSource in your Spring configuration file, and then dependency-inject that shared DataSource bean into your DAO classes, and the JdbcTemplate is created in the setter for the DataSource.

---

Configuring Data Source

• Create a table Student in our data TEST.

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 supply a dataSource to the JdbcTemplate so it can configure itself to get database access. You can configure the dataSource in the XML file with a piece of code as shown below:

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

---

Jdbc Template Approach Overview

---

Creating a JdbcTemplate

• Require a DataSource

JdbcTemplate template = new JdbcTemplate(dataSource);

• Create a template once and re-use it

• Do not create one for each use
• Thread safe after construction

---

When to use JdbcTemplate

• Useful standalone
• Anytime JDBC is needed
• In utility or test code
• To clean up messy legacy code
• Useful for implementing a repository
• Also known as a data access object (DAO)

---

Steps to implementing a JDBC-based repository

1.    Create a new class

• Typically implementing a data access interface you define.

2.    Implement data access methods using the JdbcTemplate

3.    Unit test the data access behavior

4.    Integrate the repository into your application

• Where it will typically participate in application-driven transactions

---

Creating a JDBC-based Repository class

• Spring provides two support classes you may extend from
• Extend JdbcDaoSupport on Java 1.4 

• Extend JdbcDaoSupport on Java 5.0 or greater

• After extending you inherit a JdbcTemplate you configure by injecting a Database

---

Querying for Simple Java Type

• Query with no bind variables

• Query with a bind variable

• Use Querying With SimpleJdbcTemplate
• Use SimpleJdbcTemplate if running on Java5 or >

• Generic Queries
• JdbcTemplate can return each row of a ResultSet as a MapWhen expecting a single row
• When expecting a single row
• Use queryForMap(...)
• When expecting multiple row
• Use queryForList(...)
• Useful for reporting, testing, and "Window-on-data" use cases.

• Querying for Generic Maps
• Query for a single row

• Returns:
• Map { ID=1, FIRST_NAME=”John”, LAST_NAME=”Doe”} 
• Query for a multiple rows

• Returns:
• 0 - Map { ID=1, FIRST_NAME=”John”, LAST_NAME=”Doe”}
• 1 - Map { ID=2, FIRST_NAME=”Jan”, LAST_NAME=”Doe”}
• 2 - Map { ID=3, FIRST_NAME=”Junior”, LAST_NAME=”Doe”}

• Domain Object Queries
• Often it is useful to map relational data into domain objects
• e.g. a ResultSet to an Account
• Spring’s JdbcTemplate supports this using a callback approach

• Query for a single row

• Query for multiple rows

• Querying for a Single Domain Object with SimpleJdbcTemplate

• Querying for Multiple Domain Objects with SimpleJdbcTemplate

For example

Querying and returning 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;

   }

}

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;

   }

}

---

RollCallbackHandler

• Spring provides a simpler RowCallbackHandler interface when there is no return object.
• Streaming rows to a file
• Filtering rows before adding to a Collection

• Using a RowCallbackHandler

---

ResultSetExtractor

• Spring provides a ResultSetExtractor interface for mapping an entire ResultSet to an object.

• Use a ResultSetExtractor

---

---

Summary of Callback Interfaces

• RowMapper
  

• Best choice when each row of a ResultSet maps to be a domain object.

• RowCallbackHandler

• Best choice when no value should be returned from the callback method.

• ResultSetExtractor

• Best choice when multiple rows of a ResultSet map to a domain object