數(shù)據(jù)庫(kù) 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計(jì)應(yīng)一')
go
insert into class(bjbh,bjmc) values('200201012','02計(jì)應(yīng)二')
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
?文章來(lái)源：http://www.zghlxwxcb.cn/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("學(xué)號(hào)必須為11位");
theForm.xh.focus();
return (false);
}
if(theForm.xm.value==""){
alert("請(qǐng)輸入姓名");
theForm.xm.focus();
return (false);
}
if(theForm.yw.value==""){
alert("請(qǐng)輸入語(yǔ)文成績(jī)");
theForm.yw.focus();
return (false);
}
if(theForm.sx.value==""){
alert("請(qǐng)輸入數(shù)學(xué)成績(jī)");
theForm.sx.focus();
return (false);
}
if(theForm.yy.value==""){
alert("請(qǐng)輸入英語(yǔ)成績(jī)");
theForm.yy.focus();
return (false);
}
return (true);
}
</script>
<html>
<head>
<title>學(xué)生成績(jī)?cè)黾?lt;/title>
</head>
<body>
<div align="center">
<p>學(xué)生成績(jī)?cè)黾?lt;/p>
<form id="form1" name="form1" method="post" action="XsCjZj0.jsp" onsubmit="return check(this)">
<table border="1">
<%
//1.加載驅(qū)動(dòng)
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("加載驅(qū)動(dòng)成功！");
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student";
Connection conn= DriverManager.getConnection(url, "sa", "123456");//password為密碼
System.out.println("連接數(shù)據(jù)庫(kù)成功！");
String sql="select * from class order by bjmc";
Statement stmt = conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
%>
<tr><td>班級(jí)</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>學(xué)號(hào)</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>語(yǔ)文</td><td><input name="yw" type="text" id="yw"/></td></tr>
<tr><td>數(shù)學(xué)</td><td><input name="sx" type="text" id="sx"/></td></tr>
<tr><td>英語(yǔ)</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();
//測(cè)試
//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("學(xué)生成績(jī)記錄增加成功！");
}
else {
out.print("學(xué)生成績(jī)記錄增加失敗！");
}
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>學(xué)生查詢</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">
班級(jí):
<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>學(xué)生維護(hù)</title></head>
<body>
<div align="center">
<P>學(xué)生維護(hù)</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>學(xué)號(hào)</td><td>姓名</td><td>班級(jí)</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 %>頁(yè) 共<%=pageCount %>頁(yè)
<%if(pageCurrent>1){ %>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=1">首頁(yè)</a>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCurrent-1 %>">上一頁(yè)</a>
<%} %>
<%if(pageCurrent<pageCount){ %>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCurrent+1 %>">下一頁(yè)</a>
<a href="XsWh.jsp?bjbh=<%=bjbh %>&pageno=<%=pageCount %>">尾頁(yè)</a>
<%} %>
跳轉(zhuǎn)到第<input type="text" name="pageno" size="3" maxlength="5">頁(yè)<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>學(xué)生信息</title></head>
<body>
<div align="center">
<P>學(xué)生信息</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>班級(jí)</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>學(xué)號(hào)</td><td><input name="xh" type="text" id="xh" value="<%=xh0%>" /></td></tr>
<tr><td>語(yǔ)文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>數(shù)學(xué)</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英語(yǔ)</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()" >[關(guān)閉]</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("學(xué)號(hào)必須為11位");
theForm.xh.focus();
return (false);
}
if(theForm.xm.value==""){
alert("請(qǐng)輸入姓名");
theForm.xm.focus();
return (false);
}
if(theForm.yw.value==""){
alert("請(qǐng)輸入語(yǔ)文成績(jī)");
theForm.yw.focus();
return (false);
}
if(theForm.sx.value==""){
alert("請(qǐng)輸入數(shù)學(xué)成績(jī)");
theForm.sx.focus();
return (false);
}
if(theForm.yy.value==""){
alert("請(qǐng)輸入英語(yǔ)成績(jī)");
theForm.yy.focus();
return (false);
}
return (true);
}
</script>
<html>
<head><title>?學(xué)生修改</title></head>
<body>
<div align="center">
<P>學(xué)生修改</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>班級(jí)</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>學(xué)號(hào)</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>語(yǔ)文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>數(shù)學(xué)</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英語(yǔ)</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('學(xué)生修改成功！')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
else {
out.print("<script Lanuage='JavaScript'>window.alert('學(xué)生修改失敗！')</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>學(xué)生刪除</title></head>
<body>
<div align="center">
<P>學(xué)生刪除</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>班級(jí)</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>學(xué)號(hào)</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>語(yǔ)文</td><td><input name="yw" type="text" id="yw" value="<%=yw0%>" /></td></tr>
<tr><td>數(shù)學(xué)</td><td><input name="sx" type="text" id="sx" value="<%=sx0%>" /></td></tr>
<tr><td>英語(yǔ)</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>
文章來(lái)源地址http://www.zghlxwxcb.cn/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('學(xué)生刪除成功！')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
else {
out.print("<script Lanuage='JavaScript'>window.alert('學(xué)生刪除失敗!')</script>");
out.print("<script Lanuage='JavaScript'>window.location ='XsCx.jsp'</script>");
}
stmt.close();
conn.close();
}
catch (Exception e) {
out.print(e.toString());
}
%>
</body>
</html>
?
到了這里，關(guān)于JSP設(shè)計(jì)一個(gè)簡(jiǎn)單的學(xué)生成績(jī)管理系統(tǒng)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
