cn.handyplus.lib.db.DbSql Maven / Gradle / Ivy
The newest version!
package cn.handyplus.lib.db;
import cn.handyplus.lib.core.CollUtil;
import cn.handyplus.lib.core.DateUtil;
import cn.handyplus.lib.core.StrUtil;
import cn.handyplus.lib.db.enums.DbTypeEnum;
import cn.handyplus.lib.db.enums.SqlKeyword;
import cn.handyplus.lib.db.param.FieldInfoParam;
import cn.handyplus.lib.db.param.TableInfoParam;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import java.io.Serializable;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.UUID;
/**
* dbSql处理器
*
* @author handy
* @since 1.4.8
*/
@Builder
public class DbSql implements Serializable {
private static final long serialVersionUID = 8696707404811115903L;
/**
* 表名称
*/
private String tableName;
/**
* 表信息
*/
@Getter
private TableInfoParam tableInfoParam;
/**
* field sql
*/
@Setter
private String field;
/**
* 字段信息 key: 表字段名;val: 字段信息
*/
@Getter
private LinkedHashMap fieldInfoMap;
/**
* where sql
*/
private String where;
/**
* where 条件
*
* @since 3.10.7
*/
@Getter
private LinkedHashMap whereMap;
/**
* update field sql
*/
private List updatefieldList;
/**
* update field map
*/
@Getter
private LinkedHashMap updateFieldMap;
/**
* limit sql
*/
private String limit;
/**
* order sql
*/
private String order;
/**
* group sql
*/
private String group;
/**
* 组合sql
*
* @param sqlColl sql内容
* @return sql
*/
private static String assemblySql(String... sqlColl) {
StringBuilder sb = new StringBuilder();
for (String sql : sqlColl) {
if (StrUtil.isEmpty(sql)) {
continue;
}
sb.append(sql);
}
return sb.toString();
}
protected String selectCountSql(String field) {
// 如果有传入字段就添加DISTINCT进行去重,如果没有就COUNT(*)
String countStr = StrUtil.isNotEmpty(field) ? String.format(DbConstant.COUNT_DISTINCT, field) : DbConstant.COUNT;
return assemblySql(DbConstant.SELECT, countStr, DbConstant.FORM, this.tableName, this.where);
}
protected String selectDataSql() {
return assemblySql(DbConstant.SELECT, this.field, DbConstant.FORM, this.tableName, this.where, this.group, this.order, this.limit);
}
protected String insertDataSql() {
List questionMarkList = new ArrayList<>();
for (int i = 0; i < this.fieldInfoMap.size(); i++) {
questionMarkList.add(DbConstant.QUESTION_MARK);
}
return assemblySql(DbConstant.INSERT, this.tableName, DbConstant.LEFT_BRACKET, this.field, DbConstant.RIGHT_BRACKET, DbConstant.VALUES, DbConstant.LEFT_BRACKET, CollUtil.listToStr(questionMarkList), DbConstant.RIGHT_BRACKET);
}
protected String updateDataSql() {
return assemblySql(DbConstant.UPDATE, this.tableName, DbConstant.SET, CollUtil.listToStr(this.updatefieldList), this.where);
}
protected String deleteDataSql() {
return assemblySql(DbConstant.DELETE, this.tableName, this.where);
}
/**
* 普通查询条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param sqlKeyword SQL 关键词
* @param val 条件值
*/
protected void addCondition(boolean condition, String fieldName, SqlKeyword sqlKeyword, Object val) {
if (!condition) {
return;
}
// 特殊值处理
val = this.specialHandling(val);
this.where += SqlKeyword.AND.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + sqlKeyword.getKeyword() + DbConstant.QUESTION_MARK;
this.setWhereMap(val);
}
/**
* 比较查询条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param sqlKeyword SQL 关键词
* @param compareFieldName 比较字段
*/
protected void addCondition(boolean condition, String fieldName, SqlKeyword sqlKeyword, String compareFieldName) {
if (!condition) {
return;
}
this.where += SqlKeyword.AND.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + sqlKeyword.getKeyword() + DbConstant.POINT + compareFieldName + DbConstant.POINT;
}
/**
* is null 或者 is not nul 条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param orderType 类型
* @since 3.4.1
*/
protected void addNull(boolean condition, String fieldName, SqlKeyword orderType) {
if (!condition) {
return;
}
this.where += SqlKeyword.AND.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + orderType.getKeyword();
}
/**
* 比较查询条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param val 值
* @param sqlKeyword SQL 关键词
* @param compareFieldName 比较字段
* @since 3.1.0
*/
protected void addCondition(boolean condition, String fieldName, Object val, SqlKeyword sqlKeyword, String compareFieldName) {
if (!condition) {
return;
}
this.where += SqlKeyword.AND.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + DbConstant.ADD + DbConstant.QUESTION_MARK + sqlKeyword.getKeyword() + DbConstant.POINT + compareFieldName + DbConstant.POINT;
this.setWhereMap(val);
}
/**
* order By 条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param orderType 类型
*/
protected void addOrderByCondition(boolean condition, String fieldName, SqlKeyword orderType) {
if (!condition) {
return;
}
this.order = SqlKeyword.ORDER_BY.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + orderType.getKeyword();
}
/**
* order By 条件
*
* @param condition 是否执行
* @param fieldName 属性
* @param fieldNameTwo 字段2
* @param orderType 类型
* @since 3.1.9
*/
protected void addOrderByCondition(boolean condition, String fieldName, String fieldNameTwo, SqlKeyword orderType) {
if (!condition) {
return;
}
this.order = SqlKeyword.ORDER_BY.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + orderType.getKeyword() + DbConstant.COMMA + DbConstant.POINT + fieldNameTwo + DbConstant.POINT + orderType.getKeyword();
}
/**
* 随机记录: ORDER BY RAND()
*
* @param condition 是否执行
* @since 3.5.9
*/
protected void addOrderByRand(boolean condition) {
if (!condition) {
return;
}
if (DbTypeEnum.SQLite.getType().equalsIgnoreCase(SqlManagerUtil.getInstance().getStorageMethod())) {
this.order = DbConstant.SQLITE_RAND;
} else {
this.order = DbConstant.MYSQL_RAND;
}
}
/**
* group By 条件
*
* @param condition 是否执行
* @param fieldName 属性
*/
protected void addGroupByCondition(boolean condition, String fieldName) {
if (!condition) {
return;
}
this.group = SqlKeyword.GROUP_BY.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT;
}
/**
* limit 条件
*
* @param condition 是否执行
* @param pageNo 当前页
* @param pageSize 每页显示条数
*/
protected void addLimitCondition(boolean condition, int pageNo, int pageSize) {
if (!condition) {
return;
}
int ret = (pageNo - 1) * pageSize;
int offset = Math.max(ret, 0);
this.limit = SqlKeyword.LIMIT.getKeyword() + pageSize + SqlKeyword.OFFSET.getKeyword() + offset;
}
/**
* in 条件
*
* @param condition 是否执行
* @param fieldName 赋值字段
* @param sqlKeyword SQL 关键词
* @param val 条件值
*/
protected void addInCondition(boolean condition, String fieldName, SqlKeyword sqlKeyword, List val) {
if (!condition || CollUtil.isEmpty(val)) {
return;
}
// 构建占位符字符串
String placeholders = String.join(",", Collections.nCopies(val.size(), "?"));
this.where += SqlKeyword.AND.getKeyword() + DbConstant.POINT + fieldName + DbConstant.POINT + sqlKeyword.getKeyword() + DbConstant.LEFT_BRACKET + placeholders + DbConstant.RIGHT_BRACKET;
this.setWhereMap(val);
}
/**
* update构造
*
* @param condition 是否执行
* @param fieldName 赋值字段
* @param val 条件值
*/
protected void updateCondition(boolean condition, String fieldName, Object val) {
if (!condition) {
return;
}
// 特殊值处理
val = updateSpecialHandling(val);
this.updatefieldList.add(DbConstant.POINT + fieldName + DbConstant.POINT + DbConstant.EQUALS + DbConstant.QUESTION_MARK);
this.updateFieldMap.put(this.updatefieldList.size(), val);
}
/**
* 计算类update构造
*
* @param condition 是否执行
* @param fieldName 赋值字段
* @param calculateFieldName 参与计算字段
* @param sqlKeyword 计算符
* @param val 条件值
* @since 2.1.0
*/
protected void updateCondition(boolean condition, String fieldName, String calculateFieldName, String sqlKeyword, Object val) {
if (!condition) {
return;
}
this.updatefieldList.add(DbConstant.POINT + fieldName + DbConstant.POINT + DbConstant.EQUALS + DbConstant.POINT + calculateFieldName + DbConstant.POINT + sqlKeyword + DbConstant.QUESTION_MARK);
this.updateFieldMap.put(this.updatefieldList.size(), val);
}
/**
* 特殊字段类型处理
*
* @param val 值
* @return 新值
*/
private Object specialHandling(Object val) {
if (val == null) {
return null;
}
//布尔处理
if (val instanceof Boolean) {
Boolean bool = (Boolean) val;
val = bool ? 1 : 0;
}
// UUID处理为字符
if (val instanceof UUID) {
val = val.toString();
}
// sqlite
if (DbTypeEnum.SQLite.getType().equalsIgnoreCase(SqlManagerUtil.getInstance().getStorageMethod())) {
// LocalDateTime处理
if (val instanceof LocalDateTime) {
val = DateUtil.toEpochSecond((LocalDateTime) val);
}
// Date处理
if (val instanceof Date) {
Date date = (Date) val;
val = date.getTime();
}
}
return val;
}
/**
* update特殊字段类型处理
*
* @param val 值
* @return 新值
* @since 2.6.3
*/
private Object updateSpecialHandling(Object val) {
if (val == null) {
return null;
}
//布尔处理
if (val instanceof Boolean) {
Boolean bool = (Boolean) val;
val = bool ? 1 : 0;
}
// UUID处理
if (val instanceof UUID) {
val = val.toString();
}
// sqlite
if (DbTypeEnum.SQLite.getType().equalsIgnoreCase(SqlManagerUtil.getInstance().getStorageMethod())) {
// LocalDateTime处理
if (val instanceof LocalDateTime) {
val = DateUtil.toEpochSecond((LocalDateTime) val);
}
// Date处理
if (val instanceof Date) {
Date date = (Date) val;
val = date.getTime();
}
} else {
// LocalDateTime处理
if (val instanceof LocalDateTime) {
val = new Timestamp(DateUtil.toEpochSecond((LocalDateTime) val));
}
// Date处理
if (val instanceof Date) {
Date date = (Date) val;
val = new Timestamp(date.getTime());
}
}
return val;
}
/**
* sqlite 特殊字符转义
*
* @param keyWord 字符
* @return 转义后字符
* @since 2.5.2
*/
private String sqliteEscape(String keyWord) {
keyWord = keyWord.replace("'", "''");
return keyWord;
}
/**
* 设置whereMap
*
* @param val 条件值
* @since 3.10.7
*/
private void setWhereMap(Object val) {
if (this.whereMap == null) {
this.whereMap = new LinkedHashMap<>();
}
if (val instanceof List) {
List list = (List) val;
list.forEach(item -> this.whereMap.put(this.whereMap.size() + 1, item));
} else {
this.whereMap.put(this.whereMap.size() + 1, val);
}
}
} © 2015 - 2025 Weber Informatics LLC | Privacy Policy