How to use JDBC with Spring

In this article, we will understand the Spring JDBC Framework in detail. Spring Framework is one of the powerful frameworks because it has many modules. Among the modules, It provides us with the Jdbc Template to connect with the database.

While building any web-based application or any simple application where we need to store some data into the database, we have a data-access layer in our application where we use  JDBC API. But sometimes, writing JDBC code can be tiresome, and also opening and closing a connection could be boring sometimes.

So, Spring Framework came up with the JDBC Template where we don’t need to worry about opening and closing the connection, preparing and executing the queries. These responsibilities will be taken care of by Spring Framework itself.

There are many approaches available and we can use any one of them:

  1. JdbcTemplate
  2. NamedParameterJdbcTemplate
  3. SimpleJdbcTemplate
  4. SimpleJdbcInsert and SimpleJdbcCall

JDBCTemplate Class

JdbcTemplate is a typical Spring JDBC approach. Every Other Class or Template that is built extends this class. This class’s responsibility is to open and close the connection object.

  • It is the central class in the JDBC core package.
  • It handles the creation, execution, preparation of SQL queries.
  • Not only just execution but also handling the exception and translating them into the generic, more informative way.

Let us see the methods of JDBCTemplate class.

  • update(String query): It performs insertion, deletion, and updating of records and returns integer type.
  • update(String query, Object.. args): It performs insertion, deletion, and updating of records using PreparedStatement using given argument.
  • execute(String query): This method executes DDL queries.
  • execute(String query, PreparedStatementCallback action): This method executes the query using PreparedStatementCallback.
  • query(String query, ResultSetExtractor rs): This method is used to fetch the records using ResultSetExtractor.
  • query(String query, ResultMapper rm): This method is used to fetch the records using RowMapper.

Let us take a look at a simple example to understand the use of JDBCTemplate class.

Create a Spring Project Go to File> New > Other > Search maven > Select Maven Project > Next > Search Filter org.apche.maven.archetypes/webapp > Next > Enter Group Id & Archetype id > Finish.

Step 1: Create a database and a table inside a database.

mysql> create database demo;

mysql> CREATE TABLE `demo`.`userdb` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

Step 2: Create a User.java class with id, userName, email, and password. Add the setters and getters

package com.SpringJDBCTemplateExample.model;

public class User {

  private int id;
  private String userName;
  private String email;
  private String password;
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getUserName() {
    return userName;
  }
  public void setUserName(String userName) {
    this.userName = userName;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
  public String getPassword() {
    return password;
  }
  public void setPassword(String password) {
    this.password = password;
  }
  
  
  
}

Step 3: Create a configuration file ‘beans.xml’.

  • Add the bean name DriverManagerDataSource that will hold the information of the database. (Just like the configuration file we create)
  • Add the bean name JdbcTemplate and provide the reference of the dataSource object of  DriverManagerDataSource in the JdbcTemplate class.
  • Pass the JdbcTemplate object in UserDAO class.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:p="http://www.springframework.org/schema/p"
  xsi:schemaLocation="http://www.springframework.org/schema/beans   
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

  <!-- Initialization of Data Source -->
  <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/demo" />
    <property name="username" value="root" />
    <property name="password" value="root" />
  </bean>
  
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
<property name="dataSource" ref="dataSource"></property>  
</bean> 
  <!-- Define userDAO bean  -->
  <bean id="userdao"
    class="com.SpringJDBCTemplateExample.dao.UserDAO">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
  </bean>

</beans>

Step 4: Create a UserDAO.java class

  • It has a property JdbcTemplate.
  • It has methods to perform save, update and delete.
package com.SpringJDBCTemplateExample.dao;
import org.springframework.jdbc.core.JdbcTemplate;

import com.SpringJDBCTemplateExample.model.User;



public class UserDAO {

  
  private JdbcTemplate  jdbcTemplate;
  
  
  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }
  /*
   * TODO: Save the User 
   */
  public int saveUser(User u) {
    String sql = "INSERT INTO USER VALUES('"+u.getId()+"','"+u.getUserName()+"','"+u.getEmail()+"','"+u.getPassword()+"')";
    return jdbcTemplate.update(sql);
  }
  /*
   * TODO: Update the User 
   */
  public int updateUser(User u) {
    String sql = "UPDATE USER SET USERNAME='"+u.getUserName()+"', EMAIL = '"+u.getEmail()+"' WHERE ID = '"+u.getId()+"' ";
    return jdbcTemplate.update(sql);
  }
  
  /*
   * TODO: DELETE the User 
   */
  public int deleteUser(User u) {
    String sql = "DELETE FROM USER WHERE ID= '"+u.getId()+"'";
    return jdbcTemplate.update(sql);
  }
}

Step 4: Create an App.java

  •  create the object of the Application context, get the bean from it.
  • Then, call all the methods.
package com.SpringJDBCTemplateExample;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.SpringJDBCTemplateExample.dao.UserDAO;
import com.SpringJDBCTemplateExample.model.User;

/**
 * TODO: Test the Application
 *
 */
public class App {
  public static void main(String[] args) {
    ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
    UserDAO dao = context.getBean("userdao", UserDAO.class);
    User user = new User();

    user.setUserName("Harry");
    user.setPassword("harry");
    user.setEmail("harry@hogward.edu");
    int status = dao.saveUser(user);
    System.out.println("<-------Successfully Saved-------->" + status);

    // Update user user.setUserName("Harry Potter");
    user.setEmail("harrypotter@hogward.edu");
    user.setId(1);
    dao.updateUser(user);
    System.out.println("<-------Successfully Updated-------->");

    // Delete user
    user.setId(1);
    dao.deleteUser(user);
                System.out.println("<-------Successfully Deleted-------->");
  }
}

Output:

Run the App.java and observe the following output

**********First while saving***************
mysql> select * from user;
+----+----------+-------------------+----------+
| id | username | email             | password |
+----+----------+-------------------+----------+
|  1 | Harry    | harry@hogward.edu | harry    |
+----+----------+-------------------+----------+
1 row in set (0.00 sec)
**********Second While Updating**************
mysql> select * from user;
+----+--------------+-------------------------+----------+
| id | username     | email                   | password |
+----+--------------+-------------------------+----------+
|  1 | Harry Potter | harrypotter@hogward.edu | harry    |
+----+--------------+-------------------------+----------+
1 row in set (0.00 sec)
************Last while deleting***************
mysql> select * from user;
Empty set (0.02 sec)

Thus, this is How we use the JdbcTemplate class in the Spring Application. Let us select data from the database using Spring JDBC.

There are two ways we can do it:

  1. public T queryForObject(String SQL, RowMapper<T> rowMapper, Object args): When we want to select a single object.
  2. public List<T> query(String SQL, RowMapper<T> rowMapper): When we want to return a list of Objects.

Here, SQL is the query you will pass i.e SELECT QUERY and RowMapper converts the resultset into Objects.

Getting a Single Record from the table

public User getUser(int id) {
    String sql = "SELECT * FROM USER WHERE ID = '"+id+"'";
    RowMapper<User> rowMapper = new RowMapperImple();
    return jdbcTemplate.queryForObject(sql, rowMapper);
}
  • Create the above method in the UserDAO class. 
  • Create Another class RowMapperImple that will implement RowMapper<T>. Implement the methods and the following code.
package com.SpringJDBCTemplateExample.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.SpringJDBCTemplateExample.model.User;

public class RowMapperImple implements RowMapper<User> {
  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    User u = new User();
    u.setId(rs.getInt(1));
    u.setUserName(rs.getString(2));
    u.setEmail(rs.getString(3));
    u.setPassword(rs.getString(4));
    return u;
  }

}

This will convert the resultset into an Object and that object will be used in the getUser() method.

Getting Multiple records from the table

/*
 * TODO: Select Multiple record
 */
public List<User> getListUser() {
    String sql = "SELECT * FROM USER ";
    RowMapper<User> rowMapper = new RowMapperImple();
    return jdbcTemplate.query(sql,rowMapper);
}
public class RowMapperImple implements RowMapper<User> {
  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    User u = new User();
    u.setId(rs.getInt(1));
    u.setUserName(rs.getString(2));
    u.setEmail(rs.getString(3));
    u.setPassword(rs.getString(4));
    return u;
}

Hence, this would fetch you multiple data from the tables.

In the next article of this tutorial, we will see what is Transaction Management in Spring Framework.