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层
- 在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);
}
- 在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";
}
}
测试数据
- 按dbManagerController 测试URL进行测试
- 自行到Gitee(https://gitee.com/waiter-chen/wset/tree/V0.0.1)下载postmanData配置文件导入到postman进行测试
