JdbcTemplate封装

已于 2023-10-19 14:25:23 修改
阅读量1.3k 收藏 5
点赞数
分类专栏： java基础 文章标签： java spring mysql
于 2022-09-16 10:29:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_37743758/article/details/126885400
版权

 起初，因为某个数据服务项目需要作为jar包引用到其他项目中，且其他项目中一般采用JdbcTemplate方式，所以这个数据服务项目中采用JdbcTemplate方式去调用数据库。

但在开发过程中，JdbcTemplate的写法需要写大量Sql，代码不美观，开发也比较麻烦，所以通过Java注解和反射的方法，开发出一个独立的功能包，让开发人员可以用过类似MybatisPlus的Lambda表达式的语言调用JdbcTemplate。

GitHub地址​​​​​​​

1、创建注解，用来标注类的属性对应的表字段

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

@Target(ElementType.FIELD)//标记注解的使用范围
@Retention(RetentionPolicy.RUNTIME)//标记主键的有效期
public @interface FieldName {
    String value();//记录数据库表的字段名称
}

2、用来保存单个筛选条件，相当于单条where语句

package com.aze.entity;

import com.aze.enums.FilterOperatorEnum;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class DataFilterElement {
    private FilterOperatorEnum filterOperatorEnum;
    private String fieldName;
    private Object val;
}

2、DateFilter 类似where语句+order语句，包含等于、大于、大于等于、小于、小于等于、模糊查询、正排、倒序

import cn.hutool.core.collection.CollectionUtil;
import com.zmxt.station.dataService.utils.DataFilterElement;
import lombok.Getter;
import lombok.Setter;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Getter
@Setter
public class DataFilter<T> {
    private List<DataFilterElement> filterElementList = new ArrayList<>();
    private DataOrderElement dataOrderElement = new DataOrderElement();
    private Class cls;

    public DataFilter(Class<T> cls) {
        this.cls = cls;
    }

    public void eq(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_EQUAL, fieldName, val));
    }

    public void lt(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_LT, fieldName, val));
    }

    public void gt(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_GT, fieldName, val));
    }

    public void not(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_NOT, fieldName, val));
    }

    public void ge(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_GE, fieldName, val));
    }

    public void le(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_LE, fieldName, val));
    }

    public void like(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_LIKE, fieldName, val));
    }

    public void leftLike(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_LEFT_LIKE, fieldName, val));
    }

    public void rightLike(String fieldName, Object val) {
        filterElementList.add(new DataFilterElement(FilterOperatorEnum.OPERATOR_RIGHT_LIKE, fieldName, val));
    }

    public void orderByAsc(String fieldNames) {
        if (null != dataOrderElement.getOrderOperatorEnum() || CollectionUtil.isNotEmpty(dataOrderElement.getFieldNames())) {
            throw new RuntimeException("不支持多次排序");
        }
        this.dataOrderElement.setOrderOperatorEnum(OrderOperatorEnum.OPERATOR_FS);
        this.dataOrderElement.setFieldNames(Arrays.asList(fieldNames.split(",")));
    }

    public void orderByDesc(String fieldNames) {
        if (null != dataOrderElement.getOrderOperatorEnum() || CollectionUtil.isNotEmpty(dataOrderElement.getFieldNames())) {
            throw new RuntimeException("不支持多次排序");
        }
        this.dataOrderElement.setOrderOperatorEnum(OrderOperatorEnum.OPERATOR_RS);
        this.dataOrderElement.setFieldNames(Arrays.asList(fieldNames.split(",")));
    }

}

3、DataOrderElement 保存多条排序语句

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.List;

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class DataOrderElement {
    private OrderOperatorEnum orderOperatorEnum;
    private List<String> fieldNames;
}

4、

import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
public class FilterResult {
    private String filterSql;
    private List<Object> filterParams;
}

5、

import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
public class InsertResult {
    private String insertSql;
    private List<Object> insertParams;
}

6、

import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Getter
@Setter
public class ModifyResult {
    private String modifySql;
    private List<Object> modifyParams;
}

7、

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

import static com.zmxt.station.dataService.myJdbcTemplate.JdbcTemplateUtil.*;

@Component
public class MyJdbcTemplate {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据过滤条件和排序条件查询数据
     *
     * @param dataFilter
     * @param <T>
     * @return
     */
    public <T> List<T> list(DataFilter<T> dataFilter) {
        String listSql = getListSql(dataFilter);
        Object[] params = getFilterResult(dataFilter).getFilterParams().toArray();
        return jdbcTemplate.query(listSql, new BeanPropertyRowMapper<T>(), params);
    }

    /**
     * 根据过滤条件和新值修改数据
     *
     * @param t
     * @param dataFilter
     * @param <T>
     * @return
     */
    public <T> int update(T t, DataFilter<T> dataFilter) {
        String updateSql = getUpdateSql(t, dataFilter);
        List<Object> params = new ArrayList<>();
        params.addAll(getModifyResult(t).getModifyParams());
        params.addAll(getFilterResult(dataFilter).getFilterParams());
        return jdbcTemplate.update(updateSql, params.toArray());
    }

    /**
     * 插入对象到数据库
     * @param t
     * @return
     * @param <T>
     */
    public <T> int insert(T t){
        InsertResult insertResult = getInsertResult(t);
        return jdbcTemplate.update(insertResult.getInsertSql(),insertResult.getInsertParams().toArray());
    }

    /**
     * 批量插入对象到数据库
     * @param tList
     * @return
     * @param <T>
     */
    public <T> int batchInsert(List<T> tList){
        InsertResult insertResult = getBatchInsertResult(tList);
        return jdbcTemplate.update(insertResult.getInsertSql(),insertResult.getInsertParams().toArray());
    }

    /**
     * 批量删除对象
     * @param dataFilter
     * @return
     * @param <T>
     */
    public  <T> int delete(DataFilter<T> dataFilter){
        FilterResult filterResult = getFilterResult(dataFilter);
        String deleteSql = getDeleteSql(dataFilter)+filterResult.getFilterSql();
        return jdbcTemplate.update(deleteSql,filterResult.getFilterParams());
    }

}

8、

/**
 * 过滤符号enum
 */
public enum FilterOperatorEnum {
    OPERATOR_EQUAL("=", "等于"),
    //    OPERATOR_IN("In","包含"),
    OPERATOR_LT("<", "小于"),
    OPERATOR_GT(">", "大于"),
    OPERATOR_NOT("!=", "不等于"),
    OPERATOR_GE(">=", "大于等于"),
    OPERATOR_LE("<=", "小于等于"),
    OPERATOR_LEFT_LIKE("leftLike", "左模糊查询"),
    OPERATOR_RIGHT_LIKE("rightLike", "右模糊查询"),
    OPERATOR_LIKE("like", "模糊查询");
    private final String code;
    private final String description;

    FilterOperatorEnum(String code, String description) {
        this.code = code;
        this.description = description;
    }

    public String getCode() {
        return code;
    }
}

9、

/**
 * 过滤符号enum
 */
public enum OrderOperatorEnum {
    OPERATOR_FS("asc", "正向排序"),// Forward sort
    OPERATOR_RS("desc", "反向排序");// Reverse sort
    private final String code;
    private final String description;

    OrderOperatorEnum(String code, String description) {
        this.code = code;
        this.description = description;
    }

    public String getCode() {
        return code;
    }
}

10、

import com.baomidou.mybatisplus.annotation.TableName;
import com.sun.deploy.util.StringUtils;
import com.zmxt.station.dataService.utils.DataFilterElement;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ReflectionUtils;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import static com.zmxt.station.common.utils.StringUtil.toUnderlineCase;

public class JdbcTemplateUtil {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据类获取数据库表字段集合
     *
     * @param cls
     * @param <T>
     * @return
     */
    public static <T> List<String> getFieldList(Class<T> cls) {
        try {
            List<String> fieldNameList = new ArrayList<>();
            T obj = cls.newInstance();
            Field[] fieldArr = cls.getDeclaredFields();
            for (Field f : fieldArr) {
                String name = f.getName();
                FieldName fieldName = f.getAnnotation(FieldName.class);
                // 有FieldName注解取注解中的字段名，默认驼峰转下划线
                if (null != fieldName) {
                    name = fieldName.value();
                } else {
                    name = toUnderlineCase(name);
                }
                fieldNameList.add(name);
            }
            return fieldNameList;
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取实体类所有属性名称
     *
     * @param cls
     * @param <T>
     * @return 所有属性名称
     */
    public static <T> List<String> getProperties(Class<T> cls) {
        List<String> properties = new ArrayList<>();
        Field[] fieldArr = cls.getDeclaredFields();
        for (Field f : fieldArr) {
            String name = f.getName();
            properties.add(name);
        }
        return properties;
    }

    /**
     * 获取实体类属性类型列表
     *
     * @param cls
     * @param <T>
     * @return 所有属性类型
     */
    public static <T> List<Class> getClassList(Class<T> cls) {
        List<Class> clsList = new ArrayList<>();
        Field[] fieldArr = cls.getDeclaredFields();
        for (Field f : fieldArr) {
            Class c = f.getType();
            clsList.add(c);
        }
        return clsList;
    }

    /**
     * 根据class的TableName注解获取表名
     *
     * @param cls
     * @param <T>
     * @return 表名称
     */
    public static <T> String getTableName(Class<T> cls) {
        TableName tn = cls.getAnnotation(TableName.class);
        if (null == tn) {
            throw new RuntimeException("TableName注解不能为空");
        }
        return tn.value();
    }

    /**
     * 拼接select语句
     *
     * @param cls
     * @param <T>
     * @return select 字段1,字段2 from 表名
     */

    public static <T> String getSelectSql(Class<T> cls) {
        List<String> fieldList = getFieldList(cls);
        String selectSql = "SELECT %s from %s";
        return String.format(selectSql, StringUtils.join(fieldList, ","), getTableName(cls));
    }

    /**
     * 拼接update语句、获取update参数
     *
     * @param t
     * @param <T>
     * @return
     */
    public static <T> ModifyResult getModifyResult(T t) {
        ModifyResult modifyResult = new ModifyResult();
        Class cls = t.getClass();
        Field[] fields = cls.getDeclaredFields();
        StringBuffer sb = new StringBuffer();
        List<Object> modifyParams = new ArrayList<>();
        sb.append("update " + getTableName(cls) + " set ");
        for (Field f : fields) {
            try {
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), cls);
                Method getMethod = pd.getReadMethod();
                Object valObj = ReflectionUtils.invokeMethod(getMethod, t);
                if (null != valObj) {
                    String name = f.getName();
                    FieldName fieldName = f.getAnnotation(FieldName.class);
                    if (null != fieldName) {
                        name = fieldName.value();
                    } else {
                        name = toUnderlineCase(name);
                    }
                    sb.append(name + "=?,");
                    modifyParams.add(valObj);
                }
            } catch (IntrospectionException e) {
                throw new RuntimeException(e);
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        modifyResult.setModifySql(sb.toString());
        modifyResult.setModifyParams(modifyParams);
        return modifyResult;
    }

    /**
     * 拼接insert语句,获取insert参数
     *
     * @param t
     * @param <T>
     * @return
     */
    public static <T> InsertResult getInsertResult(T t) {
        InsertResult insertResult = new InsertResult();
        Class cls = t.getClass();
        Field[] fields = cls.getDeclaredFields();
        StringBuffer sb = new StringBuffer();
        List<Object> inserParams = new ArrayList<>();
        List<String> fieldList = getFieldList(cls);
        sb.append("insert into " + getTableName(cls) + "(" + StringUtils.join(fieldList, ",") + ")");
        sb.append("values (");
        for (Field f : fields) {
            try {
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), cls);
                Method getMethod = pd.getReadMethod();
                Object valObj = ReflectionUtils.invokeMethod(getMethod, t);
                inserParams.add(valObj);
                sb.append("?,");
            } catch (IntrospectionException e) {
                throw new RuntimeException(e);
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        insertResult.setInsertSql(sb.toString());
        insertResult.setInsertParams(inserParams);
        return insertResult;
    }

    public static <T> InsertResult getBatchInsertResult(List<T> tList) {
        InsertResult insertResult = new InsertResult();
        Class cls = tList.get(0).getClass();
        Field[] fields = cls.getDeclaredFields();
        StringBuffer sb = new StringBuffer();
        List<Object> inserParams = new ArrayList<>();
        List<String> fieldList = getFieldList(cls);
        sb.append("insert into " + getTableName(cls) + "(" + StringUtils.join(fieldList, ",") + ")");
        sb.append("values ");
        for (T t : tList) {
            sb.append("(");
            for (Field f : fields) {
                try {
                    PropertyDescriptor pd = new PropertyDescriptor(f.getName(), cls);
                    Method getMethod = pd.getReadMethod();
                    Object valObj = ReflectionUtils.invokeMethod(getMethod, t);
                    inserParams.add(valObj);
                    sb.append("?,");
                } catch (IntrospectionException e) {
                    throw new RuntimeException(e);
                }
            }
            sb.deleteCharAt(sb.length() - 1);
            sb.append("),");
        }
        sb.deleteCharAt(sb.length() - 1);
        insertResult.setInsertSql(sb.toString());
        insertResult.setInsertParams(inserParams);
        return insertResult;
    }


    /**
     * 拼接where语句，获取where参数
     *
     * @param dataFilter
     * @return
     */
    public static FilterResult getFilterResult(DataFilter dataFilter) {
        List<DataFilterElement> filterElementList = dataFilter.getFilterElementList();
        FilterResult fr = new FilterResult();
        StringBuffer sb = new StringBuffer();
        List<Object> objects = new ArrayList<>();
        filterElementList.forEach(e -> {
            Field field = null;
            try {
                field = dataFilter.getCls().getDeclaredField(e.getFieldName());
            } catch (NoSuchFieldException ex) {
                throw new RuntimeException(ex);
            }
            // 获取字段类型
            Class fieldCls = field.getType();
            if (sb.toString().equals("")) {
                sb.append(" where ");
            } else {
                sb.append(" and ");
            }
            switch (e.getFilterOperatorEnum()) {
                case OPERATOR_LIKE:
                    sb.append(" ").append(toUnderlineCase(e.getFieldName())).append(" like '%").append("?%' ");
                    break;
                case OPERATOR_LEFT_LIKE:
                    sb.append(" ").append(toUnderlineCase(e.getFieldName())).append(" like '").append("?%' ");
                    break;
                case OPERATOR_RIGHT_LIKE:
                    sb.append(" ").append(toUnderlineCase(e.getFieldName())).append(" like '%").append("?' ");
                    break;
                default:
                    sb.append(" ").append(toUnderlineCase(e.getFieldName())).append(" ").append(e.getFilterOperatorEnum().getCode()).append(" ? ");
                    break;
            }
            objects.add(e.getVal());
        });
        fr.setFilterSql(sb.toString());
        fr.setFilterParams(objects);
        return fr;
    }

    /**
     * 拼接排序语句，获取where参数
     *
     * @param dataFilter
     * @return
     */
    public static String getOrderSql(DataFilter dataFilter) {
        DataOrderElement orderElement = dataFilter.getDataOrderElement();
        StringBuffer sb = new StringBuffer();
        sb.append(" order by ");
        orderElement.getFieldNames().forEach(fieldName -> {
            try {
                sb.append(dataFilter.getCls().getDeclaredField(fieldName)).append(",");
            } catch (NoSuchFieldException e) {
                throw new RuntimeException(e);
            }
        });
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" ").append(dataFilter.getDataOrderElement().getOrderOperatorEnum().getCode()).append(" ");
        return sb.toString();
    }

    /**
     * 获取完整对象集合查询语句
     *
     * @param dataFilter
     * @return
     */
    public static String getListSql(DataFilter dataFilter) {
        String selectSql = getSelectSql(dataFilter.getCls());
        FilterResult filterResult = getFilterResult(dataFilter);
        String orderSql = getOrderSql(dataFilter);
        return selectSql + filterResult.getFilterSql() + orderSql;
    }

    /**
     * 获取完整修改对象语句
     *
     * @param t
     * @param dataFilter
     * @param <T>
     * @return
     */
    public static <T> String getUpdateSql(T t, DataFilter dataFilter) {
        ModifyResult modifyResult = getModifyResult(t);
        FilterResult filterResult = getFilterResult((dataFilter));
        return modifyResult.getModifySql() + filterResult.getFilterSql();
    }

    /**
     * 拼接删除sql
     * @param dataFilter
     * @return
     * @param <T>
     */
    public static <T> String getDeleteSql(DataFilter dataFilter){
        StringBuffer sb = new StringBuffer();
        sb.append("delete from "+getTableName(dataFilter.getCls()));
        return sb.toString();
    }



}

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值