bigdata_houtai/bigData/src/com/happy/dao/Impl/SecondBufferPImplDao.java

package com.happy.dao.Impl;

import com.happy.Model.SecondBufferP;
import com.happy.Model.User;
import com.happy.dao.SecondBufferPDao;
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.ArrayList;
import java.util.List;

@Repository("SecondBufferPDao")
public class SecondBufferPImplDao implements SecondBufferPDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public int insertSecondBufferP(SecondBufferP secondBufferP){
        String sql = "insert into `secondbufferp`(dateT,`dateTime`,place,red_count,yellow_count,green_count,url) values(:dateT,:dateTime,:place,:red_count,:yellow_count,:green_count,:url)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("dateT", secondBufferP.getDateT());
        sps.addValue("dateTime", secondBufferP.getDateTime());
        sps.addValue("place", secondBufferP.getPlace());
        sps.addValue("red_count", secondBufferP.getRed_count());
        sps.addValue("yellow_count", secondBufferP.getYellow_count());
        sps.addValue("green_count", secondBufferP.getGreen_count());
        sps.addValue("url", secondBufferP.getUrl());
        System.out.println(secondBufferP.getRed_count());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 各景点扫码详情
    public List<SecondBufferP> querySecondBufferP(){
        String sql = "select * from `secondbufferp` where id in(select MAX(id) from `secondbufferp` group by place) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<SecondBufferP> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<SecondBufferP>(SecondBufferP.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 各景点扫码详情
    public List<SecondBufferP> querySecondBufferpByPlace(String place){
        String sql = "select * from `secondbufferp` where place=:place and id=(select max(id) from `secondbufferp` where place=:place) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("place",place);
        List<SecondBufferP> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<SecondBufferP>(SecondBufferP.class));
        List<SecondBufferP> list2 = new ArrayList<>();
        if (list != null && list.size() > 0) {
            return list;
        } else {
            SecondBufferP sp = new SecondBufferP();
            sp.setGreen_count("0");
            sp.setRed_count("0");
            sp.setYellow_count("0");
            list2.add(sp);
            return list2;
        }
    }

    // 各景点人员扫码信息
    public List<User> queryByPlace(String sm_place){
        String sql = "select * from `sm_message` where protect_type='2' and sm_place=:sm_place order by `id` desc limit 0,20 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("sm_place", sm_place);
        List<User> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<User>(User.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

}