Cодержание
Руководство Spring JDBC
View more Tutorials:

В данной статье я покажу вам как использовать класс JdbcTemplate, это центральный класс в Spring JDBC, который включает самую распространенную логику в использовании JDBC API, чтобы получить доступ в базу данных, например создать соединения, команды запроса, удалить, редактировать, обновить данные... Данные класс можно найти в упаковке (package) org.springframework.jdbc.core.
Цель данной статьи это предоставить примеры использования методов класса JdbcTemplate.
- JdbcTemplate.queryForList
- JdbcTemplate.queryForRowSet
- JdbcTemplate.query
- JdbcTemplate.queryForObject
- ...
Давайте удостоверимся вы уже можете создать проект используя Spring JDBC чтобы подключить к определенной базе данных, если нет, вы можете просмотреть статью ниже:

Department.java
package org.o7planning.springjdbc.model; public class Department { private Long deptId; private String deptNo; private String deptName; public Department() { } public Department(Long deptId, String deptNo, String deptName) { this.deptId = deptId; this.deptNo = deptNo; this.deptName = deptName; } public Long getDeptId() { return deptId; } public void setDeptId(Long deptId) { this.deptId = deptId; } public String getDeptNo() { return deptNo; } public void setDeptNo(String deptNo) { this.deptNo = deptNo; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } }

Первый и самый простой пример с Spring JDBC это запрос (query) на получение списка значений столбца.

QueryForListReturnListDAO.java
package org.o7planning.springjdbc.dao; import java.util.List; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryForListReturnListDAO extends JdbcDaoSupport { @Autowired public QueryForListReturnListDAO(DataSource dataSource) { this.setDataSource(dataSource); } public List<String> getDeptNames() { String sql = "Select d.dept_name from Department d "; // queryForList(String sql, Class<T> elementType) List<String> list = this.getJdbcTemplate().queryForList(sql, String.class); return list; } public List<String> getDeptNames(String searchName) { String sql = "Select d.dept_name from Department d "// + " Where d.dept_name like ? "; // queryForList(String sql, Class<T> elementType, Object... args) List<String> list = this.getJdbcTemplate().queryForList(sql, String.class, // "%" + searchName + "%"); return list; } }
QueryForListReturnList_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import java.util.List; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryForListReturnListDAO; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; public class QueryForListReturnList_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryForListReturnListDAO dao = context.getBean(QueryForListReturnListDAO.class); List<String> names = dao.getDeptNames("A"); for (String name : names) { System.out.println("Dept Name: " + name); } } }
Запуск примера:

// JdbcTemplate.queryForList methods, return List<Map<String,Object>> public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException; public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException;

Пример:
QueryForListReturnListMapDAO.java
package org.o7planning.springjdbc.dao; import java.sql.Types; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryForListReturnListMapDAO extends JdbcDaoSupport { @Autowired public QueryForListReturnListMapDAO(DataSource dataSource) { this.setDataSource(dataSource); } // Map<String columnName, Object value> public List<Map<String, Object>> queryForList_ListMap() { String sql = "Select e.Emp_No, e.Emp_Name from Employee e "; // List<Map<String, Object>> queryForList(String sql) List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql); return list; } // List<Map<String, Object>> // queryForList(String sql, Object[] args, int[] argTypes) public List<Map<String, Object>> queryForList_ListMap2() { String sql = "Select e.Emp_Id,e.Emp_No,e.Emp_Name From Employee e " // + " Where e.Hire_Date > ? and e.Salary > ? "; // Date hireDate = getDateByYear(1981); Object[] args = new Object[] { hireDate, 2800 }; int[] argTypes = new int[] { Types.DATE, Types.DOUBLE }; // List<Map<String, Object>> queryForList(String sql) List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql, args, argTypes); return list; } private Date getDateByYear(int year) { java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); Calendar c = Calendar.getInstance(); c.setTime(date); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, Calendar.JANUARY); c.set(Calendar.DAY_OF_YEAR, 1); return c.getTime(); } }
QueryForListReturnListMap_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryForListReturnListMapDAO; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; public class QueryForListReturnListMap_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryForListReturnListMapDAO dao = context.getBean(QueryForListReturnListMapDAO.class); // Map<String columnName, Object value) List<Map<String, Object>> list = dao.queryForList_ListMap2(); for (Map<String, Object> map : list) { System.out.println("-----"); for (String key : map.keySet()) { System.out.println("Key: " + key + " - value: " + map.get(key)); } } } }
Запуск примера:

// JdbcTemplate.queryForRowSet methods .... public SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException; public SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException;
QueryForRowSetReturnSqlRowSetDAO.java
package org.o7planning.springjdbc.dao; import java.sql.Types; import java.util.Calendar; import java.util.Date; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.stereotype.Repository; @Repository public class QueryForRowSetReturnSqlRowSetDAO extends JdbcDaoSupport { @Autowired public QueryForRowSetReturnSqlRowSetDAO(DataSource dataSource) { this.setDataSource(dataSource); } // SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) public SqlRowSet queryForRowSet_SqlRowSet() { String sql = "Select e.Emp_Id,e.Emp_No,e.Emp_Name From Employee e " // + " Where e.Hire_Date > ? and e.Salary > ? "; // Date hireDate = getDateByYear(1981); Object[] args = new Object[] { hireDate, 2800 }; int[] argTypes = new int[] { Types.DATE, Types.DOUBLE }; // SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) SqlRowSet rowSet = this.getJdbcTemplate().queryForRowSet(sql, args, argTypes); return rowSet; } private Date getDateByYear(int year) { java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); Calendar c = Calendar.getInstance(); c.setTime(date); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, Calendar.JANUARY); c.set(Calendar.DAY_OF_YEAR, 1); return c.getTime(); } }
QueryForRowSetReturnSqlRowSet_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryForRowSetReturnSqlRowSetDAO; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; import org.springframework.jdbc.support.rowset.SqlRowSet; public class QueryForRowSetReturnSqlRowSet_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryForRowSetReturnSqlRowSetDAO dao = context.getBean(QueryForRowSetReturnSqlRowSetDAO.class); // SqlRowSet SqlRowSet rowSet = dao.queryForRowSet_SqlRowSet(); while (rowSet.next()) { System.out.println("-----"); Long empId = rowSet.getLong("Emp_Id"); // Index = 1 String empNo = rowSet.getString(2); // Index = 2 String empName = rowSet.getString("Emp_Name"); // Index = 3 System.out.println("EmpID: " + empId + ", EmpNo: " + empNo + ", EmpName:" + empName); } } }

// query methods with RowMapper & returns List public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException; public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException; public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException;
JdbcTemplate предоставляет некоторые методы запроса, результат возвращает список объектов Java. Вам нужно предоставить RowMapper определяющий отображения между столбцами и полями класса.
QueryWithRowMapperDAO.java
package org.o7planning.springjdbc.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import javax.sql.DataSource; import org.o7planning.springjdbc.model.Department; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryWithRowMapperDAO extends JdbcDaoSupport { @Autowired public QueryWithRowMapperDAO(DataSource dataSource) { this.setDataSource(dataSource); } private static final String BASE_SQL = // "Select d.Dept_Id, d.Dept_No, d.Dept_Name from Department d "; class DepartmentRowMapper implements RowMapper<Department> { @Override public Department mapRow(ResultSet rs, int rowNum) throws SQLException { Long deptId = rs.getLong("Dept_Id"); String deptNo = rs.getString("Dept_No"); String deptName = rs.getString("Dept_Name"); return new Department(deptId, deptNo, deptName); } } public List<Department> queryDepartment() { String sql = BASE_SQL // + " Where d.Dept_Id > ? "; DepartmentRowMapper rowMapper = new DepartmentRowMapper(); Object[] args = new Object[] { 20 }; List<Department> list = this.getJdbcTemplate().query(sql, args, rowMapper); return list; } }
Test:
QueryWithRowMapper_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import java.util.List; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryWithRowMapperDAO; import org.o7planning.springjdbc.model.Department; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; public class QueryWithRowMapper_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryWithRowMapperDAO dao = context.getBean(QueryWithRowMapperDAO.class); List<Department> list = dao.queryDepartment() ; for(Department dept: list) { System.out.println("DeptNo: "+ dept.getDeptNo()+" - DeptName: "+ dept.getDeptName()); } } }

JdbcTemplate предоставляет вам несколько методов для запроса данных и интеракции с данными через ResultSet. Ниже являются эти методы:
// JdbcTemplate.query methods & RowCallbackHandler. public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException; public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException; public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException; public void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException; public void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException;
RowCallbackHandler interface:
** RowCallbackHandler **
public interface RowCallbackHandler { void processRow(ResultSet rs) throws SQLException; }
Пример:
QueryWithRowCallbackHandlerDAO.java
package org.o7planning.springjdbc.dao; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryWithRowCallbackHandlerDAO extends JdbcDaoSupport { @Autowired public QueryWithRowCallbackHandlerDAO(DataSource dataSource) { this.setDataSource(dataSource); } public void queryEmployee() { String sql = "Select e.Emp_Id, e.Emp_No, e.Emp_Name,e.Salary from Employee e "// + " Where e.Salary > ? "; RowCallbackHandler handler = new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { Long empId = rs.getLong("Emp_Id"); String empNo = rs.getString(2); System.out.println(" ---------------- "); System.out.println("EmpId:" + empId); System.out.println("EmpNo:" + empNo); } }; // query(String sql, RowCallbackHandler rch, Object... args) this.getJdbcTemplate().query(sql, handler, 2800); } }
QueryWithRowCallbackHandler_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryWithRowCallbackHandlerDAO; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; public class QueryWithRowCallbackHandler_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryWithRowCallbackHandlerDAO dao = context.getBean(QueryWithRowCallbackHandlerDAO.class); dao.queryEmployee(); } }

// JdbcTemplate.query methods with ResultSetExtractor. public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException; public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException; public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException; public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException; public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException;
Пример:
QueryWithResultSetExtractorDAO.java
package org.o7planning.springjdbc.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.o7planning.springjdbc.model.Department; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryWithResultSetExtractorDAO extends JdbcDaoSupport { @Autowired public QueryWithResultSetExtractorDAO(DataSource dataSource) { this.setDataSource(dataSource); } private static final String BASE_SQL = // "Select d.Dept_Id, d.Dept_No, d.Dept_Name from Department d "; class DepartmentListResultSetExtractor implements ResultSetExtractor<List<Department>> { @Override public List<Department> extractData(ResultSet rs) throws SQLException, DataAccessException { List<Department> list = new ArrayList<Department>(); while (rs.next()) { Long deptId = rs.getLong("Dept_Id"); String deptNo = rs.getString("Dept_No"); String deptName = rs.getString("Dept_Name"); list.add(new Department(deptId, deptNo, deptName)); } return list; } } class DepartmentResultSetExtractor implements ResultSetExtractor<Department> { @Override public Department extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { Long deptId = rs.getLong("Dept_Id"); String deptNo = rs.getString("Dept_No"); String deptName = rs.getString("Dept_Name"); return new Department(deptId, deptNo, deptName); } return null; } } public List<Department> queryDepartments() { String sql = BASE_SQL // + " Where d.Dept_Id > ? "; DepartmentListResultSetExtractor rse = new DepartmentListResultSetExtractor(); // <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) List<Department> list = this.getJdbcTemplate().query(sql, rse, 40); return list; } public Department findDepartment(Long deptId) { String sql = BASE_SQL // + " Where d.Dept_Id = ? "; DepartmentResultSetExtractor rse = new DepartmentResultSetExtractor(); // <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) Department dept = this.getJdbcTemplate().query(sql, rse, 40); return dept; } }
// JdbcTemplate.queryForObject methods: public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException; public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException; public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException; public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) throws DataAccessException; public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException; public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException;
Пример:
QueryForObjectDAO.java
package org.o7planning.springjdbc.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Date; import javax.sql.DataSource; import org.o7planning.springjdbc.model.Department; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class QueryForObjectDAO extends JdbcDaoSupport { @Autowired public QueryForObjectDAO(DataSource dataSource) { this.setDataSource(dataSource); } private static final String BASE_SQL = // "Select d.Dept_Id, d.Dept_No, d.Dept_Name from Department d "; class DepartmentRowMapper implements RowMapper<Department> { @Override public Department mapRow(ResultSet rs, int rowNum) throws SQLException { Long deptId = rs.getLong("Dept_Id"); String deptNo = rs.getString("Dept_No"); String deptName = rs.getString("Dept_Name"); return new Department(deptId, deptNo, deptName); } } public Department getDepartment(String deptNo) { try { String sql = BASE_SQL // + " Where d.Dept_No = ? "; DepartmentRowMapper rowMapper = new DepartmentRowMapper(); Object[] args = new Object[] { deptNo }; Department dept = this.getJdbcTemplate().queryForObject(sql, args, rowMapper); return dept; } catch (EmptyResultDataAccessException e) { return null; } } public String getDeptNameById(Long deptId) { try { String sql = "Select d.Dept_Name from Department d "// + " Where d.Dept_Id = ?"; Object[] args = new Object[] { deptId }; String deptName = this.getJdbcTemplate().queryForObject(sql, String.class, args); return deptName; } catch (EmptyResultDataAccessException e) { return null; } } public Date getEmpHireDateById(Long empId) { try { String sql = "Select e.Hire_Date from Employee e "// + " Where e.Emp_Id = ?"; Object[] args = new Object[] { empId }; int[] argTypes = new int[] { Types.BIGINT }; Date hireDate = this.getJdbcTemplate().queryForObject(sql, args, argTypes, Date.class); return hireDate; } catch (EmptyResultDataAccessException e) { return null; } } }
Test:
QueryForObject_Test.java
package org.o7planning.springjdbc.demo; import java.sql.SQLException; import java.util.Date; import org.o7planning.springjdbc.config.AppConfiguration; import org.o7planning.springjdbc.dao.QueryForObjectDAO; import org.o7planning.springjdbc.model.Department; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; public class QueryForObject_Test { public static void main(String[] args) throws SQLException { ApplicationContext context = new AnnotationConfigApplicationContext(AppConfiguration.class); QueryForObjectDAO dao = context.getBean(QueryForObjectDAO.class); System.out.println(" ------------- "); Department dept = dao.getDepartment("D20"); if (dept != null) { System.out.println("DeptNo: " + dept.getDeptNo() + " - DeptName: " + dept.getDeptName()); } else { System.out.println("Department not found!"); } System.out.println(" ------------- "); String deptName = dao.getDeptNameById(30L); System.out.println("Dept Name by Id 30: " + deptName); System.out.println(" ------------- "); Date hireDate = dao.getEmpHireDateById(7839L); System.out.println("HireDate by EmpId 7839: " + hireDate); } }
