Springboot整合jdbc

1、创建Springboot项目

创建Springboot项目，添加以下依赖：
Spring Web
Spring Data JDBC
MySQL Driver

如下图示：

springboot-06.png

对应pom.xml依赖配置为：

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

2、使用JdbcTemplate执行增删查改

jdbc测试接口类SpringbootJdbcController.java代码如下：

/**
 * @author whw
 * @Description jdbc测试接口类
 * @createTime 2021/9/28 12:57
 */
@RestController
@RequestMapping("/employee")
public class SpringbootJdbcController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping("/list")
    public List<Map<String, Object>> list() {
        String sql = "select * from employee";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @RequestMapping("/insert")
    public String insert(Employee employee) {
        String sql = "insert into employee(emp_no, emp_name, gender, age) values (%s, '%s', '%s', '%s') ";
        sql = String.format(sql, employee.getEmpNo(), employee.getEmpName(), employee.getGender(), employee.getAge());
        jdbcTemplate.execute(sql);
        return "insert success!!!";
    }

    @RequestMapping("/update")
    public String update(Employee employee) {
        String sql = "update employee set emp_name=?, gender=?, age=? where emp_no = ? ";
        jdbcTemplate.update(sql, employee.getEmpName(), employee.getGender(), employee.getAge(), employee.getEmpNo());
        return "update success!!!";
    }

    @RequestMapping("/delete")
    public String delete(Employee employee) {
        String sql = "delete from employee where emp_no = " + employee.getEmpNo();
        jdbcTemplate.execute(sql);
        return "delete success!!!";
    }
}

注意：此接口类仅用于实现JdbcTemplate基本增删查改功能，暂不考虑SQL注入等问题。

3、Jdbc接口测试

启动Springboot应用，访问jdbc接口，测试增删查改功能。

扩展说明：

指定druid数据源

Springboot默认数据源：
com.zaxxer.hikari.HikariDataSource

可在配置中指定spring.datasource.type数据源为druid数据源：
spring.datasource.type= com.alibaba.druid.pool.DruidDataSource

新增druid数据源依赖：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

（完）