全网最使用MySQL， JSP, Bean, JDBC(连接数据库) ，制作有权限（老师，学生，辅导员）用户登录demo，表单查看demo

1.准备相关数据,创建数据库（Lab_db）

create database Lab_DB DEFAULT CHAR SET utf8;  /*创建数据库Lab_db*/
use Lab_DB;                                     /*使用数据库Lab_db*/
/*创建表结构*/
create table user(id_user int not null auto_increment,account varchar(10)not null,
password varchar(10)null,username varchar(10)null,gender varchar(2)null ,
department varchar(20)null,access_rights varchar(10),primary key (id_user));

2.写入测试数据

insert into user(account, password, username, gender, department, access_rights)
values('demo005','007','小wa哥','男','技术','老师');
insert into user(account, password, username, gender, department, access_rights)
values('demo006','007','小wa哥','女','计算机','辅导员');
insert into user(account, password, username, gender, department, access_rights)
values('demo008','007','小wa哥','男','技术','辅导员');
insert into user(account, password, username, gender, department, access_rights)
values('demo009','007','小wa哥','男','技术','老师');
insert into user(account, password, username, gender, department, access_rights)
values('demo001','007','小wa哥','男','技术','老师');
insert into user(account, password, username, gender, department, access_rights)
values('demo002','007','小wa哥','男','技术','老师');
select * from user;

目录结构

bean.demo
- ContentBean
- dataBean
JDBC(用于测试数据库连接是否成功！)
- JDBCdemo
servlet.demo
- deleteDemo
- updataDemo
- registerDemo
WEB
- index.html
- conentbeanDemo.jsp
- user.html

ConnectBean.java

package bean.demo;

import java.sql.Connection;
import bean.demo.dataBean;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectBean {
       //数据库信息
        String url = "jdbc:mysql://localhost:3306/Lab_DB"; 
        String user = "root";   
        String pwd = "admin"; 
      
        private Connection conn;  //连接
        private Statement stmt_select;     //SQL语句
        private Statement stmt_delete;
        private Statement stmt_update;
        private Statement stmt_insert;
        ResultSet rs;            //结果集
        
       
            
        //实例化JavaBean对象的同时创建数据库连接
   public  ConnectBean() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,pwd);
        }catch(Exception e) {
            e.printStackTrace();  
        }
   }
     

    public String access(String account) throws SQLException {
       
      //"学生“权限只显示单个记录，其他权限显示所有记录；      
       String sql = "select * from user where account='"+account+"'";
       dataBean test=new dataBean();
       ResultSet rs=selectSql(sql);
       
          if(rs.next() ) {
              //System.out.println(rs.getString("access_rights"));
              test.setAccess_rights(rs.getString("access_rights"));
          
             switch(rs.getString("access_rights")) {
                case "学生":break;
                default: sql="select * from user";            
             }
             
          }
          return sql;
       
    }
    
    //查询记录
    public ResultSet selectSql(String sql) {
       
       try {
           stmt_select=conn.createStatement();
           rs = stmt_select.executeQuery(sql);
       }catch(SQLException e) {
           e.printStackTrace();   
       }
       
      return rs;       
       
    }
    
    //添加记录
    public int insertSql(String sql) {
       
       try {
           stmt_insert=conn.createStatement();
           return stmt_insert.executeUpdate(sql); 
       }catch(SQLException e) {
           e.printStackTrace();   
       }
      return 0;

     } 
    //删除记录
 public int deleteSql(String sql) {
   
   try {
       stmt_delete=conn.createStatement();
       return stmt_delete.executeUpdate(sql); 
   }catch(SQLException e) {
       e.printStackTrace();   
   }
  return 0;

 } 
 //更新记录
 public int updateSql(String sql) {
   
   try {
       stmt_update=conn.createStatement();
       return stmt_update.executeUpdate(sql);  
   }catch(SQLException e) {
       e.printStackTrace();   
   }
   
      return 0;        
   
 }
 
 //关闭数据库连接
    public void closeConn() {
       try {
           if(conn!=null)   
               conn.close();
       }catch(SQLException e) {
           e.printStackTrace();   
       }
      
    }
  
}

dataBean

package bean.demo;

public class dataBean {
   //定义成员变量
   private int id_user;
   private String account;
   private String password;
   private String username;
   private String gender; 
   private String department;
   private String access_rights; 
   
   public dataBean() {
    
   }
   
   public dataBean(int id_user, String account, String password, String username, String gender, String department,
     String access_rights) {
    super();
    this.id_user = id_user;
    this.account = account;
    this.password = password;
    this.username = username;
    this.gender = gender;
    this.department = department;
    this.access_rights = access_rights;
   }
   
   
   public int getId_user() {
    return id_user;
   }
   public void setId_user(int id_user) {
    this.id_user = id_user;
   }
   public String getAccount() {
    return account;
   }
   public void setAccount(String account) {
    this.account = account;
   }
   public String getPassword() {
    return password;
   }
   public void setPassword(String password) {
    this.password = password;
   } 
   public String getUsername() {
    return username;
   }
   public void setUsername(String username) {
    this.username = username;
   }
   public String getGender() {
    return gender;
   }
   public void setGender(String gender) {
    this.gender = gender;
   }
   public String getDepartment() {
    return department;
   }
   public void setDepartment(String department) {
    this.department = department;
   }
   public String getAccess_rights() {
    return access_rights;
   }
   public void setAccess_rights(String access_rights) {
    this.access_rights = access_rights;
   }
      
  }

JDBCdemo.java

package JDBCdemo;
import java.sql.Connection;
import java.sql.DriverManager;

public class jdbcdemo {
  public static void main(String args[]){
      try {
          Class.forName("com.mysql.cj.jdbc.Driver");
          System.out.println("成功加载mysql数据库驱动程序！");
      }
      catch(Exception e) 
      {
          System.out.println("加载sql程序时出现错误！");
          e.printStackTrace();
          
      }
      try {
          //链接数据库
          Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/lab_db","root","admin");
          System.out.println("成功连接数据库！");
      }
      catch(Exception e){
          System.out.println("连接数据失败！");
          e.printStackTrace();
      }
  }

}

deleteDemo.java

package servlet.demo;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.demo.ConnectBean;
import java.sql.*;

@WebServlet("/deleteDemo")

public class deleteDemo extends HttpServlet {
  private static final long serialVersionUID = 1L;
       
  ConnectBean conn=new ConnectBean();

 
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
      String sql="delete from user where "+request.getQueryString();  
      conn.deleteSql(sql);
        response.sendRedirect("connectbeanDemo.jsp");
  }


  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      // TODO Auto-generated method stub
      doGet(request, response);
  }

}

registerDemo

package servlet.demo;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import bean.demo.ConnectBean;


@WebServlet("/registerDemo")

public class registerDemo extends HttpServlet {
  private static final long serialVersionUID = 1L;
       

  ConnectBean conn=new ConnectBean();
    
    
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      
      request.setCharacterEncoding("UTF-8");
      String account=request.getParameter("account");
      String password=request.getParameter("password");
      String username=request.getParameter("username");
      String gender=request.getParameter("gender");
      String department=request.getParameter("department");
      String access_rights=null;
      
      //对不同的部门赋予不同的权限
       switch(department){
       case "信息部": access_rights="系统管理员";break;
       case "教务部": access_rights="老师";break;
       case "辅导员": access_rights="辅导员";break;
       default: access_rights="学生";
       }
      
      String sql="insert into user(account,password,username,gender,department,access_rights) values(\'"+account+"\',\'"+password+"\',\'"+username+"\',\'"+gender+"\',\'"+department+"\',\'"+access_rights+"\')";
   //  System.out.println(sql);
      conn.insertSql(sql);
       // response.sendRedirect("connectbeanDemo.jsp");
        response.sendRedirect("index.html");
  }


  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  
      doGet(request, response);
  }

}

updateDemo

package servlet.demo;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.websocket.Session;

import bean.demo.ConnectBean;
import java.sql.*;

@WebServlet("/updateDemo")
public class updateDemo extends HttpServlet {

  private static final long serialVersionUID = 1L;
  
  ConnectBean conn=new ConnectBean();

  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      response.setContentType("text/html");
      response.setCharacterEncoding("UTF-8");
          PrintWriter out=response.getWriter();
          HttpSession session=request.getSession(); 
          
 if(request.getParameter("update_submit")==null) {
   
    session.setAttribute("whereClause",request.getQueryString());  
    
     out.println("<!DOCTYPE html><html>");
     out.println("<form action=\"updateDemo\" method=\"get\">");
     out.println("<label>用户：</label>");
     out.println("<input type=\"text\" name=\"username\"/><br>");
     out.println("<label>性别：</label>");
     out.println("<select name='gender'>");
     out.println("<option>男</option>\n");
     out.println("<option>女</option>\n");
     out.println("</select><br>");
     out.println("<label>所属部门：</label>");
     out.println("<select name='department'>");
     out.println("<option>1班</option>\n");
     out.println("<option>2班</option>\n");
     out.println("<option>3班</option>\n");
     out.println("<option>4班</option>");
     out.println("<option>辅导员</option>");
     out.println("<option>信息部</option>");
     out.println("<option>教务部</option>");
     out.println("</select><br><br>");
     out.println("<input type=\"submit\" name=\"update_submit\" value=\"提交修改\"/><br>\n"+ "</form></html>");
     
    }else {   
         //根据“所属部门”，赋于对应权限
        String access_rights;
        switch(request.getParameter("department")) {
           case "信息部": access_rights="系统管理员";break;
           case "教务部": access_rights="老师";break;
           case "辅导员": access_rights="辅导员";break;
           default: access_rights="学生";
       }
     String sql="update user set username=\""+request.getParameter("username")+"\", gender=\""+request.getParameter("gender")+"\",department=\""+request.getParameter("department")+"\",access_rights=\""+access_rights+"\"  where "+ session.getAttribute("whereClause");
      // System.out.println(sql);
        conn.updateSql(sql);
         response.sendRedirect("connectbeanDemo.jsp");  
     }  
  
  }
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  
      doGet(request, response);
  }

}

connectionbeanDemo.JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@page import="bean.demo.ConnectBean"%>
<%@page import="bean.demo.dataBean"%>
<%@page import="java.io.IOException"%>
<%@page import="java.sql.*"%>
<%@ page import="java.sql.Connection"%>

  
<!DOCTYPE html>
<html>
<head>

<title>Insert title here</title>
</head>
<body>
 <jsp:useBean id="data" class="bean.demo.dataBean" scope="session"/>
 <jsp:useBean id="connect" class="bean.demo.ConnectBean" scope="session"/>
<%  
String sql;

if(request.getParameter("login_submit")!=null) {
  sql=connect.access(request.getParameter("account"));
 
}else sql="select * from user";

System.out.println(sql);
ResultSet rs=connect.selectSql(sql);

%>
<table border="1" width="800">
<tr>
  <td width="100" align="center">ID</td>
   <td width="200" align="center">用户</td>
   <td width="200" align="center">性别</td>
   <td width="200" align="center">所在班级</td>
   <td width="200" align="center">权限</td>
    <td width="400" align="center">操作</td>   
</tr>

<%  while(rs.next()){    %>   

  <tr>
   <td width="100" align="center"><%=rs.getInt("id_user")%></td>
     <td width="200" align="center"><%=rs.getString("username")%></td>
     <td width="200" align="center"><%=rs.getString("gender")%></td>
     <td width="200" align="center"><%=rs.getString("department")%></td>
     <td width="200" align="center"><%=rs.getString("access_rights")%></td>
     <td align="center"><a href="updateDemo?id_user=<%=rs.getInt("id_user") %>" >修改</a>
        <a href="deleteDemo?id_user=<%=rs.getInt("id_user") %>" onclick="return confirm('确定将此记录删除?')">删除</a>
     </td>    
   </tr>
<% }   %>


</table>

<input type="button" value="返回首页" onclick="{location.href='index.html'}" />
<input type="button" value="新增记录" onclick="{location.href='user.html'}" />

</body>
</html>

index.html

<%@ page language="java" contentType="text/html; charset=UTF-8"
   pageEncoding="UTF-8"%>
   
<%@page import="bean.demo.ConnectBean"%>
<%@page import="bean.demo.dataBean"%>
<%@page import="java.io.IOException"%>
<%@page import="java.sql.*"%>
<%@ page import="java.sql.Connection"%>

 
<!DOCTYPE html>
<html>
<head>

<title>Insert title here</title>
</head>
<body>
<jsp:useBean id="data" class="bean.demo.dataBean" scope="session"/>
<jsp:useBean id="connect" class="bean.demo.ConnectBean" scope="session"/>
<%  
String sql;

if(request.getParameter("login_submit")!=null) {
 sql=connect.access(request.getParameter("account"));

}else sql="select * from user";

System.out.println(sql);
ResultSet rs=connect.selectSql(sql);

%>
<table border="1" width="800">
<tr>
 <td width="100" align="center">ID</td>
  <td width="200" align="center">用户</td>
  <td width="200" align="center">性别</td>
  <td width="200" align="center">所在班级</td>
  <td width="200" align="center">权限</td>
   <td width="400" align="center">操作</td>   
</tr>

<%  while(rs.next()){    %>    

 <tr>
   <td width="100" align="center"><%=rs.getInt("id_user")%></td>
    <td width="200" align="center"><%=rs.getString("username")%></td>
    <td width="200" align="center"><%=rs.getString("gender")%></td>
    <td width="200" align="center"><%=rs.getString("department")%></td>
    <td width="200" align="center"><%=rs.getString("access_rights")%></td>
    <td align="center"><a href="updateDemo?id_user=<%=rs.getInt("id_user") %>" >修改</a>
       <a href="deleteDemo?id_user=<%=rs.getInt("id_user") %>" onclick="return confirm('确定将此记录删除?')">删除</a>
    </td>    
  </tr>
<% }   %>


</table>

<input type="button" value="返回首页" onclick="{location.href='index.html'}" />
<input type="button" value="新增记录" onclick="{location.href='user.html'}" />

</body>
</html>

user.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form  action = "registerDemo" method = "get">


  学号/工号：<input type="text" name="account"/><br>
  密码:<input type = "text" name = "password"/> <br>
  用户名:<input type = "text" name = "username"/> <br>
  性别:<input type = "text" name = "gender"/> <br>
  所在班级:<input type = "text" name = "department"/> <br>
  权限:<input type = "text" name = "access_rights"/> <br>
     <input name="Submit" type="submit" value="添加" onclick=getParameter>
</form>

</body>
</html>

全网最使用MySQL， JSP, Bean, JDBC(连接数据库) ，制作有权限（老师，学生，辅导员） 用户登录demo，表单查看demo

1.准备相关数据,创建数据库（Lab_db）

2.写入测试数据

目录结构

推荐阅读更多精彩内容

全网最使用MySQL， JSP, Bean, JDBC(连接数据库) ，制作有权限（老师，学生，辅导员）用户登录demo，表单查看demo