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>
