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

package com.happy.dao.impl;

import com.happy.Model.AdminManager;
import com.happy.Model.Hotel;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HotelDao;
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("HotelDao")
public class HotelImplDao implements HotelDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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


    @Override
    public int insertHotel(Hotel hotel) {
        String sql = "INSERT INTO hotel (id, manager_id, hname, hposition, hposition_wens, htype, hstatus, open_time, fitup_time, live_time, leave_time, room_number, remark, hconfig, is_order, is_checkout, lock_time, is_canorder, create_id, create_date, status) VALUES (:id,:manager_id,:hname,:hposition,:hposition_wens,:htype,:hstatus,:open_time,:fitup_time,:live_time,:leave_time,:room_number,:remark,:hconfig,:is_order,:is_checkout,:lock_time,:is_canorder, :create_id, :create_date, :status)";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",hotel.getManagerId());
        sps.addValue("hname",hotel.getHname());
        sps.addValue("hposition",hotel.getHposition());
        sps.addValue("hposition_wens",hotel.getHpositionWens());
        sps.addValue("htype",hotel.getHtype());
        sps.addValue("hstatus",hotel.getHstatus());
        sps.addValue("open_time",hotel.getOpenTime());
        sps.addValue("fitup_time",hotel.getFitupTime());
        sps.addValue("live_time",hotel.getLiveTime());
        sps.addValue("leave_time",hotel.getLeaveTime());
        sps.addValue("room_number",hotel.getRoomNumber());
        sps.addValue("remark",hotel.getRemark());
        sps.addValue("hconfig",hotel.getHconfig());
        sps.addValue("is_order",hotel.getIsOrder());
        sps.addValue("is_checkout",hotel.getIsCheckout());
        sps.addValue("lock_time",hotel.getLockTime());
        sps.addValue("is_canorder",hotel.getIsCheckout());
        sps.addValue("create_id", hotel.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("status", 1);
        if(hotel.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", hotel.getId());
        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateHotel(Hotel hotel) {
        String sql = "UPDATE hotel SET manager_id = :manager_id, hname = :hname, hposition = :hposition, hposition_wens = :hposition_wens, htype = :htype, hstatus = :hstatus, open_time = :open_time, fitup_time = :fitup_time, live_time = :live_time, leave_time = :leave_time, room_number = :room_number, remark = :remark, hconfig = :hconfig, is_order = :is_order, is_checkout = :is_checkout, lock_time = :lock_time, is_canorder = :is_canorder, modify_date = :modify_date, status = :status WHERE id = :id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",hotel.getManagerId());
        sps.addValue("hname",hotel.getHname());
        sps.addValue("hposition",hotel.getHposition());
        sps.addValue("hposition_wens",hotel.getHpositionWens());
        sps.addValue("htype",hotel.getHtype());
        sps.addValue("hstatus",hotel.getHstatus());
        sps.addValue("open_time",hotel.getOpenTime());
        sps.addValue("fitup_time",hotel.getFitupTime());
        sps.addValue("live_time",hotel.getLiveTime());
        sps.addValue("leave_time",hotel.getLeaveTime());
        sps.addValue("room_number",hotel.getRoomNumber());
        sps.addValue("remark",hotel.getRemark());
        sps.addValue("hconfig",hotel.getHconfig());
        sps.addValue("is_order",hotel.getIsOrder());
        sps.addValue("is_checkout",hotel.getIsCheckout());
        sps.addValue("lock_time",hotel.getLockTime());
        sps.addValue("is_canorder",hotel.getIsCheckout());
        sps.addValue("create_id", hotel.getCreateId());
        sps.addValue("modify_date",UUIDUtil.getNewDate());
        sps.addValue("status", hotel.getStatus());
        sps.addValue("id", hotel.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

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

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

//    select a.*,b.min_price from hotel a
//    left join (select manager_id,min(price) min_price from house group by manager_id) b on a.manager_id = b.manager_id  ORDER BY id DESC limit 1,3
    @Override
    public List<Hotel> queryPagePrice(String sqlx, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT a.*,b.min_price FROM `hotel` a left join (select manager_id,min(price) min_price from house group by manager_id) b on a.manager_id = b.manager_id WHERE status=1 "+sqlx+" ORDER BY id DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Hotel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Hotel.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }


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

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