betacode

Руководство Spring JDBC

  1. Modal Classes
  2. JdbcTemplate.queryForList -> List
  3. JdbcTemplate.queryForList -> List<Map>
  4. JdbcTemplate.queryForRowSet
  5. JdbcTemplate.query & RowMapper
  6. JdbcTemplate.query & RowCallbackHandler
  7. JdbcTemplate.query & ResultSetExtractor
  8. JdbcTemplate.queryForObject
  9. TODO ..
В данной статье я покажу вам как использовать класс JdbcTemplate, это центральный класс в Spring JDBC, который включает самую распространенную логику в использовании JDBC API, чтобы получить доступ в базу данных, например создать соединения, команды запроса, удалить, редактировать, обновить данные... Данные класс можно найти в упаковке (package) org.springframework.jdbc.core.
Цель данной статьи это предоставить примеры использования методов класса JdbcTemplate.
  • JdbcTemplate.queryForList
  • JdbcTemplate.queryForRowSet
  • JdbcTemplate.query
  • JdbcTemplate.queryForObject
  • ...
Давайте удостоверимся вы уже можете создать проект используя Spring JDBC чтобы подключить к определенной базе данных, если нет, вы можете просмотреть статью ниже:

1- Modal Classes

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;
    }
    
    
}

2- JdbcTemplate.queryForList -> List

Первый и самый простой пример с 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);
        }
    }

}
Запуск примера:

3- JdbcTemplate.queryForList -> List&lt;Map> 

// 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));
            }
        }
    }

}
Запуск примера:

4- JdbcTemplate.queryForRowSet 

// 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);
        }

    }
   
}

5- JdbcTemplate.query & RowMapper 

// 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());
        }
    }
   
}

6- JdbcTemplate.query & RowCallbackHandler

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();
    }
   
}

7- JdbcTemplate.query & ResultSetExtractor 

// 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;
    }

}

8- JdbcTemplate.queryForObject 

// 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);

    }

}

9- TODO ..