全網(wǎng)最使用MySQL审孽, JSP, Bean, JDBC(連接數(shù)據(jù)庫(kù)) 佑力,制作有權(quán)限(老師打颤,學(xué)生瘸洛,輔導(dǎo)員) 用戶登錄demo,表單查看demo

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>

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市姑躲,隨后出現(xiàn)的幾起案子睡扬,更是在濱河造成了極大的恐慌,老刑警劉巖黍析,帶你破解...
    沈念sama閱讀 221,198評(píng)論 6 514
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件卖怜,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡阐枣,警方通過查閱死者的電腦和手機(jī)马靠,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 94,334評(píng)論 3 398
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來蔼两,“玉大人虑粥,你說我怎么就攤上這事锁孟≡残簦” “怎么了羽历?”我有些...
    開封第一講書人閱讀 167,643評(píng)論 0 360
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我,道長(zhǎng),這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 59,495評(píng)論 1 296
  • 正文 為了忘掉前任姊舵,我火速辦了婚禮史飞,結(jié)果婚禮上陨簇,老公的妹妹穿的比我還像新娘。我一直安慰自己,他們只是感情好矮嫉,可當(dāng)我...
    茶點(diǎn)故事閱讀 68,502評(píng)論 6 397
  • 文/花漫 我一把揭開白布厦滤。 她就那樣靜靜地躺著趟咆,像睡著了一般。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,156評(píng)論 1 308
  • 那天,我揣著相機(jī)與錄音,去河邊找鬼息楔。 笑死颇蜡,一個(gè)胖子當(dāng)著我的面吹牛缤弦,可吹牛的內(nèi)容都是我干的抢韭。 我是一名探鬼主播扯夭,決...
    沈念sama閱讀 40,743評(píng)論 3 421
  • 文/蒼蘭香墨 我猛地睜開眼符糊,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼围橡!你這毒婦竟也來了塞赂?” 一聲冷哼從身側(cè)響起讹剔,我...
    開封第一講書人閱讀 39,659評(píng)論 0 276
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎膳凝,沒想到半個(gè)月后永部,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,200評(píng)論 1 319
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡毫炉,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,282評(píng)論 3 340
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了趾疚。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片宝与。...
    茶點(diǎn)故事閱讀 40,424評(píng)論 1 352
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖拒贱,靈堂內(nèi)的尸體忽然破棺而出湾揽,到底是詐尸還是另有隱情瓤逼,我是刑警寧澤,帶...
    沈念sama閱讀 36,107評(píng)論 5 349
  • 正文 年R本政府宣布库物,位于F島的核電站霸旗,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏戚揭。R本人自食惡果不足惜诱告,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,789評(píng)論 3 333
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望民晒。 院中可真熱鬧精居,春花似錦锄禽、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,264評(píng)論 0 23
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至佛吓,卻和暖如春绽慈,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背辈毯。 一陣腳步聲響...
    開封第一講書人閱讀 33,390評(píng)論 1 271
  • 我被黑心中介騙來泰國(guó)打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留搜贤,地道東北人谆沃。 一個(gè)月前我還...
    沈念sama閱讀 48,798評(píng)論 3 376
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像仪芒,于是被迫代替她去往敵國(guó)和親唁影。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,435評(píng)論 2 359

推薦閱讀更多精彩內(nèi)容

  • JAVA面試題 1掂名、作用域public,private,protected,以及不寫時(shí)的區(qū)別答:區(qū)別如下:作用域 ...
    JA尐白閱讀 1,157評(píng)論 1 0
  • 一. Java基礎(chǔ)部分.................................................
    wy_sure閱讀 3,813評(píng)論 0 11
  • 1. Java基礎(chǔ)部分 基礎(chǔ)部分的順序:基本語(yǔ)法据沈,類相關(guān)的語(yǔ)法,內(nèi)部類的語(yǔ)法饺蔑,繼承相關(guān)的語(yǔ)法锌介,異常的語(yǔ)法,線程的語(yǔ)...
    子非魚_t_閱讀 31,657評(píng)論 18 399
  • 1猾警、不安全的隨機(jī)數(shù)生成孔祸,在CSRF TOKEN生成、password reset token生成等发皿,會(huì)造成toke...
    nightmare丿閱讀 3,705評(píng)論 0 1
  • 小編費(fèi)力收集:給你想要的面試集合 1.C++或Java中的異常處理機(jī)制的簡(jiǎn)單原理和應(yīng)用崔慧。 當(dāng)JAVA程序違反了JA...
    八爺君閱讀 4,596評(píng)論 1 114