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

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

import com.github.drinkjava2.jdialects.Dialect;
import com.ydtech.core.page.PageResult;
import com.ydtech.modules.admin.dao.SysDeptDao;
import com.ydtech.modules.admin.model.SysDept;
import com.ydtech.modules.admin.vo.TreeSysDeptVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
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.ArrayList;
import java.util.List;


@Repository
public class SysDeptDaoImpl implements SysDeptDao {

    @Autowired
    public DataSource dataSource;

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

            model.setId(resultSet.getString("id"));
            model.setName(resultSet.getString("name"));
            model.setParentId(resultSet.getString("parent_id"));
            model.setOrderNum(resultSet.getInt("order_num"));
            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.setDelFlag(resultSet.getInt("del_flag"));
            model.setComtype(resultSet.getString("comtype"));
            model.setComlevel(resultSet.getString("comlevel"));
            model.setAddress(resultSet.getString("address"));
            model.setLeadername(resultSet.getString("leadername"));
            model.setLeaderphone(resultSet.getString("leaderphone"));
            model.setComattribute(resultSet.getString("comattribute"));
            model.setIsstop(resultSet.getString("isstop"));
            model.setDeptCode(resultSet.getString("dept_code"));
            model.setProvince(resultSet.getString("province"));
            model.setCity(resultSet.getString("city"));
            model.setArea(resultSet.getString("area"));
            model.setHouse(resultSet.getString("house"));
            model.setDeptType(resultSet.getString("dept_type"));
            return model;
        }
    }

    @Override
    public SysDept selectByPk(String id) {
        String sql = "select t.id, t.name, t.parent_id, t.order_num, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.del_flag, t.comtype, t.comlevel, t.address, t.leadername, t.leaderphone, t.comattribute, t.isstop, t.dept_code, t.province, t.city, t.area, t.house, t.dept_type from sys_dept t  where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

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

    @Override
    public List<SysDept> selectAll() {
        String sql = "select t.id, t.name, t.parent_id, t.order_num, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.del_flag, t.comtype, t.comlevel, t.address, t.leadername, t.leaderphone, t.comattribute, t.isstop, t.dept_code, t.province, t.city, t.area, t.house, t.dept_type from sys_dept t  ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

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

    public List<SysDept> selectList(String whereCase, Object[] paramValues) {
        String sql = "select t.id, t.name, t.parent_id, t.order_num, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.del_flag, t.comtype, t.comlevel, t.address, t.leadername, t.leaderphone, t.comattribute, t.isstop, t.dept_code, t.province, t.city, t.area, t.house, t.dept_type from sys_dept t  " + whereCase;

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

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

    public Integer count(String whereCase, Object[] paramValues) {
        String sql = "select count(1) from sys_dept 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.name, t.parent_id, t.order_num, t.create_by, t.create_time, t.last_update_by, t.last_update_time, t.del_flag, t.comtype, t.comlevel, t.address, t.leadername, t.leaderphone, t.comattribute, t.isstop, t.dept_code, t.province, t.city, t.area, t.house, t.dept_type from sys_dept t  " + whereCase);

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

        int totalCount = count(whereCase, paramValues);

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

        return PageResult;
    }


    @Override
    public Integer insert(SysDept model) {

        String sql = "insert into sys_dept ( id, name, parent_id, order_num, create_by, create_time, last_update_by, last_update_time, del_flag, comtype, comlevel, address, leadername, leaderphone, comattribute, isstop, dept_code, province, city, area, house, dept_type) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getId(), model.getName(), model.getParentId(), model.getOrderNum(), model.getCreateBy(), model.getCreateTime(), model.getLastUpdateBy(), model.getLastUpdateTime(), model.getDelFlag(), model.getComtype(), model.getComlevel(), model.getAddress(), model.getLeadername(), model.getLeaderphone(), model.getComattribute(), model.getIsstop(), model.getDeptCode(), model.getProvince(), model.getCity(), model.getArea(), model.getHouse(), model.getDeptType()};

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

        return out;
    }

    @Override
    public Integer insertReturnPk(SysDept model) {

        String sql = "insert into sys_dept ( id, name, parent_id, order_num, create_by, create_time, last_update_by, last_update_time, del_flag, comtype, comlevel, address, leadername, leaderphone, comattribute, isstop, dept_code, province, city, area, house, dept_type) 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.getName());
                ps.setObject(3, model.getParentId());
                ps.setObject(4, model.getOrderNum());
                ps.setObject(5, model.getCreateBy());
                ps.setObject(6, model.getCreateTime());
                ps.setObject(7, model.getLastUpdateBy());
                ps.setObject(8, model.getLastUpdateTime());
                ps.setObject(9, model.getDelFlag());
                ps.setObject(10, model.getComtype());
                ps.setObject(11, model.getComlevel());
                ps.setObject(12, model.getAddress());
                ps.setObject(13, model.getLeadername());
                ps.setObject(14, model.getLeaderphone());
                ps.setObject(15, model.getComattribute());
                ps.setObject(16, model.getIsstop());
                ps.setObject(17, model.getDeptCode());
                ps.setObject(18, model.getProvince());
                ps.setObject(19, model.getCity());
                ps.setObject(20, model.getArea());
                ps.setObject(21, model.getHouse());
                ps.setObject(22, model.getDeptType());
                return ps;
            }
        }, keyHolder);

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


    @Override
    public Integer insertSelective(SysDept 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.getName() != null) {
            sbSql.append(pre + "name");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getName());
            pre = ",";
        }
        if (model.getParentId() != null) {
            sbSql.append(pre + "parent_id");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getParentId());
            pre = ",";
        }
        if (model.getOrderNum() != null) {
            sbSql.append(pre + "order_num");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getOrderNum());
            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.getDelFlag() != null) {
            sbSql.append(pre + "del_flag");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getDelFlag());
            pre = ",";
        }
        if (model.getComtype() != null) {
            sbSql.append(pre + "comtype");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getComtype());
            pre = ",";
        }
        if (model.getComlevel() != null) {
            sbSql.append(pre + "comlevel");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getComlevel());
            pre = ",";
        }
        if (model.getAddress() != null) {
            sbSql.append(pre + "address");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getAddress());
            pre = ",";
        }
        if (model.getLeadername() != null) {
            sbSql.append(pre + "leadername");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getLeadername());
            pre = ",";
        }
        if (model.getLeaderphone() != null) {
            sbSql.append(pre + "leaderphone");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getLeaderphone());
            pre = ",";
        }
        if (model.getComattribute() != null) {
            sbSql.append(pre + "comattribute");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getComattribute());
            pre = ",";
        }
        if (model.getIsstop() != null) {
            sbSql.append(pre + "isstop");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getIsstop());
            pre = ",";
        }
        if (model.getDeptCode() != null) {
            sbSql.append(pre + "dept_code");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getDeptCode());
            pre = ",";
        }
        if (model.getProvince() != null) {
            sbSql.append(pre + "province");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getProvince());
            pre = ",";
        }
        if (model.getCity() != null) {
            sbSql.append(pre + "city");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getCity());
            pre = ",";
        }
        if (model.getArea() != null) {
            sbSql.append(pre + "area");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getArea());
            pre = ",";
        }
        if (model.getHouse() != null) {
            sbSql.append(pre + "house");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getHouse());
            pre = ",";
        }
        if (model.getDeptType() != null) {
            sbSql.append(pre + "dept_type");
            sbParams.append(pre + "?");
            alParamsValues.add(model.getDeptType());
            pre = ",";
        }


        String sql = "INSERT INTO sys_dept (" + 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(SysDept model) {

        String sql = "update sys_dept set name=?, parent_id=?, order_num=?, create_by=?, create_time=?, last_update_by=?, last_update_time=?, del_flag=?, comtype=?, comlevel=?, address=?, leadername=?, leaderphone=?, comattribute=?, isstop=?, dept_code=?, province=?, city=?, area=?, house=?, dept_type=? where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] params = new Object[]{model.getName(), model.getParentId(), model.getOrderNum(), model.getCreateBy(), model.getCreateTime(), model.getLastUpdateBy(), model.getLastUpdateTime(), model.getDelFlag(), model.getComtype(), model.getComlevel(), model.getAddress(), model.getLeadername(), model.getLeaderphone(), model.getComattribute(), model.getIsstop(), model.getDeptCode(), model.getProvince(), model.getCity(), model.getArea(), model.getHouse(), model.getDeptType(), model.getId()};

        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Integer updateSelective(SysDept model) {

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

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

        if (model.getName() != null) {
            sbSql.append(pre + "name=?");
            alParamsValues.add(model.getName());
            pre = ",";
        }
        if (model.getParentId() != null) {
            sbSql.append(pre + "parent_id=?");
            alParamsValues.add(model.getParentId());
            pre = ",";
        }
        if (model.getOrderNum() != null) {
            sbSql.append(pre + "order_num=?");
            alParamsValues.add(model.getOrderNum());
            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.getDelFlag() != null) {
            sbSql.append(pre + "del_flag=?");
            alParamsValues.add(model.getDelFlag());
            pre = ",";
        }
        if (model.getComtype() != null) {
            sbSql.append(pre + "comtype=?");
            alParamsValues.add(model.getComtype());
            pre = ",";
        }
        if (model.getComlevel() != null) {
            sbSql.append(pre + "comlevel=?");
            alParamsValues.add(model.getComlevel());
            pre = ",";
        }
        if (model.getAddress() != null) {
            sbSql.append(pre + "address=?");
            alParamsValues.add(model.getAddress());
            pre = ",";
        }
        if (model.getLeadername() != null) {
            sbSql.append(pre + "leadername=?");
            alParamsValues.add(model.getLeadername());
            pre = ",";
        }
        if (model.getLeaderphone() != null) {
            sbSql.append(pre + "leaderphone=?");
            alParamsValues.add(model.getLeaderphone());
            pre = ",";
        }
        if (model.getComattribute() != null) {
            sbSql.append(pre + "comattribute=?");
            alParamsValues.add(model.getComattribute());
            pre = ",";
        }
        if (model.getIsstop() != null) {
            sbSql.append(pre + "isstop=?");
            alParamsValues.add(model.getIsstop());
            pre = ",";
        }
        if (model.getDeptCode() != null) {
            sbSql.append(pre + "dept_code=?");
            alParamsValues.add(model.getDeptCode());
            pre = ",";
        }
        if (model.getProvince() != null) {
            sbSql.append(pre + "province=?");
            alParamsValues.add(model.getProvince());
            pre = ",";
        }
        if (model.getCity() != null) {
            sbSql.append(pre + "city=?");
            alParamsValues.add(model.getCity());
            pre = ",";
        }
        if (model.getArea() != null) {
            sbSql.append(pre + "area=?");
            alParamsValues.add(model.getArea());
            pre = ",";
        }
        if (model.getHouse() != null) {
            sbSql.append(pre + "house=?");
            alParamsValues.add(model.getHouse());
            pre = ",";
        }
        if (model.getDeptType() != null) {
            sbSql.append(pre + "dept_type=?");
            alParamsValues.add(model.getDeptType());
            pre = ",";
        }
        if (model.getId() != null) {
            sbParams.append(preWhere + "id=?");
            alParamsValues.add(model.getId());
            preWhere = ",";
        }


        if (!sbSql.toString().equalsIgnoreCase("")) {
            String sql = "update sys_dept 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(String id) {

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

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Integer delete(List<SysDept> lsSysDept) {
        String sql = "delete from sys_dept where id=?";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

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

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