案例名称:模糊搜索
程序名称:fuzzy.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<script language="VB"runat="server">
Sub Page Load(SenderAs Object,EAs EventArgs)
Dim connAs New
SqlConnection("server=localhost;database=pubs;uid=sa;pwd=;")
dim sqlAs String="select*from authors where au lname like"%M%""
Dim cmdAs New SqlCommand(sql,conn)
Dim drAs SqlDataReader
try
conn.Open()
dr=cmd.ExecuteReader()
while dr.read
response.write(dr("au id")&"——"& dr("au lname")&"——"& dr("au fname")
&"<br>")
end while
catch ex as Exception
response.write("出错了!")
finally
if not dr is nothing then
dr.close
end if
if not conn is nothing then
conn.close
end if
end try
End Sub
</script>
3.4使用JSP实现常规的数据库搜索
这一节的案例演示了基于数据库的搜索,使用JSP2.4技术,以及Oracle9i数据库中的scott用户的dept表。
3.4.1使用JSP实现精确搜索
案例名称:精确搜索
程序名称:exact.jsp
<%@ page contentType="text/html;charset=gbk"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{
out.print(e.getMessage());
}
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try
{
String url="jdbc:oracle:oci8:@ yuanyuan";
String user="scott";
String password="tiger";
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
String sql="select*from dept where deptno=10";
rs=stmt.executeQuery(sql);
while(rs.next())
{
String a=rs.getString("deptno");
String b=rs.getString("dname");
String c=rs.getString("loc");
out.print(a+" ; ; ; ;"+b+" ; ; ; ;"+c+"<br>");
}
}
catch(SQLException ee)
{
out.print(ee.getMessage());
}
finally
{
if(rs!=null)
{
try
{
rs.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
if(stmt!=null)
{
try
{
stmt.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
if(conn!=null)
{
try
{
conn.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
}
%>
</body>
</html>
3.4.2使用JSP实现范围搜索
案例名称:范围搜索
程序名称:range.jsp
<%@ page contentType="text/html;charset=gbk"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{
out.print(e.getMessage());
}
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try
{
String url="jdbc:oracle:thin:@ localhost:1521:yuanyuan";
String user="scott";
String password="tiger";
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
String sql="select*from dept where deptno in(10,20,30)";
rs=stmt.executeQuery(sql);
while(rs.next())
{
String a=rs.getString("deptno");
String b=rs.getString("dname");
String c=rs.getString("loc");
out.print(a+" ; ; ; ;"+b+" ; ; ; ;"+c+"<br>");
}
}
catch(SQLException ee)
{
out.print(ee.getMessage());
}
finally
{
if(rs!=null)
{
try
{
rs.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
if(stmt!=null)
{
try
{
stmt.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
if(conn!=null)
{
try
{
conn.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
}
%>
</body>
</html>
3.4.3使用JSP实现模糊搜索
这里演示使用“%”进行模式匹配的情况。
案例名称:模糊搜索
程序名称:fuzzy.jsp
<%@ page contentType="text/html;charset=gbk"%>
<%@ page import="java.sql.*"%>
<html>
<body>
<%
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{
out.print(e.getMessage());
}
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try
{
String url="jdbc:oracle:thin:@ localhost:1521:yuanyuan";
String user="scott";
String password="tiger";
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
String sql="select*from dept whereDNAME like"%TI%"";
rs=stmt.executeQuery(sql);
while(rs.next())
{
String a=rs.getString("deptno");
String b=rs.getString("dname");
String c=rs.getString("loc");
out.print(a+" ; ; ; ;"+b+" ; ; ; ;"+c+"<br>");
}
}
catch(SQLException ee)
{
out.print(ee.getMessage());
}
finally
{
if(rs!=null)
{
try
{
rs.close();
}
catch(SQLException ex)
{
out.print(ex.getMessage());
}
}
if(stmt!=null)
{
try
{