第一步,在数据中创建用户表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"}
]]
});