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.House;
import com.happy.Model.House;
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) {
        String sql = "UPDATE house SET h_name = :h_name, h_areas = :h_areas, price = :price, number = :number, h_config = :h_config, remark = :remark, modify_date = :modify_date, status = :status WHERE id = :id";
        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("modify_date",house.getModifyDate());
        sps.addValue("status",house.getStatus());
        sps.addValue("id",house.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, 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;
    }
}