实现数据库增删改查+界面效果-----jsp-编程学习网

任务

通过连接数据库完成用户登录模块。
2.登录成功后查询出一张数据库表中的内容；登录不成功返回登录页面。
3.页面面端要有空值和非法字符验证。
4.登录成功后对一张表中数据进行增加、删除、修改和查询操作。

代码

数据库相关代码

创建数据库

名字为jdbcHomework，字符编码为utf8
在这里插入图片描述

数据库创建表

学生表

DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `Sid` char(11) CHARACTER SET utf8 NOT NULL,  `Sname` char(50) CHARACTER SET utf8 DEFAULT NULL,  `Ssex` char(2) CHARACTER SET utf8 DEFAULT NULL,  `Sclass` char(50) CHARACTER SET utf8 DEFAULT NULL,  `Syuanxi` char(50) CHARACTER SET utf8 DEFAULT NULL,  PRIMARY KEY (`Sid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='jdbc作业';

用户表

DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `Userid` char(11) NOT NULL,  `Username` char(50) DEFAULT NULL,  `Userpwd` varchar(12) DEFAULT NULL,  PRIMARY KEY (`Userid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='jdbc作业';

数据库的表中插入数据

学生表

INSERT INTO `student` VALUES ('20221104122', '李静静', '女', '计科（专升本）1班', '计算机');INSERT INTO `student` VALUES ('20221104204', '柯柯', '女', '计科（专升本）2班', '计算机');INSERT INTO `student` VALUES ('20221104250', 'zyp', '男', '自动化1班', '自动化');

用户表

INSERT INTO `user` VALUES ('20221104122', '洛可可', '666666');INSERT INTO `user` VALUES ('root', '洛可可', 'root');

myeclipse中代码

src的jdbcHomework包中的代码

功能：连接数据库

LinkMysql.java为连接mysql的代码，包括驱动、密码、url，用于登录界面、登录检查、增删改查的后台处理
2.ConnectionFactory.java为为连接mysql的代码，包括驱动、密码、url，用于登录成功后界面linkMysql.jsp连接数据库
3.本次学习时上面类使用方法是通过javaBean的id获取链接，从而连接到数据库；下面类使用方法是通过在开头导入该类，代码中新建类并用 “新建的类名.getConnection();” 方法获取链接，从而连接到数据库

1．LinkMysql

package jdbcHomework;import java.sql.*;public class LinkMysql { //驱动程序    private String driverName = "com.mysql.jdbc.Driver";    //设置数据库连接URL    private String url = "jdbc:mysql://localhost:3306/jdbcHomework?useUnicode=true&characterEncoding=utf-8";    private String user = "root";//数据库登录用户名    private String password = "root";//数据库登录密码 public String getDriverName() {return driverName;}public void setDriverName(String driverName) {this.driverName = driverName;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getUser() {return user;}public void setUser(String user) {this.user = user;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;} public Connection getConnection() {        try {            Class.forName(driverName);            return DriverManager.getConnection(url, user, password);        } catch (Exception e) {            //e.printStackTrace();            System.out.println("驱动异常");            return null;        }    }  }

2.ConnectionFactory

package jdbcHomework;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class ConnectionFactory {private String driverClassName = "com.mysql.jdbc.Driver";   private String url = "jdbc:mysql://localhost:3306/jdbcHomework?useUnicode=true&characterEncoding=utf-8";   private String userName = "root";   private String password = "root";     public Connection getConnection() throws SQLException   {   try {Class.forName(driverClassName);} catch (ClassNotFoundException e) {// TODO Auto-generated catch block//e.printStackTrace();System.out.println("驱动异常");}   return DriverManager.getConnection(url, userName, password);   }    }

WebRoot/jdbcHomework下的代码

1.登录主界面代码

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'login.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">--> <script type="text/javascript">function checkForm(){if (form1.id.value == null || form1.userid.value==""){alert("请输入学号");return false ;}if (form1.name.value == null || form1.username.value==""){alert("请输入姓名");return false ;}if (form1.pwd.value == null || form1.pwd.value==""){alert("请输入密码");return false;}return true;}</script>  </head>    <body bgcolor="pink">  <div class="top" align="center">   <h1>欢迎来到洛可可学生管理系统！！</h1>  </div>      <div class="center" align="center">       <table border="1" bgcolor="rgb(248, 169, 182)">    <form name="form1" action="logincheck.jsp" method="post" onsubmit="return checkForm();">    <tr>    <td>学号：</td>    <td><input type="text" name="id" ></td>    </tr>    <tr>    <td>姓名：</td>    <td><input type="text" name="name" ></td>    </tr>    <tr>    <td>密码：</td>    <td width="50"><input type="password" name="pwd"></td>    </tr>    <tr >    <td colspan="2" align="center">    <input type="submit" value="登录"/>    <input type="reset"/>        </td>    </tr>    </form>    </table>   </div>      <div class="bottom" align="center">      </div>     </body></html>

2.登录检查代码

logincheck.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'logincheck.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body>  <jsp:useBean id="link" class="jdbcHomework.LinkMysql"></jsp:useBean>    <%        request.setCharacterEncoding("utf-8");    String studentId = request.getParameter("id");    String studentName = request.getParameter("name");    String pwd = request.getParameter("pwd");    System.out.println("学号："+studentId+"，姓名："+studentName+"，密码："+pwd);//控制台输出获取到的登录时学生的学号、姓名、密码                   Connection con=null;       PreparedStatement ps=null;       ResultSet rs=null;        con = link.getConnection();                String sql="select * from user where Userid=? and Username=? and Userpwd=?";       ps=con.prepareStatement(sql);       ps.setString(1,studentId);       ps.setString(2, studentName);       ps.setString(3,pwd);              rs=ps.executeQuery();              if(rs.next()){        System.out.println("登录成功！");             response.sendRedirect("linkMysql-2.jsp");                    }       else{        System.out.println("登录失败！");             response.sendRedirect("login.jsp");       out.print("");                }        rs.close();        ps.close();        con.close();       %>                           </body></html>

3.登录成功页面代码

linkMysql-2.jsp

<%@ page language="java" import="java.util.*, java.sql.*,jdbcHomework.ConnectionFactory" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'linkMysql.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>  <%-- 在浏览器上显示所有学生信息 --%>  <body bgcolor="pink">  <div class="top" align="center" >  <h1>欢迎加入到洛可可学生管理系统</h1>  </div>     <div class="center" align="center">        <table border="1" bgcolor="rgb(248, 169, 182)"  align="center">            <tr>                <th width="87" align="center">学号</th>                <th width="87" align="center">姓名</th>                <th width="87" align="center">性别</th>                <th width="87" align="center">班级</th>                <th width="87" align="center">院系</th>               <th width="87" align="center">操作</th>            </tr>            <% Connection con= null;                Statement stmt=null;                ResultSet rs=null;                //Class.forName("com.mysql.jdbc.Driver");                                //String url="jdbc:mysql://localhost:3306/jdbcHomework?useUnicode=true&characterEncoding=utf-8";                //con=DriverManager.getConnection(url,"root","root");                ConnectionFactory cf = new ConnectionFactory();                con = cf.getConnection();;    int intPageSize;   //一页显示的记录数                int intRowCount;   //记录总数                int intPageCount;  //总页数                int intPage;       // 待显示页码                String strPage;                int i;                intPageSize=10;     //设置一页显示的记录数                strPage = request.getParameter("page");                if(strPage==null){                //表明page的参数值为空，此时显示第一页数据                intPage = 1;                }else{                //将字符串转换成整型                intPage = java.lang.Integer.parseInt(strPage);                if(intPage<1)                intPage=1;                }                    stmt=con.createStatement();                String sql="select * from student";                rs=stmt.executeQuery(sql);  while(rs.next()){            %>        <tr>               <td><%=rs.getString("Sid")%></td>               <td><%=rs.getString("Sname")%></td>               <td><%=rs.getString("Ssex")%></td>               <td><%=rs.getString("Sclass")%></td>               <td><%=rs.getString("Syuanxi")%></td>                <td><a href="deleteStu.jsp?Sid=<%=rs.getString("Sid")%>">删除</a>                  <a href="updateStu.jsp?Sid=<%=rs.getString("Sid")%>">修改</a></td>                  </tr>     <%}                rs.close();                stmt.close();                con.close();%>                 </table>    </div>        <br>    <div class="bottom" align="center">    <a href="add.jsp">添加学生信息</a>    <a href="login.jsp">登录界面</a>        </div>  </body></html>

4.删除学生信息主界面代码

deleteStu.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'deleteStu.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body>    <jsp:useBean id="link" class="jdbcHomework.LinkMysql"></jsp:useBean>    <%   request.setCharacterEncoding("utf-8");   String sid = request.getParameter("Sid");   System.out.println(sid);   Connection con=null;   PreparedStatement ps=null;   //ResultSet rs=null;             con = link.getConnection();            String sql="delete from student where Sid=? ";       ps=con.prepareStatement(sql);       ps.setString(1,sid);                   int i=ps.executeUpdate();              if(i>0){        System.out.println("删除成功！");             response.sendRedirect("linkMysql.jsp");       //out.print("");             }       else{            System.out.println("删除的学生不存在！");           response.sendRedirect("linkMysql.jsp");       //out.print("");                }                ps.close();        con.close();        %>  </body></html>

5.修改学生信息主界面代码

deleteStu.jsp

<%@ page language="java" import="java.util.*, java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'updateStu.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body bgcolor="pink">  <div class="top" align="center">  <h1>欢迎来到洛可可学生系统修改（更新）页面</h1>  </div>    <hr>   <div class="center" align="center"> <jsp:useBean id="link" class="jdbcHomework.LinkMysql"></jsp:useBean>  <%  String sid = request.getParameter("Sid");  System.out.println(sid);  Connection con=null;       PreparedStatement ps=null;              con=link.getConnection();                     String sql="select * from student where Sid=? ";       ps=con.prepareStatement(sql);       ps.setString(1,sid);                   ResultSet rs=ps.executeQuery();       if(rs.next()){                     %>           <form action="doupdate.jsp" method="post" >       <table border="1" bgcolor="rgb(248, 169, 182)" >       <tr>              <th>学号：</th>       <td><%=rs.getString(1) %></td>       </tr>       <tr>       <th>姓名：</th>       <td><input type="text" name="sname" value="<%=rs.getString(2) %>"></td>       </tr>       <tr>       <th>性别：</th>       <td><input type="text" name="ssex" value="<%=rs.getString(3) %>"></td>       </tr>       <tr>       <th>班级：</th>       <td><input type="text" name="banji" value="<%=rs.getString(4) %>"></td>       </tr>       <tr>       <th>院系：</th>       <td><input type="text" name="yuanxi" value="<%=rs.getString(5) %>"></td>       </tr>              </table>       <br>       <input type="submit" value="修改完成">       </form>              <%       //out.print("");             }       else{            System.out.println("学生不存在！");           response.sendRedirect("linkMysql.jsp");       //out.print("");        //out.print("");                }        rs.close();        ps.close();        con.close();      %> </div>   <hr>  <br>    <div class="bottom" align="center">       <a href="linkMysql-2.jsp">返回上一界面</a>    <a href="login.jsp">登录界面</a>           </div>      </body></html>

6.修改学生信息后台处理代码

doupdate.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'doupdate.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body>    <jsp:useBean id="link" class="jdbcHomework.LinkMysql"></jsp:useBean>    <%    request.setCharacterEncoding("utf-8");    String uid = request.getParameter("sid");String uname = request.getParameter("sname");String sex = request.getParameter("ssex");String banji = request.getParameter("banji");String yuanxi = request.getParameter("yuanxi");System.out.println(uid+uname+sex+banji+yuanxi);Connection con=null;        PreparedStatement ps=null;       con=link.getConnection();       String sql="update student set Sname=?,Ssex=?,Sclass=?,Syuanxi=? where Sid=?";       ps=con.prepareStatement(sql);       ps.setString(1,uname);       ps.setString(2, sex);       ps.setString(3,banji);        ps.setString(4,yuanxi);       ps.setString(5,uid);            int i=ps.executeUpdate();       if(i>0){        System.out.println("修改成功！");             response.sendRedirect("linkMysql.jsp");             //out.print("");             }       else{        System.out.println("修改失败！");             response.sendRedirect("linkMysql.jsp");       //out.print("");                }               ps.close();        con.close();         %>  </body></html>

6.添加学生信息主界面代码

add.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'add.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">--><script type="text/javascript">function checkForm(){if (form1.id.value == null || form1.userid.value==""){alert("请输入学号");return false ;}if (form1.name.value == null || form1.username.value==""){alert("请输入姓名");return false ;}if (form1.sex.value == null || form1.sex.value==""){alert("请输入性别");return false;}if (form1.banji.value == null || form1.banji.value==""){alert("请输入班级");return false;}if (form1.yuanxi.value == null || form1.yuanxi.value==""){alert("请输入院系");return false;}return true;}</script>  </head>    <body bgcolor="pink">    <div class="top" align="center">  <h1>欢迎来到洛可可学生系统添加页面</h1>  </div>    <div class="center" align="center">      <table>    <form name="form1" action="doadd.jsp" method="post" onsubmit="return checkForm();">    <tr>    <td>学号：</td>    <td><input type="text" name="id" ></td>    </tr>    <tr>    <td>姓名：</td>    <td><input type="text" name="name" ></td>    </tr>    <tr>    <td>性别：</td>    <td width="50"><input type="text" name="sex"></td>    </tr>    <tr>    <td>班级：</td>    <td width="50"><input type="text" name="banji"></td>    </tr>    <tr>    <td>院系：</td>    <td width="50"><input type="text" name="yuanxi"></td>    </tr>    <tr >    <td colspan="2" align="center">    <input type="submit" value="添加"/>    <input type="reset"/>    </td>   </tr>   </form>   </table>   </div>      <br>   <div align="center">   <a href="linkMysql-2.jsp">返回上一界面</a>   <a href="login.jsp">切换账号</a>   </div>  </body></html>

6.添加学生信息后台处理代码

doadd.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>My JSP 'doadd.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0">    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body>      <jsp:useBean id="link" class="jdbcHomework.LinkMysql"></jsp:useBean><% request.setCharacterEncoding("utf-8");String uid = request.getParameter("id");String uname = request.getParameter("name");String sex = request.getParameter("sex");String banji = request.getParameter("banji");String yuanxi = request.getParameter("yuanxi");Connection con=null;       PreparedStatement ps=null;             con = link.getConnection();                String sql="insert into student(Sid,Sname,Ssex,Sclass,Syuanxi) values(?,?,?,?,?)";       ps=con.prepareStatement(sql);       ps.setString(1,uid);       ps.setString(2, uname);       ps.setString(3,sex);        ps.setString(4,banji);       ps.setString(5,yuanxi);            int i=ps.executeUpdate();              if(i>0){        System.out.println("添加成功！");             response.sendRedirect("linkMysql.jsp");       //out.print("");             }       else{        System.out.println("添加失败！");             response.sendRedirect("linkMysql.jsp");          //out.print("");                }               ps.close();        con.close();       %>  </body></html>