10-SpringBoot之数据库(一)——JDBC

1. 添加pom依赖

<!--数据库连接jdbc依赖-->
<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 <!--mysql链接依赖-->
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
 </dependency>

2. 配置数据源

在application.yml文件中spring节点下配置数据源，如下：

datasource:
   url: jdbc:mysql://localhost:3306/springboot?characterEncoding=UTF-8&serverTimezone=GMT
   username: root
   password: 123456
   driver-class-name: com.mysql.cj.jdbc.Driver

3. 创建数据库表

创建一个用户表（user），建表语句如下：

CREATE TABLE `user` (
  `id` varchar(32) NOT NULL COMMENT '用户ID',
  `name` varchar(30) DEFAULT NULL COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  `birthday` date NOT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4. Model

User.java

import com.alibaba.fastjson.annotation.JSONField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private String id;
    private String name;
    private Integer age;

    @JSONField(format="yyyy-MM-dd")
    private Date birthday;

    public User(String id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }
}

5. Dao层

5.1 UserDao.java

import com.springboot.web.model.User;

import java.util.List;

public interface UserDao {
    List<User> getAllUserList();
    void createUser(User user);
    User getUser(String id);
    void updateUser(User user);
    void deleteUserByUserId(String id);
}

5.2 UserDaoImpl.java

import com.springboot.web.dao.UserDao;
import com.springboot.web.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void createUser(User user) {
        jdbcTemplate.update("INSERT INTO user(id,name,age,birthday) VALUES (?,?,?,?)",
                user.getId(),user.getName(),user.getAge(),user.getBirthday());
    }

    @Override
    public List<User> getAllUserList() {
        return jdbcTemplate.query("select id,name,age,birthday FROM user",new BeanPropertyRowMapper(User.class));
    }

   @Override
    public User getUser(String id) {
        List<User> userList = jdbcTemplate.query("select id,name,age,birthday FROM 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;
        }
    }

    @Override
    public void updateUser(User user) {
        jdbcTemplate.update("UPDATE user SET name = ?, age =?, birthday =? WHERE id = ?",user.getName(),user.getAge(),user.getBirthday(),user.getId());
    }

    @Override
    public void deleteUserByUserId(String id) {
        jdbcTemplate.update("DELETE FROM user WHERE id = ?",id);
    }

}

6. Service层

6.1 UserService.java

import com.springboot.web.model.User;

import java.util.List;

public interface UserService {
    List<User> getAllUserList();
    void createUser(User user);
    User getUser(String id);
    void updateUser(User user);
    void deleteUserByUserId(String id);
}

6.2 UserServiceImpl.java

import com.springboot.web.dao.UserDao;
import com.springboot.web.model.User;
import com.springboot.web.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserDao userDao;

    @Override
    public void createUser(User user){
        userDao.createUser(user);
    }

    @Override
    public List<User> getAllUserList() {
        return userDao.getAllUserList();
    }

    @Override
    public User getUser(String id) {
        return userDao.getUser(id);
    }

    @Override
    public void updateUser(User user) {
        userDao.updateUser(user);
    }

    @Override
    public void deleteUserByUserId(String id) {
        userDao.deleteUserByUserId(id);
    }
}

7. 控制层

UserController.java

import com.springboot.web.model.User;
import com.springboot.web.service.UserService;
import com.springboot.web.utils.UUIDUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Date;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;


    @GetMapping("/getAllUserList")
    public Object getAllUserList() {
       return userService.getAllUserList();
    }

    @PostMapping("/createUser")
    public void createUser(@RequestParam("name") String name, @RequestParam("age") Integer age) {
        User user = new User(UUIDUtil.getUUID(), name, age, new Date());
        userService.createUser(user);
    }

    @PutMapping("/updateUser/{id}")
    public void updateUser(@PathVariable("id") String id, @RequestParam("name") String name,
                           @RequestParam("age") Integer age){
        User user = new User(id, name, age, new Date(2018,1,8));
        userService.updateUser(user);
    }

    @GetMapping("/getUser/{id}")
    public User getUser(@PathVariable("id")  String id){
        return userService.getUser(id);
    }

    @DeleteMapping("/deleteUserByUserId/{id}")
    public void deleteUserByUserId(@PathVariable("id")  String id){
        userService.deleteUserByUserId(id);
    }
    
}

8. 测试

使用Postman工具进行测试。

createUser

getAllUserList

getUser

updateUser

deleteUserByUserId

9. 源码下载

源码下载地址：https://download.csdn.net/download/huangjun0210/10778196