All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.tmsps.ne4spring.base.BaseRDJCSQLService Maven / Gradle / Ivy

Go to download

NE For Spring is good :)

There is a newer version: 999.0.0.0
Show newest version
package com.tmsps.ne4spring.base;

import com.alibaba.fastjson.JSON;
import com.tmsps.ne4spring.exception.NEServiceException;
import com.tmsps.ne4spring.orm.ClassUtil;
import com.tmsps.ne4spring.orm.RDJCSQLUtil;
import com.tmsps.ne4spring.orm.ORMUtil;
import com.tmsps.ne4spring.orm.model.DataModel;
import com.tmsps.ne4spring.orm.param.NeParamList;
import com.tmsps.ne4spring.orm.param.NeParamTools;
import com.tmsps.ne4spring.page.Page;
import com.tmsps.ne4spring.page.PageVo;
import com.tmsps.ne4spring.utils.ChkUtil;
import com.tmsps.ne4spring.utils.GenerateLongUtil;
import com.tmsps.ne4spring.utils.GenerateUtil;
import com.tmsps.ne4spring.utils.StrUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Service
public class BaseRDJCSQLService implements IBaseService  {
    @Autowired
    private JdbcTemplate jt;

    @Override
    public JdbcTemplate getJdbcTemplate() {
        return this.jt;
    }

    @Override
    public int saveObj(DataModel model) {
        log.debug("save the model" + model.toJsonString());
        if (ChkUtil.isNull(model)) {
            log.error("保存的对象不能为空");
            throw new NEServiceException("保存的对象不能为空");
        }

        // 获取对象中ID属性字段
        Field idField = ClassUtil.getIdField(model.getClass());
        // 获取ID的值
        Object idVal = ClassUtil.getClassVal(idField, model);
        // 如果ID字段为空，则生成一个主键
        if (ChkUtil.isNull(idVal)) {
            if (idField.getType() == String.class) {
                idVal = GenerateUtil.getBase58ID();
                ClassUtil.setClassVal(idField, model, idVal);
            } else if (idField.getType() == long.class || idField.getType() == Long.class) {
                idVal = GenerateLongUtil.getInstance().nextId();
                ClassUtil.setClassVal(idField, model, idVal);
            }
        }
        // 获取Bean中的属性值
        final List vals = ClassUtil.getValuesPar(model);
        // 根据Bean生成插入语句
        String sql = RDJCSQLUtil.getInsSQL(model.getClass());
        log.debug(sql);
        return jt.update(sql, vals.toArray());
    }

    @Override
    public DataModel saveObj(DataModel model, boolean sync) {
        log.debug("save the model" + model.toJsonString());
        this.saveObj(model);
        if (sync) {
            model = findById(model.getPK(), model.getClass());
        }
        return model;
    }

    // 模版保存,只保存字段不为空的属性
    @Override
    public int saveTemplateObj(DataModel model) {
        log.debug("save the model by template:{}", model.toJsonString());
        if (ChkUtil.isNull(model)) {
            log.error("保存的对象不能为空");
            throw new NEServiceException("保存的对象不能为空");
        }
        // 获取对象中ID属性字段
        Field idField = ClassUtil.getIdField(model.getClass());
        // 获取ID的值
        Object idVal = ClassUtil.getClassVal(idField, model);
        // 如果ID字段为空，则生成一个主键
        if (ChkUtil.isNull(idVal)) {
            if (idField.getType() == String.class) {
                idVal = GenerateUtil.getBase58ID();
                ClassUtil.setClassVal(idField, model, idVal);
            } else if (idField.getType() == long.class || idField.getType() == Long.class) {
                idVal = GenerateLongUtil.getInstance().nextId();
                ClassUtil.setClassVal(idField, model, idVal);
            }
        }

        LinkedHashMap modelKeyVal = ClassUtil.getClassKeyValNotNull(model);
        // 获取属性名称
        final List propertys = ClassUtil.getKeyList(modelKeyVal);
        // 获取属性值
        final List vals = ClassUtil.getValList(modelKeyVal);
        // 根据Bean生成插入语句
        String sql = RDJCSQLUtil.getTemplateInsSQL(ClassUtil.getTableName(model.getClass()), propertys);
        log.debug(sql);
        return jt.update(sql, vals.toArray());
    }

    @Override
    public DataModel saveTemplateObj(DataModel model, boolean sync) {
        log.debug("save the model by template:{} with {}", model.toJsonString(), String.valueOf(sync));
        this.saveTemplateObj(model);
        if (sync) {
            model = this.findById(model.getPK(), model.getClass());
        }
        return model;
    }

    @Override
    @Transactional
    public void saveObjs(List objs) {
        if (ChkUtil.listIsNull(objs)) {
            log.error("保存的对象集合不能为空");
            throw new NEServiceException("保存的对象集合不能为空");
        }
        objs.forEach(model -> {
            this.saveObj(model);
        });
    }

    @Override
    @Deprecated
    /**
     * since 1.4.8 use findById(idVal, clazz);
     */
    public  T findObjById(Object idVal, Class clazz) {
        return findById(idVal, clazz);
    }

    @Override
    public  T findById(Object idVal, Class clazz) {
        // 主键为空，无法查询，返回null
        if (ChkUtil.isNull(idVal)) {
            log.warn("主键为null,无法查询,返回null");
            return null;
        }
        String sql = RDJCSQLUtil.getSelectSQL(clazz, false);
        log.debug(sql);
        return jt.query(sql, new Object[] { idVal }, new ResultSetExtractor() {
            @SuppressWarnings("unchecked")
            public T extractData(ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next()) {
                    T obj = ((T) RDJCSQLUtil.fillPojoByResultSet(rs, clazz));
                    // 只取第一个
                    return (T) obj;
                }
                return null;
            }
        });
    }

    @Override
    public Map findById(String tableName, String pkName, String pkVal) {
        List> list = jt.queryForList(RDJCSQLUtil.getSelectByIdSQL(tableName, pkName), pkVal);
        return list.size() == 1 ? list.get(0) : null;
    }

    @Override
    public Map findById(String tableName, String pkVal) {
        return jt.queryForMap(RDJCSQLUtil.getSelectByIdSQL(tableName), pkVal);
    }

    @Override
    public List> findList(String sql, Object[] vals) {
        log.debug("{},{}", sql, JSON.toJSONString(vals));
        return jt.queryForList(sql, vals);
    }

    @Override
    public Map findObj(String sql, Object[] vals) {
        log.debug("{},{}", sql, JSON.toJSONString(vals));
        List> list = this.findList(sql, vals);
        if (ChkUtil.listIsNotNull(list)) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public Map findObj(String sql) {
        List> list = findList(sql);
        if (ChkUtil.listIsNotNull(list)) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public  T findObj(String sql, Class clazz) {
        List> list = findList(sql);
        if (ChkUtil.listIsNotNull(list)) {
            return (T) ORMUtil.fillMapToBean(clazz, list.get(0));
        } else {
            return null;
        }
    }

    @Override
    public  T findForObj(String sql, Class clazz) {
        return findObj(sql, clazz);
    }

    @Override
    public List> findList(String sql) {
        log.debug(sql);
        return jt.queryForList(sql);
    }

    @Override
    public  List findList(String sql, Class clazz) {
        List list = new ArrayList();
        jt.query(sql, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                T t = ORMUtil.fillResultSetToBean(rs, clazz);
                list.add(t);
                return t;
            }
        });
        return list;
    }

    @Override
    public  T findObj(String sql, Object[] vals, Class modelClass) {
        return ORMUtil.fillMapToBean(modelClass, findObj(sql, vals));
    }

    @Override
    public int deleteObjById(Object id, Class clazz) {
        final String sql = RDJCSQLUtil.getDelRealSQL(clazz);
        return jt.update(sql, id);
    }

    @Override
    public int deleteByID(String pkColumn, Object id, String tableName) {
        return jt.update(RDJCSQLUtil.getDelRealSQL(tableName, pkColumn), id);
    }

    @Override
    public int updateObj(DataModel obj) {
        return this.updateObjT(obj);
    }

    @Override
    public int updateObjT(DataModel obj) {
        if (ChkUtil.isNull(obj)) {
            log.error("update faile , the obj can not null !");
            return 0;
        } else {
            String sql = RDJCSQLUtil.getUpdateSQL(obj.getClass());
            final LinkedHashMap clsky = ClassUtil.getClassKeyValWithoutPk(obj);
            List vals = new ArrayList();
            clsky.entrySet().forEach(entry -> {
                vals.add(entry.getValue());
            });
            Object pkVal = ClassUtil.getClassVal(ClassUtil.getIdField(obj.getClass()), obj);
            vals.add(pkVal);
            log.debug("sql:{} and paras is {}", sql, vals.toString());
            return jt.update(sql, vals.toArray());
        }
    }

    @Override
    public int updateChangeObj(DataModel obj) {
        return updateTemplateObj(obj);
    }

    @Override
    public int updateTemplateObj(DataModel obj) {
        if (ChkUtil.isNull(obj)) {
            log.error("修改失败！对象不允许为空！");
            return 0;
        } else {
            String sql = RDJCSQLUtil.getChangeUpdateSQL(obj);
            final LinkedHashMap clsky = ClassUtil.getClassKeyValNotNull(obj);
            List vals = new ArrayList();
            clsky.entrySet().forEach(entry -> {
                vals.add(entry.getValue());
            });

            String pk = ClassUtil.getIdField(obj.getClass()).getName();
            Object pkVal = clsky.get(pk);
            vals.remove(pkVal);
            vals.add(pkVal);
            log.debug("sql:{} and paras is {}", sql, vals.toString());
            return jt.update(sql, vals.toArray());
        }
    }

    @Override
    public List> findList(String sql, String sql_cnt, Object[] vals, Page page) {
        log.debug("{},{}", sql, sql_cnt);
        log.debug("{},{}", JSON.toJSONString(vals), JSON.toJSONString(page));
        List> ret = null;
        int cnt = jt.queryForObject(sql_cnt, vals, Integer.class);
        page.setTotalRow(cnt);
        page.setTotalPage((page.getTotalRow() - 1) / page.getPageSize() + 1);
        if (page.getPageNumber() > page.getTotalPage()) {
            page.setPageNumber(page.getTotalPage());
        }
        if (page.getPageNumber() <= 0) {
            page.setPageNumber(1);
        }
        // 分页
        sql += " limit :start,:pageSize";
        sql = sql.replace(":start", page.getPageSize() * (page.getPageNumber() - 1) + "");
        sql = sql.replace(":pageSize", page.getPageSize() + "");
        ret = jt.queryForList(sql, vals);
        return ret;
    }

    @Override
    public List> findList(String sql, Object[] vals, Page page) {
        return findList(sql, vals, null, page);
    }

    @Override
    public List> findList(String sql, Object[] vals, Map sort_params, Page page) {
        String sql_cnt = RDJCSQLUtil.getCntSql(sql);

        // 排序
        if (ChkUtil.isNotNull(sort_params)) {
            StringBuilder sb = new StringBuilder();
            for (String key : sort_params.keySet()) {
                if ("table".equals(key)) {
                    continue;
                }
                String table = sort_params.get("table");
                table = ChkUtil.isNull(table) ? "" : "\"" + table + "\"" + ".";
                sb.append(" ").append(table).append("\"").append(key).append("\"").append(" ").append(sort_params.get(key)).append(",");
            }
            String ob = "order by";
            int orderIndex = sql.toLowerCase().indexOf(ob);
            if (orderIndex == -1) {
                sb.deleteCharAt(sb.length() - 1);
                sql += ob + sb.toString();
            }
            /*
             * else { System.out.println(sql); sql =
             * sql.substring(0,orderIndex); sb.deleteCharAt(sb.length() - 1);
             * sql += ob + sb.toString(); //sql = new
             * StringBuilder(sql).insert(orderIndex + ob.length(),
             * sb).toString(); }
             */
        } // #if a
        return findList(sql, sql_cnt, vals, page);
    }

    @Override
    public List> findList(String sql, Page page) {
        return findList(sql, null, page);
    }

    @Override
    public int updObj(Class clazz, Map parm, Map whereparm) {
        StringBuffer sb = new StringBuffer(" update ");
        sb.append(" \"").append(ClassUtil.getClassName(clazz)).append("\" ");
        sb.append(" set ");
        List cxparm = new ArrayList();
        int i = 0;
        for (String key : parm.keySet()) {
            if (i > 0) {
                sb.append(",");
            }
            sb.append(" \"").append(key).append("\" = ? ");
            i++;
            cxparm.add(parm.get(key));
        }
        sb.append(" where 1=1 ");
        if (ChkUtil.isNotNull(whereparm)) {
            for (String key : whereparm.keySet()) {
                sb.append(" and \"").append(key).append("\" = ?  ");
                cxparm.add(whereparm.get(key));
            }
        }
        return this.jt.update(sb.toString(), cxparm.toArray());
    }

    @Override
    public Map getMap(String key, Object val) {
        return getMap(new String[] { key }, new Object[] { val });
    }

    @Override
    public Map> getSearchMap() {
        Map> cxparms = new HashMap>();
        cxparms.put("cname", new ArrayList());
        cxparms.put("cwhere", new ArrayList());
        cxparms.put("cval", new ArrayList());
        return cxparms;
    }

    @Override
    public Map getMap(String[] key, Object[] val) {
        Map mapobj = new HashMap();
        for (int i = 0; i < key.length; i++) {
            mapobj.put(key[i], val[i]);
        }
        return mapobj;
    }

    @Override
    public Page pageinate(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) {
        if (pageNumber < 1 || pageSize < 1) {
            log.error("当前页面页数和页面记录展示条数不能小于1");
            return new Page(pageNumber, pageSize);
        }
        long totalRow = 0;
        int totalPage = 0;
        if (!StrUtil.notNull(paras)) {
            return pageinate(pageNumber, pageSize, select, sqlExceptSelect);
        }
        totalRow = jt.queryForObject("SELECT COUNT(*) " + sqlExceptSelect, paras, Long.class);
        if (totalRow % pageSize == 0) {
            totalPage = (int) (totalRow / pageSize);
        } else {
            totalPage = (int) (totalRow / pageSize) + 1;
        }
        log.debug("The TotalPage is :" + totalPage);
        String sql = RDJCSQLUtil.getPageSQL(select, sqlExceptSelect);
        log.debug("SQL: " + sql);
        // 判断当前页面是否大于最大页面
        pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
        List objs = new ArrayList(Arrays.asList(paras));
        objs.add((pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize);
        objs.add(pageSize);

        List> result = jt.queryForList(sql, objs.toArray());
        return new Page(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
    }

    @Override
    public Page pageinate(int pageNumber, int pageSize, String select, String sqlExceptSelect) {
        if (pageNumber < 1 || pageSize < 1) {
            log.error("当前页面页数和页面记录展示条数不能小于1");
            return new Page(pageNumber, pageSize);
        }
        long totalRow = 0;
        int totalPage = 0;
        totalRow = jt.queryForObject("SELECT COUNT(*) " + sqlExceptSelect, Long.class);
        if (totalRow % pageSize == 0) {
            totalPage = (int) (totalRow / pageSize);
        } else {
            totalPage = (int) (totalRow / pageSize) + 1;
        }
        log.debug("The TotalPage is :" + totalPage);
        String sql = RDJCSQLUtil.getPageSQL(select, sqlExceptSelect);
        log.debug("SQL: {}", sql);

        // 判断当前页面是否大于最大页面
        pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
        List> result = jt.queryForList(sql, (pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize, pageSize);
        return new Page(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
    }

    @Override
    public Page pageinate(Page page, String select, String sqlExceptSelect, NeParamList params) {
        return pageinate(page, select, NeParamTools.handleSql(sqlExceptSelect.toString(), params), params.getParamValues());
    }

    @Override
    public Page pageinate(Page page, String select, String sqlExceptSelect, Object... paras) {
        return pageinate(page.getPageNumber(), page.getPageSize(), select, sqlExceptSelect, paras);
    }

    @Override
    public Page pageinate(Page page, String select, String sqlExceptSelect) {
        return pageinate(page.getPageNumber(), page.getPageSize(), select, sqlExceptSelect);
    }

    // 新版本的查询
    @Override
    public List> findList(String sql, NeParamList params, Map sort_params, Page page) {
        String endSql = NeParamTools.handleSql(sql, params);
        log.debug("{},{}", endSql, params.toString());
        return this.findList(endSql, params.getParamValues(), sort_params, page);
    }

    @Override
    public List> findList(String sql, NeParamList params) {
        String endSql = NeParamTools.handleSql(sql, params);
        log.debug("{},{}", endSql, params.toString());
        return jt.queryForList(endSql, params.getParamValues());
    }

    @Override
    public  List findList(Class clazz, String sql, Object... vals) {
        List> list = this.findList(sql, vals);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  T findObj(Class clazz, String sql, Object... vals) {
        Map map = this.findObj(sql, vals);
        return ORMUtil.fillMapToAnyBean(clazz, map);
    }

    @Override
    public  List findList(Class clazz, Page page, String sql, String sql_cnt, Object... vals) {
        List> list = this.findList(sql, sql_cnt, vals, page);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  List findList(Class clazz, Page page, String sql, Map sort_params, Object... vals) {
        List> list = this.findList(sql, vals, page);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  List findList(Class clazz, Page page, String sql, Object... vals) {
        List> list = this.findList(sql, vals, page);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  List findList(Class clazz, Page page, String sql) {
        List> list = this.findList(sql, page);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  List findList(Class clazz, String sql, NeParamList params, Map sort_params, Page page) {
        List> list = this.findList(sql, params, sort_params, page);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  List findList(Class clazz, String sql, NeParamList params) {
        List> list = this.findList(sql, params);
        return ORMUtil.fillMapToList(list, clazz);
    }

    @Override
    public  T queryForObject(String sql, Class requiredType) {
        log.debug("{},{}", sql, requiredType.getClass().toString());
        return getJdbcTemplate().queryForObject(sql, requiredType);
    }

    @Override
    public  T queryForObject(String sql, NeParamList params, Class requiredType) {
        String endSql = NeParamTools.handleSql(sql, params);
        log.debug("{},{}", endSql, params.toString());
        return getJdbcTemplate().queryForObject(endSql, requiredType, params.getParamValues());
    }

    //2020-01-19 新增
    @Override
    public  PageVo pageinate(PageVo page, String select, String sqlExceptSelect, NeParamList params,Class clazz) {
        return pageinateVo(page.getPageNumber(), page.getPageSize(), select, NeParamTools.handleSql(sqlExceptSelect.toString(), params),clazz, params.getParamValues());
    }

    @Override
    public  PageVo pageinate(PageVo page, String select, String sqlExceptSelect,Class clazz, Object... paras) {
        return pageinateVo(page.getPageNumber(), page.getPageSize(), select, sqlExceptSelect,clazz, paras);
    }

    @Override
    public  PageVo pageinate(PageVo page, String select, String sqlExceptSelect,Class clazz) {
        return pageinateVo(page.getPageNumber(), page.getPageSize(), select, sqlExceptSelect,clazz);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public  PageVo pageinateVo(int pageNumber, int pageSize, String select, String sqlExceptSelect,Class clazz,
                                     Object... paras) {
        if (pageNumber < 1 || pageSize < 1) {
            log.error("当前页面页数和页面记录展示条数不能小于1");
            return new PageVo(pageNumber, pageSize);
        }
        long totalRow = 0;
        int totalPage = 0;
        if (!StrUtil.notNull(paras)) {
            return pageinateVo(pageNumber, pageSize, select, sqlExceptSelect,clazz);
        }
        totalRow = jt.queryForObject("SELECT COUNT(1) " + sqlExceptSelect, paras, Long.class);
        if (totalRow % pageSize == 0) {
            totalPage = (int) (totalRow / pageSize);
        } else {
            totalPage = (int) (totalRow / pageSize) + 1;
        }
        log.debug("The TotalPage is :" + totalPage);
        String sql = RDJCSQLUtil.getPageSQL(select, sqlExceptSelect);
        log.debug("SQL: " + sql);
        // 判断当前页面是否大于最大页面
        pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
        List objs = new ArrayList(Arrays.asList(paras));
        objs.add((pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize);
        objs.add(pageSize);
        List result = jt.query(sql,new BeanPropertyRowMapper(clazz), objs.toArray());
        return new PageVo(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
    }

    @Override
    public  PageVo pageinateVo(int pageNumber, int pageSize, String select, String sqlExceptSelect,Class clazz) {
        if (pageNumber < 1 || pageSize < 1) {
            log.error("当前页面页数和页面记录展示条数不能小于1");
            return new PageVo(pageNumber, pageSize);
        }
        long totalRow = 0;
        int totalPage = 0;
        totalRow = jt.queryForObject("SELECT COUNT(1) " + sqlExceptSelect, Long.class);
        if (totalRow % pageSize == 0) {
            totalPage = (int) (totalRow / pageSize);
        } else {
            totalPage = (int) (totalRow / pageSize) + 1;
        }
        log.debug("The TotalPage is :" + totalPage);
        String sql = RDJCSQLUtil.getPageSQL(select, sqlExceptSelect);
        log.debug("SQL: {}", sql);

        // 判断当前页面是否大于最大页面
        pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
        @SuppressWarnings({ "unchecked", "rawtypes" })
        List result = jt.query(sql,new BeanPropertyRowMapper(clazz), (pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize, pageSize);
        return new PageVo(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
    }
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy