sql语句操作集合

使用效果
可以得到操作类所需要的sql语句及参数值，最后使用DBUtil直接执行

import com.jsonliu.bean.Page;
import com.jsonliu.bean.SqlConsist;
import com.jsonliu.bean.SqlExpression;
import com.jsonliu.bean.TableAnn;
import org.apache.commons.lang.StringUtils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: JSONLiu
 * @Description: sql语句操作集合
 * @Date Created in 2021-08-24 20:02
 * @Modified By:
 */
public class SqlUtil {


    /**
     * 获取查询语句及参数
     *
     * @param t          查询条件实体
     * @param sqlConsist 表达式
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getSelectListSql(T t, SqlConsist sqlConsist) {
        SqlConsist condition = getSelectSql("select * from ", t, sqlConsist);
        return condition;
    }

    /**
     * 获取数量条数语句及参数
     *
     * @param sqlConsist 表达式
     * @param t
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getSelectCountSql(T t, SqlConsist sqlConsist) {
        SqlConsist condition = getSelectSql("select count(1) as counts from ", t, sqlConsist);
        return condition;
    }

    /**
     * 查询获取基础语句
     *
     * @param baseSql    查询sql语句
     * @param t          查询条件
     * @param sqlConsist 表达式
     * @param <T>
     * @return
     */
    private static <T> SqlConsist getSelectSql(String baseSql, T t, SqlConsist sqlConsist) {
        Class<?> aClass = t.getClass();//获取类对象
        String sql = "";
        TableAnn tableAnn = aClass.getAnnotation(TableAnn.class);
        String tableName = tableAnn.value();
        if (!tableName.equals("")) {
            sql = baseSql + tableName;
        }
        SqlConsist queryCondition = getQueryCondition(t, sqlConsist);
        //存在查询条件时
        if (queryCondition != null && queryCondition.getSql() != null
                && !queryCondition.getSql().equals("")) {
            sql += queryCondition.getSql();
        }
        queryCondition.setSql(sql);
        return queryCondition;
    }

    /**
     * 获取分页查询语句及参数
     *
     * @param t          查询条件实体
     * @param page       分页条件
     * @param sqlConsist 条件表达式、排序
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getSelectPageSql(T t, Page page, SqlConsist sqlConsist) {
        SqlConsist queryCondition = getSelectListSql(t, sqlConsist);
        String sql = queryCondition.getSql();
        //存在分页查询条件时
        if (page != null && page.getPageIndex() > 0) {
            sql += " limit ?,? ";
            queryCondition.setSql(sql);
            List values = queryCondition.getValues();
            if (values == null) values = new ArrayList();
            values.add(page.getPageSize() * (page.getPageIndex() - 1));
            values.add(page.getPageSize());
            queryCondition.setValues(values);
        }
        return queryCondition;
    }

    /**
     * 获取新增语句及参数
     *
     * @param t   新增数据
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getInsertSql(T t) {
        Class<?> aClass = t.getClass();//获取类对象
        String sql = "";
        TableAnn tableAnn = aClass.getAnnotation(TableAnn.class);
        String tableName = tableAnn.value();
        if (!tableName.equals("")) {
            sql = "insert into " + tableName;
        }
        SqlConsist queryCondition = getAddCondition(t);
        //新增数据
        if (queryCondition != null && queryCondition.getSql() != null
                && !queryCondition.getSql().equals("")) {
            sql += queryCondition.getSql();
            queryCondition.setSql(sql);
        }
        return queryCondition;
    }

    /**
     * @param t          更新数据
     * @param t1         更新条件
     * @param sqlConsist 更新条件表达式
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getUpdateSql(T t, T t1, SqlConsist sqlConsist) {
        Class<?> aClass = t.getClass();//获取类对象
        String sql = "";
        TableAnn tableAnn = aClass.getAnnotation(TableAnn.class);
        String tableName = tableAnn.value();
        if (!tableName.equals("")) {
            sql = "update " + tableName + " set ";
        }
        SqlConsist updateCondition = getUpdateCondition(t);
        //构造更新字段，不存在更新字段则sql不成立，返回null
        if (updateCondition != null && updateCondition.getSql() != null
                && !updateCondition.getSql().equals("")) {
            sql += updateCondition.getSql();
            updateCondition.setSql(sql);
        } else {
            return null;
        }
        //存在更新条件时
        SqlConsist queryCondition = getQueryCondition(t1, sqlConsist);
        if (queryCondition != null && queryCondition.getSql() != null
                && !queryCondition.getSql().equals("")) {
            sql += queryCondition.getSql();
            updateCondition.setSql(sql);
            //组合更新数据和更新条件参数
            updateCondition.getValues().addAll(queryCondition.getValues());
        }
        return updateCondition;
    }

    public static <T> SqlConsist getDeleteSql(T t, SqlConsist sqlConsist) {
        Class<?> aClass = t.getClass();//获取类对象
        String sql = "";
        TableAnn tableAnn = aClass.getAnnotation(TableAnn.class);
        String tableName = tableAnn.value();
        if (!tableName.equals("")) {
            sql = "delete from " + tableName;
        }
        //存在更新条件时
        SqlConsist queryCondition = getQueryCondition(t, sqlConsist);
        if (queryCondition != null && queryCondition.getSql() != null
                && !queryCondition.getSql().equals("")) {
            sql += queryCondition.getSql();
        }
        queryCondition.setSql(sql);
        return queryCondition;
    }

    /**
     * 获取更新字段
     *
     * @param t   更新数据
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getUpdateCondition(T t) {
        Class<?> aClass = t.getClass();//获取类对象
        List list = new ArrayList();//条件值
        StringBuilder sb = new StringBuilder();//条件表达式
        SqlConsist queryCondition = new SqlConsist();//条件结果
        try {
            Field[] fields = aClass.getDeclaredFields();
            String pk = "";//主键
            for (Field field : fields) {
                field.setAccessible(true);
                Object o = field.get(t);//获取字段值
                String dbField = toDbField(field.getName());//得到字段对应数据库字段
                TableAnn ann = field.getAnnotation(TableAnn.class);//获取主键字段
                if (ann != null) pk = ann.pk();
                if (o != null && !dbField.equals(pk)) { //主键不更新
                    sb.append(changeKeywords(dbField) + " = ? , ");//获取字段名称
                    list.add(o);
                }
            }
            String s = sb.toString();
            if (sb.length() > 0) {//如果存在条件的话
                String s1 = sb.substring(0, sb.lastIndexOf(","));
                queryCondition.setSql(s1);
                queryCondition.setValues(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryCondition;
    }

    /**
     * 获取新增字段
     *
     * @param t   新增数据
     * @param <T>
     * @return
     */
    public static <T> SqlConsist getAddCondition(T t) {
        Class<?> aClass = t.getClass();//获取类对象
        List list = new ArrayList();//条件值
        StringBuilder sb = new StringBuilder();//条件表达式
        StringBuilder sb1 = new StringBuilder();//条件表达式
        SqlConsist queryCondition = new SqlConsist();//条件结果
        try {
            Field[] fields = aClass.getDeclaredFields();
            String pk = "";//主键
            for (Field field : fields) {
                field.setAccessible(true);
                Object o = field.get(t);//获取字段值
                String dbField = toDbField(field.getName());//得到字段对应数据库字段
                TableAnn ann = field.getAnnotation(TableAnn.class);//获取主键字段
                if (ann != null) pk = ann.pk();
                if (o != null && !dbField.equals(pk)) { //主键不更新
                    //关键词过滤
                    sb.append(changeKeywords(dbField) + " , ");//获取字段名称
                    sb1.append(" ? , ");//获取字段名称
                    list.add(o);
                }
            }
            String s = sb.toString();
            String s1 = sb1.toString();
            if (sb.length() > 0) {//如果存在条件的话
                String ss = s.substring(0, s.lastIndexOf(","));
                String ss1 = s1.substring(0, s1.lastIndexOf(","));
                queryCondition.setSql("(" + ss + ") values (" + ss1 + ")");
                queryCondition.setValues(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryCondition;
    }

    /**
     * 关键词过滤
     * @param keywords
     * @return
     */
    private static String changeKeywords(String keywords){
        String kw=keywords;
        //针对mysql操作
        if(keywords.equalsIgnoreCase("desc")||keywords.equalsIgnoreCase("asc")){
            kw="`"+kw+"`";
        }
        return kw;
    }

    /**
     * 获取查询条件
     *
     * @param t
     * @param <T>
     * @param sqlConsist 条件表达式
     * @return
     */
    public static <T> SqlConsist getQueryCondition(T t, SqlConsist sqlConsist) {
        Class<?> aClass = t.getClass();//获取类对象
        List list = new ArrayList();//条件值
        StringBuilder sb = new StringBuilder();//条件表达式
        SqlConsist queryCondition = new SqlConsist();//条件结果
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(aClass);//获取class的bean对象
            PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor descriptor : descriptors) {
                Method readMethod = descriptor.getReadMethod();//获取get方法
                Object o = readMethod.invoke(t, null);
                if (o != null && !descriptor.getName().equals("class")) {
                    //匹配条件表达式
                    String exp = SqlExpression.EQ.getCode();
                    if (sqlConsist != null && sqlConsist.getExpression() != null) {
                        HashMap<String, String> hashMap = sqlConsist.getExpression();
                        for (Map.Entry entry : hashMap.entrySet()) {
                            if (entry.getKey().equals(descriptor.getName())) {
                                //条件表达式
                                exp = (String) entry.getValue();
                                //如果条件值单独传入
                                if(sqlConsist.getExpressionValue()!=null&&sqlConsist.getExpressionValue().get(entry.getKey())!=null){
                                    o=sqlConsist.getExpressionValue().get(entry.getKey());
                                }
                                break;
                            }
                        }
                    }
                    sb.append(toDbField(descriptor.getName()));//获取字段名称
                    switch (exp) {
                        case "eq":
                            sb.append(" =? and ");
                            list.add(o);
                            break;
                        case "like":
                            sb.append(" like ? and ");
                            list.add("%" + o + "%");
                            break;
                        case "in":
                            if(o instanceof java.util.List){
                                List list1= (List) o;
                                String listStr = StringUtils.join(list1.toArray(), ",");
                                sb.append(" in ("+listStr+") and ");
                            }
                            break;
                    }
                }
            }
            String s = sb.toString();
            if (sb.length() > 0) {//如果存在条件的话
                String s1 = sb.substring(0, sb.lastIndexOf("and"));
                queryCondition.setSql(" where " + s1 + getOrderSql(sqlConsist));

                queryCondition.setValues(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return queryCondition;
    }

    /**
     * 获得排序语句
     *
     * @param sqlConsist
     * @return
     */
    private static String getOrderSql(SqlConsist sqlConsist) {
        String orderSql = " ";
        //是否存在排序
        if (sqlConsist != null) {
            //正序
            if (sqlConsist.getOrderAsc() != null) {
                StringBuilder ascSql = new StringBuilder();
                ascSql.append(" order by ");
                for (String asc : sqlConsist.getOrderAsc()) {
                    ascSql.append(toDbField(asc) + ",");
                }
                orderSql = ascSql.toString().substring(0, ascSql.toString().lastIndexOf(",")) + " asc ";
            }
            //倒序
            if (sqlConsist.getOrderDesc() != null) {
                StringBuilder descSql = new StringBuilder();
                if (orderSql.equals("")) {
                    descSql.append(" order by ");
                } else {
                    descSql.append(" , ");
                }
                for (String asc : sqlConsist.getOrderAsc()) {
                    descSql.append(toDbField(asc) + ",");
                }
                orderSql += descSql.toString().substring(0, descSql.toString().lastIndexOf(",")) + " desc ";
            }
        }
        return orderSql;
    }

    /**
     * 实体字段转换为数据库字段
     *
     * @param entityField 实体字段
     * @return
     */
    public static String toDbField(String entityField) {
        String dbField = "";
        StringBuilder sb = new StringBuilder();
        sb.append(entityField);
        //获得每个排序字段的所有字母
        char[] chars = entityField.toCharArray();
        int num = 0, index = 0;
        //循环每个单词的字母数组，如果是大写的就进行转换
        for (char aChar : chars) {
            if (Character.isUpperCase(aChar)) {
//                    int i1 = orders[i].indexOf(aChar) + num;// 因为每次循环变更后都多了一个下划线
                int i1 = index + num;// 因为每次循环变更后都多了一个下划线
                sb.replace(i1, i1 + 1, "_" + String.valueOf(Character.toLowerCase(aChar)));
                num++;
            }
            index++;
        }
        dbField = sb.toString();
        return dbField;
    }
}

注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Author: JSONLiu
 * @Description:table注解
 * @Date Created in 2021-08-24 20:10
 * @Modified By:
 */
@Target({ElementType.TYPE,ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableAnn {
    String value() default "";
    String pk() default "";
}

枚举

/**
 * @Author: JSONLiu
 * @Description:sql表达式
 * @Date Created in 2021-09-11 17:44
 * @Modified By:
 */
public enum SqlExpression {
    EQ("eq"),   //等于
    LIKE("like"),
    GT("gt"),   //大于
    LT("lt"),   //小于
    IN("in");

    private String code;
    private SqlExpression(String code){
        this.code=code;
    }

    public String getCode() {
        return code;
    }

}

实体类

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * @Author: JSONLiu
 * @Description: sql语句相关
 * @Date Created in 2021-08-17 21:58
 * @Modified By:
 */
public class SqlConsist {
    //sql语句
    private String sql;
    //参数值
    private List values;
    //查询条件表达式
    private HashMap<String, String> expression;
    //查询条件表达式值
    private HashMap<String, Object> expressionValue;
    //正排序数组
    private String[] orderAsc;
    //倒排序数组
    private String[] orderDesc;

    @Override
    public String toString() {
        return "SqlConsist{" +
                "sql='" + sql + '\'' +
                ", values=" + values +
                ", expression=" + expression +
                ", expressionValue=" + expressionValue +
                ", orderAsc=" + Arrays.toString(orderAsc) +
                ", orderDesc=" + Arrays.toString(orderDesc) +
                '}';
    }

    public SqlConsist() {
    }

    public SqlConsist(String sql, List values, HashMap<String, String> expression, HashMap<String, Object> expressionValue, String[] orderAsc, String[] orderDesc) {
        this.sql = sql;
        this.values = values;
        this.expression = expression;
        this.expressionValue = expressionValue;
        this.orderAsc = orderAsc;
        this.orderDesc = orderDesc;
    }

    public HashMap<String, Object> getExpressionValue() {
        return expressionValue;
    }

    public void setExpressionValue(HashMap<String, Object> expressionValue) {
        this.expressionValue = expressionValue;
    }

    public String getSql() {
        return sql;
    }

    public void setSql(String sql) {
        this.sql = sql;
    }

    public List getValues() {
        return values;
    }

    public void setValues(List values) {
        this.values = values;
    }

    public HashMap<String, String> getExpression() {
        return expression;
    }

    public void setExpression(HashMap<String, String> expression) {
        this.expression = expression;
    }

    public String[] getOrderAsc() {
        return orderAsc;
    }

    public void setOrderAsc(String[] orderAsc) {
        this.orderAsc = orderAsc;
    }

    public String[] getOrderDesc() {
        return orderDesc;
    }

    public void setOrderDesc(String[] orderDesc) {
        this.orderDesc = orderDesc;
    }
}

import java.util.List;
import java.util.Objects;

/**
 * @Author: JSONLiu
 * @Description:分页
 * @Date Created in 2021-09-07 20:29
 * @Modified By:
 */
public class Page<T> {
    private Integer pageSize=10;//每页条数
    private Integer pageIndex=1;//当前页数
    private Integer pageTotal;//总页数
    private Integer totalSize;//总条数
    private List<T> data;//数据

    public Page() {
    }

    @Override
    public String toString() {
        return "{" +
                "pageSize=" + pageSize +
                ", pageIndex=" + pageIndex +
                ", pageTotal=" + pageTotal +
                ", totalSize=" + totalSize +
                ", data=" + data +
                '}';
    }

    public Page(Integer pageSize, Integer pageIndex, Integer pageTotal, Integer totalSize, List<T> data) {
        this.pageSize = pageSize;
        this.pageIndex = pageIndex;
        this.pageTotal = pageTotal;
        this.totalSize = totalSize;
        this.data = data;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Page<?> page = (Page<?>) o;
        return Objects.equals(pageSize, page.pageSize) &&
                Objects.equals(pageIndex, page.pageIndex) &&
                Objects.equals(pageTotal, page.pageTotal) &&
                Objects.equals(totalSize, page.totalSize) &&
                Objects.equals(data, page.data);
    }


    @Override
    public int hashCode() {
        return Objects.hash(pageSize, pageIndex, pageTotal, totalSize, data);
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getPageIndex() {
        return pageIndex;
    }

    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }

    public Integer getPageTotal() {
        return totalSize/pageSize+(totalSize%pageSize==0?0:1);
    }

    public void setPageTotal(Integer pageTotal) {
        this.pageTotal = pageTotal;
    }

    public Integer getTotalSize() {
        return totalSize;
    }

    public void setTotalSize(Integer totalSize) {
        this.totalSize = totalSize;
    }
}