查询数据库客户信息显示在页面并进行相关操作

数据bean:

package com.samdi.quotesystem.common.bean.clientinfo;

import com.samdi.cps.common.bean.CPSRequestbaseBean;
import com.samdi.samdilab.utils.SamdiDaoLink;

import java.util.Date;

/**
 * Created by panguangyou on 2017/04/08.
 */
public class ClientinfoBean extends CPSRequestbaseBean {
    @SamdiDaoLink
    private Long clientid;
    @SamdiDaoLink
    private Long uid;
    @SamdiDaoLink(length = 64)
    private String clientname;
    @SamdiDaoLink(length = 256)
    private String business;
    @SamdiDaoLink(length = 128)
    private String address;
    @SamdiDaoLink(length = 64)
    private String email;
    @SamdiDaoLink
    private Integer grade;
    @SamdiDaoLink(length = 64)
    private String contacts;
    @SamdiDaoLink
    private String contactman;
    @SamdiDaoLink
    private String contactmansex;
    @SamdiDaoLink
    private Date createtime;
    @SamdiDaoLink
    private Date updatetime;
    @SamdiDaoLink(length = 256)
    private String mem;

    private Long workerid;

    public Long getClientid() {
        return clientid;
    }

    public void setClientid(Long clientid) {
        this.clientid = clientid;
    }

    public Long getUid() {
        return uid;
    }

    public void setUid(Long uid) {
        this.uid = uid;
    }

    public String getClientname() {
        return clientname;
    }

    public void setClientname(String clientname) {
        this.clientname = clientname;
    }

    public String getBusiness() {
        return business;
    }

    public void setBusiness(String business) {
        this.business = business;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getGrade() {
        return grade;
    }

    public void setGrade(Integer grade) {
        this.grade = grade;
    }

    public String getContacts() {
        return contacts;
    }

    public void setContacts(String contacts) {
        this.contacts = contacts;
    }

    public String getContactman() {
        return contactman;
    }

    public void setContactman(String contactman) {
        this.contactman = contactman;
    }

    public String getContactmansex() {
        return contactmansex;
    }

    public void setContactmansex(String contactmansex) {
        this.contactmansex = contactmansex;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public Date getUpdatetime() {
        return updatetime;
    }

    public void setUpdatetime(Date updatetime) {
        this.updatetime = updatetime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getMem() {
        return mem;
    }

    public void setMem(String mem) {
        this.mem = mem;
    }

    public Long getWorkerid() {
        return workerid;
    }

    public void setWorkerid(Long workerid) {
        this.workerid = workerid;
    }
}

用工具生成查询的sql文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.samdi.quotesystem.dao.client.ClientinfoDao">
    <insert id="insertClientinfo" parameterType="Clientinfo" useGeneratedKeys="true" keyProperty="clientid" keyColumn="clientid">
        <![CDATA[insert into clientinfo (uid,clientname,business,address,email,grade,contacts,contactman,contactmansex,createtime,updatetime,mem) values(#{uid},#{clientname},#{business},#{address},#{email},0,#{contacts},#{contactman},#{contactmansex},now(),now(),#{mem})]]>
    </insert>

    <update id="deleteClientinfo" parameterType="Long">
        <![CDATA[update clientinfo set fast=1,updatetime=now() where clientid=#{clientid}]]>
    </update>
    <update id="updateClientinfo" parameterType="Clientinfo">
        update clientinfo
        <set>
            <if test="uid != null">uid=#{uid},</if>
            <if test="clientname != null">clientname=#{clientname},</if>
            <if test="business != null">business=#{business},</if>
            <if test="address != null">address=#{address},</if>
            <if test="email != null">email=#{email},</if>
            <if test="grade != null">grade=#{grade},</if>
            <if test="contacts != null">contacts=#{contacts},</if>
            <if test="contactman != null">contactman=#{contactman},</if>
            <if test="contactmansex != null">contactmansex=#{contactmansex},</if>
            <if test="mem != null">mem=#{mem},</if>
            updatetime = now()
        </set>
        where clientid=#{clientid}
    </update>
    <select id="selectClientinfoList" parameterType="Clientinfo" resultType="Clientinfo">
        <![CDATA[select ci.*,cw.workerid from clientinfo ci,clientworker cw]]>
        <where>
            ci.fast=0 and ci.`clientid`=cw.`clientid`
            <if test="clientid != null">and ci.clientid = #{clientid}</if>
            <if test="uid != null">and ci.uid = #{uid}</if>
            <if test="clientname != null">and ci.clientname = #{clientname}</if>
            <if test="address != null">and ci.address = #{address}</if>
            <if test="email != null">and ci.email = #{email}</if>
            <if test="grade != null">and ci.grade = #{grade}</if>
            <if test="contacts != null">and ci.contacts = #{contacts}</if>
            <if test="contactman != null">and ci.contactman=#{contactman}</if>
            <if test="contactmansex != null">and ci.contactmansex=#{contactmansex}</if>
            <if test="searchkey != null">
                and ci.clientname like '%${searchkey}%'
            </if>
        </where>
        <if test="pagesize > 0">
            limit ${pagestart} ,${pagesize}
        </if>
    </select>

    <select id="selectClientinfoListByWorkid" parameterType="Clientinfo" resultType="Clientinfo">
        <![CDATA[select ci.* from clientworker cw,clientinfo ci]]>
        <where>
            ci.`clientid`=cw.`clientid` and cw.workerid = #{workerid} and ci.fast=0
            <if test="clientid != null">and ci.clientid = #{clientid}</if>
            <if test="uid != null">and ci.uid = #{uid}</if>
            <if test="clientname != null">and ci.clientname = #{clientname}</if>
            <if test="address != null">and ci.address = #{address}</if>
            <if test="email != null">and ci.email = #{email}</if>
            <if test="grade != null">and ci.grade = #{grade}</if>
            <if test="contacts != null">and ci.contacts = #{contacts}</if>
            <if test="searchkey != null">
                and ci.clientname like '%${searchkey}%'
            </if>
        </where>
        <if test="pagesize > 0">
            limit ${pagestart} ,${pagesize}
        </if>
    </select>
</mapper>

写查询方法：

package com.samdi.quotesystem.manager.client;

import com.samdi.quotesystem.common.bean.clientinfo.ClientinfoBean;
import com.samdi.quotesystem.common.bean.clientinfo.ClientworkerBean;
import com.samdi.quotesystem.dao.client.ClientinfoDao;
import com.samdi.quotesystem.dao.client.ClientworkerDao;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;

@Service
@Transactional
public  class  ClientManagerImp implements ClientManager{
    private final ClientinfoDao clientinfoDao;
    private final ClientworkerDao clientworkerDao;

    @Autowired
    public ClientManagerImp(ClientinfoDao clientinfoDao, ClientworkerDao clientworkerDao) {
        this.clientinfoDao = clientinfoDao;
        this.clientworkerDao = clientworkerDao;
    }

    @Override
    public List<ClientinfoBean> getClientinfoList(ClientinfoBean workerid) {
        workerid.setWorkerid(workerid.getWorkerid());
        workerid.setSearchkey(workerid.getSearchkey());
        if (workerid.getPagesize()>0) {
            workerid.setPagesize(workerid.getPagesize());
            workerid.setPageindex(workerid.getPageindex());
        }
        List<ClientinfoBean> list;
        try {
            list=clientinfoDao.selectClientinfoList(workerid);
        } catch (Exception e) {
            e.printStackTrace();
            list=new ArrayList<>();
        }
        return list;
    }

    @Override
    public List<ClientinfoBean> getAllClientinfo(ClientinfoBean page) {
        page.setWorkerid(page.getWorkerid());
        page.setSearchkey(page.getSearchkey());
        if (page.getPagesize()>0) {
            page.setPagesize(25);
            page.setPageindex(page.getPageindex());
        }
        List<ClientinfoBean> list;
        try {
            list=clientinfoDao.selectClientinfoList(page);
        } catch (Exception e) {
            e.printStackTrace();
            list =new ArrayList<>();
        }
        return list;
    }

    @Override
    public boolean getClientinfoByCid(ClientinfoBean clientinfo) {
        boolean r=false;
        ClientinfoBean param=new ClientinfoBean();
        param.setClientid(clientinfo.getClientid());
        List<ClientinfoBean> list= clientinfoDao.selectClientinfoList(param);
        if (list!=null&&list.size()>0){
            try {
                BeanUtils.copyProperties(clientinfo,list.get(0));
                r=true;
            } catch (Exception e) {
                r=false;
            }
        }
        return r;
    }

    @Override
    public boolean createClientinfo(ClientinfoBean clientinfo) {
        boolean r;
        try {
            clientinfoDao.insertClientinfo(clientinfo);
            ClientworkerBean cw=new ClientworkerBean();
            cw.setClientid(clientinfo.getClientid());
            cw.setWorkerid(clientinfo.getWorkerid());
            clientworkerDao.insertClientworker(cw);
            r=true;
        } catch (Exception e) {
            e.printStackTrace();
            r=false;
        }
        return r;
    }

    @Override
    public boolean updateClientinfo(ClientinfoBean clientinfo) {
        boolean r;
        try {
            clientinfoDao.updateClientinfo(clientinfo);
            r=true;
        } catch (Exception e) {
            e.printStackTrace();
            r=false;
        }
        return r;
    }

    @Override
    public boolean removeClientinfo(Long cid) {
        boolean r;
        try {
            clientinfoDao.deleteClientinfo(cid);
            r=true;
        } catch (Exception e) {
            e.printStackTrace();
            r=false;
        }
        return r;
    }

    @Override
    public List<ClientinfoBean> searchClientinfo(ClientinfoBean param) {
        return clientinfoDao.selectClientinfoList(param);
    }
}

写controller文件：

package com.samdi.quotesystem.controller.crm;

import com.samdi.quotesystem.common.bean.clientinfo.ClientinfoBean;
import com.samdi.quotesystem.controller.QSBaseController;
import com.samdi.quotesystem.manager.client.ClientManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by panguangyou on 2017/04/08.
 */
@Controller
@RequestMapping("/crm")
public class CRMClientinfoController extends QSBaseController {
    private final ClientManager clientManager;

    @Autowired
    public CRMClientinfoController(ClientManager clientManager) {
        this.clientManager = clientManager;
    }

    @RequestMapping(value = "/clientlist.act")
    public String clientlist(@RequestParam(value = "p",required = false,defaultValue = "0") int pageindex,@RequestParam(value = "s",required = false,defaultValue = "") String searchkey){
        getRequest().setAttribute("searchkey",searchkey);
        if(getSessionUserinfo().getType() == 1){
            ClientinfoBean workerparam = new ClientinfoBean();
            workerparam.setWorkerid(getSessionUserinfo().getWid());
            workerparam.setPagesize(25);
            workerparam.setPageindex(pageindex);
            if(!searchkey.equals("")){
                workerparam.setSearchkey(searchkey);
            }
            getRequest().setAttribute("list",clientManager.getClientinfoList(workerparam));
        }else if(getSessionUserinfo().getType() == 2){
            ClientinfoBean pageparam = new ClientinfoBean();
            pageparam.setPagesize(25);
            pageparam.setPageindex(pageindex);
            if(!searchkey.equals("")){
                pageparam.setSearchkey(searchkey);
            }
            getRequest().setAttribute("list",clientManager.getAllClientinfo(pageparam));
        }else{
            List<ClientinfoBean> list = new ArrayList<>();
            getRequest().setAttribute("list",list);
        }
        return "crm/clients/clientlist";
    }
    @RequestMapping(value = "/clientCreate.act")
    public String clientCreate(){
        getRequest().setAttribute("act","create");
        return "crm/clients/client";
    }
    @RequestMapping(value = "/clientEdit.act")
    public String clientEdit(@RequestParam("cid") Long cid){
        ClientinfoBean clientinfoBean = new ClientinfoBean();
        clientinfoBean.setClientid(cid);
        boolean r = clientManager.getClientinfoByCid(clientinfoBean);
        if(r){
            clientinfoBean.setStatus(0);
        }else{
            clientinfoBean.setStatus(1);
        }
        getRequest().setAttribute("client",clientinfoBean);
        getRequest().setAttribute("act","update");
        return "crm/clients/client";
    }

    @RequestMapping(value = "/createClientInfo.act")
    public @ResponseBody Map<String,Object> createClientInfo(ClientinfoBean clientinfo){
        clientinfo.setWorkerid(getSessionUserinfo().getWid());
        boolean r = clientManager.createClientinfo(clientinfo);
        if(r){
            clientinfo.setStatus(0);
        }else{
            clientinfo.setStatus(-1);
        }
        return bean2map(clientinfo);
    }

    @RequestMapping(value = "/updateClientInfo.act")
    public @ResponseBody Map<String,Object> updateClientInfo(ClientinfoBean clientinfo){
        boolean r = clientManager.updateClientinfo(clientinfo);
        if(r){
            clientinfo.setStatus(0);
        }else{
            clientinfo.setStatus(-1);
        }
        return bean2map(clientinfo);
    }

    @RequestMapping(value = "/deleteClientInfo.act")
    public @ResponseBody Map<String,Object> deleteClientInfo(@RequestParam("cid") Long cid){
        boolean r = clientManager.removeClientinfo(cid);
        Integer status;
        if(r){
            status = 0;
        }else{
            status = -1;
        }
        return status2map(status);
    }
}

写jsp页面：

<%--
  Created by IntelliJ IDEA.
  User: SevilinMa
  Date: 2016/11/8
  Time: 14:58
  To change this template use File | Settings | File Templates.
--%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
    <head>
        <title>客户信息</title>
        <script src="<c:url value="/js/jquery-2.2.3.min.js"/>"></script>
        <script src="<c:url value="/js/jalert.js"/>"></script>
        <script src="<c:url value="/js/crm/client.js"/>"></script>
        <script src="<c:url value="/js/crm/utils.js"/>"></script>
        <link href="<c:url value="/css/clientinfo.css"/>" rel="stylesheet" />
        <script>
            <c:choose>
                <c:when test="${paramValues['p'][0] == null}">var p = 0;</c:when>
                <c:otherwise>var p = ${paramValues['p'][0]};</c:otherwise>
            </c:choose>
            <c:choose>
                <c:when test="${paramValues['s'][0] != null}">var s = "&s=${paramValues['s'][0]}";</c:when>
                <c:otherwise>var s = '';</c:otherwise>
            </c:choose>
            function last() {
                if(p>0){
                    self.location.href = "/crm/clientlist.act?p="+(p-1)+s;
                }
            }
            function next() {
                self.location.href = "/crm/clientlist.act?p="+(p+1)+s;
            }
        </script>
    </head>
    <body>
        <div class="samdi-origin-div">
            <div class="crm-main-banner">
                <div class="div-line">
                    <div class="welcome-title">${userssion_info.workername},欢迎登录 SamdiCRM系统</div>
                    <div class="welcome-title"><a href="javascript:logout()"> | 退出</a></div>
                    <div class="welcome-title"><a href="javascript:changePassword()"> | 修改密码</a></div>
                </div>
                <div class="div-line">
                    <button type="button" οnclick="clientlist()" >客户信息</button>
                    <button type="button" οnclick="orderlist()">订单信息</button>
                    <c:if test="${userssion_info.type == 2}">
                        <button type="button" οnclick="workerlist()">人员信息</button>
                        <button type="button" οnclick="orderPrProcess()">询单分配</button>
                    </c:if>
                    <button type="button" οnclick="addClientinfo()">录入客户信息</button>
                    <input id="searchkey" type="text" value="${searchkey}" placeholder="支持模糊查找"/><button type="button" οnclick="search()">查询</button>
                </div>
                <div class="div-line">
                    <button type="button" <c:if test="${paramValues['p'][0] == null || paramValues['p'][0] > 0}">οnclick="last()"</c:if>>上一页</button>
                    <button type="button" <c:if test="${fn:length(list) == 25}">οnclick="next()"</c:if>>下一页</button>
                </div>
            </div>
            <div class="crm-clientlist-body">
                <div class="clientlist-line">
                    <div class="clientlist-line-index">序号</div>
                    <div class="clientlist-line-name">客户名称</div>
                    <div class="clientlist-line-grade">等级</div>
                    <div class="clientlist-line-createtime">创建时间</div>
                    <div class="clientlist-line-control">操作</div>
                </div>
                <c:forEach var="item" items="${list}">
                    <div class="clientlist-line">
                        <div class="clientlist-line-index">${item.clientid}</div>
                        <div class="clientlist-line-name">${item.clientname}</div>
                        <div class="clientlist-line-grade">${item.grade}</div>
                        <div class="clientlist-line-createtime"><fmt:formatDate value="${item.createtime}" pattern="yyyy-MM-dd"/></div>
                        <div class="clientlist-line-control">
                            <a href="javascript:editClientinfo(${item.clientid})">编辑</a>
                            |
                            <a href="javascript:orderslistByClientid(${item.clientid})">订单</a>
                            |
                            <a href="javascript:deleteClientinfo(${item.clientid})">删除</a>
                        </div>
                    </div>
                </c:forEach>
            </div>
        </div>
        <form id="cpf">
            <input type="hidden" name="wid" value="${userssion_info.wid}"/>
            <input type="hidden" name="password">
        </form>
    </body>
</html>