1.準(zhǔn)備相關(guān)數(shù)據(jù),創(chuàng)建數(shù)據(jù)庫(kù)(Lab_db)
create database Lab_DB DEFAULT CHAR SET utf8; /*創(chuàng)建數(shù)據(jù)庫(kù)Lab_db*/
use Lab_DB; /*使用數(shù)據(jù)庫(kù)Lab_db*/
/*創(chuàng)建表結(jié)構(gòu)*/
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.寫入測(cè)試數(shù)據(jù)
insert into user(account, password, username, gender, department, access_rights)
values('demo005','007','小wa哥','男','技術(shù)','老師');
insert into user(account, password, username, gender, department, access_rights)
values('demo006','007','小wa哥','女','計(jì)算機(jī)','輔導(dǎo)員');
insert into user(account, password, username, gender, department, access_rights)
values('demo008','007','小wa哥','男','技術(shù)','輔導(dǎo)員');
insert into user(account, password, username, gender, department, access_rights)
values('demo009','007','小wa哥','男','技術(shù)','老師');
insert into user(account, password, username, gender, department, access_rights)
values('demo001','007','小wa哥','男','技術(shù)','老師');
insert into user(account, password, username, gender, department, access_rights)
values('demo002','007','小wa哥','男','技術(shù)','老師');
select * from user;
目錄結(jié)構(gòu)
- bean.demo
- ContentBean
- dataBean
- JDBC(用于測(cè)試數(shù)據(jù)庫(kù)連接是否成功!)
- 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 { //數(shù)據(jù)庫(kù)信息 String url = "jdbc:mysql://localhost:3306/Lab_DB"; String user = "root"; String pwd = "admin"; private Connection conn; //連接 private Statement stmt_select; //SQL語(yǔ)句 private Statement stmt_delete; private Statement stmt_update; private Statement stmt_insert; ResultSet rs; //結(jié)果集 //實(shí)例化JavaBean對(duì)象的同時(shí)創(chuàng)建數(shù)據(jù)庫(kù)連接 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 { //"學(xué)生“權(quán)限只顯示單個(gè)記錄,其他權(quán)限顯示所有記錄棍厌; 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 "學(xué)生":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; } //關(guān)閉數(shù)據(jù)庫(kù)連接 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數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序!"); } catch(Exception e) { System.out.println("加載sql程序時(shí)出現(xiàn)錯(cuò)誤憎亚!"); e.printStackTrace(); } try { //鏈接數(shù)據(jù)庫(kù) Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/lab_db","root","admin"); System.out.println("成功連接數(shù)據(jù)庫(kù)第美!"); } catch(Exception e){ System.out.println("連接數(shù)據(jù)失斏馈!"); 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; //對(duì)不同的部門賦予不同的權(quán)限 switch(department){ case "信息部": access_rights="系統(tǒng)管理員";break; case "教務(wù)部": access_rights="老師";break; case "輔導(dǎo)員": access_rights="輔導(dǎo)員";break; default: access_rights="學(xué)生"; } 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>輔導(dǎo)員</option>"); out.println("<option>信息部</option>"); out.println("<option>教務(wù)部</option>"); out.println("</select><br><br>"); out.println("<input type=\"submit\" name=\"update_submit\" value=\"提交修改\"/><br>\n"+ "</form></html>"); }else { //根據(jù)“所屬部門”利花,賦于對(duì)應(yīng)權(quán)限 String access_rights; switch(request.getParameter("department")) { case "信息部": access_rights="系統(tǒng)管理員";break; case "教務(wù)部": access_rights="老師";break; case "輔導(dǎo)員": access_rights="輔導(dǎo)員";break; default: access_rights="學(xué)生"; } 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">所在班級(jí)</td> <td width="200" align="center">權(quán)限</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="返回首頁(yè)" 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">所在班級(jí)</td> <td width="200" align="center">權(quán)限</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="返回首頁(yè)" 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"> 學(xué)號(hào)/工號(hào):<input type="text" name="account"/><br> 密碼:<input type = "text" name = "password"/> <br> 用戶名:<input type = "text" name = "username"/> <br> 性別:<input type = "text" name = "gender"/> <br> 所在班級(jí):<input type = "text" name = "department"/> <br> 權(quán)限:<input type = "text" name = "access_rights"/> <br> <input name="Submit" type="submit" value="添加" onclick=getParameter> </form> </body> </html>