【5】JdbcTemplate

JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用
JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。
他帮助我们避免一些常见的错误，比如忘了总要关闭连接。

    org.springframework.boot
    spring-boot-starter-jdbc

package com.sjcredit.ccbsiapi.repository;

import com.sjcredit.ccbsiapi.entity.BizAuthEntity;
import com.sjcredit.ccbsiapi.entity.SiPayinfoEntity;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository

@RequiredArgsConstructor
public class JdbcTemplateDemo {

    private final JdbcTemplate jdbcTemplate;

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    
    public int insert(BizAuthEntity bizAuthEntity) {
        String sql = "insert into biz_auth (id,auth_no,corp_id,start_date,approval_status,approval_desc,dead_line,create_by,create_time,update_by,update_time,auth_org_code,file_name)" +
                "values (:id, :authNo, :corpId, :startDate, :approvalStatus, :approvalDesc, :deadLine, :createBy, :createTime, :updateBy, :updateTime, :authOrgCode, :fileName)";

        int count = namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(bizAuthEntity));
        return count;
    }

    
    public int insert(String corpName, String year, Double param) {

        String sql = "INSERT INTO score_result VALUES (?,?,?)";
        int result = jdbcTemplate.update(sql, corpName, year, param == null ? 0.00 : param);
        return result;

    }

    
    public Integer deleteBy(String corpId) {
        String sql = "DELETE from biz_auth where corp_id = ?";
        return jdbcTemplate.update(sql, corpId);
    }

    
    public int update(Date deadLine, String id) {
        String sql = "UPDATE biz_auth SET dead_line = ? where id = ?";
        int update = jdbcTemplate.update(sql, deadLine, id);
        return update;
    }

    
    public BizAuthEntity selectObject(String orgCode, String corpName, String corpCode) {
        String sql = "select auth.* from biz_auth auth left join biz_corp corp on auth.corp_id = corp.id " +
                "where auth.auth_org_code = ? and (corp.corp_name = ? or corp.corp_code = ?) ";
        BizAuthEntity bizAuthEntity = null;
        //查询结果为空的时候会报异常
        try {
            bizAuthEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BizAuthEntity.class), orgCode, corpName, corpCode);
        } catch (Exception e) {
        }
        return bizAuthEntity;
    }

    
    public String selectString(String orgCode, String corpName, String corpCode) {
        String sql = "select corp.id from biz_corp corp left join biz_auth auth on auth.corp_id = corp.id " +
                "where auth.auth_org_code = ? and (corp.corp_name = ? or corp.corp_code = ?)";
        return jdbcTemplate.queryForObject(sql, String.class, orgCode, corpName, corpCode);
    }

    
    public List selectEntityList(String dwmc, String tyshxydm) {
        String sql = "SELECT * FROM si_payinfo WHERe dwmc = ? and tyshxydm = ?";
        List list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SiPayinfoEntity.class), dwmc, tyshxydm);

        return list;
    }

    public List selectStringList(String ruleId) {

        String sql = "SELECT name FROM pub_rule_detail where rule_id = ? and status = 1";
        List columnList = jdbcTemplate.queryForList(sql, String.class, ruleId);
        return columnList;
    }

    
    public List> selectMapList(String dwmc, String tyshxydm) {
        String sql = "SELECT * FROM si_latefeeinfo WHERe dwmc = ? and tyshxydm = ?";

        List> listResult = jdbcTemplate.query(sql, (resultSet, i) -> {
            Map map = new HashMap<>();
            map.put("TYSHXYDM", resultSet.getString("TYSHXYDM"));
            map.put("DWMC", resultSet.getString("DWMC"));
            map.put("JFSSQQ", resultSet.getString("JFSSQQ"));
            map.put("JFSSQZ", resultSet.getString("JFSSQZ"));
            map.put("ZNJ", resultSet.getBigDecimal("ZNJ"));
            return map;
        }, dwmc, tyshxydm);

        return listResult;
    }

    
    public List