jsp页面操作本地数据库
环境准备
2022idea专业版+maven3.8.6+tomcat10+mysql8
检查驱动
1，首先检查mysql安装目录里面有没有连接驱动
下载驱动
2，如果没有就打开安装目录下载
3，在弹出的界面框中选择添加
4，点击mysql connect，connect/j代表java，在下面选择一个任意版本箭头拖到右边就可以了，然后一路回车默认就可以下载好了
配置Tomcat
1，将下载好的jar包放到tomcat的lib目录下
2，在tomcat的web目录下的MATA-INF中配置connect.xml文件
把此连接信息配置到下面的文件中（记住是包裹在Context标签中的）
<Resource name="jdbc/mysql"
type="javax.sql.DataSource"
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/students"
username="root"
password="Root@123"
maxActive="4"
maxIdle="2"
maxWait="6000"/>
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<Context antiResourceLocking="false" privileged="true" >
<CookieProcessor className="org.apache.tomcat.util.http.Rfc6265CookieProcessor"
sameSiteCookies="strict" />
<Valve className="org.apache.catalina.valves.RemoteAddrValve"
allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1" />
<Manager sessionAttributeValueClassNameFilter="java\.lang\.(?:Boolean|Integer|Long|Number|String)|org\.apache\.catalina\.filters\.CsrfPreventionFilter\$LruCache(?:\$1)?|java\.util\.(?:Linked)?HashMap"/>
<Resource name="jdbc/mysql"
type="javax.sql.DataSource"
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/students"
username="root"
password="Root@123"
maxActive="4"
maxIdle="2"
maxWait="6000"/>
</Context>
配置idea
1，使用maven创建一个web项目
2，在web项目WEB-INFO下创建lib目录，把connectjar包放入
3，项目设置中配置jar包位置
开始上手
设计要求
1.列出全部学生模块：
该功能模块在【例4-3】中已实现，其程序为find_stu_1.jsp。
2.按条件查询学生模块
该功能模块在【例4-5】中已实现，其程序为find_stu_3_tijiao.jsp和find_stu_3.jsp。
3．新添加学生模块
该功能模块在【例4-2】中已实现，其程序为insert_stu_2_tijiao.jsp和insert_stu_2.jsp。
4．按条件删除学生模块
该功能模块在【例4-9】中已实现，其程序为delete_stu_2_tijiao.jsp和delete_stu_3.jsp。
5．按条件修改学生模块
该功能模块在【例4-7】中已实现，其程序为update_stu_2_tijiao.jsp、update_stu_2_edit.jsp、update_stu_2.jsp。
6．主页面框架的设计
该应用系统的主页面框架如图4-10所示，由2部分组成：最上方的显示标题部分（index_title.jsp），左边的显示操作菜单的显示(index_stu_left.jsp)，由这2部分组合形成主页面的程序（index.jsp）
注；tomcat的配置就不多说了，这个很简单，大家应该都会
测试连接
1，在本地数据库先创建数据库students和测试stu_info表，结构如下
mysql> desc stu_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
| age | int | YES | | NULL | |
| weight | float | YES | | NULL | |
| hight | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql>
2，插入数据
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:22
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="GB2312"%>
<html>
<head>
<title>利用PreparedStatement对象添加一条记录页面</title>
</head>
<body>
<%
String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=UTF-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
String sql="Insert into stu_info(id,name,sex,age,weight,hight) values(?,?,?,?,?,?)";
PreparedStatement pstmt= conn.prepareStatement(sql);
pstmt.setInt(1,16);
pstmt.setString(2,"张三");
pstmt.setString(3,"男");
pstmt.setInt(4,20);
pstmt.setFloat(5,70);
pstmt.setFloat(6,175);
try{
int n=pstmt.executeUpdate();
if(n==1){%>
数据插入操作成功！<br>
<%}
else{%>
数据插入操作失败！<br>
<%}
}catch(Exception e){%>
更新过程出现异常错误！<br>
<%=e.getMessage()%>
<%;
}
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
</body>
</html>
界面效果如下
3，查看
表中数据id为16是新创建的记录
主界面显示
在web目录下创建index.jsp，index_title.jsp，index_stu_left.jsp页面
index.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 23:46
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
<head> <title>学生身体体质信息管理系统</title> </head>
<frameset rows="80,*">
<frame src="index_title.jsp" scrolling="no">
<frameset cols="140,*">
<frame src="index_stu_left.jsp" scrolling="no">
</frameset>
</frameset>
</html>
index_title.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 23:46
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
<head> <title>页面标题</title> </head>
<body> <center> <h1>学生身体体质信息管理系统</h1> </center> </body>
</html>
index_stu_left.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 23:47
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="GB2312"%>
<html>
<head> <title>菜单页面</title> </head>
<body>
<br><br><br> <br><br><br>
<p><a href="find_stu_1.jsp" target="right">列出全部学生</a></p>
<p><a href="find_stu_3_tijiao.jsp" target="right">按条件查询学生</a></p>
<p><a href="insert_stu_2_tijiao.jsp" target="right">新添加学生</a></p>
<p><a href="delete_stu_2_tijiao.jsp" target="right">按条件删除学生</a></p>
<p> <a href="update_stu_2_tijiao.jsp" target="right">按条件修改学生</a> </p>
</body>
</html>
界面效果展示
列出全部学生模块实现
程序为find_stu_1.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:47
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<html>
<head> <title>显示所有学生的页面</title> </head>
<body>
<center>
<%String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=UTF-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
String sql="select * from stu_info ";
PreparedStatement pstmt= conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=pstmt.executeQuery();
rs.last(); //移至最后一条记录
%>你要查询的学生数据表中共有
<font size="5" color="red"> <%=rs.getRow()%></font>人
<table border="2" bgcolor= "ccceee" width="650">
<tr bgcolor="CCCCCC" align="center">
<td>记录条数</td> <td>学号</td> <td>姓名</td><td>性别</td> <td>年龄</td><td>体重</td><td>身高</td>
</tr>
<% rs.beforeFirst(); //移至第一条记录之前
while(rs.next()){
%> <tr align="center">
<td><%= rs.getRow()%></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("sex") %></td>
<td><%= rs.getString("age") %></td>
<td><%= rs.getString("weight") %></td>
<td><%= rs.getString("hight") %></td>
</tr>
<% }%>
</table>
</center>
<%if(rs!=null){ rs.close(); }
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
</body>
</html>
界面效果如下
条件查询学生模块实现
程序为find_stu_3_tijiao.jsp和find_stu_3.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:48
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" pageEncoding="GB2312"%>
<html> <head> <title>查询条件提交页面</title> </head>
<body>
请选择查询条件<hr width="100%" size="3">
<form action= "find_stu_3.jsp" method="post">
性别：男<input type="radio" value="男"
name="sex" checked="checked">
女<input type="radio" value="女" name="sex"><br><br>
体重范围:<p>
最小<input type="text" name="w1" value="0"><br><br>
最大<input type="text" name="w2" value="150"> <p>
<input type="submit" value="提 交">
<input type="reset" value="取 消">
</form>
</body>
</html>
界面效果如下
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:49
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<html>
<head> <title>由提交页面获取查询条件并实现查询的页面</title> </head>
<body>
<center>
<%String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=GB2312";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
request.setCharacterEncoding("GB2312");//设置字符编码，避免出现乱码
String sex=request.getParameter("sex");
float weight1=Float.parseFloat(request.getParameter("w1"));
float weight2=Float.parseFloat(request.getParameter("w2"));
String sql="select * from stu_info where sex=? and weight>=? and weight<=?";
PreparedStatement pstmt= conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstmt.setString(1,sex);
pstmt.setFloat(2,weight1);
pstmt.setFloat(3,weight2);
ResultSet rs=pstmt.executeQuery();
rs.last(); //移至最后一条记录
%>你要查询的学生数据表中共有
<font size="5" color="red"> <%=rs.getRow()%></font>人
<table border="2" bgcolor= "ccceee" width="650">
<tr bgcolor="CCCCCC" align="center">
<td>记录条数</td> <td>学号</td> <td>姓名</td><td>性别</td> <td>年龄</td><td>体重</td><td>身高</td>
</tr>
<% rs.beforeFirst(); //移至第一条记录之前
while(rs.next()){
%> <tr align="center">
<td><%= rs.getRow()%></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("sex") %></td>
<td><%= rs.getString("age") %></td>
<td><%= rs.getString("weight") %></td>
<td><%= rs.getString("hight") %></td>
</tr>
<% }%>
</table>
</center>
<%if(rs!=null){ rs.close(); }
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
</body>
</html>
界面效果如下
添加学生模块实现
程序为insert_stu_2_tijiao.jsp和insert_stu_2.jsp。
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:33
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
<head> <title>添加任意学生的提交页面</title> </head>
<body>
<form action= "insert_stu_2.jsp" method="post">
<table border="0" width="238" height="252">
<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><input type="text" name="sex" ></td> </tr>
<tr> <td>年龄</td> <td><input type="text" name="age"></td> </tr>
<tr> <td>体重</td> <td><input type="text" name="weight"></td> </tr>
<tr> <td>身高</td> <td><input type="text" name="hight"></td> </tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="提 交">
<input type="reset" value="取 消">
</td>
</tr>
</table>
</form>
</body>
</html>
效果如下
查询是否成功
条件删除学生模块实现
程序为delete_stu_2_tijiao.jsp和delete_stu_3.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:53
To change this template use File | Settings | File Templates.
--%>
<<%@ page language="java" pageEncoding="GB2312"%>
<html>
<head> <title>删除条件提交页面</title> </head>
<body>
请选择删除记录条件<hr width="100%" size="3">
<form action= "delete_stu_3.jsp" method="post">
姓名：<input type="text" name="name"><br><br>
性别：男 <input type="radio" value="男" name="sex">
女<input type="radio" value="女" name="sex"><br><br>
体重范围:<p>
最小<input type="text" name="w1"><br><br>
最大<input type="text" name="w2"> <p>
<input type="submit" value="提 交">
<input type="reset" value="取 消">
</form>
</body>
</html>
效果如下
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:54
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="GB2312"%>
<html>
<head>
<title>利用提交条件删除记录页面</title>
</head>
<body>
<%
String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=GB2312";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
Statement stmt=conn.createStatement();
request.setCharacterEncoding("GB2312");//设置字符编码，避免出现乱码
String name=request.getParameter("name");
String sex=request.getParameter("sex");
String ww1=request.getParameter("w1");
String ww2=request.getParameter("w2");
String s="1=1 ";
if(!name.equals("")) s=s+" and name='"+name+"'";
if(sex!=null) s=s+" and sex='"+sex+"'";
float w1,w2;
if(!ww1.equals("")) {
w1=Float.parseFloat(ww1);
s=s+"and weight>="+w1;
}
if(!ww2.equals("")) {
w2=Float.parseFloat(ww2);
s=s+"and weight<="+w2;
}
String sql="delete from stu_info where "+s;
PreparedStatement pstmt= conn.prepareStatement(sql);
try{
int n=pstmt.executeUpdate();
if(n>=1){%>
数据删除操作成功！<br>
<%}
else{%>
数据删除操作失败！<br>
<%}
}catch(Exception e){%>
删除更新过程出现异常错误！<br>
<%=e.getMessage()%>
<%
}
if(stmt!=null){ stmt.close(); }
if(conn!=null){ conn.close(); }
%>
</body>
</html>
效果如下
查询记录
条件修改学生模块实现
update_stu_2_tijiao.jsp、update_stu_2_edit.jsp、update_stu_2.jsp。
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:50
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" pageEncoding="UTF-8"%>
<html>
<head> <title>修改记录的条件提交页面</title> </head>
<body>
请选择修改记录所满足的条件<hr width="100%" size="3">
<form action= "update_stu_2_edit.jsp" method="post"><br>
姓名：<input type="text" name="name"><br><br>
性别：男 <input type="radio" value="男" name="sex">
女<input type="radio" value="女" name="sex"><br><br>
<input type="submit" value="提 交">
<input type="reset" value="取 消">
</form>
</body>
</html>
效果如下
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:51
To change this template use File | Settings | File Templates.
--%>
<%@page contentType="text/html" import="java.sql.*" pageEncoding="GB2312"%>
<html>
<head> <title>修改编辑页面</title> </head>
<body>
<% String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=GB2312";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
request.setCharacterEncoding("GB2312");//设置字符编码，避免出现乱码
String sex=request.getParameter("sex");
String name=request.getParameter("name");
session.setAttribute("sex",sex);
session.setAttribute("name",name);
String sql="select * from stu_info where sex=? and name=?";
PreparedStatement pstmt= conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstmt.setString(1,sex);
pstmt.setString(2,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()){
int id=rs.getInt("id");
String name2=rs.getString("name");
String sex2=rs.getString("sex");
int age=rs.getInt("age");
float weight=rs.getFloat("weight");
float hight=rs.getFloat("hight");
if(rs!=null){ rs.close(); }
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
<form action= "update_stu_2.jsp" method="post">
<table border="0" width="238" height="252">
<tr><td>学号</td><td><input name="id" value=<%=id%>></td></tr>
<tr><td>姓名</td><td><input name="name2" value=<%=name2%>></td></tr>
<tr><td>性别</td><td><input name="sex2" value=<%=sex2%>></td></tr>
<tr><td>年龄</td><td><input name="age"value=<%=age%>></td></tr>
<tr><td>体重</td><td><input name="weight"value=<%=weight%>></td></tr>
<tr><td>身高</td><td><input name="hight"value=<%=hight%>></td></tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="提 交">
<input type="reset" value="取 消">
</td>
</tr>
</table>
</form>
<%}
else{%>
没有找到合适条件的记录！！<%
if(rs!=null){ rs.close(); }
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
}%>
</body>
</html>
效果如下
update_stu_2.jsp
<%--
Created by IntelliJ IDEA.
User: huhy
Date: 2022/10/8
Time: 18:52
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="GB2312"%>
<html>
<head>
<title>修改后重写记录页面</title>
</head>
<body>
<%
String driverName = "com.mysql.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "Root@123"; //密码
String dbName = "students"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2 ="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=GB2312";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
String sql="update stu_info set id=?,name=?,sex=?,age=?,weight=?,hight=? where name=? and sex=?";
PreparedStatement pstmt= conn.prepareStatement(sql);
request.setCharacterEncoding("GB2312");//设置字符编码，避免出现乱码
int id=Integer.parseInt(request.getParameter("id"));
String name2=request.getParameter("name2");
String sex2=request.getParameter("sex2");
int age=Integer.parseInt(request.getParameter("age"));
float weight=Float.parseFloat(request.getParameter("weight"));
float hight=Float.parseFloat(request.getParameter("hight"));
String name=(String) session.getAttribute("name");
String sex=(String) session.getAttribute("sex");
pstmt.setInt(1,id);
pstmt.setString(2,name2);
pstmt.setString(3,sex2);
pstmt.setInt(4,age);
pstmt.setFloat(5,weight);
pstmt.setFloat(6,hight);
pstmt.setString(7,name);
pstmt.setString(8,sex);
try{
int n=pstmt.executeUpdate();
if(n>=1){%>
重写数据操作成功！<br>
<%}
else{%>
重写数据操作失败！<%=n%><br>
<%}
}catch(Exception e){%>
重写过程出现异常错误！<br>
<%=e.getMessage()%>
<%
}
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
</body>
</html>
查看是否修改成功
至此jsp操作本地数据成功
容易遇到的问题
1，乱码错误，可能会在出现按条件修改数据库时出现找不到记录错误，是因为提交时发生了乱码错误，导致数据库不能识别，GB2312，UTF-8两种格式注意修改
2，如果出现505内部语法错误
PreparedStatement pstmt=conn.prepareStatement(sql);
括号中sql修改为；
PreparedStatement pstmt= conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
2014
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
