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.
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.
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.
The Maven project structure is shown below:
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 | <project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" <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 | <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" <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
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
After loading the new bean to App.java class we can call its methods, 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 | 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.
You can download the full source code of this example here: Spring JdbcTemplate Example
Last updated on Dec. 17th, 2021
Realy good code. You helped me to understand how to make connection to DB using spring!!! Thanks.
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!!!