Spring Boot JDBC 连接数据库

接下来将对在Spring Boot构建的Web应用中，基于MYSQL数据库的几种数据库连接方式进行介绍，本节说下jdbc方式

创建表

配置相关文件

配置maven依赖

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

在application.properties文件配置mysql的驱动类，数据库地址，数据库账号、密码信息。

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1/test?characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=9958

通过引入这些依赖和配置一些基本信息，springboot就可以访问数据库类。

具体编码

实体类

public class User {

    int id;

    String name;

    String sex;

    ...省略了getter. setter

}

dao层

public interface IUserDao {
    void addUser(String name, String sex);
    User getUser(Integer id);
    void updateUser(int id, String name);
    void deleteUserById(Integer id);
}

@Component
public class UserDaoImpl implements IUserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void addUser(String name, String sex) {
        jdbcTemplate.update("INSERT INTO `user`(`name`,sex) VALUES (?,?)", name, sex);
    }
    @Override
    public User getUser(Integer id) {
        List<User> userList = jdbcTemplate.query("select * FROM first_db.user WHERE id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
        if (userList != null && userList.size() > 0) {
            User user = userList.get(0);
            return user;
        }
        return null;
    }
    @Override
    public void updateUser(int id, String name) {
        jdbcTemplate.update("UPDATE user SET `name` = ? WHERE id = ?", name, id);
    }
    @Override
    public void deleteUserById(Integer id) {
        jdbcTemplate.update("DELETE FROM `user` WHERE id = ?", id);
    }
}

service层

public interface IUserService {
    void addUser(String name, String sex);
    User getUser(Integer id);
    void updateUser(int id, String name);
    void deleteUserById(Integer id);
}

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void addUser(String name,String sex) {
        jdbcTemplate.update("INSERT INTO `user`(`name`,sex) VALUES (?,?)",name,sex);
    }
    public User getUser(Integer id) {
        List<User> userList = jdbcTemplate.query("select * FROM first_db.user WHERE id = ?",new Object[]{id},new BeanPropertyRowMapper(User.class));
        if(userList != null && userList.size() > 0){
            User user = userList.get(0);
            return user;
        }else {
            return null;
        }
    }
    public void updateUser(int id, String name) {
        jdbcTemplate.update("UPDATE user SET `name` = ? WHERE id = ?",name,id);
    }
    public void deleteUserById(Integer id) {
        jdbcTemplate.update("DELETE FROM `user` WHERE id = ?",id);
    }
}

controller类

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping(value = "/{id}")
    public User getUser(@PathVariable("id") Integer id) {
        return userService.getUser(id);
    }
    @PostMapping(value = "/add")
    public void addUser(@RequestParam("name") String name, @RequestParam("sex") String sex) {
        userService.addUser(name, sex);
    }
    @PutMapping(value = "/update/{id}")
    public void updateUser(@PathVariable("id") Integer id, @RequestParam("name") String name) {
        userService.updateUser(id, name);
    }
    @DeleteMapping(value = "/delete/{id}")
    public void deleteUser(@PathVariable("id") Integer id) {
        userService.deleteUserById(id);
    }
}

所有功能都已经通过postman来测试，小伙伴也可以尝试下，没有任何问题。