Руководство Spring JDBC
View more Tutorials:
Следуйте за нами на нашей фан-странице, чтобы получать уведомления каждый раз, когда появляются новые статьи.
Facebook
В данной статье я покажу вам как использовать класс 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);
}
}
}
Запуск примера:
Dept Name: ACCOUNTING
Dept Name: RESEARCH
Dept Name: SALES
Dept Name: OPERATIONS
// 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));
}
}
}
}
Запуск примера:
-----
Key: EMP_ID - value: 7839
Key: EMP_ID - value: E7839
Key: EMP_NAME - value: KING
-----
Key: EMP_ID - value: 7566
Key: EMP_ID - value: E7566
Key: EMP_NAME - value: JONES
...
// 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);
}
}
}
Output:
-----
EmpID: 7839, EmpNo: E7839, EmpName:KING
-----
EmpID: 7566, EmpNo: E7566, EmpName:JONES
-----
EmpID: 7902, EmpNo: E7902, EmpName:FORD
-----
EmpID: 7698, EmpNo: E7698, EmpName:BLAKE
...
// 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());
}
}
}
Output:
DeptNo: D30 - DeptName: SALES
DeptNo: D40 - DeptName: OPERATIONS
DeptNo: D41 - DeptName: HR
DeptNo: D42 - DeptName: INV
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();
}
}
Output:
----------------
EmpId:7839
EmpNo:E7839
----------------
EmpId:7566
EmpNo:E7566
...
// 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);
}
}
Output:
-------------
DeptNo: D20 - DeptName: RESEARCH
-------------
Dept Name by Id 30: SALES
-------------
HireDate by EmpId 7839: 1981-11-17 00:00:00.0
*
