BuilderQuery
import com.lubansoft.builder.common.exception.SQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import java.beans.Introspector;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.List;
import java.util.regex.Pattern;
/**
* @Description : 查询封装
* @Author : cxw
* @Date : 2023/8/7 13:13
* @Version : 1.0
**/
public class BuilderQuery<T> {
private static final Pattern GET_PATTERN = Pattern.compile("^get[A-Z].*");
private static final Pattern IS_PATTERN = Pattern.compile("^is[A-Z].*");
private static Logger logger = LoggerFactory.getLogger(SQLUtil.class);
private Field[] fields;
private String className;
private Class _clazz;
/**
* sql
*/
private StringBuilder sql=new StringBuilder();
/**
* 参数
*/
private MapSqlParameterSource parameterSource=new MapSqlParameterSource();
public BuilderQuery(Class<?> clazz) {
fields = clazz.getDeclaredFields();
className=clazz.getName();
_clazz=clazz;
}
/**
* 精准匹配 field=val 的sql
* @param func 类的字段名get方法
* @param val 类对应的字段值
* @return
*/
public BuilderQuery eq(BuilderFunction<T, String> func,Object val) throws Exception {
String field = getFieldName(func.getMethodName());
String _field = getSQLField(field);
if(sql.toString().toLowerCase().indexOf("where")>0){
sql.append(" and ").append(_field).append(" = :").append(field).append(" ");
}else {
sql.append(" where ").append(_field).append(" = :").append(field).append(" ");
}
parameterSource.addValue(field,val);
return this;
}
/**
* in 查询
* @param func 类的字段名get方法
* @param val 类对应的字段值
* @return
*/
public BuilderQuery in(BuilderFunction<T, String> func, List<Object> val) throws Exception {
String field = getFieldName(func.getMethodName());
String _field = getSQLField(field);
if(sql.toString().toLowerCase().indexOf("and")>0){
sql.append(" and ").append(_field).append(" in ( :").append(field).append(") ");
}else {
sql.append(" where ").append(_field).append(" in (:").append(field).append(") ");
}
parameterSource.addValue(field,val);
return this;
}
/**
* like模糊 查询
* @param func 类的字段名get方法
* @param val 类对应的字段值
* @return
*/
public BuilderQuery like(BuilderFunction<T, String> func, String val,Boolean allLike) throws Exception {
String field = getFieldName(func.getMethodName());
String _field = getSQLField(field);
val="%"+ val;
if(sql.toString().toLowerCase().indexOf("and")>0){
sql.append(" and ").append(_field).append(" like :").append(field).append(" ");
}else {
sql.append(" where ").append(_field).append(" like :").append(field).append(" ");
}
if(allLike!=null&&allLike)val+="%";
parameterSource.addValue(field,val);
return this;
}
/**
* 大于查询
* @param func 类的字段名get方法
* @param val 类对应的字段值
* @return
*/
public BuilderQuery gt(BuilderFunction<T, String> func, String val) throws Exception {
String field = getFieldName(func.getMethodName());
String _field = getSQLField(field);
if(sql.toString().toLowerCase().indexOf("and")>0){
sql.append(" and ").append(_field).append(" > :").append(field).append(" ");
}else {
sql.append(" where ").append(_field).append(" > :").append(field).append(" ");
}
parameterSource.addValue(field,val);
return this;
}
/**
* 小于查询
* @param func 类的字段名get方法
* @param val 类对应的字段值
* @return
*/
public BuilderQuery lt(BuilderFunction<T, String> func, String val) throws Exception {
String field = getFieldName(func.getMethodName());
String _field = getSQLField(field);
if(sql.toString().toLowerCase().indexOf("and")>0){
sql.append(" and ").append(_field).append(" < :").append(field).append(" ");
}else {
sql.append(" where ").append(_field).append(" < :").append(field).append(" ");
}
parameterSource.addValue(field,val);
return this;
}
/**
* 生成字段
* @param field
* @return
* @throws Exception
*/
private String getSQLField(String field) throws Exception {
for (Field field1 : fields) {
if(field1.getName().equals(field)){
// 获取字段上的注解对象
Annotation[] annotations =field1.getDeclaredAnnotations();
String tableField = SQLUtil.hasBuilderField(annotations);
if(tableField==null)tableField=SQLUtil.humpToLine(field);
return tableField;
}
}
throw new SQLException("当前类:"+className+"不存在字段:"+field+"！");
}
/**
* 方法名转字段名
* @param methodName
* @return
*/
private String getFieldName(String methodName){
// 对于非标准变量生成的Get方法这里可以直接抛出异常，或者打印异常日志
if (GET_PATTERN.matcher(methodName).matches()) {
methodName = methodName.substring(3);
} else if (IS_PATTERN.matcher(methodName).matches()) {
methodName = methodName.substring(2);
}
return Introspector.decapitalize(methodName);
}
/**
* 输出sql
* @param tableName 表名
* @return
*/
public String getSQL(String tableName){
String result = "select * from "+tableName+" "+sql.toString();
logger.debug("【query sql】={}",result);
return result;
}
/**
* 输出参数
* @return
*/
public MapSqlParameterSource getParameterSource(){
return parameterSource;
}
/***
* 使用案例
*
* BuilderQuery<OrgV2DO> builderQuery=new BuilderQuery(OrgV2DO.class);
* builderQuery.eq(OrgV2DO::getId,"123213");
* builderQuery.eq(OrgV2DO::getLastModifiedBy,"123213");
* String org = builderQuery.getSQL("org");
* System.out.println(org);
*
* ****/
}
SQLUtil
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @Description : sql工具类
* @Author : cxw
* @Date : 2023/8/3 14:31
* @Version : 1.0
**/
public class SQLUtil {
private static Logger logger = LoggerFactory.getLogger(SQLUtil.class);
private static Pattern humpPattern = Pattern.compile("[A-Z]");
/**
* 创建插入sql
* @param tableName 数据库表名
* @param obj 类对象
* @return
*/
public static String createInsertSQL(String tableName, Object obj)throws Exception{
StringBuilder sql=new StringBuilder();
sql.append(" INSERT INTO ").append(tableName).append(" ");
String field="";
String values="";
List<Field> fieldList = getAllFields(obj.getClass());
for(Field f : fieldList){
f.setAccessible(true);
// 获取字段上的注解对象
Annotation[] annotations = f.getDeclaredAnnotations();
String tableField = hasBuilderField(annotations);
if(tableField==null)tableField=humpToLine(f.getName());
field += tableField+",";
values += ":"+f.getName()+",";
}
field = field.substring(0, field.length() - 1);
values = values.substring(0, values.length() - 1);
sql.append("(").append(field).append(")").append(" VALUES ");
sql.append("(").append(values).append(")").append(" ");
logger.debug("【insert sql】={}",sql.toString());
return sql.toString();
}
/**
* 创建更新sql 类对象字段值为null的也更新
* @param tableName 表名
* @param idField 主键id
* @param obj 类对象
* @return
*/
public static String createUpdateNullSQL(String tableName,String idField, Object obj){
StringBuilder sql=new StringBuilder();
sql.append(" UPDATE ").append(tableName).append(" SET ");
String where=" where "+idField+"=:"+idField;
List<Field> fieldList = getAllFields(obj.getClass());
for(Field f : fieldList){
f.setAccessible(true);
// 获取字段上的注解对象
Annotation[] annotations = f.getDeclaredAnnotations();
String tableField = hasBuilderField(annotations);
if(tableField==null)tableField=humpToLine(f.getName());
if(tableField.equals(idField))continue;
sql.append(tableField).append(" = ").append(":").append(f.getName()).append(",");
}
String result = sql.toString().toString();
result = result.substring(0, result.length() - 1);
result = result+where;
logger.debug("【update sql】={}",result);
return result;
}
/**
* 创建更新sql 类对象字段值为null不更新
* @param tableName 表名
* @param idField 主键id
* @param obj 类对象
* @return
*/
public static String createUpdateNotNullSQL(String tableName,String idField, Object obj) throws Exception {
StringBuilder sql=new StringBuilder();
sql.append(" UPDATE ").append(tableName).append(" SET ");
String where=" where "+idField+"=:"+idField;
List<Field> fieldList = getAllFields(obj.getClass());
for(Field f : fieldList){
f.setAccessible(true);
if(f.get(obj)==null)continue;
// 获取字段上的注解对象
Annotation[] annotations = f.getDeclaredAnnotations();
String tableField = hasBuilderField(annotations);
if(tableField==null)tableField=humpToLine(f.getName());
if(tableField.equals(idField))continue;
sql.append(tableField).append(" = ").append(":").append(f.getName()).append(",");
}
String result = sql.toString().toString();
result = result.substring(0, result.length() - 1);
result = result+where;
logger.debug("【update sql】={}",result);
return result;
}
/**
* 创建查询sql
* @param tableName 表名
* @param obj 类对象
* @return
*/
public static String createQuerySQL(String tableName,Object obj) throws Exception {
StringBuilder sql=new StringBuilder();
sql.append(" select * from ").append(tableName).append(" where ");
List<Field> fieldList = getAllFields(obj.getClass());
for(Field f : fieldList){
f.setAccessible(true);
if(f.get(obj)==null)continue;
// 获取字段上的注解对象
Annotation[] annotations = f.getDeclaredAnnotations();
String tableField = hasBuilderField(annotations);
if(tableField==null)tableField=humpToLine(f.getName());
sql.append(tableField).append(" = ").append(":").append(f.getName()).append(" and ");
}
String result = sql.toString().toString();
result = result.substring(0, result.length() - 4);
logger.debug("【query sql】={}",result);
return result;
}
/**
* 创建变量
* @param obj
* @param hasNull 是否包含null的值
* @return
*/
public static MapSqlParameterSource createParam(Object obj,Boolean hasNull){
MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
try{
List<Field> fieldList = getAllFields(obj.getClass());
for(Field f : fieldList){
f.setAccessible(true);
if(!hasNull&&f.get(obj)==null)continue;
mapSqlParameterSource.addValue(f.getName(), f.get(obj));
}
return mapSqlParameterSource;
}catch(Exception e){
}
return null;
}
/***------------------以下方法为内部调用方法---------------------***/
/**
* 获取所有字段的
* @param cls
* @return
*/
private static List<Field> getAllFields(Class<?> cls){
List<Field> fieldList = new ArrayList<Field>();
while(null != cls){
fieldList.addAll(Arrays.asList(cls.getDeclaredFields()));
//得到父类,然后赋给自己
cls = cls.getSuperclass();
}
return fieldList;
}
/**
* 驼峰转下划线
* @param str
* @return
*/
public static String humpToLine(String str){
Matcher matcher = humpPattern.matcher(str);
StringBuffer sb = new StringBuffer();
while(matcher.find()){
matcher.appendReplacement(sb, "_"+matcher.group(0).toLowerCase());
}
matcher.appendTail(sb);
return sb.toString();
}
/**
* 判断是否存在BuilderField注解
* @param annotations
* @return
*/
public static String hasBuilderField(Annotation[] annotations) {
if(annotations==null||annotations.length==0)return null;
for (Annotation annotation : annotations) {
if(annotation instanceof BuilderField){
return ((BuilderField) annotation).name();
}
}
return null;
}
}
BuilderFunction
import lombok.SneakyThrows;
import java.io.Serializable;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Method;
import java.util.function.Function;
@FunctionalInterface
public interface BuilderFunction<T, R> extends Function<T, R>, Serializable {
@SneakyThrows
default String getFieldName() {
String methodName = getMethodName();
if (methodName.startsWith("get")) {
methodName = methodName.substring(3);
}
return methodName;
}
@SneakyThrows
default String getMethodName() {
return getSerializedLambda().getImplMethodName();
}
@SneakyThrows
default Class<?> getFieldClass() {
return getReturnType();
}
@SneakyThrows
default SerializedLambda getSerializedLambda() {
Method method = getClass().getDeclaredMethod("writeReplace");
method.setAccessible(true);
return (SerializedLambda) method.invoke(this);
}
@SneakyThrows
default Class<?> getReturnType() {
SerializedLambda lambda = getSerializedLambda();
Class<?> className = Class.forName(lambda.getImplClass().replace("/", "."));
Method method = className.getMethod(getMethodName());
return method.getReturnType();
}
}
SQLException
/**
* @Description : 数据库异常
* @Author : cxw
* @Date : 2023/8/7 13:32
* @Version : 1.0
**/
public class SQLException extends Exception{
public SQLException(String message) {
super(message);
}
}
/**
* 字段注解
*/
public @interface BuilderField {
/**
* 对应的数据库字段名称
* @return
*/
String name();
}
浙公网安备 33010602011771号