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

package com.happy.dao.impl;

import com.happy.Model.Admin;
import com.happy.Model.Hotel;
import com.happy.Model.House;
import com.happy.Model.House;
import com.happy.Until.Func;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HouseDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("HouseDao")
public class HouseImplDao implements HouseDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    @Override
    public int insertHouse(House house) {
        String sql = "INSERT INTO house (id, h_name, h_areas, price, number, h_config, remark, create_id, create_date, status) VALUES (:id, :h_name, :h_areas, :price, :number, :h_config, :remark, :create_id, :create_date, :status)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("h_name",house.gethName());
        sps.addValue("h_areas",house.gethAreas());
        sps.addValue("price",house.getPrice());
        sps.addValue("number",house.getNumber());
        sps.addValue("h_config",house.gethConfig());
        sps.addValue("remark",house.getRemark());
        sps.addValue("create_id",house.getCreateId());
        sps.addValue("create_date",house.getCreateDate());
        sps.addValue("status",house.getStatus());
        if(house.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", house.getId());

        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

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

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

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

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

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

    public void appendValue(House house, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(house.getManagerId())){
            stringBuffer.append(" manager_id=:manager_id ,");
            sps.addValue("manager_id",house.getManagerId());
        }
        if (!Func.checkNull(house.gethName())){
            stringBuffer.append(" h_name=:h_name ,");
            sps.addValue("h_name",house.gethName());
        }
        if (!Func.checkNull(house.gethAreas())){
            stringBuffer.append(" h_areas=:h_areas ,");
            sps.addValue("h_areas",house.gethAreas());
        }
        if (!Func.checkNull(String.valueOf(house.getPrice()))){
            stringBuffer.append(" price=:price ,");
            sps.addValue("price",house.getPrice());
        }
        if (!Func.checkNull(String.valueOf(house.getNumber()))){
            stringBuffer.append(" number=:number ,");
            sps.addValue("number",house.getNumber());
        }
        if (!Func.checkNull(house.gethConfig())){
            stringBuffer.append(" h_config=:h_config ,");
            sps.addValue("h_config",house.gethConfig());
        }
        if (!Func.checkNull(house.getRemark())){
            stringBuffer.append(" remark=:remark ,");
            sps.addValue("remark",house.getRemark());
        }
        if (!Func.checkNull(String.valueOf(house.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status", house.getStatus());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }
}