waterElectricityService/ConsumeDaoImpl.java

package com.happy.dao.impl;

import com.happy.Model.Consume;
import com.happy.Model.Price;
import com.happy.dao.ConsumeDao;
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.math.BigDecimal;
import java.util.List;

@Repository("ConsumeDao")
public class ConsumeDaoImpl implements ConsumeDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public List<Consume> queryRecordByOrder(String order_no){
        String sql="select * from consume where order_no=:order_no";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("order_no", order_no);
        List<Consume> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Consume>(Consume.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public List<Consume> queryRecordByCard(String card_number, String begin_time){
        String sql="select * from consume where card_number=:card_number and use_amount>0 and begin_time like '"+ begin_time +"%' order by id desc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("card_number", card_number);
        List<Consume> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Consume>(Consume.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public List<Consume> queryRecordByStu(String stu_number, String begin_time){
        String sql="select * from consume where stu_number=:stu_number and use_amount>0 and begin_time like '"+ begin_time +"%' order by id desc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("stu_number", stu_number);
        List<Consume> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Consume>(Consume.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int insertComsume(Consume consume){
        String sql = "insert into consume(stu_number, user_name, order_no, begin_time, start_code, device_code, use_amount, use_size, rate) values(:stu_number, :user_name, :order_no, :begin_time, :start_code, :device_code, :use_amount, :use_size, :rate)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("stu_number", consume.getStu_number());
        sps.addValue("user_name", consume.getUser_name());
        sps.addValue("order_no", consume.getOrder_no());
        sps.addValue("begin_time", consume.getBegin_time());
        sps.addValue("start_code", consume.getStart_code());
        sps.addValue("device_code", consume.getDevice_code());
        sps.addValue("use_amount", consume.getUse_amount());
        sps.addValue("use_size", consume.getUse_size());
        sps.addValue("rate", consume.getRate());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateBalance(String stu_number, double balance){
        String sql = "update users set balance=:balance where stu_number=:stu_number";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("stu_number", stu_number);
        sps.addValue("balance", balance);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateConsume(String order_no, String pay_time, BigDecimal use_amount, BigDecimal use_size, int state){
        String sql = "update consume set pay_time=:pay_time, use_amount=:use_amount, use_size=:use_size, state=:state where order_no=:order_no";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pay_time", pay_time);
        sps.addValue("use_amount", use_amount);
        sps.addValue("state", state);
        sps.addValue("order_no", order_no);
        sps.addValue("use_size", use_size);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateConsumeStu(String stu_number, String user_name, String order_no){
        String sql = "update consume set stu_number=:stu_number, user_name=:user_name, order_no=:order_no where order_no=:order_no";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("stu_number", stu_number);
        sps.addValue("user_name", user_name);
        sps.addValue("order_no", order_no);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateConsume2(String order_no, String pay_time, double use_amount, double use_size, int state){
        String sql = "update consume set pay_time=:pay_time, use_amount=:use_amount, use_size=:use_size, state=:state where order_no=:order_no";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pay_time", pay_time);
        sps.addValue("use_amount", use_amount);
        sps.addValue("state", state);
        sps.addValue("order_no", order_no);
        sps.addValue("use_size", use_size);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public List<Price> water_price(String name){
        String sql = "select * from price where name=:name and state=1 and time in (select MAX(time) from `price` where name=:name and state=1)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", name);
        List<Price> list = namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Price>(Price.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }
}