Java 编写 PG单表sql构造器
不依赖其他工具类，jdk11 可以用，方便简洁轻量,直接能跑~~~~~~
测试代码
public static void main(String[] args) {
SqlWrapper.SqlBuildResult result = SqlWrapper
.select("t_user")
.where(SqlWrapper.condition().eq("userId", 1)
.or(SqlWrapper.condition().gt("age", 18)
.and(SqlWrapper.condition().eq("status", true).like("username", "admin"))
.in("name", List.of("xiaoming", "xiaozhang"))))
.build();
System.out.println("搜索> " + result.toString());
result = SqlWrapper.delete("t_user")
.where(SqlWrapper.condition().in("userId", List.of(1, 2, 3, 4))).build();
System.out.println("删除> " + result.toString());
result = SqlWrapper.update("t_user").set("name", "admin123").set("age", 18)
.where(SqlWrapper.condition().eq("username", "admin")).build();
System.out.println("更新> " + result.toString());
result = SqlWrapper.insert("t_user").set("userId", 1234567L).set("userName", "amdin").set("status", true).build();
System.out.println("插入> " + result.toString());
}
结果打印
搜索> select * from t_user where user_id = $1 and ( age > $2 or ( status = $3 and username like $4 )or name in ($5,$6) ) ====== [1, 18, true, %admin%, xiaoming, xiaozhang]
删除> delete from t_user where user_id in ($1,$2,$3,$4) ====== [1, 2, 3, 4]
更新> update t_user set name = $1,age = $2 where username = $3 ====== [admin123, 18, admin]
插入> insert into t_user ( user_id,user_name,status ) values ( $1,$2,$3 ) ====== [1234567, amdin, true]
Sql构造器
package com.dec.web;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
/**
* @author lx 2024/1/31
*/
public class SqlWrapper {
/**
* 正则匹配驼峰
*/
private static final Pattern COMPILE_UNDERLINE = Pattern.compile("[A-Z]");
/**
* 数据库操作类型枚举
*/
private enum DbHandleType {
INSERT, UPDATE, DELETE, SELECT;
}
/**
* select 查询条件存储
*/
public static class Condition {
private Condition() {
}
private static Condition build() {
return new Condition();
}
/**
* 字段的查询条件存储
*/
private static class ConditionInfo {
String column;
Object value;
DbSearchType searchType;
public ConditionInfo(String column, Object value, DbSearchType searchType) {
this.column = column;
this.value = value;
this.searchType = searchType;
}
}
/**
* 查询条件枚举
*/
private enum DbSearchType {
EQ("="),
NEQ("!="),
GT(">"),
LT("<"),
LIKE("like"),
IN("in"),
OR("or"),
AND("and");
private String symbol;
DbSearchType(String symbol) {
this.symbol = symbol;
}
}
/**
* 条件 带顺序
*/
private final LinkedList<ConditionInfo> linkedList = new LinkedList();
public Condition eq(String column, Object value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.EQ));
return this;
}
public Condition gt(String column, Object value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.GT));
return this;
}
public Condition lt(String column, Object value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.LT));
return this;
}
public Condition neq(String column, Object value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.NEQ));
return this;
}
public Condition in(String column, List<Object> value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.IN));
return this;
}
public Condition or(Condition condition) {
this.linkedList.offer(new ConditionInfo("", condition, DbSearchType.OR));
return this;
}
public Condition and(Condition condition) {
this.linkedList.offer(new ConditionInfo("", condition, DbSearchType.AND));
return this;
}
public Condition like(String column, String value) {
linkedList.offer(new ConditionInfo(column, value, DbSearchType.LIKE));
return this;
}
}
public static class SqlBuildResult {
/**
* 预编译sql
*/
private String precompileSql;
/**
* 查看参数
*/
private List<Object> params;
@Override
public String toString() {
return precompileSql + " ====== " + params;
}
}
/**
* 查询字段
*/
private List<String> queryFieldList;
/**
* 条件
*/
private Condition where;
/**
* 分组
*/
private List<String> groupBy = new ArrayList<>();
/**
* 排序
*/
private final Map<String, String> orderBy = new LinkedHashMap<>();
/**
* 设置插入更新字段
*/
private final Map<String, Object> set = new LinkedHashMap<>();
/**
* 分页
*/
private Integer offset;
private Integer limit;
private String tableName;
/**
* select update delete insert 类型
*/
private DbHandleType type;
private SqlWrapper() {
}
/**
* 构造条件实体
*
* @return
*/
public static Condition condition() {
return Condition.build();
}
public static SqlWrapper select(String tableName) {
SqlWrapper wrapper = new SqlWrapper();
wrapper.type = DbHandleType.SELECT;
wrapper.tableName = tableName;
wrapper.queryFieldList = List.of("*");
return wrapper;
}
public static SqlWrapper select(String tableName, List<String> queryFieldList) {
SqlWrapper wrapper = new SqlWrapper();
wrapper.type = DbHandleType.SELECT;
wrapper.tableName = tableName;
wrapper.queryFieldList = queryFieldList.stream().map(SqlWrapper::underline).collect(Collectors.toList());
return wrapper;
}
public static SqlWrapper update(String tableName) {
SqlWrapper wrapper = new SqlWrapper();
wrapper.type = DbHandleType.UPDATE;
wrapper.tableName = tableName;
return wrapper;
}
public static SqlWrapper insert(String tableName) {
SqlWrapper wrapper = new SqlWrapper();
wrapper.type = DbHandleType.INSERT;
wrapper.tableName = tableName;
return wrapper;
}
public static SqlWrapper delete(String tableName) {
SqlWrapper wrapper = new SqlWrapper();
wrapper.type = DbHandleType.DELETE;
wrapper.tableName = tableName;
return wrapper;
}
/**
* （更新） 或者 （插入） 设置值
*
* @param column
* @param value
* @return
*/
public SqlWrapper set(String column, Object value) {
this.set.put(column, value);
return this;
}
/**
* 查询设置条件
*
* @param condition
* @return
*/
public SqlWrapper where(Condition condition) {
this.where = condition;
return this;
}
/**
* 排序
*
* @param column
* @return
*/
public SqlWrapper orderByDesc(String column) {
orderBy.put(column, "desc");
return this;
}
/**
* 排序
*
* @param column
* @return
*/
public SqlWrapper orderByAsc(String column) {
orderBy.put(column, "asc");
return this;
}
/**
* 分组
*
* @param groupByList
* @return
*/
public SqlWrapper groupBy(List<String> groupByList) {
this.groupBy = groupByList;
return this;
}
/**
* 分页
*
* @param page
* @param size
* @return
*/
public SqlWrapper page(int page, int size) {
this.limit = size;
this.offset = (page - 1) * size;
return this;
}
/**
* 构建sql
*
* @return
*/
public SqlBuildResult build() {
StringBuilder builder = new StringBuilder(256);
List<Object> params = new ArrayList<>();
StringBuilder whereBuilder = null;
switch (type) {
case INSERT:
builder.append("insert into ").append(tableName).append(" ");
StringBuilder fieldBuilder = new StringBuilder(64);
StringBuilder valueBuilder = new StringBuilder(64);
for (Map.Entry<String, Object> entry : set.entrySet()) {
// 转成下划线
fieldBuilder.append(underline(entry.getKey())).append(",");
params.add(entry.getValue());
valueBuilder.append("$").append(params.size()).append(",");
}
builder.append("( ")
.append(fieldBuilder.delete(fieldBuilder.length() - 1, fieldBuilder.length()))
.append(" ) values ( ")
.append(valueBuilder.delete(valueBuilder.length() - 1, valueBuilder.length()))
.append(" )");
break;
case UPDATE:
if (set.isEmpty()) {
throw new RuntimeException("更新字段为空");
}
if (Objects.isNull(where)) {
throw new RuntimeException("更新条件错误");
}
builder.append("update ").append(tableName).append(" ").append("set").append(" ");
for (Map.Entry<String, Object> entry : set.entrySet()) {
if (entry.getValue() == null) {
continue;
}
params.add(entry.getValue());
// 转下划线
builder.append(underline(entry.getKey())).append(" = ").append("$").append(params.size()).append(",");
}
builder.delete(builder.length() - 1, builder.length());
whereBuilder = conditionBuilder(where, params, "and");
builder.append(" where ").append(whereBuilder);
break;
case SELECT:
builder.append("select ")
.append(String.join(",", queryFieldList))
.append(" from ").append(tableName).append(" ");
whereBuilder = conditionBuilder(where, params, "and");
if (whereBuilder.length() > 0) {
builder.append(" where ").append(whereBuilder);
}
if (!groupBy.isEmpty()) {
builder.append(" ").append("group by").append(String.join(",", groupBy)).append(" ");
}
if (!orderBy.isEmpty()) {
builder.append(" ").append("order by").append(" ");
for (Map.Entry<String, String> entry : orderBy.entrySet()) {
builder.append(underline(entry.getKey())).append(" ").append(entry.getValue()).append(" ").append(",");
}
builder.delete(builder.length() - 1, builder.length());
}
if (limit != null) {
params.add(limit);
builder.append(" ").append("limit $").append(params.size());
params.add(offset);
builder.append(" ").append("offset $").append(params.size());
}
break;
case DELETE:
builder.append("delete from ").append(tableName).append(" ");
whereBuilder = conditionBuilder(where, params, "and");
if (whereBuilder.length() == 0) {
throw new RuntimeException("更新无条件限制");
}
builder.append(" where ").append(whereBuilder);
break;
}
SqlBuildResult sqlBuildResult = new SqlBuildResult();
sqlBuildResult.precompileSql = builder.toString();
sqlBuildResult.params = params;
return sqlBuildResult;
}
/**
* 构建where 后条件
*
* @param condition
* @param params
* @param link
* @return
*/
private StringBuilder conditionBuilder(Condition condition, List<Object> params, String link) {
StringBuilder builder = new StringBuilder(128);
if (Objects.isNull(condition)) {
return builder;
}
for (Condition.ConditionInfo info : condition.linkedList) {
switch (info.searchType) {
case EQ:
case NEQ:
case GT:
case LT:
params.add(info.value);
builder.append(link).append(" ")
.append(underline(info.column))
.append(" ").append(info.searchType.symbol).append(" ")
.append("$").append(params.size()).append(" ");
break;
case IN: {
if (info.value instanceof List<?>) {
builder.append(link).append(" ")
.append(underline(info.column))
.append(" ").append(info.searchType.symbol).append(" (");
for (Object obj : (List) info.value) {
params.add(obj);
builder.append("$").append(params.size()).append(",");
}
builder.delete(builder.length() - 1, builder.length());
builder.append(") ");
}
break;
}
case OR:
case AND:
StringBuilder orBuilder = conditionBuilder((Condition) info.value, params, info.searchType.symbol);
if (orBuilder.length() > 0) {
builder.append(" ").append(link).append(" (").append(orBuilder).append(" )");
}
break;
case LIKE:
params.add("%" + info.value + "%");
builder.append(link).append(" ").append(underline(info.column)).append(" ")
.append(info.searchType.symbol).append(" ").append("$").append(params.size()).append(" ");
break;
default:
break;
}
}
int index = builder.indexOf(link);
if (index > -1) {
builder.delete(0, index + link.length());
}
return builder;
}
/**
* 将驼峰转为下划线
*/
private static String underline(String str) {
Matcher matcher = COMPILE_UNDERLINE.matcher(str);
StringBuffer sb = new StringBuffer(12);
while (matcher.find()) {
matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
}
matcher.appendTail(sb);
return sb.toString();
}
}
扫一扫
1607
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
