JDBC

Spring JdbcTemplate Example

In this article, we will explain Spring Jdbc Template with examples.

When we need to interface with databases the Spring JDBC framework provides solutions to all the low-level details, like open/close a connection, prepare and execute SQL statements, process exceptions, and handling transactions. Thus, the only thing a developer must do is just define connection parameters and specify the SQL statement to be executed.

Spring JDBC provides several approaches and different classes to form the basis for JDBC database access. The most popular approach makes use of JdbcTemplate class. This is the central framework class that manages all the database communication and exception handling.
 
 

 
In order to work with JDBC in Spring, we will make use of the Data Access Objects. DAOs in Spring are commonly used for database interaction, using data access technologies like JDBC, Hibernate, JPA or JDO in a consistent way. DAOs provide a means to read and write data to the database and they can expose this functionality through an interface by which the rest of the application can access them.

Here, we shall begin by showing you a simple example of JDBC integration. We will use a simple DAO, to make a simple insert and select a database. We will continue with examples of the JdbcTemplate class to make SQL operations even easier. We will make use of the methods JdbcTemplate class provides to perform various selects and updates to the database.

Our preferred development environment is Eclipse. We are using Eclipse Juno (4.2) version, along with the Maven Integration plugin version 3.1.0. You can download Eclipse from here and Maven Plugin for Eclipse from here. The installation of the Maven plugin for Eclipse is out of the scope of this tutorial and will not be discussed. We are also using Spring version 3.2.3 and the JDK 7_u_21. The database used in the example is MySQL Database Server 5.6.

Let’s begin!

1. Create a new Maven project

Go to File -> Project ->Maven -> Maven Project.

Spring Jdbc - New-Maven-Project
New Maven Project

In the “Select project name and location” page of the wizard, make sure that the “Create a simple project (skip archetype selection)” option is checked, hit “Next” to continue with default values.

Spring Jdbc - Maven-Project-Name-Location
Name Location

In the “Enter an artifact id” page of the wizard, you can define the name and main package of your project. We will set the “Group Id” variable to "com.javacodegeeks.snippets.enterprise" and the “Artifact Id” variable to "springexample". The aforementioned selections compose the main project package "com.javacodegeeks.snippets.enterprise.springexample" and the project name as "springexample". Hit “Finish” to exit the wizard and to create your project.

Spring Jdbc - Configure-Maven-Project
Configure Maven project

The Maven project structure is shown below:

Spring Jdbc - Maven-project-structure
Project Structure

It consists of the following folders:

  • /src/main/java folder, that contains source files for the dynamic content of the application,
  • /src/test/java folder contains all source files for unit tests,
  • /src/main/resources folder contains configurations files,
  • /target folder contains the compiled and packaged deliverables,
  • the pom.xml is the project object model (POM) file. The single file that contains all project related configuration.

2. Add Spring 3.2.3 dependency

  • Locate the “Properties” section at the “Overview” page of the POM editor and perform the following changes:
    Create a new property with name org.springframework.version and value 3.2.3.RELEASE.
  • Navigate to the “Dependencies” page of the POM editor and create the following dependencies (you should fill the “GroupId”, “Artifact Id” and “Version” fields of the “Dependency Details” section at that page):
    Group Id : org.springframework Artifact Id : spring-web Version : ${org.springframework.version}

Alternatively, you can add the Spring dependencies in Maven’s pom.xml file, by directly editing it at the “Pom.xml” page of the POM editor, as shown below:

pom.xml:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.javacodegeeks.snippets.enterprise</groupId>
    <artifactId>springexample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
    </dependencies>
 
    <properties>
        <spring.version>3.2.3.RELEASE</spring.version>
    </properties>
</project>

As you can see Maven manages library dependencies declaratively. A local repository is created (by default under {user_home}/.m2 folder) and all required libraries are downloaded and placed there from public repositories. Furthermore intra – library dependencies are automatically resolved and manipulated.

3. Add the JDBC dependencies in Spring

The dependencies needed for Spring JDBC are the ones below:

pom.xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.javacodegeeks.snippets.enterprise</groupId>
    <artifactId>springexample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
        </dependency>
         <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
      </dependency>
    </dependencies>
 
    <properties>
        <spring.version>3.2.3.RELEASE</spring.version>
    </properties>
</project>

4. Execute a simple insert and select example

Let’s start, by creating a simple table in the database. We create a simple Employee table, that has three columns. The SQL statement that is executed in MySQL Workbench is shown below:

Create a table

1
2
3
4
5
6
CREATE TABLE `Employee` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  `AGE` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

We also create a class, Employee.java, that has three fields that are the same as the columns of Employee table.

Employee.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.javacodegeeks.snippets.enterprise;
 
public class Employee {
     
    private int id;
     
    private String name;
     
    private int age;
 
    public Employee(){
         
    }
     
    public Employee(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public int getAge() {
        return age;
    }
 
    public void setAge(int age) {
        this.age = age;
    }
 
    @Override
    public String toString() {
        return Employee ["id= "+ id + ", name= "+ name + ", age= "+ age
                +"]";
    }
     
}

The DAO created to interact between the java class and the table is the EmployeeDAOImpl.java. It has two methods, insert(Employee employee) and findById(int id), that implement the insert and select statements to the database.

Both methods use the DataSource class, a utility class that provides a connection to the database. It is part of the JDBC specification and allows a container or a framework to hide connection pooling and transaction management issues from the application code.

In addition, both methods open a Connection to the database and use the PreparedStatement, that is an object representing a precompiled SQL statement.

EmployeeDAO.java

1
2
3
4
5
6
7
8
9
package com.javacodegeeks.snippets.enterprise.dao;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface EmployeeDAO {
 
        public void insert(Employee employee);
        public Employee findById(int id);
}

EmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.sql.DataSource;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
 
public class EmployeeDAOImpl implements EmployeeDAO
{
    private DataSource dataSource;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO employee " +
                "(ID, NAME, AGE) VALUES (?, ?, ?)";
        Connection conn = null;
  
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, employee.getId());
            ps.setString(2, employee.getName());
            ps.setInt(3, employee.getAge());
            ps.executeUpdate();
            ps.close();
  
        } catch (SQLException e) {
            throw new RuntimeException(e);
  
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }
    }
  
    public Employee findById(int id){
  
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
  
        Connection conn = null;
  
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            Employee employee = null;
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                employee = new Employee(
                    rs.getInt("ID"),
                    rs.getString("NAME"),
                    rs.getInt("AGE")
                );
            }
            rs.close();
            ps.close();
            return employee;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (conn != null) {
                try {
                conn.close();
                } catch (SQLException e) {}
            }
        }
    }
}

The Datasource is configured in applicationContext.xml file. All parameters needed for the connection to the database are set here. It is defined in other bean definitions using the ref element.

applicationContext.xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
 
 
    <bean id="employeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.EmployeeDAOImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>
     
    <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="root" />
    </bean>
</beans>

We can run the example, using the App.java class. We load the employeeBean and then create a new Employee object. We first insert it to the table and then make a select to find it.

App.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
package com.javacodegeeks.snippets.enterprise;
 
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class App {
 
    public static void main(String[] args) {
     
            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");
            Employee employee1 = new Employee(123, "javacodegeeks", 30);
            employeeDAO.insert(employee1);
            Employee employee2 = employeeDAO.findById(123);
            System.out.println(employee2); 
            context.close();
    }
}

The output is shown below:

Output

1
Employee [id=123, name=javacodegeeks, age=30]

5. Use of the Spring Jdbc Template Class

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It handles the creation and release of resources, thus avoiding errors such as forgetting to close the connection. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

A simple insert example in JDBCEmployeeDAOImpl.java class, using the JdbcTemplate class is shown below:

JDBCEmployeeDAO.java

1
2
3
4
5
6
7
8
9
package com.javacodegeeks.snippets.enterprise.dao;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface JDBCEmployeeDAO {
 
    public void insert(Employee employee);
  
}

JDBCEmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }  
}

5.1 Select examples

Now, let’s see how to make use of the JdbcTemplate class to make select statements in different ways. We can add new queries in EmployeeDAOImpl.java class as shown in the following cases.

5.1.1 Select a single row

In order to make a single row select, we can implement the RowMapper interface. Thus, we can override the mapRow(ResultSet rs, int rowNum) method of RowMapper to map the table fields to the object, as shown below:

EmployeeRowMapper.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
package com.javacodegeeks.snippets.enterprise;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import org.springframework.jdbc.core.RowMapper;
 
@SuppressWarnings("rawtypes")
public class EmployeeRowMapper implements RowMapper {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee employee = new Employee();
            employee.setId(rs.getInt("ID"));
            employee.setName(rs.getString("NAME"));
            employee.setAge(rs.getInt("AGE"));
            return employee;
        }
}

We add a new method findById(int id) to JDBCEmployeeDAO.java and JDBCEmployeeDAOImpl.java. Here, the queryForObject(String sql, Object[] args, RowMapper rowMapper) method of JDBCTemplate class will create the select with the given sql statement and the given id. It will then map the result that is a single row to the Employee object using the EmployeeRowMapper.java implementation.

JDBCEmployeeDAO.java

01
02
03
04
05
06
07
08
09
10
package com.javacodegeeks.snippets.enterprise.dao;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface JDBCEmployeeDAO {
 
    public void insert(Employee employee);
    public Employee findById(int id);
  
}

JDBCEmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.EmployeeRowMapper;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }
     
    @SuppressWarnings({ "unchecked" })
    public Employee findById(int id){
          
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        Employee employee = (Employee) jdbcTemplate.queryForObject(
                sql, new Object[] { id }, new EmployeeRowMapper());
      
        return employee;
    }
 
}

Another way to get a single result is to use the BeanPropertyRowMapper implementation of RowMapper that converts a row into a new instance of the specified mapped target class. The BeanPropertyRowMapper maps a row column value to a property of the object by matching their names.

JDBCEmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }
     
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Employee findById(int id){
          
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        Employee employee = (Employee) jdbcTemplate.queryForObject(
                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
      
        return employee;
    }
 
}

We add the jdbcEmployeeDAOBean to applicationContext.xml:

applicationContext.xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
 
    <bean id="employeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.EmployeeDAOImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>
     
    <bean id="jdbcEmployeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.JDBCEmployeeDAOImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>
     
    <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="root" />
    </bean>
</beans>

After loading the new bean to App.java class we can call its methods, as shown below:

Want to master Spring Framework ?
Subscribe to our newsletter and download the Spring Framework Cookbook right now!
In order to help you master the leading and innovative Java framework, we have compiled a kick-ass guide with all its major features and use cases! Besides studying them online you may download the eBook in PDF format!

App.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.javacodegeeks.snippets.enterprise;
 
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class App {
 
    public static void main(String[] args) {
     
            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
             
            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
            Employee employee3 = new Employee(456, "javacodegeeks", 34);
            jdbcEmployeeDAO.insert(employee3);
      
            Employee employee4 = jdbcEmployeeDAO.findById(456);
            System.out.println(employee4); 
            context.close();
    }
}

The output is the one below:
Output

1
Employee [id=456, name=javacodegeeks, age=34]

5.1.2 Select total rows

Now, we can query for total number of rows in the database. Again, there are two ways to map the Result. The easiest way is to use the BeanPropertyRowMapper, as shown in the example above, but another way is to create our own mapping. We add a new method to query in JDBCEmployeeDAOImpl.java class. The new method is List<Employee> findAll(), and it uses the queryForInt(String sql) method of JdbcTemplate class execute the query, as shown below:

JDBCEmployeeDAO.java

01
02
03
04
05
06
07
08
09
10
11
12
package com.javacodegeeks.snippets.enterprise.dao;
 
import java.util.List;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface JDBCEmployeeDAO {
 
    public void insert(Employee employee);
    public Employee findById(int id);
    public List<Employee> findAll();
}

JDBCEmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }
     
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Employee findById(int id){
          
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        Employee employee = (Employee) jdbcTemplate.queryForObject(
                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
      
        return employee;
    }
 
    @SuppressWarnings("rawtypes")
    public List<Employee> findAll(){
         
        jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "SELECT * FROM EMPLOYEE";
      
        List<Employee> employees = new ArrayList<Employee>();
      
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
        for (Map row : rows) {
            Employee employee = new Employee();
            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
            employee.setName((String)row.get("NAME"));
            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
            employees.add(employee);
        }
      
        return employees;
    }  
}

5.1.3 Select a single column

To get a specified column name we create a new method, String findNameById(int id), where we use the queryForObject(String sql, Object[] args, Class<String> requiredType) method of JdbcTemplate class. In this method we can set the type of the column that the query will return.

JDBCEmployeeDAO.java

01
02
03
04
05
06
07
08
09
10
11
12
13
package com.javacodegeeks.snippets.enterprise.dao;
 
import java.util.List;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface JDBCEmployeeDAO {
 
    public void insert(Employee employee);
    public Employee findById(int id);
    public List<Employee> findAll();
    public String findNameById(int id);
}

JDBCEmployeeDAOImpl.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }
     
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Employee findById(int id){
          
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        Employee employee = (Employee) jdbcTemplate.queryForObject(
                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
      
        return employee;
    }
 
    @SuppressWarnings("rawtypes")
    public List<Employee> findAll(){
         
        jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "SELECT * FROM EMPLOYEE";
      
        List<Employee> employees = new ArrayList<Employee>();
      
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
        for (Map row : rows) {
            Employee employee = new Employee();
            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
            employee.setName((String)row.get("NAME"));
            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
            employees.add(employee);
        }
      
        return employees;
    }
     
    public String findNameById(int id){
          
        String sql = "SELECT NAME FROM EMPLOYEE WHERE ID = ?";
      
        String name = (String)jdbcTemplate.queryForObject(
                sql, new Object[] { id }, String.class);
      
        return name;
    }
 
}

We use the new queries in App.class as shown below:

App.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.javacodegeeks.snippets.enterprise;
 
import java.util.List;
 
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class App {
 
    public static void main(String[] args) {
     
            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
         
            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
             
            List<Employee> employees = jdbcEmployeeDAO.findAll();
            System.out.println(employees); 
 
            String name = jdbcEmployeeDAO.findNameById(456);
            System.out.println(name);  
 
            context.close();
    }
}

In the result below we first see the list of Employees from the findAll() method and then the value of the name column from the findNameById(int id) method.

Output

1
2
[Employee [id=123, name=javacodegeeks, age=30], Employee [id=456, name=javacodegeeks, age=34]]
javacodegeeks

5.2 BatchUpdate example

The batchUpdate() method of JdbcTemplate class can be used to perform all batch inserts to the database. Below there are two implementations of a batchUpdate() to the database.

The first one, insertBatch1(final List<Employee> employees) uses the BatchPreparedStatementSetter to insert a list of Objects to the database. The BatchPreparedStatementSetter is passed as the second parameter in the batchUpdate() method. It provides two methods that can be overriden. The getBatchSize() method provides the size of the current batch, whereas the setValues(PreparedStatement ps, int i) method is used to set the values for the parameters of the prepared statement.

The second method insertBatch2(final String sql) calls the batchUpdate() method of JDBCTemplate class to execute an sql statement.

JDBCEmployeeDAO.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
package com.javacodegeeks.snippets.enterprise.dao;
 
import java.util.List;
 
import com.javacodegeeks.snippets.enterprise.Employee;
 
public interface JDBCEmployeeDAO {
 
    public void insert(Employee employee);
    public Employee findById(int id);
    public List<Employee> findAll();
    public String findNameById(int id);
    public void insertBatch1(final List<Employee> employees);
    public void insertBatch2(final String sql);
}

JDBCEmployeeDAO.java

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
package com.javacodegeeks.snippets.enterprise.dao.impl;
 
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.Employee;
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
  
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
  
    public void insert(Employee employee){
  
        String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
  
        jdbcTemplate = new JdbcTemplate(dataSource);
  
        jdbcTemplate.update(sql, new Object[] { employee.getId(),
                employee.getName(), employee.getAge() 
        });
    }
     
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Employee findById(int id){
          
        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        Employee employee = (Employee) jdbcTemplate.queryForObject(
                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
      
        return employee;
    }
 
    @SuppressWarnings("rawtypes")
    public List<Employee> findAll(){
         
        jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "SELECT * FROM EMPLOYEE";
      
        List<Employee> employees = new ArrayList<Employee>();
      
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
        for (Map row : rows) {
            Employee employee = new Employee();
            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
            employee.setName((String)row.get("NAME"));
            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
            employees.add(employee);
        }
      
        return employees;
    }
     
    public String findNameById(int id){
 
        jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "SELECT NAME FROM EMPLOYEE WHERE ID = ?";
      
        String name = (String)jdbcTemplate.queryForObject(
                sql, new Object[] { id }, String.class);
      
        return name;
    }
 
    public void insertBatchSQL(final String sql){
          
        jdbcTemplate.batchUpdate(new String[]{sql});
      
    }
     
    public void insertBatch1(final List<Employee> employees){
 
        jdbcTemplate = new JdbcTemplate(dataSource);
          String sql = "INSERT INTO EMPLOYEE " +
            "(ID, NAME, AGE) VALUES (?, ?, ?)";
          
          jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
          
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Employee employee = employees.get(i);
                ps.setLong(1, employee.getId());
                ps.setString(2, employee.getName());
                ps.setInt(3, employee.getAge() );
            }
          
            public int getBatchSize() {
                return employees.size();
            }
          });
        }
     
    public void insertBatch2(final String sql){
        jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.batchUpdate(new String[]{sql});
      
    }
         
}

Let’s run App.java class again. We call the two new methods to insert two new rows to the Employee table and then update the table setting all values of a a column to a specified value.

App.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.javacodegeeks.snippets.enterprise;
 
import java.util.ArrayList;
import java.util.List;
 
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
 
import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
 
public class App {
 
    public static void main(String[] args) {
     
            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
 
            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
             
            Employee emplNew1 = new Employee(23, "John", 23);
            Employee emplNew2 = new Employee(223, "Mark", 43);
            List<Employee> employeesN = new ArrayList();
            employeesN.add(emplNew1);
            employeesN.add(emplNew2);
            jdbcEmployeeDAO.insertBatch1(employeesN);
            System.out.println(" inserted rows: " + employeesN);
 
            System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
            jdbcEmployeeDAO.insertBatch2("UPDATE EMPLOYEE SET NAME ='Mary'");
             
            List<Employee> employees = jdbcEmployeeDAO.findAll();
            System.out.println("Updated column name of table: " + employees);  
             
            System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
            context.close();
    }
}

The result is shown below:

Output

1
inserted rows: [Employee [id=23, name=John, age=23], Employee [id=223, name=Mark, age=43]]  FindAll : [Employee [id=23, name=John, age=23], Employee [id=223, name=Mark, age=43]] Updated column name of table: [Employee [id=23, name=Mary, age=23], Employee [id=223, name=Mary, age=43]]  FindAll : [Employee [id=23, name=Mary, age=23], Employee [id=223, name=Mary, age=43]]

6. Download the Source Code

This was an example of Spring JDBC integration and JdbcTemplate class.

Download
You can download the full source code of this example here: Spring JdbcTemplate Example

Last updated on Dec. 17th, 2021

Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy

Theodora Fragkouli

Theodora has graduated from Computer Engineering and Informatics Department in the University of Patras. She also holds a Master degree in Economics from the National and Technical University of Athens. During her studies she has been involved with a large number of projects ranging from programming and software engineering to telecommunications, hardware design and analysis. She works as a junior Software Engineer in the telecommunications sector where she is mainly involved with projects based on Java and Big Data technologies.
Subscribe
Notify of
guest


This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Igor
Igor
5 years ago

Realy good code. You helped me to understand how to make connection to DB using spring!!! Thanks.

Anonymous developer
Anonymous developer
4 years ago

This article is like the “Lost Diamond” in the thousands of “Garbage” web pages. Since I’m a fresher who just started his IT career as developer needed a clear and understanding picture of the concept and trust me i searched hundred of articles available on the web but finally I got what i was looking for!!!

Back to top button