SpringBoot学习（四）——jdbcTemplate连接MySQL

用JdbcTemplate连接MySQL一直是前台没法操作后台，今天再大佬的指点下终于可以完成了。环境是spring 2.2.5和MySQL 8.0,（之前报错的时候，用的是MySQL5.7一直不行，换了之后才成功的）

基本配置

POM文件的依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>springboot-test07</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-test07</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
            <scope>runtime</scope>
        </dependency>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.properties:

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot?useUnicode=true&characterEncoding=UTF-8

用MySQL8.0和Navicat完成本地数据库的新建

完成好基本配置后，真正的开始了

基本用法

1. 创建User Bean

public class User {
    private Long id;
    private String username;
    private String address;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

2. 创建UserService类，注入JdbcTemplate，并且完成增删改查的业务逻辑

@Service
public class UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    // 新增
    public int addUser(User user) {
        return jdbcTemplate.update("insert into user (username,address) values (?,?);", user.getUsername(), user.getAddress());
    }

    // 新增，主键回填
    public int addUser2(User user) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int r = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("insert into t_user (username,address) values (?,?);", Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getAddress());
                return ps;
            }
        }, keyHolder);
        user.setId(keyHolder.getKey().intValue());
        return r;
    }

    // 删除
    public int deleteUserById(Integer id) {
        return jdbcTemplate.update("delete from user where id=?", id);
    }

    // 修改
    public int updateUserById(User user) {
        return jdbcTemplate.update("update user set username=?,address=? where id=?", user.getUsername(), user.getAddress(), user.getId());
    }

    // 查询，使用 RowMapper 手动实现数据库字段和对象属性的映射
    public List<User> getAllUsers() {
        return jdbcTemplate.query("select * from user", new RowMapper<User>() {
            @Override            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String address = resultSet.getString("address");
                user.setId(id);
                user.setUsername(username);
                user.setAddress(address);
                return user;
            }
        });
    }

    // 查询，使用 BeanPropertyRowMapper 简单实现，前提是数据库字段和对象属性名称一致
    public List<User> getAllUsers2() {
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    }
}

3. 编写DoJdbcController

@RestController
public class DoJdbcController {
//    注入JdbcTemplate
    @Autowired
    JdbcTemplate jdbcTemplate;
//注入userservice
    @Autowired
    UserService userService;

    @GetMapping("/query")
    @ResponseBody
    public Map<String,Object> map(){
        List<Map<String,Object>> list = jdbcTemplate.queryForList("select * from user");
        return list.get(0);
    }

    @RequestMapping("/add")
    public Object add(String username,String address){
        User user = new User();
        user.setUsername(username);
        user.setAddress(address);
        int i = userService.addUser(user);
        return i;
    }

    @RequestMapping("/delete")
    public Object delete(Integer id){
//        User user = new User();
//        user.setId(id);
        int i = userService.deleteUserById(id);
        return i;

    }
}

4. 运行工程，浏览器访问http://localhost:8080/add?username=XXX&address=XXX