brakeService/VisitorDaoImpl.java

package com.happy.dao.impl;

import com.happy.Model.Visitor;
import com.happy.dao.VisitorDao;
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("Visitor")
public class VisitorDaoImpl implements VisitorDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public List<Visitor> combGetInfoList(String sqlx, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="select * from info " + sqlx + " limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Visitor> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Visitor>(Visitor.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int findCombPageTotal(String sqlx) {
        String sql = "select count(*) from info " + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public int findInfoTotal() {
        String sql = "select count(*) from info ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("fake", 0);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    // 根据当前页和每页条数查询所有员工
    public List<Visitor> getlist(int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from info order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Visitor> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Visitor>(Visitor.class));
        return list;
    }

    public int insertInfo(Visitor visitor){
        String sql = "insert into info(visitor_name, visitor_phone, reason, status, status_with_time, card_number, visited_at, id_card_img, created_time, visit_start_time, visit_end_time, member_name, member_phone, member_org, car_number, time) values(:visitor_name, :visitor_phone, :reason, :status, :status_with_time, :card_number, :visited_at, :id_card_img, :created_time, :visit_start_time, :visit_end_time, :member_name, :member_phone, :member_org, :car_number, :time)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("visitor_name", visitor.getVisitor_name());
        sps.addValue("visitor_phone", visitor.getVisitor_phone());
        sps.addValue("reason", visitor.getReason());
        sps.addValue("status", visitor.getStatus());
        sps.addValue("status_with_time", visitor.getStatus_with_time());
        sps.addValue("card_number", visitor.getCard_number());
        sps.addValue("visited_at", visitor.getVisited_at());
        sps.addValue("id_card_img", visitor.getId_card_img());
        sps.addValue("created_time", visitor.getCreated_time());
        sps.addValue("visit_start_time", visitor.getVisit_start_time());
        sps.addValue("visit_end_time", visitor.getVisit_end_time());
        sps.addValue("member_name", visitor.getMember_name());
        sps.addValue("member_phone", visitor.getMember_phone());
        sps.addValue("member_org", visitor.getMember_org());
        sps.addValue("car_number", visitor.getCar_number());
        sps.addValue("time", visitor.getTime());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

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

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

}