【Spring】JdbcTemplate

Jdbc

jdk提供的最基础的访问数据库的方式

package com.sjcredit.orm.jdbc;

import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author dingyn
 * @description JdbcDemo类，jdk提供的最基础的访问数据库的方式
 * @create 2021-07-22 19:15
 */
public class JdbcDemo {

    public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&serverTimezone=GMT%2B8";

    public static final String USER = "root";

    public static final String PASSWORD = "admin123";

    public static void main(String[] args) throws Exception {

        //1.加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM country where name ='China'");
        //如果有数据，rs.next()返回true
        while (rs.next()) {
            System.out.println(rs.getString("name") + " region：" + rs.getString("region"));
        }
        // 需要手动关闭连接
        if (!StringUtils.isEmpty(rs)) {
            rs.close();
        }
        if (StringUtils.isEmpty(conn)) {
            conn.close();
        }
    }

}

JdbcTemplate

简介

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

pom

<!--JdbcTemplate-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

demo

package com.sjcredit.management.demo;

import com.sjcredit.management.demo.entity.BizAuthEntity;
import com.sjcredit.management.demo.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.*;

/**
 * @author dingyn
 * @description JdbcTemplateDemo
 * @create 2022-02-07 9:46
 */
@Repository
/**
 * 生成必须参数的构造方法
 */
@RequiredArgsConstructor
public class JdbcTemplateDemo {

    private final JdbcTemplate jdbcTemplate;

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * @Description: insert 对象
     * @Param: [bizAuthEntity]
     * @return: int
     * @Author: dingyn
     * @Date: 2022/2/7 9:47
     */
    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;
    }

    /**
     * @Description: insert 参数
     * @Param: [corpName, year, param]
     * @return: int
     * @Author: dingyn
     * @Date: 2022/2/7 14:19
     */
    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;

    }

    /**
     * @Description: delete 通过条件删除
     * @Param: [corpId]
     * @return: java.lang.Integer
     * @Author: dingyn
     * @Date: 2022/2/7 9:48
     */
    public Integer deleteBy(String corpId) {
        String sql = "DELETE from biz_auth where corp_id = ?";
        return jdbcTemplate.update(sql, corpId);
    }

    /**
     * @Description: update
     * @Param: [deadLine, id]
     * @return: int
     * @Author: dingyn
     * @Date: 2022/2/7 10:29
     */
    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;
    }

    /**
     * @Description: select 返回对象
     * @Param: [orgCode, corpName, corpCode]
     * @return: com.sjcredit.ccbsiapi.entity.BizAuthEntity
     * @Author: dingyn
     * @Date: 2022/2/7 9:48
     */
    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;
    }

    /**
     * @Description: select 返回字符串
     * @Param: [orgCode, corpName, corpCode]
     * @return: java.lang.String
     * @Author: dingyn
     * @Date: 2022/2/7 9:49
     */
    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);
    }

    /**
     * @Description: select 返回list
     * 查询list的，如果查询到0条数据，则不会报错，会返回一个包含0个元素的list对象，所以这里不需要捕获异常
     * @Param: [dwmc, tyshxydm]
     * @return: java.util.List<com.sjcredit.ccbsiapi.entity.SiPayinfoEntity>
     * @Author: dingyn
     * @Date: 2022/2/7 9:50
     */
    public List<SiPayinfoEntity> selectEntityList(String dwmc, String tyshxydm) {
        String sql = "SELECT * FROM si_payinfo WHERE dwmc = ? and tyshxydm = ?";
        List<SiPayinfoEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SiPayinfoEntity.class), dwmc, tyshxydm);

        return list;
    }

    public List<String> selectStringList(String ruleId) {

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

    /**
     * @Description: select 返回map list
     * @Param: [dwmc, tyshxydm]
     * @return: java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @Author: dingyn
     * @Date: 2022/2/7 10:29
     */
    public List<Map<String, Object>> selectMapList(String dwmc, String tyshxydm) {
        String sql = "SELECT * FROM si_latefeeinfo WHERE dwmc = ? and tyshxydm = ?";

        List<Map<String, Object>> listResult = jdbcTemplate.query(sql, (resultSet, i) -> {
            Map<String, Object> 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;
    }

    /**
     * @Description: select 返回Object list 传入多个参数或list
     * @Param: [dwmc, tyshxydm]
     * @return: java.util.List<java.lang.Object>
     * @Author: dingyn
     * @Date: 2022/2/7 15:28
     */
    public List<Object> selectObjectList() {
        String sql = "SELECT * FROM si_latefeeinfo WHERE dwmc = :dwmc and tyshxydm = :tyshxydm";

        List<String> ids = new ArrayList<>();
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("corp", "corp_name");
        paramMap.put("ids", ids);

        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

        List<Object> listResult = jdbc.query(sql, paramMap, (resultSet, i) -> {

            Map<String, String> map = new HashMap<>();
            map.put("corp", resultSet.getString("corp_name"));
            map.put("code", resultSet.getString("corp_code"));
            return map;
        });

        return listResult;
    }

    /**
     * @Description: execute
     * @Param: []
     * @return: void
     * @Author: dingyn
     * @Date: 2022/2/7 10:41
     */
    public void execute() {

        String sql = "commit";
        jdbcTemplate.execute(sql);
    }

}

JdbcTemplate基本使用

NamedParameterJdbcTemplate常用方法总结