Spring Boot - JdbcTemplate

爱被打了一巴掌 2023-10-12 18:10 61阅读 0赞

## 建立数据库springboot\_study ##

### 建表 ###

create table orm_user
    (
        id               int auto_increment comment '主键'
            primary key,
        name             varchar(32)                        not null comment '用户名',
        password         varchar(32)                        not null comment '加密后的密码',
        salt             varchar(32)                        not null comment '加密使用的盐',
        email            varchar(32)                        not null comment '邮箱',
        phone_number     varchar(15)                        not null comment '手机号码',
        status           int      default 1                 not null comment '状态，-1：逻辑删除，0：禁用，1：启用',
        create_time      datetime default CURRENT_TIMESTAMP not null comment '创建时间',
        last_login_time  datetime                           null comment '上次登录时间',
        last_update_time datetime default CURRENT_TIMESTAMP not null comment '上次更新时间',
        constraint email
            unique (email),
        constraint name
            unique (name),
        constraint phone_number
            unique (phone_number)
    )
        comment 'Spring Boot Demo Orm 系列示例表' charset = utf8mb3;

### 插入数据 ###

INSERT INTO springboot_study.orm_user (id, name, password, salt, email, phone_number, status, create_time, last_login_time, last_update_time) VALUES (1, '验清', 'c87d2206ae8084fbadedf886d10421a3', '27b9dedb54fe4a12b31117000f18e15a', 'o.lppna@ngqfcx.pw', '14875592383', 1, '2023-06-18 21:11:35', null, '2023-06-18 21:11:35');
    INSERT INTO springboot_study.orm_user (id, name, password, salt, email, phone_number, status, create_time, last_login_time, last_update_time) VALUES (2, '时队构', '5c21e5669bd812ed57e746f598948efa', '460d9696e998442fbf96d9341b87a6cf', 'v.vdnkcojs@txwf.bt', '19756901223', 1, '2023-06-18 21:11:49', null, '2023-06-18 21:11:49');
    INSERT INTO springboot_study.orm_user (id, name, password, salt, email, phone_number, status, create_time, last_login_time, last_update_time) VALUES (3, '因际难', 'fc0d14d576f8358add69bca6ff40a044', 'e20d63e5d32a4d3d9d56a2795acc5b39', 'f.too@xfjacrsqv.bo', '10166907167', 1, '2023-06-18 21:11:50', null, '2023-06-18 21:11:50');

## application.yml配置 ##

server:
      port: 8080
      servlet:
        context-path: /
    spring:
      datasource:
        url: jdbc:mysql://127.0.0.1:3306/springboot_study?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
        username: root
        password: 123321
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        initialization-mode: always
        continue-on-error: true
        schema:
        - "classpath:db/schema.sql"
        data:
        - "classpath:db/data.sql"
        hikari:
          minimum-idle: 5
          connection-test-query: SELECT 1 FROM DUAL
          maximum-pool-size: 20
          auto-commit: true
          idle-timeout: 30000
          pool-name: SpringBootDemoHikariCP
          max-lifetime: 60000
          connection-timeout: 30000
    logging:
      level:
        com.xkcoding: debug

## 创建实体类 ##

package com.lfsun.springbootormjdbctemplate.entity;
 
 
 import com.fasterxml.jackson.annotation.JsonFormat;
 import com.lfsun.springbootormjdbctemplate.annotation.Column;
 import com.lfsun.springbootormjdbctemplate.annotation.Pk;
 import com.lfsun.springbootormjdbctemplate.annotation.Table;
 import lombok.Data;
 
 import java.io.Serializable;
 import java.util.Date;
 
 /**
 * 
 * 用户实体类
 * 
 */
 @Data
 @Table(name = "orm_user")
 public class User implements Serializable {
 
 /**
 * 主键
 */
 @Pk
 private Long id;
 
 /**
 * 用户名
 */
 private String name;
 
 /**
 * 加密后的密码
 */
 private String password;
 
 /**
 * 加密使用的盐
 */
 private String salt;
 
 /**
 * 邮箱
 */
 private String email;
 
 /**
 * 手机号码
 */
 @Column(name = "phone_number")
 private String phoneNumber;
 
 /**
 * 状态，-1：逻辑删除，0：禁用，1：启用
 */
 private Integer status;
 
 /**
 * 创建时间
 */
 @Column(name = "create_time")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 private Date createTime;
 
 /**
 * 上次登录时间
 */
 @Column(name = "last_login_time")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 private Date lastLoginTime;
 
 /**
 * 上次更新时间
 */
 @Column(name = "last_update_time")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 private Date lastUpdateTime;
 }

## 涉及到的注解 ##

package com.lfsun.springbootormjdbctemplate.annotation;
 
 import java.lang.annotation.ElementType;
 import java.lang.annotation.Retention;
 import java.lang.annotation.RetentionPolicy;
 import java.lang.annotation.Target;
 
 /**
 * 
 * 列注解
 * 
 */
 @Retention(RetentionPolicy.RUNTIME)
 @Target({
 ElementType.FIELD})
 public @interface Column {
 
 /**
 * 列名
 *
 * @return 列名
 */
 String name();
 }

package com.lfsun.springbootormjdbctemplate.annotation;
 
 import java.lang.annotation.ElementType;
 import java.lang.annotation.Retention;
 import java.lang.annotation.RetentionPolicy;
 import java.lang.annotation.Target;
 
 /**
 * 
 * 需要忽略的字段
 * 
 */
 @Retention(RetentionPolicy.RUNTIME)
 @Target({
 ElementType.FIELD})
 public @interface Ignore {
 
 }

package com.lfsun.springbootormjdbctemplate.annotation;
 
 import java.lang.annotation.ElementType;
 import java.lang.annotation.Retention;
 import java.lang.annotation.RetentionPolicy;
 import java.lang.annotation.Target;
 
 /**
 * 
 * 主键注解
 * 
 */
 @Retention(RetentionPolicy.RUNTIME)
 @Target({
 ElementType.FIELD})
 public @interface Pk {
 
 /**
 * 自增
 *
 * @return 自增主键
 */
 boolean auto() default true;
 }

package com.lfsun.springbootormjdbctemplate.annotation;
 
 import java.lang.annotation.ElementType;
 import java.lang.annotation.Retention;
 import java.lang.annotation.RetentionPolicy;
 import java.lang.annotation.Target;
 
 /**
 * 
 * 表注解
 * 
 */
 @Retention(RetentionPolicy.RUNTIME)
 @Target({
 ElementType.TYPE})
 public @interface Table {
 
 /**
 * 表名
 *
 * @return 表名
 */
 String name();
 }

## 常量池 ##

package com.lfsun.springbootormjdbctemplate.constant;
 
 /**
 * 
 * 常量池
 * 
 */
 public interface Const {
 
 /**
 * 加密盐前缀
 */
 String SALT_PREFIX = "::lfsun666::";
 
 /**
 * 逗号分隔符
 */
 String SEPARATOR_COMMA = ",";
 }

## 操作基类 ##

package com.lfsun.springbootormjdbctemplate.dao.base;
 
 import cn.hutool.core.bean.BeanUtil;
 import cn.hutool.core.collection.CollUtil;
 import cn.hutool.core.lang.Dict;
 import cn.hutool.core.util.ArrayUtil;
 import cn.hutool.core.util.ObjectUtil;
 import cn.hutool.core.util.ReflectUtil;
 import cn.hutool.core.util.StrUtil;
 import cn.hutool.json.JSONUtil;
 import com.lfsun.springbootormjdbctemplate.annotation.Column;
 import com.lfsun.springbootormjdbctemplate.annotation.Ignore;
 import com.lfsun.springbootormjdbctemplate.annotation.Pk;
 import com.lfsun.springbootormjdbctemplate.annotation.Table;
 import com.lfsun.springbootormjdbctemplate.constant.Const;
 import lombok.extern.slf4j.Slf4j;
 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;
 
 import java.lang.reflect.Field;
 import java.lang.reflect.ParameterizedType;
 import java.util.List;
 import java.util.Map;
 import java.util.stream.Collectors;
 import java.util.stream.Stream;
 
 /**
 * 
 * Dao基类
 * 
 */
 @Slf4j
 public class BaseDao<T, P> {
 
 private JdbcTemplate jdbcTemplate;
 private Class<T> clazz;
 
 @SuppressWarnings(value = "unchecked")
 public BaseDao(JdbcTemplate jdbcTemplate) {
 
 this.jdbcTemplate = jdbcTemplate;
 clazz = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
 }
 
 /**
 * 通用插入，自增列需要添加 {
 @link Pk} 注解
 *
 * @param t 对象
 * @param ignoreNull 是否忽略 null 值
 * @return 操作的行数
 */
 protected Integer insert(T t, Boolean ignoreNull) {
 
 String table = getTableName(t);
 
 List<Field> filterField = getField(t, ignoreNull);
 
 List<String> columnList = getColumns(filterField);
 
 String columns = StrUtil.join(Const.SEPARATOR_COMMA, columnList);
 
 // 构造占位符
 String params = StrUtil.repeatAndJoin("?", columnList.size(), Const.SEPARATOR_COMMA);
 
 // 构造值
 Object[] values = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).toArray();
 
 String sql = StrUtil.format("INSERT INTO {table} ({columns}) VALUES ({params})", Dict.create().set("table", table).set("columns", columns).set("params", params));
 log.debug("【执行SQL】SQL：{}", sql);
 log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
 return jdbcTemplate.update(sql, values);
 }
 
 /**
 * 通用根据主键删除
 *
 * @param pk 主键
 * @return 影响行数
 */
 protected Integer deleteById(P pk) {
 
 String tableName = getTableName();
 String sql = StrUtil.format("DELETE FROM {table} where id = ?", Dict.create().set("table", tableName));
 log.debug("【执行SQL】SQL：{}", sql);
 log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
 return jdbcTemplate.update(sql, pk);
 }
 
 /**
 * 通用根据主键更新，自增列需要添加 {
 @link Pk} 注解
 *
 * @param t 对象
 * @param pk 主键
 * @param ignoreNull 是否忽略 null 值
 * @return 操作的行数
 */
 protected Integer updateById(T t, P pk, Boolean ignoreNull) {
 
 String tableName = getTableName(t);
 
 List<Field> filterField = getField(t, ignoreNull);
 
 List<String> columnList = getColumns(filterField);
 
 List<String> columns = columnList.stream().map(s -> StrUtil.appendIfMissing(s, " = ?")).collect(Collectors.toList());
 String params = StrUtil.join(Const.SEPARATOR_COMMA, columns);
 
 // 构造值
 List<Object> valueList = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).collect(Collectors.toList());
 valueList.add(pk);
 
 Object[] values = ArrayUtil.toArray(valueList, Object.class);
 
 String sql = StrUtil.format("UPDATE {table} SET {params} where id = ?", Dict.create().set("table", tableName).set("params", params));
 log.debug("【执行SQL】SQL：{}", sql);
 log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
 return jdbcTemplate.update(sql, values);
 }
 
 /**
 * 通用根据主键查询单条记录
 *
 * @param pk 主键
 * @return 单条记录
 */
 public T findOneById(P pk) {
 
 String tableName = getTableName();
 String sql = StrUtil.format("SELECT * FROM {table} where id = ?", Dict.create().set("table", tableName));
 RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(clazz);
 log.debug("【执行SQL】SQL：{}", sql);
 log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(pk));
 return jdbcTemplate.queryForObject(sql, new Object[]{
 pk}, rowMapper);
 }
 
 /**
 * 根据对象查询
 *
 * @param t 查询条件
 * @return 对象列表
 */
 public List<T> findByExample(T t) {
 
 String tableName = getTableName(t);
 List<Field> filterField = getField(t, true);
 List<String> columnList = getColumns(filterField);
 
 List<String> columns = columnList.stream().map(s -> " and " + s + " = ? ").collect(Collectors.toList());
 
 String where = StrUtil.join(" ", columns);
 // 构造值
 Object[] values = filterField.stream().map(field -> ReflectUtil.getFieldValue(t, field)).toArray();
 
 String sql = StrUtil.format("SELECT * FROM {table} where 1=1 {where}", Dict.create().set("table", tableName).set("where", StrUtil.isBlank(where) ? "" : where));
 log.debug("【执行SQL】SQL：{}", sql);
 log.debug("【执行SQL】参数：{}", JSONUtil.toJsonStr(values));
 List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, values);
 List<T> ret = CollUtil.newArrayList();
 maps.forEach(map -> ret.add(BeanUtil.fillBeanWithMap(map, ReflectUtil.newInstance(clazz), true, false)));
 return ret;
 }
 
 /**
 * 获取表名
 *
 * @param t 对象
 * @return 表名
 */
 private String getTableName(T t) {
 
 Table tableAnnotation = t.getClass().getAnnotation(Table.class);
 if (ObjectUtil.isNotNull(tableAnnotation)) {
 
 return StrUtil.format("`{}`", tableAnnotation.name());
 } else {
 
 return StrUtil.format("`{}`", t.getClass().getName().toLowerCase());
 }
 }
 
 /**
 * 获取表名
 *
 * @return 表名
 */
 private String getTableName() {
 
 Table tableAnnotation = clazz.getAnnotation(Table.class);
 if (ObjectUtil.isNotNull(tableAnnotation)) {
 
 return StrUtil.format("`{}`", tableAnnotation.name());
 } else {
 
 return StrUtil.format("`{}`", clazz.getName().toLowerCase());
 }
 }
 
 /**
 * 获取列
 *
 * @param fieldList 字段列表
 * @return 列信息列表
 */
 private List<String> getColumns(List<Field> fieldList) {
 
 // 构造列
 List<String> columnList = CollUtil.newArrayList();
 for (Field field : fieldList) {
 
 Column columnAnnotation = field.getAnnotation(Column.class);
 String columnName;
 // 处理 这种 phoneNumber --> phone_number
 // @Column(name = "phone_number")
 // private String phoneNumber;
 if (ObjectUtil.isNotNull(columnAnnotation)) {
 
 columnName = columnAnnotation.name();
 } else {
 
 columnName = field.getName();
 }
 columnList.add(StrUtil.format("`{}`", columnName));
 }
 return columnList;
 }
 
 /**
 * 获取字段列表 {
 @code 过滤数据库中不存在的字段，以及自增列}
 *
 * @param t 对象
 * @param ignoreNull 是否忽略空值
 * @return 字段列表
 */
 private List<Field> getField(T t, Boolean ignoreNull) {
 
 // 获取所有字段，包含父类中的字段
 Field[] fields = ReflectUtil.getFields(t.getClass());
 
 // 过滤数据库中不存在的字段，以及自增列
 // 也就是说只保留那些字段对象中没有Ignore注解或Pk注解的元素。
 List<Field> filterField;
 Stream<Field> fieldStream = CollUtil.toList(fields).stream().filter(field -> ObjectUtil.isNull(field.getAnnotation(Ignore.class)) || ObjectUtil.isNull(field.getAnnotation(Pk.class)));
 
 // 是否过滤字段值为null的字段
 if (ignoreNull) {
 
 filterField = fieldStream.filter(field -> ObjectUtil.isNotNull(ReflectUtil.getFieldValue(t, field))).collect(Collectors.toList());
 } else {
 
 filterField = fieldStream.collect(Collectors.toList());
 }
 return filterField;
 }
 
 }

## 实际操作dao ##

package com.lfsun.springbootormjdbctemplate.dao;
 
 
 import com.lfsun.springbootormjdbctemplate.dao.base.BaseDao;
 import com.lfsun.springbootormjdbctemplate.entity.User;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.stereotype.Repository;
 
 import java.util.List;
 
 /**
 * 
 * User Dao
 * 
 */
 @Repository
 public class UserDao extends BaseDao<User, Long> {
 
 
 @Autowired
 public UserDao(JdbcTemplate jdbcTemplate) {
 
 super(jdbcTemplate);
 }
 
 /**
 * 保存用户
 *
 * @param user 用户对象
 * @return 操作影响行数
 */
 public Integer insert(User user) {
 
 return super.insert(user, true);
 }
 
 /**
 * 根据主键删除用户
 *
 * @param id 主键id
 * @return 操作影响行数
 */
 public Integer delete(Long id) {
 
 return super.deleteById(id);
 }
 
 /**
 * 更新用户
 *
 * @param user 用户对象
 * @param id 主键id
 * @return 操作影响行数
 */
 public Integer update(User user, Long id) {
 
 return super.updateById(user, id, true);
 }
 
 /**
 * 根据主键获取用户
 *
 * @param id 主键id
 * @return id对应的用户
 */
 public User selectById(Long id) {
 
 return super.findOneById(id);
 }
 
 /**
 * 根据查询条件获取用户列表
 *
 * @param user 用户查询条件
 * @return 用户列表
 */
 public List<User> selectUserList(User user) {
 
 return super.findByExample(user);
 }
 }

## 实现类（实现接口略，可文尾查看完整代码） ##

package com.lfsun.springbootormjdbctemplate.service.impl;
 
 import cn.hutool.core.bean.BeanUtil;
 import cn.hutool.core.bean.copier.CopyOptions;
 import cn.hutool.core.date.DateTime;
 import cn.hutool.core.util.IdUtil;
 import cn.hutool.core.util.StrUtil;
 import cn.hutool.crypto.SecureUtil;
 import com.lfsun.springbootormjdbctemplate.constant.Const;
 import com.lfsun.springbootormjdbctemplate.dao.UserDao;
 import com.lfsun.springbootormjdbctemplate.entity.User;
 import com.lfsun.springbootormjdbctemplate.service.IUserService;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
 import java.util.List;
 
 /**
 * 
 * User Service Implement
 * 
 */
 @Service
 public class UserServiceImpl implements IUserService {
 
 private final UserDao userDao;
 
 @Autowired
 public UserServiceImpl(UserDao userDao) {
 
 this.userDao = userDao;
 }
 
 /**
 * 保存用户
 *
 * @param user 用户实体
 * @return 保存成功 {
 @code true} 保存失败 {
 @code false}
 */
 @Override
 public Boolean save(User user) {
 
 String rawPass = user.getPassword();
 String salt = IdUtil.simpleUUID();
 String pass = SecureUtil.md5(rawPass + Const.SALT_PREFIX + salt);
 user.setPassword(pass);
 user.setSalt(salt);
 return userDao.insert(user) > 0;
 }
 
 /**
 * 删除用户
 *
 * @param id 主键id
 * @return 删除成功 {
 @code true} 删除失败 {
 @code false}
 */
 @Override
 public Boolean delete(Long id) {
 
 return userDao.delete(id) > 0;
 }
 
 /**
 * 更新用户
 *
 * @param user 用户实体
 * @param id 主键id
 * @return 更新成功 {
 @code true} 更新失败 {
 @code false}
 */
 @Override
 public Boolean update(User user, Long id) {
 
 User exist = getUser(id);
 if (StrUtil.isNotBlank(user.getPassword())) {
 
 String rawPass = user.getPassword();
 String salt = IdUtil.simpleUUID();
 String pass = SecureUtil.md5(rawPass + Const.SALT_PREFIX + salt);
 user.setPassword(pass);
 user.setSalt(salt);
 }
 BeanUtil.copyProperties(user, exist, CopyOptions.create().setIgnoreNullValue(true));
 exist.setLastUpdateTime(new DateTime());
 return userDao.update(exist, id) > 0;
 }
 
 /**
 * 获取单个用户
 *
 * @param id 主键id
 * @return 单个用户对象
 */
 @Override
 public User getUser(Long id) {
 
 return userDao.findOneById(id);
 }
 
 /**
 * 获取用户列表
 *
 * @param user 用户实体
 * @return 用户列表
 */
 @Override
 public List<User> getUser(User user) {
 
 return userDao.findByExample(user);
 }
 }

## controller ##

package com.lfsun.springbootormjdbctemplate.controller;
 
 import cn.hutool.core.lang.Dict;
 import com.lfsun.springbootormjdbctemplate.entity.User;
 import com.lfsun.springbootormjdbctemplate.service.IUserService;
 import lombok.extern.slf4j.Slf4j;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.*;
 
 import java.util.List;
 
 /**
 * 
 * User Controller
 * 
 */
 @RestController
 @Slf4j
 public class UserController {
 
 private final IUserService userService;
 
 @Autowired
 public UserController(IUserService userService) {
 
 this.userService = userService;
 }
 
 @PostMapping("/user")
 public Dict save(@RequestBody User user) {
 
 Boolean save = userService.save(user);
 return Dict.create().set("code", save ? 200 : 500).set("msg", save ? "成功" : "失败").set("data", save ? user : null);
 }
 
 @DeleteMapping("/user/{id}")
 public Dict delete(@PathVariable Long id) {
 
 Boolean delete = userService.delete(id);
 return Dict.create().set("code", delete ? 200 : 500).set("msg", delete ? "成功" : "失败");
 }
 
 @PutMapping("/user/{id}")
 public Dict update(@RequestBody User user, @PathVariable Long id) {
 
 Boolean update = userService.update(user, id);
 return Dict.create().set("code", update ? 200 : 500).set("msg", update ? "成功" : "失败").set("data", update ? user : null);
 }
 
 @GetMapping("/user/{id}")
 public Dict getUser(@PathVariable Long id) {
 
 User user = userService.getUser(id);
 return Dict.create().set("code", 200).set("msg", "成功").set("data", user);
 }
 
 @GetMapping("/user")
 public Dict getUser(User user) {
 
 List<User> userList = userService.getUser(user);
 return Dict.create().set("code", 200).set("msg", "成功").set("data", userList);
 }
 }

## 完整代码地址 ##

[Spring Boot 项目集成 JdbcTemplate][Spring Boot _ JdbcTemplate]

[Spring Boot _ JdbcTemplate]: https://gitee.com/lfsun666/springboot-study-2023/tree/master/spring-boot-orm-jdbctemplate