全网最使用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>

---