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

package com.happy.dao.impl;

import com.happy.Model.Booking;
import com.happy.Model.weixin.Users;
import com.happy.Until.Func;
import com.happy.Until.UUIDUtil;
import com.happy.dao.UserDao;
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("UserDao")
public class UserDaoImpl implements UserDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public int insert(Users users){
        String sql = "insert into `users`(card_number,user_name,user_phone,user_zz,openid,create_time,remark,identity_type,contact_id,contact_name,data_type,head_photo) values(:card_number,:user_name,:user_phone,:user_zz,:openid,:create_time,:remark,:identity_type,:contact_id,:contact_name,:data_type,:head_photo) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("card_number", users.getCard_number());
        sps.addValue("user_name", users.getUser_name());
        sps.addValue("user_phone", users.getUser_phone());
        sps.addValue("user_zz", users.getUser_zz());
        sps.addValue("openid", users.getOpenid());
        sps.addValue("create_time", UUIDUtil.getNewDate());
        sps.addValue("remark", users.getRemark());
        sps.addValue("identity_type", users.getIdentity_type());
        sps.addValue("contact_id", users.getContactId());
        sps.addValue("contact_name", users.getContactName());
        sps.addValue("data_type", users.getDataType());
        sps.addValue("head_photo", users.getHeadPhoto());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 修改信息
    public int update(Users users){
        String sql = "update `users` set user_name=:user_name,user_phone=:user_phone,user_zz=:user_zz,remark=:remark, head_photo=:head_photo,card_number=:card_number  where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", users.getId());
        sps.addValue("user_name", users.getUser_name());
        sps.addValue("user_phone", users.getUser_phone());
        sps.addValue("user_zz", users.getUser_zz());
        sps.addValue("remark", users.getRemark());
        sps.addValue("head_photo", users.getHeadPhoto());
        sps.addValue("card_number", users.getCard_number());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 修改信息
    public int updateUserByParam(Users users){
        StringBuffer stringBuffer =  new StringBuffer(" update `users` set  ");
        MapSqlParameterSource msp = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        appendValue(users,stringBuffer,msp);

        stringBuffer.append("  where id=:id  ");
        msp.addValue("id", users.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), msp);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public void appendValue(Users users, StringBuffer stringBuffer,MapSqlParameterSource msp)
    {
        if (!Func.checkNull(users.getUser_name()))
        {
            stringBuffer.append(" user_name=:user_name, ");
            msp.addValue("user_name", users.getUser_name());
        }
        if (!Func.checkNull(users.getCard_number()))
        {
            stringBuffer.append(" card_number=:card_number, ");
            msp.addValue("card_number", users.getCard_number());
        }
        if (!Func.checkNull(users.getUser_phone()))
        {
            stringBuffer.append(" user_phone=:user_phone ,");
            msp.addValue("user_phone", users.getUser_phone());
        }
        if (!Func.checkNull(users.getUser_zz()))
        {
            stringBuffer.append(" user_zz=:user_zz ,");
            msp.addValue("user_zz", users.getUser_zz());
        }
        if (!Func.checkNull(users.getOpenid()))
        {
            stringBuffer.append(" openid=:openid ,");
            msp.addValue("openid", users.getOpenid());
        }
        if (!Func.checkNull(users.getRemark()))
        {
            stringBuffer.append(" remark=:remark ,");
            msp.addValue("remark", users.getRemark());
        }
        if (!Func.checkDblZero(users.getIdentity_type()))
        {
            stringBuffer.append(" identity_type=:identity_type ,");
            msp.addValue("identity_type", users.getIdentity_type());
        }
        if (!Func.checkNull(users.getContactId()))
        {
            stringBuffer.append(" contact_id=:contact_id ,");
            msp.addValue("contact_id", users.getContactId());
        }
        if (!Func.checkNull(users.getContactName()))
        {
            stringBuffer.append(" contact_id=:contact_id ,");
            msp.addValue("contact_id", users.getContactId());
        }
        if (!Func.checkNull(users.getDataType()))
        {
            stringBuffer.append(" data_type=:data_type ,");
            msp.addValue("data_type", users.getDataType());
        }
        if (!Func.checkNull(users.getHeadPhoto()))
        {
            stringBuffer.append(" head_photo=:head_photo ,");
            msp.addValue("head_photo", users.getHeadPhoto());
        }

        stringBuffer.append(" remark=''");
    }


    public int del(int id){
        String sql = "delete from `users` 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;
    }

    public Users queryByNum(String card_number){
        String sql = "select * from `users` where card_number=:card_number ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("card_number", card_number);
        List<Users> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Users.class));
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

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

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

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

    @Override
    public int queryUserTotal(String sqlStr) {
        String sql = "select count(*) from `users` where 1=1 "+sqlStr;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<Users> queryUserPage(String sqlx, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from `users` where 1=1 "+sqlx+" order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Users> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Users.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return new ArrayList<>();
    }

}