【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@RequiredArgsConstructorpublic 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