功能实现 —— 数据库管理（Hutool-db - 简单使用增删改查）

Hutool - Db
基础学习：
--- 官方文档
--- 个人整理文档
源代码：Wset - Gitee.com

搭建环境

本文以mariadb作为测试数据库
测试api工具：postman
数据库和api测试工具可自行选择

1. 引入依赖

        <!--引入Hutool依赖-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.2</version>
        </dependency>
        <!--引入mariadb驱动依赖-->
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.1.2</version>
        </dependency>

2. mariadb数据库创建user和article测试表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'san.zhang', 'man', 22);
INSERT INTO `user` VALUES (2, '李四', '女', 18);

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `authId` int(11) NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `authIId`(`authId`) USING BTREE,
  CONSTRAINT `authIId` FOREIGN KEY (`authId`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES (1, 'hello world', 1, 'say hi');
INSERT INTO `article` VALUES (2, '你好', 2, 'hello');

SET FOREIGN_KEY_CHECKS = 1;

3. 设置db.setting

## 基本配置信息
# JDBC URL，根据不同的数据库，使用相应的JDBC连接字符串
url = jdbc:mariadb://192.168.205.131:3306/test
# 用户名，此处也可以使用 user 代替
username = root
# 密码，此处也可以使用 pass 代替
password = root

## 可选配置
# 是否在日志中显示执行的SQL
showSql = true
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = true
# 打印SQL的日志等级，默认debug
sqlLevel = debug

简单测试连通

package com.west.server.test;

import java.sql.SQLException;
import java.util.List;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;

public class dbTest {

    public static void main(String[] args) throws SQLException {
        List<Entity> list =  Db.use().findAll("user");
        System.out.print(list);
    }

}

返回数据则成功连通


testReq.png

创建service层

  1. 在service目录下创建dbManagerService.java文件
package com.west.server.service;

import java.util.List;

import cn.hutool.db.Entity;
import cn.hutool.db.PageResult;
import cn.hutool.db.meta.Table;

public interface dbManagerService {
    //获取当前库的所有表名
    List<String> getAllTablesName();
    
    //获取表结构
    Table getTableStructure(String tableName);

    //插入数据
    boolean addData(Entity data);

    //插入数据并返回自增主键
    Long addDataForGeneratedKey(Entity data);

    //删除数据
    boolean delData(Entity where);

    //更新数据
    boolean updateData(Entity data,Entity where);

    //查询表全部数据
    List<Entity> findAllList(String tableName);

    //按条件进行查询，含模糊查询
    List<Entity> findAllList(Entity where);

    //模糊查询
    List<Entity> findLikeList(String tableName,String field,String value);

    //分页查询
    PageResult<Entity> getPage(Entity where ,int pages,int pageSize);

    //自定义查询SQL
    List<Entity> customerRSqL(String sql);

    //自定义增删改SQL
    boolean customerCUDSql(String sql);
}
  1. 在serviceImpl目录下创建dbManagerServiceImpl.java文件
package com.west.server.service.serviceImpl;

import java.sql.SQLException;
import java.util.List;

import com.west.server.service.dbManagerService;

import org.springframework.stereotype.Repository;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.db.ds.DSFactory;
import cn.hutool.db.meta.MetaUtil;
import cn.hutool.db.meta.Table;
import cn.hutool.db.sql.Condition.LikeType;

@Repository
public class dbManagerServiceImpl implements dbManagerService {

    @Override
    public List<String> getAllTablesName() {
        // TODO Auto-generated method stub
        return MetaUtil.getTables(DSFactory.get());
    }

    @Override
    public Table getTableStructure(String tableName) {
        // TODO Auto-generated method stub
        return MetaUtil.getTableMeta(DSFactory.get(), tableName);
    }

    @Override
    public boolean addData(Entity data) {
        // TODO Auto-generated method stub
        boolean result = false;
        try {
            if (Db.use().insert(data) == 1)
                result = true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Long addDataForGeneratedKey(Entity data) {
        // TODO Auto-generated method stub
        try {
            return Db.use().insertForGeneratedKey(data);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return (long) 0;
        }
    }

    @Override
    public boolean delData(Entity where) {
        // TODO Auto-generated method stub
        boolean result = false;
        try {
            if (Db.use().del(where) == 1)
                result = true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean updateData(Entity data, Entity where) {
        // TODO Auto-generated method stub
         // TODO Auto-generated method stub
         boolean result = false;
         try {
             if (Db.use().update(data,where) == 1)
                 result = true;
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         return result;
    }

    @Override
    public List<Entity> findAllList(String tableName) {
        // TODO Auto-generated method stub
        try {
            return Db.use().findAll(tableName);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Entity> findAllList(Entity where) {
         // TODO Auto-generated method stub
         try {
            return Db.use().findAll(where);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Entity> findLikeList(String tableName, String field, String value) {
         // TODO Auto-generated method stub
         try {
            return Db.use().findLike(tableName,field,value,LikeType.Contains);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public PageResult<Entity> getPage(Entity where, int pages, int pageSize) {
        // TODO Auto-generated method stub
        try {
            return Db.use().page(where, new Page(pages, pageSize));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Entity> customerRSqL(String sql) {
        // TODO Auto-generated method stub
        try {
            return Db.use().query(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean customerCUDSql(String sql) {
        // TODO Auto-generated method stub
        boolean result = false;
         try {
             if (Db.use().execute(sql) == 1)
                 result = true;
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         return result;
    }
    
}

创建controller层

package com.west.server.controller;

import java.util.List;

import com.west.server.service.dbManagerService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import cn.hutool.db.Entity;
import cn.hutool.db.meta.Table;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;

@RequestMapping("/dbManager")
@RestController
public class dbManagerController {
    @Autowired
    dbManagerService dManagerService;

    
    /**
     * 获取当前库的所有表名
     * API测试URL:http://localhost:8080/dbManager/getAllTablesName
     * @return
     */
    @GetMapping("/getAllTablesName")
    public JSONObject getAllTablesName(){
        List<String> lis = dManagerService.getAllTablesName();
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }

    /**
     * 获取表结构
     * API测试URL:http://localhost:8080/dbManager/getTableStructure/?tablename=article
     * @param tablename 对应数据库的表名
     * @return
     */
    @GetMapping("/getTableStructure")
    public JSONObject getTableStructure(@RequestParam("tablename") String tablename){
        Table table = dManagerService.getTableStructure(tablename);
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", table.getColumns().size())
            .putOnce("data", table.getColumns());
        return jsonObject;
    }

    /**
    * 插入数据
     * API测试URL:http://localhost:8080/dbManager/addData
     * @param request 传入数据：JSON
     * 格式：
     * {
        "#table":"对应数据库的表名",
        "tablename" : "{{tablename}}",
        "#data":"需要插入的数据,例如：{id:1,title:你好}，data能为空",
        "data":{{data}} 
        }
     * @return
     */
    @PostMapping("/addData")
    public String addData(@RequestBody JSONObject request){
        Entity data = Entity.create(request.getStr("tablename")).parseBean(request.get("data"));
        if (dManagerService.addData(data)) return "success";
        return "error";
        
    }

    /**
    * 插入数据并返回自增主键
     * API测试URL:http://localhost:8080/dbManager/addDataForGeneratedKey
     * @param request 传入数据：JSON
     * 格式：
     * {
        "#table":"对应数据库的表名",
        "tablename" : "{{tablename}}",
        "#data":"需要插入的数据,例如：{id:1,title:你好}，data能为空",
        "data":{{data}} 
        }
     * @return
     */
    @PostMapping("/addDataForGeneratedKey")
    public Long addDataForGeneratedKey(@RequestBody JSONObject request){
        Entity data = Entity.create(request.getStr("tablename")).parseBean(request.get("data"));
        return dManagerService.addDataForGeneratedKey(data); 
    }

    /**
    * 删除数据
     * API测试URL:http://localhost:8080/dbManager/delData
     * @param request 传入数据：JSON
     * 格式：
        {
            "#table":"需要修改的数据库表名",
            "tablename" : "{{tablename}}",
            "#where":"限制条件，例如：{id:1}，where不能为空",
            "where":{{where}}
        }
     * @return
     */
    @PostMapping("/delData")
    public String delData(@RequestBody JSONObject request){
        Entity where = Entity.create(request.getStr("tablename")).parseBean(request.get("where"));
        if (dManagerService.delData(where)) return "success";
        return "error";
    }

    /**
    * 更新数据
     * API测试URL:http://localhost:8080/dbManager/updateData
     * @param request 传入数据：JSON
     * 格式：
        {
            "#table":"对应数据库的表名",
            "tablename" : "{{tablename}}",
            "#data":"需要插入的数据,例如：{id:1,title:你好}，data不能为空",
            "data":{{data}},
            "#where":"限制条件，例如：{id:1}，where不能为空",
            "where":{{where}}
        }
     * @return
     */
    @PostMapping("/updateData")
    public String updateData(@RequestBody JSONObject request){
        Entity data = Entity.create(request.getStr("tablename")).parseBean(request.get("data"));
        Entity where = Entity.create(request.getStr("tablename")).parseBean(request.get("where"));
        if (dManagerService.updateData(data, where)) return "success";
        return "error";
    }

    /**
     * 查询表全部数据
     * API测试URL:http://localhost:8080/dbManager/findTableAllList?tablename=article
     * @param tablename 对应数据库的表名
     * @return
     */
    @GetMapping("/findTableAllList")
    public JSONObject findAllList(@RequestParam("tablename") String tablename){
        List<Entity> lis = dManagerService.findAllList(tablename);
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }

    /**
    * 按条件进行查询，含模糊查询
     * API测试URL:http://localhost:8080/dbManager/findTableAllList
     * @param request 传入数据：JSON
     * 格式：
        {
            "#table":"需要修改的数据库表名",
            "tablename" : "{{tablename}}",
            "#where":"限制条件，例如：{id:1}，where不能为空",
            "where":{{where}}
        }
     * @return
     */
    @PostMapping("/findTableAllList")
    public JSONObject findAllList(@RequestBody JSONObject request){
        Entity where = Entity.create(request.getStr("tablename")).parseBean(request.get("where"));
        List<Entity> lis = dManagerService.findAllList(where);
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }

    /**
     * 模糊查询
     * API测试URL:http://localhost:8080/dbManager/findTableAllList?tablename=article
     * @param tablename 对应数据库的表名
     * @param field 字段名
     * @param value 对应值
     * @return
     */
    @GetMapping("/findTableLikeList")
    public JSONObject findLikeList(@RequestParam("tablename") String tablename,@RequestParam("field") String field,@RequestParam("value") String value){
        List<Entity> lis = dManagerService.findLikeList(tablename,field,value);
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }

    /**
    * 分页查询
     * API测试URL:http://localhost:8080/dbManager/getPage
     * @param request 传入数据：JSON
     * 格式：
        {
            "#table":"需要修改的数据库表名",
            "tablename" : "{{tablename}}",
            "#where":"限制条件，例如：{id:1}，where不能为空",
            "where":{{where}},
            "#pages":"页码",
            "pages":{{pages}},
            "#pageSize":"每页数量",
            "pageSize":{{pagesize}}
        }
     * @return
     */
    @PostMapping("/getPage")
    public JSONObject getPage(@RequestBody JSONObject request){
        Entity where = Entity.create(request.getStr("tablename")).parseBean(request.get("where"));
        List<Entity> lis = dManagerService.getPage(where, request.getInt("pages"), request.getInt("pageSize"));
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }

    /**
     * 自定义查询语句
     * API测试URL:http://localhost:8080/dbManager/customerRSqL
     * @param request 传入数据：JSON
     * 格式：
        {
            "#sql": "自定义sql语句",
            "sql": "{{selectSQL}}"
        }
     * @return
     */
    @PostMapping("/customerRSqL")
    public JSONObject customerRSqL(@RequestBody JSONObject request){
        List<Entity> lis = dManagerService.customerRSqL(request.getStr("sql"));
        JSONObject jsonObject = JSONUtil.createObj()
            .putOnce("count", lis.size())
            .putOnce("data", lis);
        return jsonObject;
    }
    
    /**
     * 自定义查询语句
     * API测试URL:http://localhost:8080/dbManager/customerCUDSql
     * @param request 传入数据：JSON
     * 格式：
        {
            "#sql": "自定义sql语句",
            "sql": "{{selectSQL}}"
        }
     * @return
     */
    @PostMapping("/customerCUDSql")
    public String customerCUDSql(@RequestBody JSONObject request){
        if (dManagerService.customerCUDSql(request.getStr("sql"))) return "success";
        return "error";
    }
}

测试数据

  1. 按dbManagerController 测试URL进行测试
  2. 自行到Gitee(https://gitee.com/waiter-chen/wset/tree/V0.0.1)下载postmanData配置文件导入到postman进行测试
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容