概述

JdbcTemplate是Spring Data Jdbc 提供的一个操作数据库的便捷工具，可以帮我们方便地操作数据库，而不用直接使用jdbc,它主要提供以下五种类型的方法：

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句。
update、batchUpdate方法：用于执行新增、修改、删除等语句。
query方法及queryForXXX方法：用于执行查询相关的语句。
call方法：用于执行数据库存储过程和函数相关的语句。

当然,我们日常开发的项目里使用的是更强大的框架,像MyBatis、Hibernate或者Spring Data JPA，这里只是简单介绍一下JdbcTemplate的使用。

开始使用JdbcTemplate

在04节 Spring Boot学习：如何使用MyBatis框架的项目基础里,删除MyBatis的依赖,加入jdbc相关依赖

        <!-- jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

创建实体类SysAdminUser.java

package com.zhlab.demo.model;

import java.util.Date;

public class SysAdminUser {
    private Long adminUserId;

    private String userName;

    private String password;

    private String nickName;

    private Long deptId;

    private String phone;

    private String email;

    private String avatar;

    private Boolean status;

    private Boolean deletedFlag;

    private String loginIp;

    private Date loginTime;

    private Date createdAt;

    private Date updatedAt;

    private Date deletedAt;

    private Long createdBy;

    private Long updatedBy;

    private Long deletedBy;

    private String remark;

    //省略getter setter
}

创建包com.zhlab.demo.dao作为DAO层,并创建SysAdminUserRepository.java

package com.zhlab.demo.dao;

import com.zhlab.demo.model.SysAdminUser;
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;

/**
 * @ClassName SysAdminUserRepository
 * @Description //SysAdminUserRepository
 * @Author singleZhang
 * @Email 405780096@qq.com
 * @Date 2020/10/31 0031 下午 2:45
 **/
@Repository
public class SysAdminUserRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 新增用户
     */
    public void save(SysAdminUser user) {
        String sql = "insert into sys_admin_user(admin_user_id, user_name, password, nick_name, dept_id, phone, email) values(?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,
                user.getAdminUserId(),
                user.getUserName(),
                user.getPassword(),
                user.getNickName(),
                user.getDeptId(),
                user.getPhone(),
                user.getEmail());
    }

    /**
     * 删除用户
     */
    public void delete(Long id) {
        String sql = "delete from sys_admin_user where admin_user_id=?";
        jdbcTemplate.update(sql, id);
    }

    /**
     * 查询全部用户
     */
    public List<SysAdminUser> findAll() {
        String sql = "select * from sys_admin_user order by admin_user_id desc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SysAdminUser.class));
    }

}

在Service层,创建SysAdminUserService.java

package com.zhlab.demo.service;

import com.zhlab.demo.dao.SysAdminUserRepository;
import com.zhlab.demo.model.SysAdminUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @ClassName SysAdminUserService
 * @Description //SysAdminUserService 
 * @Author singleZhang
 * @Email 405780096@qq.com
 * @Date 2020/10/31 0031 上午 9:45
 **/
@Service
public class SysAdminUserService {

    @Autowired
    SysAdminUserRepository sysAdminUserRepository;

    /**
     * 新增用户
     * */
    public void addUser(SysAdminUser user){
        sysAdminUserRepository.save(user);
    }

    /**
     * 查询所有用户
     * */
    public List<SysAdminUser> findAll(){
        return sysAdminUserRepository.findAll();
    }

}

5.在接口层创建UserController.java

package com.zhlab.demo.controller;

import com.zhlab.demo.model.SysAdminUser;
import com.zhlab.demo.service.SysAdminUserService;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @ClassName UserController
 * @Description //用户接口层
 * @Author singleZhang
 * @Email 405780096@qq.com
 * @Date 2020/10/31 0031 上午 9:43
 **/
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    SysAdminUserService sysAdminUserService;

    /* 方法注解 */
    @ApiOperation(value = "方法名:用户列表", notes = "获取用户列表")
    @GetMapping("/list")
    public List<SysAdminUser> list(){
        List<SysAdminUser> list = sysAdminUserService.findAll();

        return list;
    }

    @ApiOperation(value = "方法名:新增用户", notes = "新增用户")
    @PostMapping("/add")
    public void add(@ApiParam(value = "参数:用户信息" , required=true ) @RequestParam SysAdminUser user){
        sysAdminUserService.addUser(user);
    }
}