本文主要介绍网页显示三个班成绩查询界面。主要内容:JSP基础,SQLServer基础,以及数据库的连接和配置相关知识。
环境配置:eclipse(Java EE版本最好,不过Java SE也可以通过安装各种插件实现web工程的创建),tomcat(版本不建议太高,也不能太低,笔者用的7.0,以官方推荐三合一为标准,ps:建议没有安装过jdk的小白直接官网三合一豪华礼包down一份)sqlserver 2012。
主要结构:①站点+②数据库,无论先做哪一个,后续连接起来即可。
话不多说,Go!
一、完成站点的创建
工程目录如下:
(1)在eclipse 中创建一个Dynamic web project,如图所示(如果是SE,无法创建web工程,详细操作步骤后续补充): (填好project name,直接finish即可)
(2)新建查询界面login.jsp,代码如下:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>welcome</title>
</head>
<body bgcolor="#C4E1FF">
<center>
请选择班级<br>
<hr>
<input type="button" value="一班成绩" οnclick="window.location='class_1.jsp'">
<input type="button" value="二班成绩" οnclick="window.location='class_2.jsp'">
<input type="button" value="三班成绩" οnclick="window.location='class_3.jsp'">
</center>
</body>
</html>
(3)新建班级界面class_1.jsp,代码如下:
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Driver"%>
<%@page import="java.sql.DriverManager"%>
<%@page import=" java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>一班成绩单</title>
</head>
<body bgcolor="#C4E1FF">
<center>
<a href="login.jsp">返回班级界面</a>
<hr>
<h1>成绩单</h1>
<%
//定义四个变量
int pageSize=10;
int pageNow = 1;//默认显示第一页
int rowCount=0;//从数据库查询该值
int pageCount=0;//
//接收用户希望显示的页数 pagenow
String s_pageNow=request.getParameter("pageNow");
if (s_pageNow!=null){
//接收到pagenow
pageNow = Integer.parseInt(s_pageNow);
}
//查询得到pagecount
//1.加载驱动
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//2.创建链接 "jdbc:microsoft:sqlserver://127.0.0.1:1433:databaseName=spdb1","sa",""
Connection ct = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;DatabaseName=StudScore_DB3","sa","123456");
//3.创建statement
Statement sm = ct.createStatement();
//4.查询
ResultSet rs = sm.executeQuery("select count(*) from StudScore");
if (rs.next()){
rowCount =rs.getInt(1);
}
//计算pagecount
if (rowCount%pageSize==0){
pageCount = rowCount/pageSize;
}else{
pageCount = rowCount/pageSize+1;
}
//查询出需要显示的记录
rs=sm.executeQuery("select top "+pageSize+" *from StudScore where StudId not in (select top "
+pageSize*(pageNow-1)+" StudId from StudScore)");
//显示
%>
<table border="1">
<tr><td> 学生ID </td><td> 姓名 </td><td> 性别 </td><td> 成绩 </td><td> 班级 </td></tr>
<%
while(rs.next()){
%>
<tr><td><%=rs.getInt(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td></tr>
<%
}
%>
</table>
<%
//显示超链接
for(int i = 1; i<=pageCount;i++){
out.println("<a href=class_1.jsp?pageNow="+i+">["+i+"]<a>");
}
%>
</center>
</body>
</html>
(4)新建班级界面class_2.jsp,代码如下:
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Driver"%>
<%@page import="java.sql.DriverManager"%>
<%@page import=" java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>二班成绩单</title>
</head>
<body bgcolor="#C4E1FF">
<center>
<a href="login.jsp">返回班级界面</a>
<hr>
<h1>成绩单</h1>
<%
//定义四个变量
int pageSize=5;
int pageNow = 1;//默认显示第一页
int rowCount=0;//从数据库查询该值
int pageCount=0;//
//接收用户希望显示的页数 pagenow
String s_pageNow=request.getParameter("pageNow");
if (s_pageNow!=null){
//接收到pagenow
pageNow = Integer.parseInt(s_pageNow);
}
//查询得到pagecount
//1.加载驱动
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//2.创建链接 "jdbc:microsoft:sqlserver://127.0.0.1:1433:databaseName=spdb1","sa",""
Connection ct = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;DatabaseName=StudScore_DB3","sa","123456");
//3.创建statement
Statement sm = ct.createStatement();
//4.查询
ResultSet rs = sm.executeQuery("select count(*) from StudScore_2");
if (rs.next()){
rowCount =rs.getInt(1);
}
//计算pagecount
if (rowCount%pageSize==0){
pageCount = rowCount/pageSize;
}else{
pageCount = rowCount/pageSize+1;
}
//查询出需要显示的记录
rs=sm.executeQuery("select top "+pageSize+" *from StudScore_2 where StudId not in (select top "
+pageSize*(pageNow-1)+" StudId from StudScore_2)");
//显示
%>
<table border="1">
<tr><td> 学生ID </td><td> 姓名 </td><td> 性别 </td><td> 成绩 </td><td> 班级 </td></tr>
<%
while(rs.next()){
%>
<tr><td><%=rs.getInt(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td></tr>
<%
}
%>
</table>
<%
//显示超链接
for(int i = 1; i<=pageCount;i++){
out.println("<a href=class_2.jsp?pageNow="+i+">["+i+"]<a>");
}
%>
</center>
</body>
</html>
(5)新建班级界面class_3.jsp,代码如下:
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.Driver"%>
<%@page import="java.sql.DriverManager"%>
<%@page import=" java.sql.Statement"%>
<%@page import="java.sql.ResultSet"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>三班成绩单</title>
</head>
<body bgcolor="#C4E1FF">
<center>
<a href="login.jsp">返回班级界面</a>
<hr>
<h1>成绩单</h1>
<%
//定义四个变量
int pageSize=5;
int pageNow = 1;//默认显示第一页
int rowCount=0;//从数据库查询该值
int pageCount=0;//
//接收用户希望显示的页数 pagenow
String s_pageNow=request.getParameter("pageNow");
if (s_pageNow!=null){
//接收到pagenow
pageNow = Integer.parseInt(s_pageNow);
}
//查询得到pagecount
//1.加载驱动
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//2.创建链接 "jdbc:microsoft:sqlserver://127.0.0.1:1433:databaseName=spdb1","sa",""
Connection ct = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;DatabaseName=StudScore_DB3","sa","123456");
//3.创建statement
Statement sm = ct.createStatement();
//4.查询
ResultSet rs = sm.executeQuery("select count(*) from StudScore_3");
if (rs.next()){
rowCount =rs.getInt(1);
}
//计算pagecount
if (rowCount%pageSize==0){
pageCount = rowCount/pageSize;
}else{
pageCount = rowCount/pageSize+1;
}
//查询出需要显示的记录
rs=sm.executeQuery("select top "+pageSize+" *from StudScore_3 where StudId not in (select top "
+pageSize*(pageNow-1)+" StudId from StudScore_3)");
//显示
%>
<table border="1">
<tr><td> 学生ID </td><td> 姓名 </td><td> 性别 </td><td> 成绩 </td><td> 班级 </td></tr>
<%
while(rs.next()){
%>
<tr><td><%=rs.getInt(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td></tr>
<%
}
%>
</table>
<%
//显示超链接
for(int i = 1; i<=pageCount;i++){
out.println("<a href=class_3.jsp?pageNow="+i+">["+i+"]<a>");
}
%>
</center>
</body>
</body>
</html>
二、数据库的创建
注意事项:①.务必用个人账户登录,不要用windows引擎,原因是后续连接数据库的过程中只能对用户操作,如果是windows引擎的话,不能进行连接,账户名默认是sa密码为空;②.数据库的建立,直接进行了三个表的创建,创建过程soeasy,恕不赘述,如有需要,可到文章末尾的连接自取。
三、运行效果如图所示:
主界面:
成绩查询界面: