java-insagent-pc/src/main/java/com/ydtech/modules/admin/dao/impl/SysDictDaoImpl.java

package com.ydtech.modules.admin.dao.impl;

import com.ydtech.modules.admin.dao.SysDictDao;
import com.ydtech.modules.admin.model.SysDict;
import com.ydtech.core.page.PageResult;
import com.github.drinkjava2.jdialects.Dialect;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;


import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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


@Repository
public class SysDictDaoImpl implements SysDictDao {

    @Autowired
    public DataSource dataSource;

    public static class SysDictRowMapper implements RowMapper {
        public SysDict mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            SysDict model = new SysDict();

            model.setId(resultSet.getLong("id"));
            model.setValue(resultSet.getString("value"));
            model.setLabel(resultSet.getString("label"));
            model.setItemname(resultSet.getString("itemname"));
            model.setDescription(resultSet.getString("description"));
            model.setSort(resultSet.getDouble("sort"));
            model.setCreateBy(resultSet.getString("create_by"));
            model.setCreateTime(resultSet.getDate("create_time"));
            model.setLastUpdateBy(resultSet.getString("last_update_by"));
            model.setLastUpdateTime(resultSet.getDate("last_update_time"));
            model.setRemarks(resultSet.getString("remarks"));
            model.setDelFlag(resultSet.getInt("del_flag"));
            return model;
        }
    }

    @Override
    public SysDict selectByPk(Long id) {
        String sql = "select t.id, t.value, t.label, t.itemname, t.description, t.sort, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.remarks, t.del_flag from sys_dict t  where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return (SysDict) jdbcTemplate.queryForObject(sql,
                new Object[]{id}, new SysDictRowMapper());
    }

    @Override
    public List<SysDict> selectAll() {
        String sql = "select t.id, t.value, t.label, t.itemname, t.description, t.sort, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.remarks, t.del_flag from sys_dict t  ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.query(sql, new SysDictRowMapper());
    }

    public List<SysDict> selectList(String whereCase, Object[] paramValues) {
        String sql = "select t.id, t.value, t.label, t.itemname, t.description, t.sort, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.remarks, t.del_flag from sys_dict t  " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.query(sql, paramValues, new SysDictRowMapper());
    }

    public Integer count(String whereCase, Object[] paramValues) {
        String sql = "select count(1) from sys_dict t " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.queryForObject(sql, paramValues, Integer.class);
    }

    public PageResult selectPaging(int pageNumber, int pageSize, String whereCase, Object[] paramValues) {

        Dialect dialect = Dialect.guessDialect(dataSource);
        String sql = dialect.pagin(pageNumber, pageSize, "select t.id, t.value, t.label, t.itemname, t.description, t.sort, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.remarks, t.del_flag from sys_dict t  " + whereCase);

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<SysDict> recordList = jdbcTemplate.query(sql, paramValues, new SysDictRowMapper());

        int totalCount = count(whereCase, paramValues);

        PageResult PageResult = new PageResult(pageNumber, pageSize, totalCount, recordList);

        return PageResult;
    }


    @Override
    public Integer insert(SysDict model) {

        String sql = "insert into sys_dict ( id, value, label, itemname, description, sort, create_by, create_time, last_update_by, last_update_time, remarks, del_flag) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getId(), model.getValue(), model.getLabel(), model.getItemname(), model.getDescription(), model.getSort(), model.getCreateBy(), model.getCreateTime(), model.getLastUpdateBy(), model.getLastUpdateTime(), model.getRemarks(), model.getDelFlag()};

        int out = jdbcTemplate.update(sql, params);

        return out;
    }

    @Override
    public Integer insertReturnPk(SysDict model) {

        String sql = "insert into sys_dict ( id, value, label, itemname, description, sort, create_by, create_time, last_update_by, last_update_time, remarks, del_flag) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setObject(1, model.getId());
                ps.setObject(2, model.getValue());
                ps.setObject(3, model.getLabel());
                ps.setObject(4, model.getItemname());
                ps.setObject(5, model.getDescription());
                ps.setObject(6, model.getSort());
                ps.setObject(7, model.getCreateBy());
                ps.setObject(8, model.getCreateTime());
                ps.setObject(9, model.getLastUpdateBy());
                ps.setObject(10, model.getLastUpdateTime());
                ps.setObject(11, model.getRemarks());
                ps.setObject(12, model.getDelFlag());
                return ps;
            }
        }, keyHolder);

        return keyHolder.getKey().intValue();
    }


    @Override
    public Integer insertSelective(SysDict model) {

        StringBuffer sbSql = new StringBuffer();
        StringBuffer sbParams = new StringBuffer();
        ArrayList<Object> alParamsValues = new ArrayList<Object>();

        String pre = "";

        if (model.getId() != null) {
            sbSql.append(pre + "id");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getId());
            pre = ",";
        }
        if (model.getValue() != null) {
            sbSql.append(pre + "value");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getValue());
            pre = ",";
        }
        if (model.getLabel() != null) {
            sbSql.append(pre + "label");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getLabel());
            pre = ",";
        }
        if (model.getItemname() != null) {
            sbSql.append(pre + "itemname");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getItemname());
            pre = ",";
        }
        if (model.getDescription() != null) {
            sbSql.append(pre + "description");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getDescription());
            pre = ",";
        }
        if (model.getSort() != null) {
            sbSql.append(pre + "sort");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getSort());
            pre = ",";
        }
        if (model.getCreateBy() != null) {
            sbSql.append(pre + "create_by");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getCreateBy());
            pre = ",";
        }
        if (model.getCreateTime() != null) {
            sbSql.append(pre + "create_time");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getCreateTime());
            pre = ",";
        }
        if (model.getLastUpdateBy() != null) {
            sbSql.append(pre + "last_update_by");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getLastUpdateBy());
            pre = ",";
        }
        if (model.getLastUpdateTime() != null) {
            sbSql.append(pre + "last_update_time");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getLastUpdateTime());
            pre = ",";
        }
        if (model.getRemarks() != null) {
            sbSql.append(pre + "remarks");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getRemarks());
            pre = ",";
        }
        if (model.getDelFlag() != null) {
            sbSql.append(pre + "del_flag");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getDelFlag());
            pre = ",";
        }


        String sql = "INSERT INTO sys_dict (" + sbSql.toString() + ") VALUES (" + sbParams.toString() + ")";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = alParamsValues.toArray();

        int out = jdbcTemplate.update(sql, params);

        return out;
    }

    @Override
    public Integer update(SysDict model) {

        String sql = "update sys_dict set value=?, label=?, itemname=?, description=?, sort=?, create_by=?, create_time=?, last_update_by=?, last_update_time=?, remarks=?, del_flag=? where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getValue(), model.getLabel(), model.getItemname(), model.getDescription(), model.getSort(), model.getCreateBy(), model.getCreateTime(), model.getLastUpdateBy(), model.getLastUpdateTime(), model.getRemarks(), model.getDelFlag(), model.getId()};

        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Integer updateSelective(SysDict model) {

        StringBuffer sbSql = new StringBuffer();
        StringBuffer sbParams = new StringBuffer();
        ArrayList<Object> alParamsValues = new ArrayList<Object>();

        String pre = "";
        String preWhere = "";

        if (model.getValue() != null) {
            sbSql.append(pre + "value=?");
            alParamsValues.add(model.getValue());
            pre = ",";
        }
        if (model.getLabel() != null) {
            sbSql.append(pre + "label=?");
            alParamsValues.add(model.getLabel());
            pre = ",";
        }
        if (model.getItemname() != null) {
            sbSql.append(pre + "itemname=?");
            alParamsValues.add(model.getItemname());
            pre = ",";
        }
        if (model.getDescription() != null) {
            sbSql.append(pre + "description=?");
            alParamsValues.add(model.getDescription());
            pre = ",";
        }
        if (model.getSort() != null) {
            sbSql.append(pre + "sort=?");
            alParamsValues.add(model.getSort());
            pre = ",";
        }
        if (model.getCreateBy() != null) {
            sbSql.append(pre + "create_by=?");
            alParamsValues.add(model.getCreateBy());
            pre = ",";
        }
        if (model.getCreateTime() != null) {
            sbSql.append(pre + "create_time=?");
            alParamsValues.add(model.getCreateTime());
            pre = ",";
        }
        if (model.getLastUpdateBy() != null) {
            sbSql.append(pre + "last_update_by=?");
            alParamsValues.add(model.getLastUpdateBy());
            pre = ",";
        }
        if (model.getLastUpdateTime() != null) {
            sbSql.append(pre + "last_update_time=?");
            alParamsValues.add(model.getLastUpdateTime());
            pre = ",";
        }
        if (model.getRemarks() != null) {
            sbSql.append(pre + "remarks=?");
            alParamsValues.add(model.getRemarks());
            pre = ",";
        }
        if (model.getDelFlag() != null) {
            sbSql.append(pre + "del_flag=?");
            alParamsValues.add(model.getDelFlag());
            pre = ",";
        }
        if (model.getId() != null) {
            sbParams.append(preWhere + "id=?");
            alParamsValues.add(model.getId());
            preWhere = ",";
        }


        if (!sbSql.toString().equalsIgnoreCase("")) {
            String sql = "update sys_dict set " + sbSql.toString() + " where " + sbParams.toString();

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            Object[] params = alParamsValues.toArray();

            return jdbcTemplate.update(sql, params);
        }

        return 0;
    }

    @Override
    public Integer delete(Long id) {

        String sql = "delete from sys_dict where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Integer delete(List<SysDict> lsSysDict) {
        String sql = "delete from sys_dict where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setLong(1, lsSysDict.get(i).getId());
            }

            @Override
            public int getBatchSize() {
                return lsSysDict.size();
            }
        }).length;
    }
}