jsp+servlet+layui用户查询

第一步，在数据中创建用户表userbale，为了方便创建数据库写的简单一点。

create table usertable(
    usersId char(20),
    userName char(20),
    userEmail char(20),
    userSex char(20),
    userStatus char(20),
    userGrade char(20),
    userEndTime char(20),
    userDesc char(20)
)

第二步，在idea中创建一个jsp项目如下：

创建jsp项目链接：

第三步：

1.创建类包命名为bean,然后创建一个类命名为userbean,代码如下：

public class userbean {
    private String usersId;
    private String userName;
    private String userEmail;
    private String userSex;
    private String userStatus;
    private String userGrade;
    private String userEndTime;
    private String userDesc;

    public String getUsersId() {
        return usersId;
    }

    public void setUsersId(String usersId) {
        this.usersId = usersId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserEmail() {
        return userEmail;
    }

    public void setUserEmail(String userEmail) {
        this.userEmail = userEmail;
    }

    public String getUserSex() {
        return userSex;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public String getUserStatus() {
        return userStatus;
    }

    public void setUserStatus(String userStatus) {
        this.userStatus = userStatus;
    }

    public String getUserGrade() {
        return userGrade;
    }

    public void setUserGrade(String userGrade) {
        this.userGrade = userGrade;
    }

    public String getUserEndTime() {
        return userEndTime;
    }

    public void setUserEndTime(String userEndTime) {
        this.userEndTime = userEndTime;
    }

    public String getUserDesc() {
        return userDesc;
    }

    public void setUserDesc(String userDesc) {
        this.userDesc = userDesc;
    }
}

2.创建dao包,创建一个类命名为userdao:代码如下：

public class userdao {
    String FORNAME="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    String url="jdbc:sqlserver://localhost:1433;DatabaseName=stu";
    String user="sa";
    String pwd="hsfy123321123";
    public userdao()
    {
        try{
            Class.forName(FORNAME);
        }catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }
    public Connection getConnection() throws SQLException
    {
        return  DriverManager.getConnection(url,user,pwd);
    }
    public List<userbean> userlist(String sql) throws SQLException
    {
        List<userbean> user=new ArrayList<>();
        Connection c=getConnection();
        PreparedStatement ps=c.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        while(rs.next())
        {
            userbean ur=new userbean();
            ur.setUsersId(rs.getString("usersId").trim());
            ur.setUserName(rs.getString("userName").trim());
            ur.setUserEmail(rs.getString("userEmail").trim());
            ur.setUserSex(rs.getString("userSex").trim());
            ur.setUserStatus(rs.getString("userStatus").trim());
            ur.setUserGrade(rs.getString("userGrade").trim());
            ur.setUserEndTime(rs.getString("userEndTime").trim());
            ur.setUserDesc(rs.getString("userDesc").trim());
            user.add(ur);
        }
        ps.close();
        c.close();
        return user;
    }

3.创建servlet包，创建一个类命名为Userservlet

public class Userservlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }


    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out=resp.getWriter();
        org.json.JSONObject jsonObject=new org.json.JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","");
        jsonObject.put("count",1);
        String sql="select * from usertable";
        JSONArray result=null;
        try{
            userdao he=new userdao();
            result = JSONArray.fromObject( he.userlist(sql));
        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        jsonObject.put("data",result);
        System.out.println(jsonObject.toString());
        out.println(jsonObject.toString());
        out.flush();
        out.close();
    }
}

servlet配置如下：

    <servlet>
        <servlet-name>UserServlet</servlet-name>
        <servlet-class>servlet.Userservlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>UserServlet</servlet-name>
        <url-pattern>/UserServlet</url-pattern>
    </servlet-mapping>

第四步 输出UserServlet

第五部 使用layui列表

//用户列表
    var tableIns = table.render({
        elem: '#userList',
        url : '/usersystem/UserServlet',
        cellMinWidth : 95,
        page : true,
        height : "full-125",
        limits : [10,15,20,25],
        limit : 20,
        id : "userListTable",
        cols : [[
            {type: "checkbox", fixed:"left", width:50},
            {field: 'userName', title: '用户名', minWidth:100, align:"center",sort: true},
            {field: 'userEmail', title: '用户邮箱', minWidth:200, align:'center',templet:function(d){
                return '<a class="layui-blue" href="mailto:'+d.userEmail+'">'+d.userEmail+'</a>';
            }},
            {field: 'userSex', title: '用户性别', align:'center',sort: true},
            {field: 'userStatus', title: '用户状态',  align:'center',templet:function(d){
                return d.userStatus == "0" ? "正常使用" : "限制使用";
            }},
            {field: 'userGrade', title: '用户等级', align:'center',templet:function(d){
                if(d.userGrade == "0"){
                    return "注册会员";
                }else if(d.userGrade == "1"){
                    return "中级会员";
                }else if(d.userGrade == "2"){
                    return "高级会员";
                }else if(d.userGrade == "3"){
                    return "钻石会员";
                }else if(d.userGrade == "4"){
                    return "超级会员";
                }
            }},
            {field: 'userEndTime', title: '最后登录时间', align:'center',minWidth:150},
            {title: '操作', minWidth:175, templet:'#userListBar',fixed:"right",align:"center"}
        ]]
    });