小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

一、基础知识

NamedParameterJdbcTemplate对JdbcTemplate进行了封装，用":parameterName"代替了"?"进行占位，提高了代码的可读性。当使用NamedParameterJdbcTemplate时，sql往往是这个样子： select count(*) from T_ACTOR where first_name = :first_name，有三种方式将参数first_name传入：

1. MapSqlParameterSource

 MapSqlParameterSource source = new MapSqlParameterSource();
  source.addValue("first_name", "xxx");

2. Map-based style

Map<String, String> namedParameters = Collections.singletonMap("first_name", "xxx");

3. BeanPropertySqlParameterSource

// exampleActor是一个JavaBean，它拥有属性first_name
    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

二、代码实践

1. 导入依赖

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>

2. 创建DataSource和NamedParameterJdbcTemplate

MySQL连接配置：

uphand:
  datasource:
    jdbc-url: jdbc:mysql://localhost:3306/uphand?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 511129

使用@ConfigurationProperties注解来获得连接配置

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import javax.sql.DataSource;

@Configuration
public class DbConfiguration {

    @Bean("dataSource")
    @ConfigurationProperties(prefix = "uphand.datasource")
    DataSource uphandDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean("template")
    NamedParameterJdbcTemplate uphandTemplate(@Qualifier(value = "dataSource") DataSource dataSource){
        return new NamedParameterJdbcTemplate(dataSource);
    }

}

3. 使用NamedParameterJdbcTemplate

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

public class BaseStorage {
    @Autowired
    @Qualifier("template")
    protected NamedParameterJdbcTemplate dbClient;
}

import com.junex.xiuadmin.data.Colleagemate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.List;

@Repository
public class ColleagemateStorageStorage extends BaseStorage implements ColleagemateStorage {


@Override
public boolean insert(Colleagemate colleagemate) {
    String sql = "insert into `colleagemate`(nickName, joinTime, realName, phone, workPlace, isBus, isJoin) " +
            "value(:nickName, :joinTime, :realName, :phone, :workPlace, :isBus, :isJoin)";
    MapSqlParameterSource source = new MapSqlParameterSource();
    source.addValue("nickName", colleagemate.getNickName());
    source.addValue("time", colleagemate.getTime());
    source.addValue("realName", colleagemate.getRealName());
    source.addValue("phone", colleagemate.getPhone());
    source.addValue("birthday", colleagemate.getBirthday());
    source.addValue("year", colleagemate.getYear());
    source.addValue("gender", colleagemate.getGender());

    return dbClient.update(sql, source) > 0;
}


@Override
    public Colleagemate queryByRealName(String realName) {
        String sql = "select * from colleagemate where realName = :realName";
        MapSqlParameterSource source = new MapSqlParameterSource("realName", realName);
        return dbClient.queryForObject(sql, source, ROW_MAPPER);
    }

    @Override
    public List<Colleagemate> queryAll() {
        String sql = "select * from colleagemate";
        return dbClient.query(sql, ROW_MAPPER);
    }

    private static final RowMapper<Colleagemate> ROW_MAPPER = ((rs, rowNum) -> {
        Colleagemate mate = new Colleagemate();
        ……
        ……
        ……
        return mate;
    });
}