学习：记录Spring-JDBC的简单使用

环境搭建:

在SpringBoot中使用Spring-JDBC。首先搭好SpringBoot，
1. pom.xml中的依赖

<dependencies>
		<!--springBoot的web启动器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>1.5.8.RELEASE</version>
        </dependency>
        <!--阿里巴巴druid依赖-->
        <dependency>
            <groupId>com.alibaba.druid</groupId>
            <artifactId>druid-wrapper</artifactId>
            <version>0.2.8</version>
        </dependency>
        <!--spring的orm框架 spring-jdbc-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.8.RELEASE</version>
        </dependency>
        <!--mysql数据驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <!--需要spring的事务相关依赖 spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>
    </dependencies>
    
	<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
        <relativePath/>
    </parent>

其中spring-boot-starter-parent，通过继承此父级maven项目来获得一些基本的配置

parent的作用

2. springBoot启动类(项目父包下)

@SpringBootApplication
public class SpringBootJDBCApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootJDBCApplication.class,args);
    }
}

3. 配置文件 采用 application.yml

server:
  port: 8090
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=GMT
    type: com.alibaba.druid.pool.DruidDataSource
    testOnBorrow: false
    testWhileIdle: true
    validationQuery: select 1

配置类 ConfigBean
用德鲁伊数据源替换默认的数据源

@Configuration
public class ConfigBean {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
}

---

代码编写:

1. dao层
dao的实现类，通过NamedParameterJdbcTemplate完成与数据库的交互。使用方法类似QueryRunner

@Repository
public class AccountDaoImpl implements AccountDao {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Override
    public int addAccount(Account account) {
        String sql = "insert into account(id,money) values(:id,:money)";
        Map<String,Object> map = new HashMap<>();
        map.put("id",account.getId());
        map.put("money",account.getMoney());
        return jdbcTemplate.update(sql,map);
    }
    @Override
    public int delete(Account account){
        String sql = "delete from account where id = :id";
        Map<String,Object> map = new HashMap<>();
        map.put("id",account.getId());
        return jdbcTemplate.update(sql,map);
    }
    @Override
    public int update(Account account){
        String sql = "update account set money = :money where id = :id";
        Map<String,Object> map = new HashMap<>();
        map.put("money",account.getMoney());
        map.put("id",account.getId());
        return jdbcTemplate.update(sql,map);
    }
    @Override
    public List<Account> findAccount() {
        String sql = "select * from account";
        return jdbcTemplate.query(sql,new AccountMapper());
    }
}

值得注意的是：
(1) 在执行查询语句时需要传入一个AccountMapper类型的对象，该类继承了org.springframework.jdbc.core.RowMapper<T> 接口，实现mapRow方法，在该方法中完成对象的orm映射，类似QueryRunner的反射机制实现映射
AccountMapper

public class AccountMapper implements RowMapper<Account> {

    @Override
    public Account mapRow(ResultSet resultSet, int i) throws SQLException {
        int id = resultSet.getInt("id");
        int money = resultSet.getInt("money");
        Account account = new Account();
        account.setId(id);
        account.setMoney(money);
        return account;
    }
}

(2)在Spring-JDBC中，为防止sql注入问题使用占位符，与QueryRunner的占位符 " ? " 不同，Spring-JDBC使用了语义化更强的占位符 " :xxx "

String sql = "insert into account(id,money) values(:id,:money)";

而后，通过传递一个Map<String,Object>集合的方式，将占位符与参数对应起来。

controller和service层不是重点，省略。

以上代码，经测试可用