Homestay_app_houtai/mhotel/src/com/happy/dao/impl/HousePriceDaoImpl.java

package com.happy.dao.impl;

import com.happy.Model.HousePrice;
import com.happy.Until.BeanMapTool;
import com.happy.dao.HousePriceDao;
import com.happy.dto.HousePriceDto;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("housePriceDao")
public class HousePriceDaoImpl implements HousePriceDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    static final String KEY_START_SET_DATE = "startSetDate";
    static final String KEY_END_SET_DATE = "endSetDate";

    @Override
    public void insertBatch(List<HousePrice> housePriceList) {
        final String sql = "insert into house_price values (:id, :managerId, :houseId, :price, :remark, :setDate, :createId, :createDate, :modifyDate, :status)";
        namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(housePriceList.toArray()));
    }

    @Override
    public List<HousePriceDto> queryListOne(HousePriceDto housePriceDto, Integer pageNumber, Integer pageSize) {
        final String SQL_HEAD = "select t1.id as houseId, t1.h_name as houseName, t1.price as originalPrice, t1.create_date from house t1 where t1.status = 1";
        final String SQL_TAIL = " order by t1.create_date desc";
        Map<String, Object> paramMap = buildParamMap(housePriceDto, pageNumber, pageSize);
        StringBuilder sql = buildSqlHouse(SQL_HEAD, "", housePriceDto, pageNumber, pageSize);
        return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(HousePriceDto.class));
    }

    @Override
    public int queryTotalOne(HousePriceDto housePriceDto) {
        final String SQL_HEAD = "select count(1) from house t1 where t1.status = 1";
        Map<String, Object> paramMap = buildParamMap(housePriceDto, null, null);
        StringBuilder sql = buildSqlHouse(SQL_HEAD, "", housePriceDto, null, null);
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, Integer.class);
    }

    @Override
    public List<HousePriceDto> queryListTwo(HousePriceDto housePriceDto) {
        final String SQL_HEAD = "select t1.id, t1.manager_id, t1.house_id, t1.price, t1.remark, t1.set_date, t1.create_id, t1.create_date, t1.modify_date, t1.status from house_price t1 where t1.status = 1";
        final String SQL_TAIL = " order by create_date desc";
        Map<String, Object> paramMap = buildParamMap(housePriceDto, null, null);
        StringBuilder sql = buildSqlHousePrice(SQL_HEAD, SQL_TAIL, housePriceDto, null, null);
        return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(HousePriceDto.class));
    }

    @Override
    public List<HousePriceDto> queryListThree(HousePriceDto housePriceDto, Integer pageNumber, Integer pageSize) {
        final String SQL_HEAD = "select t.* from(select t1.id, t1.house_id, t2.h_name as houseName, group_concat(replace(t1.set_date, ',', ' 至 ')) as set_date, t1.price, t1.create_id, date_format(t1.create_date, '%Y-%m-%d %H:%i:%s') as operationTime from house_price t1 left join house t2 on t1.status = t2.status and t1.manager_id = t2.manager_id and t1.house_id = t2.id where t1.status = 1";
        final String SQL_TAIL = " group by t1.id) t order by t.operationTime desc";
        Map<String, Object> paramMap = buildParamMap(housePriceDto, pageNumber, pageSize);
        StringBuilder sql = buildSqlHousePrice(SQL_HEAD, SQL_TAIL, housePriceDto, pageNumber, pageSize);
        return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(HousePriceDto.class));
    }

    @Override
    public int queryTotalThree(HousePriceDto housePriceDto) {
        final String SQL_HEAD = "select count(1) from (select 1 from house_price t1 left join house t2 on t1.status = t2.status and t1.manager_id = t2.manager_id and t1.house_id = t2.id where t1.status = 1";
        final String SQL_TAIL = ") t";
        Map<String, Object> paramMap = buildParamMap(housePriceDto, null, null);
        StringBuilder sql = buildSqlHousePrice(SQL_HEAD, SQL_TAIL, housePriceDto, null, null);
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, Integer.class);
    }

    /**
     * 构建主表t1为house_price的sql查询条件
     */
    private StringBuilder buildSqlHousePrice(String sqlHead, String sqlTail, HousePriceDto housePriceDto, Integer pageNumber, Integer pageSize) {
        StringBuilder sql = new StringBuilder(sqlHead);
        if (StringUtils.isNotBlank(housePriceDto.getManagerId())) {
            sql.append(" and t1.manager_id=:managerId");
        }
        if (housePriceDto.getHouseIdList() != null && !housePriceDto.getHouseIdList().isEmpty()) {
            sql.append(" and t1.house_id in (:houseIdList)");
        }
        if (StringUtils.isNotBlank(housePriceDto.getHouseId())) {
            sql.append(" and t1.house_id=:houseId");
        }
        String setDate = housePriceDto.getSetDate();
        if (StringUtils.isNotBlank(setDate) && setDate.split(",").length == 2) {
            sql.append(String.format(" and(substring_index(t1.set_date, ',', 1) between :%s and :%s", KEY_START_SET_DATE, KEY_END_SET_DATE))
                    .append(String.format(" or substring_index(t1.set_date, ',', -1) between :%s and :%s)", KEY_START_SET_DATE, KEY_END_SET_DATE));
        }
        sql.append(sqlTail).append(buildSqlPage(pageNumber, pageSize));
        return sql;
    }

    /**
     * 构建主表t1为house的sql查询条件
     */
    private StringBuilder buildSqlHouse(String sqlHead, String sqlTail, HousePriceDto housePriceDto, Integer pageNumber, Integer pageSize) {
        StringBuilder sql = new StringBuilder(sqlHead);
        if (StringUtils.isNotBlank(housePriceDto.getManagerId())) {
            sql.append(" and t1.manager_id=:managerId");
        }
        if (StringUtils.isNotBlank(housePriceDto.getHouseId())) {
            sql.append(" and t1.id=:houseId");
        }
        sql.append(sqlTail).append(buildSqlPage(pageNumber, pageSize));
        return sql;
    }

    /**
     * 构建分页查询条件
     */
    private String buildSqlPage(Integer pageNumber, Integer pageSize) {
        String result = "";
        if (pageNumber != null && pageSize != null) {
            result = " limit :pageNumber, :pageSize";
        } else if (pageSize != null) {
            result = " limit :pageSize";
        }
        return result;
    }

    /**
     * 构建sql查询参数
     */
    private Map<String, Object> buildParamMap(HousePriceDto housePriceDto, Integer pageNumber, Integer pageSize) {
        Map<String, Object> paramMap = BeanMapTool.beanToMap(housePriceDto);
        String setDate = housePriceDto.getSetDate();
        if (StringUtils.isNotBlank(setDate) && setDate.split(",").length == 2) {
            String[] setDateArr = setDate.split(",");
            paramMap.put(KEY_START_SET_DATE, setDateArr[0]);
            paramMap.put(KEY_END_SET_DATE, setDateArr[1]);
        }
        return putPageParams(pageNumber, pageSize, paramMap);
    }

    /**
     * 追加sql查询分页参数
     */
    static Map<String, Object> putPageParams(Integer pageNumber, Integer pageSize, Map<String, Object> paramMap) {
        if (pageNumber != null && pageSize != null) {
            paramMap.put("pageNumber", (pageNumber - 1) * pageSize);
            paramMap.put("pageSize", pageSize);
        } else if (pageSize != null) {
            paramMap.put("pageSize", pageSize);
        }
        return paramMap;
    }
}