数据库 sqlserver
Create database student
go
use student
go
create table class(
bjbh char(9) primary key,
bjmc varchar(20) not null
)
go
insert into class(bjbh,bjmc) values('200201011','02计应一')
go
insert into class(bjbh,bjmc) values('200201012','02计应二')
go
create table score(
xh char(11) primary key,
xm char(10) not null,
yw decimal(6,2) not null,
sx decimal(6,2) not null,
yy decimal(6,2) not null,
zf decimal(6,2) ,
bh char(9) not null
)
go
insert into score(xh,xm,yw,sx,yy,zf,bh) values('20020101101','张三',85,89,76,250,'200201011')
go
insert into score(xh,xm,yw,sx,yy,zf,bh) values('20020101102','李四',79,78,79,236,'200201011')
go
insert into score(xh,xm,yw,sx,yy,zf,bh) values('20020101201','王五',82,75,67,224,'200201012')
go
JSP代码
XsCjZj.jsp
文章来源：https://www.toymoban.com/news/detail-508313.html
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 14:09
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*" %>
<%request.setCharacterEncoding("gb2312");%>
<script language="JavaScript">
function check(theForm)
{
if(theForm.xh.value.length!=11){
alert("学号必须为11位");
theForm.xh.focus();
return (false);
}
if(theForm.xm.value==""){
alert("请输入姓名");
theForm.xm.focus();
return (false);
}
if(theForm.yw.value==""){
alert("请输入语文成绩");
theForm.yw.focus();
return (false);
}
if(theForm.sx.value==""){
alert("请输入数学成绩");
theForm.sx.focus();
return (false);
}
if(theForm.yy.value==""){
alert("请输入英语成绩");
theForm.yy.focus();
return (false);
}
return (true);
}
</script>
<html>
<head>
<title>学生成绩增加</title>
</head>
<body>
<div align="center">
<p>学生成绩增加</p>
<form id="form1" name="form1" method="post" action="XsCjZj0.jsp" onsubmit="return check(this)">
<table border="1">
<%
//1.加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("加载驱动成功！");
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student";
Connection conn= DriverManager.getConnection(url, "sa", "123456");//password为密码
System.out.println("连接数据库成功！");
String sql="select * from class order by bjmc";
Statement stmt = conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
%>
<tr><td>班级</td>
<td><select name="bjbh">
<% while(rs.next())
{
String bjbh=rs.getString("bjbh");
String bjmc=rs.getString("bjmc");%>
<option value="<%=bjbh%>"><%=bjmc%></option>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</select></td>
</tr>
<tr><td>学号</td><td><input name="xh" type="text" id="xh"/></td></tr>
<tr><td>姓名</td><td><input name="xm" type="text" id="xm"/></td></tr>
<tr><td>语文</td><td><input name="yw" type="text" id="yw"/></td></tr>
<tr><td>数学</td><td><input name="sx" type="text" id="sx"/></td></tr>
<tr><td>英语</td><td><input name="yy" type="text" id="yy"/></td></tr>
</table>
<input name="submit" type="submit" value="确定"/>
<input name="reset" type="reset" value="重置">
</form>
</div>
</body>
</html>
XsCjZj0.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 14:52
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.math.BigInteger" %>
<%request.setCharacterEncoding("gb2312"); %>
<html>
<head><title></title></head>
<body>
<%
String xh = request.getParameter("xh");
String xm = request.getParameter("xm");
String bh = request.getParameter("bjbh");
String yw = request.getParameter("yw");
String sx = request.getParameter("sx");
String yy = request.getParameter("yy");
String zf=new BigInteger(yw).add(new BigInteger(sx)).add(new BigInteger(yy)).toString();
//测试
//out.print(xh+" "+xm+" "+bh+" "+yw+" "+sx+" "+yy);
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String sql="insert into score(xh,xm,bh,yw,sx,yy,zf) values(?,?,?,?,?,?,?)";
PreparedStatement stmt=conn.prepareStatement(sql);
stmt.setString(1,xh);
stmt.setString(2,xm);
stmt.setString(3,bh);
stmt.setFloat(4,Float.valueOf(yw));
stmt.setFloat(5,Float.valueOf(sx));
stmt.setFloat(6,Float.valueOf(yy));
stmt.setFloat(7,Float.valueOf(zf));
int n = stmt.executeUpdate();
if (n>0){
out.print("学生成绩记录增加成功！");
}
else {
out.print("学生成绩记录增加失败！");
}
stmt.close();
conn.close();
}
catch (Exception e) {
out.print(e.toString());
}
%>
</body>
</html>
XsCx.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 16:41
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=GB2312" language="java" %>
<%@ page import="java.sql.*"%>
<%request.setCharacterEncoding("gb2312"); %>
<html>
<head><title>学生查询</title></head>
<body>
<%
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String sql = "select * from class order by bjmc";
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<form method="post" action="XsWh.jsp">
班级:
<select name="bjbh">
<%
while(rs.next())
{
String bjbh=rs.getString("bjbh");
String bjmc=rs.getString("bjmc");
%>
<option value="<%=bjbh%>"><%=bjmc%></option>
<%
}
%>
</select>
<input name="Submit" type="submit" value="确定" />
</form>
<%
rs.close();
stmt.close();
conn.close();
}
catch (Exception e) {
out.print(e.toString());
}
%>
</body>
</html>
XsWh.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 16:44
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=utf-8" language="java" %>
<%@ page import="java.sql.*"%>
<%request.setCharacterEncoding("utf-8"); %>
<html>
<head><title>学生维护</title></head>
<body>
<div align="center">
<P>学生维护</P>
<%
String bjbh=request.getParameter("bjbh");
String pageNo=request.getParameter("pageno");
int pageSize=3;
int pageCount;
int rowCount;
int pageCurrent;
int rowCurrent;
if(pageNo==null||pageNo.trim().length()==0){
pageCurrent=1;
}else{
pageCurrent=Integer.parseInt(pageNo);
}
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String sql = "select xh,xm,bjmc,zf from class,score";
sql=sql+" where class.bjbh=score.bh and bh='"+bjbh+"'";
sql=sql+" order by bh";
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);
rs.last();
rowCount = rs.getRow();
pageCount = (rowCount + pageSize - 1)/pageSize;
if(pageCurrent>pageCount)
pageCurrent=pageCount;
if(pageCurrent<1)
pageCurrent=1;
%>
<table border="1">
<tr><td>学号</td><td>姓名</td><td>班级</td><td>总分</td><td>操作</td></tr>
<%
rs.beforeFirst();
rowCurrent=1;
while(rs.next()){
if(rowCurrent>(pageCurrent-1)*pageSize&&rowCurrent<=pageCurrent*pageSize){
String xh=rs.getString("xh");
String xm=rs.getString("xm");
String bjmc=rs.getString("bjmc");
String zf=String.valueOf(rs.getFloat("zf"));
%>
<tr><td><%=xh%></td><td><%=xm%></td><td><%=bjmc%></td><td><%=zf%></td>
<td><a href="XsXq.jsp?xh=<%=xh%>" target="_blank">详情</a> <a href="XsXg.jsp?xh=<%=xh%>">修改</a> <a href="XsSc.jsp?xh=<%=xh%>">删除</a></td></tr>
<%
}
rowCurrent++;
}
%>
</table>
<p align="center">
<form method="POST" action="XsWh.jsp">
第<%=pageCurrent %>页 共<%=pageCount %>页
<%if(pageCurrent>1){ %>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=1">首页</a>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCurrent-1 %>">上一页</a>
<%} %>
<%if(pageCurrent<pageCount){ %>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCurrent+1 %>">下一页</a>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCount %>">尾页</a>
<%} %>
跳转到第<input type="text" name="pageno" size="3" maxlength="5">页<input name="submit" type="submit" value="GO">
<input name="bjbh" type="hidden" value="<%=bjbh %>">
</form>
<%
rs.close();
stmt.close();
conn.close();
}
catch(ClassNotFoundException e)
{
out.println(e.getMessage());
}
catch(SQLException e)
{
out.println(e.getMessage());
}
catch (Exception e) {
out.print(e.toString());
}
%>
</div>
</body>
</html>
XsXq.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 16:58
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.text.*"%>
<%request.setCharacterEncoding("gb2312"); %>
<html>
<head><title>学生信息</title></head>
<body>
<div align="center">
<P>学生信息</P>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String xh0=request.getParameter("xh").trim();
String sql0 = "select * from score where xh='"+xh0+"'";
Statement stmt0=conn.createStatement();
ResultSet rs0 = stmt0.executeQuery(sql0);
rs0.next();
String xm0=rs0.getString("xm").trim();
String bh0=rs0.getString("bh").trim();
String yw0=String.valueOf(rs0.getFloat("yw"));
String sx0=String.valueOf(rs0.getFloat("sx"));
String yy0=String.valueOf(rs0.getFloat("yy"));
String zf0=String.valueOf(rs0.getFloat("zf"));
rs0.close();
stmt0.close();
%>
<form id="form1" name="form1" method="post" action="">
<table border="1">
<%
String sql = "select * from class order by bjbh";
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<tr><td>班级</td>
<td><select name="bh" disabled="disabled">
<%
while(rs.next())
{
String bjbh=rs.getString("bjbh").trim();
String bjmc=rs.getString("bjmc").trim();
%>
<option value="<%=bjbh%>" <% if (bh0.equals(bjbh)){ %> selected <% } %>><%=bjmc%></option>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</select></td></tr>
<tr><td>姓名</td><td><input name="xm" type="text" id="xm" value="<%=xm0%>" /></td></tr>
<tr><td>学号</td><td><input name="xh" type="text" id="xh" value="<%=xh0%>" /></td></tr>
<tr><td>语文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>数学</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英语</td><td><input name="yy" type="text" id="yy" value="<%=yy0%>" /></td></tr>
<tr><td>总分</td><td><input name="zf" type="text" id="zf" value="<%=zf0%>" /></td></tr>
</table>
<br>
<a href="javascript:window.close()" >[关闭]</a>
</form>
</div>
</body>
</html>
XsXg.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 18:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=utf-8" language="java" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.math.BigInteger" %>
<%request.setCharacterEncoding("utf-8"); %>
<script language="JavaScript">
function check(theForm)
{
if(theForm.xh.value.length!=11){
alert("学号必须为11位");
theForm.xh.focus();
return (false);
}
if(theForm.xm.value==""){
alert("请输入姓名");
theForm.xm.focus();
return (false);
}
if(theForm.yw.value==""){
alert("请输入语文成绩");
theForm.yw.focus();
return (false);
}
if(theForm.sx.value==""){
alert("请输入数学成绩");
theForm.sx.focus();
return (false);
}
if(theForm.yy.value==""){
alert("请输入英语成绩");
theForm.yy.focus();
return (false);
}
return (true);
}
</script>
<html>
<head><title>ְ学生修改</title></head>
<body>
<div align="center">
<P>学生修改</P>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String xh0=request.getParameter("xh").trim();
String sql0 = "select * from score where xh='"+xh0+"'";
Statement stmt0=conn.createStatement();
ResultSet rs0 = stmt0.executeQuery(sql0);
rs0.next();
String xm0=rs0.getString("xm").trim();
String bh0=rs0.getString("bh").trim();
String yw0=String.valueOf(rs0.getFloat("yw"));
String sx0=String.valueOf(rs0.getFloat("sx"));
String yy0=String.valueOf(rs0.getFloat("yy"));
rs0.close();
stmt0.close();
%>
<form id="form1" name="form1" method="post" action="XsXg0.jsp" onSubmit="return check(this)">
<table border="1">
<%
String sql = "select * from class order by bjbh";
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<tr><td>班级</td>
<td><select name="bh>
<%
while(rs.next())
{
String bjbh=rs.getString("bjbh").trim();
String bjmc=rs.getString("bjmc").trim();
%>
<option value="<%=bjbh%>" <% if (bh0.equals(bjbh)){ %> selected <% } %>><%=bjmc%></option>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</select>
</td></tr>
<tr><td>学号</td><td><input name="xh" type="text" id="xh" value="<%=xh0%>" /></td></tr>
<tr><td>姓名</td><td><input name="xm" type="text" id="xm" value="<%=xm0%>" /></td></tr>
<tr><td>语文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>数学</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英语</td><td><input name="yy" type="text" id="yy" value="<%=yy0%>" /></td></tr>
</table>
<br>
<input name="submit" type="submit" value="确定" />
<input name="reset" type="reset" value="重置" />
</form>
</div>
</body>
</html>
XsXg0.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 18:29
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=utf-8" language="java" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.math.BigInteger" %>
<%request.setCharacterEncoding("utf-8"); %>
<html>
<head><title></title></head>
<body>
<%
String xh = request.getParameter("xh");
String xm = request.getParameter("xm");
String bh = request.getParameter("bjbh");
String yw = request.getParameter("yw");
String sx = request.getParameter("sx");
String yy = request.getParameter("yy");
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String zf=new BigInteger(yw).add(new BigInteger(sx)).add(new BigInteger(yy)).toString();
String sql="update score set xm=?,yw=?,sx=?,yy=?,zf=? where xh=?";
PreparedStatement stmt=conn.prepareStatement(sql);
stmt.setString(1,xm);
stmt.setFloat(2,Float.valueOf(yw));
stmt.setFloat(3,Float.valueOf(sx));
stmt.setFloat(4,Float.valueOf(yy));
stmt.setFloat(5,Float.valueOf(zf));
stmt.setString(6,xh);
int n = stmt.executeUpdate();
if (n>0){
out.print("<script Lanuage='JavaScript'>window.alert('学生修改成功！')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
else {
out.print("<script Lanuage='JavaScript'>window.alert('学生修改失败！')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
stmt.close();
conn.close();
}
catch (Exception e) {
out.print(e.toString());
}
%>
</body>
</html>
XsSc.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 16:57
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<%@ page import="java.text.*"%>
<%request.setCharacterEncoding("gb2312"); %>
<html>
<head><title>学生删除</title></head>
<body>
<div align="center">
<P>学生删除</P>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String xh0=request.getParameter("xh").trim();
String sql0 = "select * from score where xh='"+xh0+"'";
Statement stmt0=conn.createStatement();
ResultSet rs0 = stmt0.executeQuery(sql0);
rs0.next();
String xm0=rs0.getString("xm").trim();
String bh0=rs0.getString("bh").trim();
String yw0=String.valueOf(rs0.getFloat("yw"));
String sx0=String.valueOf(rs0.getFloat("sx"));
String yy0=String.valueOf(rs0.getFloat("yy"));
rs0.close();
stmt0.close();
%>
<form id="form1" name="form1" method="post" action="XsSc0.jsp" onSubmit="return check(this)">
<table border="1">
<%
String sql = "select * from class order by bjbh";
Statement stmt=conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<tr><td>班级</td>
<td><select name="bh">
<%
while(rs.next())
{
String bjbh=rs.getString("bjbh").trim();
String bjmc=rs.getString("bjmc").trim();
%>
<option value="<%=bjbh%>" <% if (bh0.equals(bjbh)){ %> selected <% } %>><%=bjmc%></option>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</select>
</td></tr>
<tr><td>学号</td><td><input name="xh" type="text" id="xh" value="<%=xh0%>" /></td></tr>
<tr><td>姓名</td><td><input name="xm" type="text" id="xm" value="<%=xm0%>" /></td></tr>
<tr><td>语文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>数学</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英语</td><td><input name="yy" type="text" id="yy" value="<%=yy0%>" /></td></tr>
</table>
<br>
<input name="submit" type="submit" value="提交" />
<input name="reset" type="reset" value="重置" />
</form>
</div>
</body>
</html>
文章来源地址https://www.toymoban.com/news/detail-508313.html
XsSc0.jsp
<%--
Created by IntelliJ IDEA.
User: ruiling
Date: 2022/10/27
Time: 19:29
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<%@ page import="java.sql.*"%>
<%request.setCharacterEncoding("gb2312"); %>
<html>
<head><title></title></head>
<body>
<%
String xh = request.getParameter("xh");
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=student";
String user="sa";
String password="123456";
Connection conn=DriverManager.getConnection(url,user,password);
String sql = "delete from score";
sql=sql+" where xh='"+xh+"'";
Statement stmt=conn.createStatement();
int n = stmt.executeUpdate(sql);
if (n>0){
out.print("<script Lanuage='JavaScript'>window.alert('学生删除成功！')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
else {
out.print("<script Lanuage='JavaScript'>window.alert('学生删除失败!')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
stmt.close();
conn.close();
}
catch (Exception e) {
out.print(e.toString());
}
%>
</body>
</html>
到了这里，关于JSP设计一个简单的学生成绩管理系统的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
