Spring Boot学习笔记（十一）SpringBoot 整合 jdbcTemplate（附带工程源码）

此篇参考了 SpringBoot整合jdbcTemplate特详细教程，对代码进行了改进，添加了注释

一、准备工作

这个是我的源码：优快云下载路径
百度网盘 提取码：fhpf
请大家注意，我安装了Lombok插件，若要运行我的工程，则也需要安装Lombok插件

1、搭建工程

为了方便学习，我们直接创建一个新的Spring Boot工程，勾选web的Spring web、Sql的Spring Data JDBC、Spring MySql Driver即可

此时可以点开Pom.xml，查看是否有以下依赖，没有的话可能是忘记勾选对应项目，在pom.xml中添加即可

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

在application.properties配置

下面的url 和uername，password需要根据个人进行修改（其中的jdbclearn是数据库的库）

# 服务器端口，如果不配置默认是8080端口
server.port=8096 

  # 数据库设置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jdbclearn?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456

我们添加Lombok插件，

在pom.xml中添加以下依赖

<dependency>    
    <groupId>org.projectlombok</groupId>    
    <artifactId>lombok</artifactId>
</dependency>

2、准备数据库

安装好mysql数据库后，打开workbench

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `ctm` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1', '张三', '18', '2019-01-24 09:07:41');
INSERT INTO `tb_user` VALUES ('2', '李四', '20', '2019-01-24 09:07:41');
INSERT INTO `tb_user` VALUES ('3', '王五', '19', '2019-01-24 09:07:41');

3、代码示例

1. 创建实体类

import lombok.Data;
import java.util.Date;

//@data，可以简单理解为包含@Getter、@Setter、@ToString注解，
// @Getter、@Setter、@ToString的作用就是自动生成getter、setter、toString方法。
@Data
public class UserEntity {
    private int id;
    private String username;
    private int age;
    private Date ctm;

    public UserEntity() {
    }

    public UserEntity(String username, int age) {
        this.username = username;
        this.age = age;
        this.ctm = new Date();
    }
}

2. 创建Dao层

import java.util.List;

public interface UserDao {
    UserEntity getUserById(Integer id);
    public List<UserEntity> getUserList();
    public int add (UserEntity userEntity);
    public int update(Integer id, UserEntity userEntity);
    public int delete(Integer id);
}

3. 创建Dao层的实现类

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.Date;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public UserEntity getUserById(Integer id) {
//        BeanPropertyRowMapper，用于将数据库查到的信息转换为Java对象
        List<UserEntity> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(UserEntity.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public List<UserEntity> getUserList() {
        List<UserEntity> list = jdbcTemplate.query("select * from tb_user" , new BeanPropertyRowMapper(UserEntity.class));
        if (list != null && list.size() > 0) {
            return list;
        } else {
            return null;
        }
    }


    @Override
    public int add(UserEntity user) {
        return jdbcTemplate.update("insert into tb_user(username, age, ctm) values(?, ?, ?)",
                user.getUsername(), user.getAge(), new Date());
    }

    @Override
    public int update(Integer id, UserEntity user) {
        return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",
                user.getUsername(), user.getAge(), id);
    }

    @Override
    public int delete(Integer id) {
        return jdbcTemplate.update("DELETE from tb_user where id = ? ", id);
    }
}

4. 创建Service接口

import java.util.List;

public interface UserService {
    UserEntity getUserById(Integer id);
    public List<UserEntity> getUserList();
    public int add(UserEntity user);
    public int update(Integer id, UserEntity user);
    public int delete(Integer id);
}

5. 创建Service实现

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public UserEntity getUserById(Integer id) {
        return userDao.getUserById(id);
    }

    @Override
    public List<UserEntity> getUserList() {
        return userDao.getUserList();
    }

    @Override
    public int add(UserEntity user) {
        return userDao.add(user);
    }

    @Override
    public int update(Integer id, UserEntity user) {
        return userDao.update(id, user);
    }

    @Override
    public int delete(Integer id) {
        return userDao.delete(id);
    }
}

6. 创建Controller层

@RestController
@RequestMapping("/api")
public class Controller {

    @Autowired
    private UserService userService;

    /**
     * 根据ID查询用户
     * @param id
     * @return
     */
    @RequestMapping(value = "user/{id}", method = RequestMethod.GET)
    public UserEntity getUserById (@PathVariable(value = "id") Integer id){
        UserEntity user = new UserEntity();
        try {
            user = userService.getUserById(id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 查询用户列表
     * @return
     */
    @RequestMapping(value = "users", method = RequestMethod.GET)
    public List<UserEntity> getUserList (){
        List<UserEntity> users = new ArrayList<>();
        try {
            users = userService.getUserList();

        } catch (Exception e) {

            e.printStackTrace();
        }
        return users;
    }
    /**
     * 添加用户
     * @param user
     * @return
     */
    @RequestMapping(value = "userAdd", method = RequestMethod.POST)
    public String add (@RequestBody UserEntity user){

        int orderId = 0;
        try {
            //添加操作返回的是受影响的行数
            orderId = userService.add(user);
            if(orderId==0){
                //添加失败
                return "添加失败";
            }else{
                return "添加成功";
            }
        } catch (Exception e) {
            e.printStackTrace();
            return "添加失败";
        }

    }

    /**
     * 根据id删除用户
     * @param id
     * @return
     */
    @RequestMapping(value = "userDelete/{id}", method = RequestMethod.DELETE)
    public String delete (@PathVariable(value = "id") Integer id){

        try {
            int ret =0;
            ret = userService.delete(id);
            if (ret <= 0) {
                return "删除失败";
            } else {
                return "删除成功";
            }
        } catch (Exception e) {
            e.printStackTrace();
            return "删除失败";
        }
    }

    /**
     * 根据id修改用户信息
     * @param user
     * @return
     */
    @RequestMapping(value = "userUpdate/{id}", method = RequestMethod.PUT)
    public String update (@PathVariable("id") Integer id, @RequestBody UserEntity user){
        try {
            int ret = 0;
            ret  = userService.update(id, user);
            if (ret <= 0) {
                return "修改失败";
            } else {
                return "修改成功";
            }
        } catch (Exception e) {
            e.printStackTrace();
            return "修改失败";
        }
    }
}

4、结果测试

我们使用postman，对接口进行测试。
因为Controller最外层的@RequestMapping("/api")，所以所有接口都会有/api的前缀

/user/{id}接口成功

users接口成功

userAdd接口成功

userDelete接口成功

userUpdate修改成功