java第5章

第5章 JSP中使用数据库

例子1
Example5_1.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM students");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+rs.getString(1)+"	"+rs.getString(2)+"	"+rs.getInt("数学成绩")+"	"+rs.getInt("英语成绩")+"	"+rs.getInt("物理成绩")+"

");
con.close();
}
catch(SQLException e1) {}
%>



例子2
Example5_2.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM students WHERE 英语成绩 >= 80 ");
out.print("");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
out.print("");
out.print("");
out.print("") ;
}
out.print("

"+"姓名"); out.print("	"+"英语成绩"); out.print("
"+rs.getString(2)+"	"+rs.getInt("英语成绩")+"

");
con.close();
}
catch(SQLException e1) {}
%>



例子3
Example5_3.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% String name,number;
int math,physics,english;
Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try{ con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//返回可滚动的结果集：
rs=sql

.executeQuery("SELECT * FROM students");
//将游标移动到最后一行：
https://www.360docs.net/doc/a65925729.html,st();
//获取最后一行的行号：
int lownumber=rs.getRow();
out.print("该表共有"+lownumber+"条记录");
out.print("
现在逆序输出记录：");
out.print("

");
out.print("");
out.print("");
//为了逆序输出记录，需将游标移动到最后一行之后：
rs.afterLast();
while(rs.previous())
{ out.print("");
number=rs.getString(1);
out.print("");
name=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+name+"	"+math+"	"+english+"	"+physics+"

");
out.print("单独输出第5条记录
");
rs.absolute(5);
number=rs.getString(1);
out.print(number+",");
name=rs.getString(2);
out.print(name+",");
math=rs.getInt("数学成绩");
out.print(math+",");
english=rs.getInt("英语成绩");
out.print(english+",");
physics=rs.getInt("物理成绩");
out.print(physics+"。");
con.close();
}
catch(SQLException e1) {}
%>



例子4
Example5_4.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% String xuehao,name;
int math;
Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//返回可滚动的结果集：
rs=sql.executeQuery("SELECT * FROM students");
out.print("");
out.print("");
out.print("");
//将游标移动到最后一行：
https://www.360docs.net/doc/a65925729.html,st();
//获取最后一行的行号：
int lownumber=rs.getRow();
//获取记录数

：
int number=lownumber;
double sum=0;
int 抽取数目=4;
int old_i[]={0,0,0,0};
int k=抽取数目;
int j=0;
while(抽取数目>0)
{int i=(int)(Math.random()*number+1);//随机获取一个1到number之间的数。
boolean boo=false;
for(int m=0;m{if(i==old_i[m])
boo=true;
}
if(boo) continue; //假如该行已被取出，结束本次循环，继续产生随机数。
rs.absolute(i); //游标移到这一行。
out.print("

");
xuehao=rs.getString(1); //获取该行学号字段的值。
out.print("");
name=rs.getString(2); //获取该行姓名字段的值。
out.print("");
math=rs.getInt("数学成绩"); //获取改行数学成绩字段的值。
out.print("");
out.print("") ;
sum=sum+math;
抽取数目--;
old_i[j]=i; //记录已取出的行号。
j++;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("
"+xuehao+"	"+name+"	"+math+"

");
out.print("平均成绩是："+sum/k);
con.close();
}
catch(SQLException e1) {}
%>



例子5
Example5_5.jsp：
<%@ page contentType="text/html;charset=GB2312" %>

成绩查询

输入姓名:

根据分数查询名单:
英语分数在

和

之间

数学分数在

和

之间

bynename.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的姓名：
String name=request.getParameter("name");
if(name==null)
{name="";
}
byte b[]=name.getBytes("ISO-8859-1");
name=new String(b);
Connection con=null;
Statement sql=null;
ResultSet rs=null;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
String condition="SELECT * FROM students WHERE 姓名 = "+"'"+name+"'";

rs=sql.executeQuery(condition);
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+rs.getString(1)+"	"+rs.getString(2)+"	"+rs.getInt("数学成绩")+"	"+rs.getInt("英语成绩")+"	"+rs.getInt("物理成绩")+"

");
con.close();
}
catch(SQLException e)
{ }
%>



byscore.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的分数的最大值和最小值：
String englishmax=request.getParameter("englishmax");
if(englishmax==null)
{englishmax="100";
}
String englishmin=request.getParameter("englishmin");
if(englishmin==null)
{englishmin="0";
}
String mathmax=request.getParameter("mathmax");
if(mathmax==null)
{mathmax="100";
}
String mathmin=request.getParameter("mathmin");
if(mathmin==null)
{mathmin="0";
}
Connection con=null;
Statement sql=null;
ResultSet rs=null;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try
{ con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
String eCondition="英语成绩 <= "+englishmax+" AND "+"英语成绩 >= "+englishmin;
String mCondition="数学成绩 <= "+mathmax+" AND "+"数学成绩 >= "+mathmin;
String condition="SELECT * FROM students WHERE "+mCondition+" and "+eCondition;
rs=sql.executeQuery(condition);
out.print("");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+rs.getString(1)+"	"+rs.getString(2)+"	"+rs.getInt( "数学成绩")+"	"+rs.getInt("英语成绩")+"	"+rs.getInt("物理成绩")+"

");
con.close();
}
catch(SQLException e)
{}
%>



例子6
Example5_6:
<%@ page contentType="text/html;charset=GB2312" %>

查询成绩：

按姓氏排序
按总分排序
按英语排序






byname1.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的排序方式：
String name=request.getParameter("R");
if(name==null)
{name="";}
byte b[]=name.getBytes("ISO-8859-1");
name=new String(b);
String number,xingming;
Connection con=null;
Statement sql=null;
ResultSet rs=null;
int math,english,physics;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
String condition="SELECT * FROM students ORDER BY "+name;
rs=sql.executeQuery(condition);
out.print("");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
number=rs.getString(1);
out.print("");
xingming=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
int total=math+english+physics;
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("	"+"总成绩"); out.print("
"+number+"	"+xingming+"	"+math+"	"+english+"	"+physics+"	"+total+"

");
con.close();
}
catch(SQLException e)
{ }
%>



例子7
Example5_7.jsp：
<%@ page contentType="text/html;charset=GB2312" %>

DY>

查询成绩：

输入学生的姓氏:

byname2.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的姓氏：
String name=request.getParameter("name");
if(name==null)
{name="";
}
byte b[]=name.getBytes("ISO-8859-1");
name=new String(b);
String number,xingming;
Connection con=null;
Statement sql=null;
ResultSet rs=null;
int math,english,physics;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try{ con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
String condition="SELECT * FROM students" ;
rs=sql.executeQuery(condition);
out.print("");
out.print("");
out.print("");
while(rs.next())
{ number=rs.getString(1);
xingming=rs.getString(2);
if(xingming.startsWith("王"))
{ out.print("");
out.print("");
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+xingming+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException e)
{ }
%>



例子8
Example5_8.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>

输入要修改成绩的同学的姓名：



输入新的数学成绩：


输入新的英语成绩：


输入新的物理成绩：

数据库更新前的数据记录是：
<% String name,number;
int math,physics,english;
Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}

catch(ClassNotFoundException e){}
try{ con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM students");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
number=rs.getString(1);
out.print("");
name=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+name+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException e1) {}
%>




newResult.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的姓名：
String name=request.getParameter("name");
if(name==null)
{name="";
}
byte b[]=name.getBytes("ISO-8859-1");
name=new String(b);
//获取提交的新的数学成绩：
String newMath=request.getParameter("math");
if(newMath==null)
{newMath="-100";
}
//获取提交的新的英语成绩：
String newEnglish=request.getParameter("english");
if(newEnglish==null)
{newEnglish="-100";
}
//获取提交的新的物理成绩：
String newPhysics=request.getParameter("physics");
if(newPhysics==null)
{newPhysics="-100";
}
Connection con=null;
Statement sql=null;
ResultSet rs=null;
String number,xingming;
int math,english,physics;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try {con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
String condition1=
"UPDATE students SET 数学成绩 = "+newMath+" WHERE 姓名="+"'"+name+"'" ,
condition2=
"UPDATE students SET 英语成绩 = "+newEnglish+" WHERE 姓名="+"'"+name+"'" ,
condition3=
"UPDATE students SET 物理成绩 = "+newPhysics+" WHERE 姓名="+"'"+name+"'" ;
//执行更新操作：
sql.executeUpdate(condition1);
sql.executeUpdate(condition2);
sql.execute

Update(condition3);
//显示更新后的表中的记录：
%>

更新后的表的记录：
<%
rs=sql.executeQuery("SELECT * FROM students");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{
out.print("");
number=rs.getString(1);
out.print("");
xingming=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+xingming+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException e)
{ }
%>




例子9
Example5_9.jsp:
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>

添加新的记录到数据库：

同学学号：


同学姓名：


数学成绩：


英语成绩：


物理成绩：

数据库添加记录前的数据记录是：
<% String name,number;
int math,physics,english;
Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:moon","sa","");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM students");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
number=rs.getString(1);
out.print("");
name=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+name+"	"+math+ "	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException e1) {}
%>




newDatabase.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的学号：
String number=request.getParameter("number");
if(number==null)
{number="";
}
byte b[]=number.getBytes("ISO-8859-1");
number=new String(b);
//获取提交的姓名：
String name=request.getParameter("name");
if(name==null)
{name="";
}
byte c[]=name.getBytes("ISO-8859-1");
name=new String(c);
//获取提交的新的数学成绩：
String m=request.getParameter("math");
if(m==null)
{m="-100"; }
//获取提交的新的英语成绩：
String e=request.getParameter("english");
if(e==null)
{e="-100"; }
//获取提交的新的物理成绩：
String p=request.getParameter("physics");
if(p==null)
{p="-100"; }
Connection con=null;
Statement sql=null;
ResultSet rs=null;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); }
catch(ClassNotFoundException event){}
try {con=DriverManager.getConnection("jdbc:odbc:moon","sa","");
sql=con.createStatement();
String condition=
"INSERT INTO students VALUES"+"("+"'"+number+"','"+name+"',"+m+","+e+","+p+")";
sql.executeUpdate(condition); //执行添加操作：
//显示添加新记录后表中的记录：
%>

添加新记录后的表：
<%
rs=sql.executeQuery("SELECT * FROM students ORDER BY 学号 ");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
String n=rs.getString(1);
out.print("");
String xingming=rs.getString(2);
out.print("");
int math=rs.getInt("数学成绩");
out.print("");
int english=rs.getInt("英语成绩");
out.print("");
int physics=rs.getInt("物理成绩");
out.print("");

out.print("

") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+n+"	"+xingming+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException event)
{ }
%>




例子10
Example5_10.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>

删除记录：

输入被删除记录的学号：

数据库删除前的数据记录是：
<% String name,number;
int math,physics,english;
Connection con;
Statement sql;
ResultSet rs;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e){}
try { con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM students");
out.print("

");
out.print("");
out.print("");
while(rs.next())
{ out.print("");
number=rs.getString(1);
out.print("");
name=rs.getString(2);
out.print("");
math=rs.getInt("数学成绩");
out.print("");
english=rs.getInt("英语成绩");
out.print("");
physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+number+"	"+name+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException e1) {}
%>

delete.jsp：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>


<% //获取提交的学号：
String number=request.getParameter("number");
if(number==null)
{number="";
}
byte b[]=number.getBytes("ISO-8859-1");
number=new String(b);
Connection con=null;
Statement sql=null;
ResultSet rs=null;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException event){}
try {con=DriverManager.getConnection("jdbc:odbc:sun","sa","");
sql=con.createStatement();
//删除操作：
String deleteAll="DELETE FROM students WHERE 学号"+" = "+"'"+number+"'";
sql.executeUpdate(deleteAll);
%>

删除记录后的表：
<%
rs=sql.executeQuery("SELECT * FROM students ORDER BY 学号 ");
out.print("

"

);
out.print("

");
out.print("");
while(rs.next())
{ out.print("");
String n=rs.getString(1);
out.print("");
String xingming=rs.getString(2);
out.print("");
int math=rs.getInt("数学成绩");
out.print("");
int english=rs.getInt("英语成绩");
out.print("");
int physics=rs.getInt("物理成绩");
out.print("");
out.print("") ;
}
out.print("

"+"学号"); out.print("	"+"姓名"); out.print("	"+"数学成绩"); out.print("	"+"英语成绩"); out.print("	"+"物理成绩"); out.print("
"+n+"	"+xingming+"	"+math+"	"+english+"	"+physics+"

");
con.close();
}
catch(SQLException event)
{out.print(""+event);
}
%>