springboot引入jdbcTemplate

1、pom

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

知识兔

2、数据库配置

spring 
  datasource:
  	driver-class-name: com.mysql.jdbc.Driver
  	url: jdbc:mysql://localhost:3306/demo_test?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8
  	password: root
  	username: root

知识兔

3、使用

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Parent> testJdbcTemplate(Integer id) {

        // 不用参数,返回list实体类
        String sql = " select * from parent ";
        List<Parent> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Parent.class));

        // 有两个参数 (方法1)
        String sqlA = "select * from parent where relation = ? and child = ? ";
        Object[] params = {"父","1"};
        List<Parent> listA = jdbcTemplate.query(sqlA,params, new BeanPropertyRowMapper<>(Parent.class));

        // 有两个参数 (方法2)
        List<Parent> listB = jdbcTemplate.query(sqlA,new BeanPropertyRowMapper<>(Parent.class),"父","1");

        // 有两个参数 (方法3)
        ArrayList<String> paramList = new ArrayList<>();
        paramList.add("父");
        paramList.add("1");  
        Object[] args = paramList.toArray();    // list转数组
        List<Parent> listC = jdbcTemplate.query(sqlA, args,new BeanPropertyRowMapper<>(Parent.class));

        // 返回map
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sqlA, params);

        // 返回一个值
        String sqlC = " select count(*) abc from (select * from parent where relation = ? and child =?) c ";
        Integer integer = jdbcTemplate.queryForObject(sqlC, params, Integer.class);

        // 返回list<Map>, 只用到其中一个字段
        String sqlD = "select id,name from parent where relation = ? ";
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sqlD,"父");
        // 先过滤空,再提取字段name,转换成String
        List<String> name1 = list1.stream().filter(o -> !ObjectUtils.isEmpty(o.get("name")))
                                .map(o -> (String)o.get("name")).collect(Collectors.toList());

        return null;
    }

知识兔