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

package com.happy.dao.impl;

import com.happy.Model.AdminManager;
import com.happy.Model.HotelDict;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HotelDictDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("HotelDictDao")
public class HotelDictImplDao implements HotelDictDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }


    @Override
    public int insertHotelDict(HotelDict hotelDict) {
        String sql = "INSERT INTO hotel_dict (id, code, name, file_url, create_id, create_date, modify_date, status) VALUES (:id, :code, :name, :file_url, :create_id, :create_date, :modify_date, :status);\n";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("code",hotelDict.getCode());
        sps.addValue("name",hotelDict.getName());
        sps.addValue("file_url",hotelDict.getFileUrl());
        sps.addValue("create_id",hotelDict.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("modify_date",hotelDict.getModifyDate());
        sps.addValue("status",1);
        if(hotelDict.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id",  hotelDict.getId());
        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateHotelDict(HotelDict hotelDict) {
        StringBuffer stringBuffer =  new StringBuffer(" update `hotel_dict` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        appendValue(hotelDict,stringBuffer,sps);
        stringBuffer.append("  where id=:id  ");
        sps.addValue("id", hotelDict.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int delHotelDict(int id) {
        String sql = "UPDATE `hotel_dict` SET status=0 WHERE id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public HotelDict getById(int id) {
        String sql = "SELECT * FROM `hotel_dict` WHERE id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<HotelDict> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(HotelDict.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public List<HotelDict> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT * FROM `hotel_dict` WHERE status=1 "+sqlx+" ORDER BY id DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<HotelDict> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HotelDict.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int queryTotal(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT count(*) FROM`hotel_dict` where status=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<HotelDict> queryList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT * FROM `hotel_dict` WHERE status=1 "+sqlx;
        List<HotelDict> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HotelDict.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public String setConfig(String ids){
        String sql = "select GROUP_CONCAT(id) id from hotel_dict where status=1 and FIND_IN_SET(id,'"+ids+"')";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("ids", ids);
        String config = "";
        try{
            config = namedParameterJdbcTemplate.queryForObject(sql, EmptySqlParameterSource.INSTANCE, String.class);
        }catch (Exception e){
            e.printStackTrace();
        }
        return config;
    }

    public void appendValue(HotelDict hotelDict, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(String.valueOf(hotelDict.getCode()))){
            stringBuffer.append(" code=:code ,");
            sps.addValue("code",hotelDict.getCode());
        }
        if (!Func.checkNull(hotelDict.getName())){
            stringBuffer.append(" name=:name ,");
            sps.addValue("name",hotelDict.getName());
        }
        if (!Func.checkNull(hotelDict.getFileUrl())){
            stringBuffer.append(" file_url=:file_url ,");
            sps.addValue("file_url",hotelDict.getFileUrl());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }

}