实战——Spring中提供的数据库操作API

一、使用步骤

只需要在pom中引入spring的依赖jar包，即可。

1 注入依赖

NamedParameterJdbcTemplate

2 操作使用（3步骤）

public List<EcssDepartment> getDepartmentsByLongDepIds(List<Long> deptSet) {
        //1.构建sql
        String sql = " SELECT department.n_id id, department.c_ec_code ecCode, department.c_name name, department.c_code code, " +
                " department.n_parent_id parentId ,department.n_sort sort " +
                " FROM t_ecss_department department WHERE  " +
                " department.n_id IN (:depIds) " +
                " AND department.n_last_update_type <> :lastUpdateType ";
        //2.构建sql中的占位符参数
        MapSqlParameterSource sqlParam = new MapSqlParameterSource();
        sqlParam.addValue("depIds", deptSet);
        sqlParam.addValue("lastUpdateType",EcssDepartment.ENUM_LAST_UPDATE_TYPE.DELETETYPE.value);

        //3.执行sql的API 参数1：sql 参数2：sql参数 参数3：映射关系对象
        List<EcssDepartment> departmentList = namedJdbcTemplate.query(sql, sqlParam, BeanPropertyRowMapper.newInstance(EcssDepartment.class));
        return departmentList;
    }

3 映射关系对象：RowMapper

1、若select 的字段（可以起别名） 跟 实体列保持一致，则可以使用：
BeanPropertyRowMapper.newInstance(EcssDepartment.class) ，它的返回值 就是一个 RowMapper接口的实现类对象。

2、也可以自定义映射关系对象：

二、SpringJdbcTemplate实操演示

/**
 *
 * 基础jdbc操作类，提供基于标准sql的数据库操作方法
 *
 */
@Component
public class JdbcOperator implements InitializingBean {

    public Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private DataSource dataSource;

    @Autowired
    public NamedParameterJdbcTemplate namedJdbcTemplate;

    private DbName dbName;

    //数据库类型
    private enum DbName{
        MYSQL,ORACLE,H2
    }

    @Override
    public void afterPropertiesSet() {
        String dialect = Hibernates.getDialect(dataSource);
        if (MySQL5InnoDBDialect.class.getName().equals(dialect)) {
            dbName = DbName.MYSQL;
        } else if (Oracle10gDialect.class.getName().equals(dialect)){
            dbName = DbName.ORACLE;
        } else if (H2Dialect.class.getName().equals(dialect)) {
            dbName = DbName.H2;
        }

        Assert.notNull(dbName, "不支持的数据库类型：" + dialect);

        logger.debug("当前系统使用的数据库为：{}", dbName);
    }


    /**
     *
     * @Title: queryForPage
     * @Description: 不带参数的分页查询
     * @param sql
     * @param pageSize 每页条数
     * @param pageNo 当前页码
     * @return Pagination 分页信息，其中包含有数据列表:List<Map<String, Object>>
     */
    public Pagination<Map<String, Object>> queryForPage(String sql, Integer pageSize, Integer pageNo) {
        return queryForPage(sql, pageSize, pageNo, new MapSqlParameterSource());
    }

    /**
     *
     * 不带参数的分布查询
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param mapper
     * @return Pagination 分页信息，其中包含有数据列表:List<T>
     */
    public <T> Pagination<T> queryForPage(String sql, Integer pageSize, Integer pageNo, RowMapper<T> mapper) {
        return queryForPage(sql, pageSize, pageNo, null, mapper);
    }

    /**
     * 分页查询，程序根据连接对象自动区分数据库类型，调用时不必区分数据库类型(仅支持oralce、mysql)
     * @Title: queryForPage
     * @Description: 带参数的分页查询
     * @param sql
     * @param pageSize 每页条数
     * @param pageNo 当前页码
     * @param paramSource 查询条件参数
     * @return Pagination 分页信息，其中包含有数据列表:List<Map<String, Object>>
     */
    public Pagination<Map<String, Object>> queryForPage(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource) {
        switch (dbName) {
            case MYSQL:
                return queryForPageMysql(sql, pageSize, pageNo, paramSource);
            case ORACLE:
                return queryForPageOracle(sql, pageSize, pageNo, paramSource);
            case H2:
                return queryForPageMysql(sql, pageSize, pageNo, paramSource);
            default:
                throw new UnsupportedDatabaseException("不支持的数据库类型：" + dbName);
        }
    }

    /**
     *
     * 分页查询，程序根据连接对象自动区分数据库类型，调用时不必区分数据库类型(仅支持oralce、mysql)
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param paramSource
     * @param mapper
     * @return Pagination 分页信息，其中包含有数据列表:List<T>
     */
    public <T> Pagination<T> queryForPage(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource, RowMapper<T> mapper) {
        switch (dbName) {
            case MYSQL:
                return queryForPageMysql(sql, pageSize, pageNo, paramSource, mapper);
            case ORACLE:
                return queryForPageOracle(sql, pageSize, pageNo, paramSource, mapper);
            case H2:
                return queryForPageMysql(sql, pageSize, pageNo, paramSource, mapper);
            default:
                throw new UnsupportedDatabaseException("不支持的数据库类型：" + dbName);
        }
    }

    /**
     * mysql分页查询
     * @Description：
     * @author: 周玉杰
     * @date: 2013-12-23 下午3:41:28
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param paramSource
     * @return
     */
    private Pagination<Map<String, Object>> queryForPageMysql(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource){
        int totalCount = 0;
        if(null != paramSource){
            totalCount = namedJdbcTemplate.queryForObject(getRowCountSql(sql), paramSource, Integer.class);
        }

        Pagination<Map<String, Object>> p = new Pagination<Map<String, Object>>(pageNo, pageSize, totalCount);
        if (totalCount < 1) {
            p.setList(new ArrayList<Map<String, Object>>());
            return p;
        }

        sql += " limit :offset, :rows";
        if(paramSource == null) {
            paramSource = new MapSqlParameterSource();
        }
        paramSource.addValue("offset", p.getFirstResult());
        paramSource.addValue("rows", p.getPageSize());

        p.setList(namedJdbcTemplate.queryForList(sql, paramSource));
        return p;
    }

    /**
     *
     * 基于mysql的分页查询
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param paramSource
     * @param mapper
     * @return
     */
    private <T> Pagination<T> queryForPageMysql(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource, RowMapper<T> mapper){
        int totalCount = 0;
        if(null != paramSource){
            totalCount = namedJdbcTemplate.queryForObject(getRowCountSql(sql), paramSource, Integer.class);
        }
        Pagination<T> p = new Pagination<T>(pageNo, pageSize, totalCount);
        if (totalCount < 1) {
            p.setList(new ArrayList<T>());
            return p;
        }

        sql += " limit :offset, :rows";
        if(paramSource == null) {
            paramSource = new MapSqlParameterSource();
        }
        paramSource.addValue("offset", p.getFirstResult());
        paramSource.addValue("rows", p.getPageSize());

        p.setList(namedJdbcTemplate.query(sql, paramSource, mapper));
        return p;
    }

    /**
     * oracle分页查询
     * @Description：
     * @author: 周玉杰
     * @date: 2013-12-23 下午3:41:32
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param paramSource
     * @return
     */
    private Pagination<Map<String, Object>> queryForPageOracle(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource){
        int totalCount = 0;
        if(null != paramSource){
            totalCount = namedJdbcTemplate.queryForObject(getRowCountSql(sql), paramSource, Integer.class);
        }
        Pagination<Map<String, Object>> p = new Pagination<Map<String, Object>>(pageNo, pageSize, totalCount);
        if (totalCount < 1) {
            p.setList(new ArrayList<Map<String, Object>>());
            return p;
        }
        sql =  "SELECT * FROM (SELECT pagedTable.*, ROWNUM AS myRownum FROM (" +
                sql + ") pagedTable WHERE ROWNUM<= :offset" +
                " ) WHERE myRownum>= :rows";
        if(paramSource == null) {
            paramSource = new MapSqlParameterSource();
        }
        paramSource.addValue("offset", p.getFirstResult() + p.getPageSize());
        paramSource.addValue("rows", p.getFirstResult() + 1);
        p.setList(namedJdbcTemplate.queryForList(sql, paramSource));
        return p;
    }

    /**
     *
     * 基于oracle的分页查询
     * @param sql
     * @param pageSize
     * @param pageNo
     * @param paramSource
     * @param mapper
     * @return
     */
    private <T> Pagination<T> queryForPageOracle(String sql, Integer pageSize, Integer pageNo, MapSqlParameterSource paramSource, RowMapper<T> mapper){
        int totalCount = 0;
        if(null != paramSource){
            totalCount = namedJdbcTemplate.queryForObject(getRowCountSql(sql), paramSource, Integer.class);
        }
        Pagination<T> p = new Pagination<T>(pageNo, pageSize, totalCount);
        if (totalCount < 1) {
            p.setList(new ArrayList<T>());
            return p;
        }
        sql =  "SELECT * FROM (SELECT pagedTable.*, ROWNUM AS myRownum FROM (" +
                sql + ") pagedTable WHERE ROWNUM<= :offset" +
                " ) WHERE myRownum>= :rows";
        if(paramSource == null) {
            paramSource = new MapSqlParameterSource();
        }
        paramSource.addValue("offset", p.getFirstResult() + p.getPageSize());
        paramSource.addValue("rows", p.getFirstResult() + 1);
        p.setList(namedJdbcTemplate.query(sql, paramSource, mapper));
        return p;
    }

    /**
     *
     * @Title: getRowCountSql
     * @Description: 获得查询数据库记录数的sql语句
     * @param sql
     * @return String
     */
    private String getRowCountSql(String sql) {
        int fromIndex = sql.toLowerCase().indexOf("from");
        String rowCountSql = sql.substring(fromIndex);

        int index = rowCountSql.toLowerCase().indexOf("order by");
        if (index > 0) {
            rowCountSql = rowCountSql.substring(0, index);
        }
        return "SELECT COUNT(*)" + rowCountSql;
    }

    /**
     *
     * @Description：执行sql语句执行数据删除操作
     * @author: 任瑞修
     * @date: 2013-11-15 下午2:20:02
     * @param sql 要执行的删除语句
     * @param sqlParams 条件
     * @return int 影响的记录数
     */
    public int deleteBySql(String sql, SqlParameterSource sqlParams) {
        return this.namedJdbcTemplate.update(sql, sqlParams);
    }

    /**
     *
     * 执行sql语句执行数据批量删除操作
     * @param sql 要执行的删除语句
     * @param sqlParams 条件
     * @return int[] 影响的记录数
     */
    public int[] deleteBySql(String sql, SqlParameterSource... sqlParams) {
        if(ArrayUtils.isEmpty(sqlParams)) {
            sqlParams = ArrayUtils.add(sqlParams, null);
        }
        return namedJdbcTemplate.batchUpdate(sql, sqlParams);
    }

    /**
     *
     * @Description：根据指定条件执行对指定数据表的数据删除操作
     * @author: 任瑞修
     * @date: 2013-11-19 上午9:35:10
     * @param tableName 要删除数据的表名
     * @param condition 条件(where后的表达式)
     * @return int 影响的记录数
     */
    public int deleteByCondition(String tableName, Condition condition) {
        StringBuffer sql = new StringBuffer("DELETE FROM ");
        sql.append(tableName).append(" WHERE ").append(condition.toSqlString());
        return deleteBySql(sql.toString(), new MapSqlParameterSource(condition.getParams()));
    }

    /**
     *
     * @Title: executeSql
     * @Description: 执行指定的sql语句
     * @param sql
     * @param sqlParam void
     * @return int 影响的记录数
     */
    public int executeSql(String sql, SqlParameterSource sqlParam) {
        return this.namedJdbcTemplate.update(sql, sqlParam);
    }

    /**
     *
     * @Title: queryForList
     * @Description: 执行指定的SQL语句查询数据
     * @param sql
     * @param sqlParam
     * @return List<Map<String,Object>>
     */
    public List<Map<String, Object>> queryForList(String sql, SqlParameterSource sqlParam) {
        return namedJdbcTemplate.queryForList(sql, sqlParam);
    }

    /**
     *
     * @Title: queryForList
     * @Description: 执行指定的SQL语句查询数据
     * @param sql
     * @param sqlParam
     * @param mapper
     * @return List<T>
     */
    public <T> List<T> queryForList(String sql, SqlParameterSource sqlParam, RowMapper<T> mapper) {
        return namedJdbcTemplate.query(sql, sqlParam, mapper);
    }

    /**
     *
     * @Title: queryForObject
     * @Description: 执行指定的SQL语句查询数据
     * @param sql
     * @param sqlParam
     * @param mapper
     * @return T
     */
    public <T> T queryForObject(String sql, SqlParameterSource sqlParam, RowMapper<T> mapper) {
        return namedJdbcTemplate.queryForObject(sql, sqlParam, mapper);
    }

    /**
     *
     * @Description：执行指定的SQL语句查询数据
     * @author: 任瑞修
     * @date: 2013-11-6 上午11:40:38
     * @param sql
     * @param sqlParam
     * @return
     */
    public Map<String, Object> queryForMap(String sql, SqlParameterSource sqlParam) {
        return namedJdbcTemplate.queryForMap(sql, sqlParam);
    }

    /**
     *
     * @Title: queryForInt
     * @Description: 执行指定的SQL语句查询结果，多用于count等
     * @param sql
     * @param sqlParam
     * @return int
     */
    public int queryForInt(String sql, SqlParameterSource sqlParam) {
        return namedJdbcTemplate.queryForObject(sql, sqlParam, Integer.class);
    }

    /**
     *
     * @Description：执行指定的SQL语句查询结果
     * @author: 任瑞修
     * @date: 2013-11-12 上午8:45:07
     * @param sql
     * @param sqlParam
     * @return long
     */
    public long queryForLong(String sql, SqlParameterSource sqlParam) {
        return namedJdbcTemplate.queryForObject(sql, sqlParam, Long.class);
    }

    /**
     *
     * @Description：执行指定的sql批量添加或更新数据
     * @author: 任瑞修
     * @date: 2013-11-6 下午5:17:50
     * @param sql
     * @param sqlParams
     * @return int[] 影响的记录数
     */
    public int[] addOrUpdate(String sql, SqlParameterSource... sqlParams) {
        if(ArrayUtils.isEmpty(sqlParams)) {
            sqlParams = ArrayUtils.add(sqlParams, null);
        }
        return namedJdbcTemplate.batchUpdate(sql, sqlParams);
    }

    /**
     *
     * @Description：按指定条件统计记录数
     * @author: 任瑞修
     * @date: 2013-11-19 上午10:46:45
     * @param tableName 要执行记录数统计的表名
     * @param condition 条件
     * @return 记录数
     */
    public int countByCondition(String tableName, Condition condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(1) FROM ").append(tableName).append(" WHERE ").append(condition.toSqlString());
        return queryForInt(sql.toString(), new MapSqlParameterSource(condition.getParams()));
    }
}