用JSP查询SQL数据库中的数据,要求是在JSP的一个页面中输入药品编号,跳转到另一个界面显示药品表中的相应记录,下面这个代码是显示全部记录的.....大家帮帮忙啊......可以给个答案么.....额......不要用类操作......
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head><body>
<table width="776" border="0" cellspacing="1" cellpadding="0">
<tr>
<td width="63" bgcolor="#99CCFF">药品编号</td>
<td width="56" bgcolor="#99CCFF">药品名称</td>
<td width="51" bgcolor="#99CCFF">方法</td>
<td width="56" bgcolor="#99CCFF">剂量</td>
<td width="51" bgcolor="#99CCFF">价格</td>
<td width="142" bgcolor="#99CCFF">功能</td>
<td width="284" bgcolor="#99CCFF">不良反应</td>
</tr>
<%@page import="com.microsoft.sqlserver.jdbc.*"%>
<%
Connection con=null;
Statement st =null;
ResultSet rs=null;
String yaopinID=null;
String yaopinname=null;
String fangfa=null;
String jiliang=null;
String price=null;
String funtion=null;
String buliangfanying=null;
int i=0;
String color=null;
try
{
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("sa");
ds.setPassword("");
ds.setServerName("127.0.0.1");
ds.setPortNumber(1433);
ds.setDatabaseName("zhuyuan");
con= ds.getConnection();
st=con.createStatement(); sql="select yaopinID,yaopinname,fangfa,jiliang,price,funtion,buliangfanying from yaopinbiao where";
rs=st.executeQuery(sql);
while(rs.next())
{
i++;
if(i%2==0)
color="#CCFFFF";
else
color="#FFFFCC";
yaopinID=rs.getString("yaopinID");
yaopinname=rs.getString("yaopinname");
fangfa=rs.getString("fangfa");
jiliang=rs.getString("jiliang");
price=rs.getString("price");
funtion=rs.getString("funtion");
buliangfanying =rs.getString("buliangfanying");
%>
<tr bgcolor="<%=color%>">
<td><%=yaopinID%></td>
<td><%=yaopinname%></td>
<td><%=fangfa%></td>
<td><%=jiliang%></td>
<td><%=price%></td>
<td><%=funtion%></td>
<td><%=buliangfanying%></td>
</tr>
<%
}
}
catch(Exception e)
{
out.print("数据库操作出错:"+e);
}
finally
{
if(rs!=null)
rs.close();
if(st!=null)
st.close();
if(con!=null)
con.close();
}
%>
</table>
</body>
</html>
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head><body>
<table width="776" border="0" cellspacing="1" cellpadding="0">
<tr>
<td width="63" bgcolor="#99CCFF">药品编号</td>
<td width="56" bgcolor="#99CCFF">药品名称</td>
<td width="51" bgcolor="#99CCFF">方法</td>
<td width="56" bgcolor="#99CCFF">剂量</td>
<td width="51" bgcolor="#99CCFF">价格</td>
<td width="142" bgcolor="#99CCFF">功能</td>
<td width="284" bgcolor="#99CCFF">不良反应</td>
</tr>
<%@page import="com.microsoft.sqlserver.jdbc.*"%>
<%
Connection con=null;
Statement st =null;
ResultSet rs=null;
String yaopinID=null;
String yaopinname=null;
String fangfa=null;
String jiliang=null;
String price=null;
String funtion=null;
String buliangfanying=null;
int i=0;
String color=null;
try
{
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("sa");
ds.setPassword("");
ds.setServerName("127.0.0.1");
ds.setPortNumber(1433);
ds.setDatabaseName("zhuyuan");
con= ds.getConnection();
st=con.createStatement(); sql="select yaopinID,yaopinname,fangfa,jiliang,price,funtion,buliangfanying from yaopinbiao where";
rs=st.executeQuery(sql);
while(rs.next())
{
i++;
if(i%2==0)
color="#CCFFFF";
else
color="#FFFFCC";
yaopinID=rs.getString("yaopinID");
yaopinname=rs.getString("yaopinname");
fangfa=rs.getString("fangfa");
jiliang=rs.getString("jiliang");
price=rs.getString("price");
funtion=rs.getString("funtion");
buliangfanying =rs.getString("buliangfanying");
%>
<tr bgcolor="<%=color%>">
<td><%=yaopinID%></td>
<td><%=yaopinname%></td>
<td><%=fangfa%></td>
<td><%=jiliang%></td>
<td><%=price%></td>
<td><%=funtion%></td>
<td><%=buliangfanying%></td>
</tr>
<%
}
}
catch(Exception e)
{
out.print("数据库操作出错:"+e);
}
finally
{
if(rs!=null)
rs.close();
if(st!=null)
st.close();
if(con!=null)
con.close();
}
%>
</table>
</body>
</html>
扔 ServletException , IOException {陈述 stmt=null;连接反对= connMgr.getConnection("medicine");如果(空的反对==){response.sendRedirect("/medicine/con_error.html");归还;}// ==================== 创建数据库Statement =============================试一试{stmt=con.createStatement();}抓住(例外 e ){connMgr.freeConnection("medicine",con);response.sendRedirect("/medicine/stmt_error.html");归还;}//-------------------------------------------------------------long all_count=0;//存满足条件的药品总数//===================从request中得到session======================试一试{HttpSession会议= request.getSession(false);如果(空的会议==){connMgr.freeConnection("medicine",con);response.sendRedirect("/medicine/session_error.html");归还;}//-----------------------------------------------------------//======从“”提交来的参数取得前页最后一条数据的药品编码=======串 ll_pos=request.getParameter("pos");
//如果pos=-1则表示要从头开始查//----------------------------------------------------------//=====================查询用的SQL语句串==========================绳sqlstatment=& quot ;选择 medicines.ypbm, medsmalltypes.zlmc, medsupertypes.clmc,
medtypes.dlmc, medicines.ypm, medicines.zyyx, medicines.ypzy来自药,
medsmalltypes , medsupertypes , medtypes在哪儿 medicines.ypbm>"+ll_pos+"
并且(( medsmalltypes.zlbm = medicines.zlbm )并且( medsupertypes.clbm = medicines.clbm )
并且( medtypes.dlbm = medicines.dlbm )并且(( medicines.del_flag =0)& quot ;;//-----------------------------------------------------绳 zlbm;绳 dlbm;绳 clbm;绳 zyyx;绳 ypm;searchData slist=新建 searchData ();同步(会议){slist=(searchData)session.getAttribute("searchList");}if(ll_pos.equals("-1")){//===表示不是由“”过来要从头开始查数据==========如果( slist ! =null ){同步(会议){session.removeAttribute("searchList");}}slist=新建 searchData ();//=============从设置查询条件页面取得查询条件的参数===================zlbm=request.getParameter("zlbm");dlbm=request.getParameter("dlbm");clbm=request.getParameter("clbm");zyyx=request.getParameter("zyyx");ypm=request.getParameter("ypm");//------------------------------------------------------//=================将查询条件参数通过Vector存到session中==========slist.setZlbm ( zlbm );slist.setDlbm ( dlbm );slist.setClbm ( clbm );slist.setZyyx ( zyyx );slist.setYpm ( ypm );同步(会议){session.setAttribute (& quot ; searchList & quot ;, slist );}//---------------------------------------------------------}//--------------------------------------------------------另外{//============================取出查询条件参数====================如果( slist ! =null ){zlbm=slist.getZlbm();dlbm=slist.getDlbm();clbm=slist.getClbm();zyyx=slist.getZyyx();ypm=slist.getYpm();}另外{如果( stmt ! =null ) stmt.close();stmt.setMaxRows(0);connMgr.freeConnection("medicine",con);response.sendRedirect("/medicine/session_error.html");归还;}//-------------------------------------------------}绳sql2=& quot ;选择数(*)来自药在哪儿del_flag=0& quot ;;//=====================根据条件参数设置SQL语句=======================if(!(zlbm.trim().equals("0"))) {sqlstatment+=(& quot ;并且( medicines.zlbm=''"+zlbm.trim()+"'' )& quot ;);sql2+=(& quot ;和 zlbm=''"+zlbm.trim()+"''");}if(!(dlbm.trim().equals("0"))) {sqlstatment+=(& quot ;并且( medicines.dlbm=''"+dlbm.trim()+"'' )& quot ;);sql2+=(& quot ;和 dlbm=''"+dlbm.trim()+"''");}if(!(clbm.trim().equals("0"))) {sqlstatment+=(& quot ;并且( medicines.clbm=''"+clbm.trim()+"'' )& quot ;);sql2+=(& quot ;和 clbm=''"+clbm.trim()+"''");}if(!(zyyx.trim().length ()==0)){sqlstatment+=(& quot ;并且(象 ''%"+zyyx.trim()+"%''一样的 medicines.zyyx )& quot ;);sql2+=(象 ''%"+zyyx.trim()+"%''");一样的& quot ;和 zyyx}if(!(ypm.trim().length ()==0)){sqlstatment+=(& quot ;并且(象 ''%"+ypm.trim()+"%''一样的 medicines.ypm )& quot ;);sql2+=(象 ''%"+ypm.trim()+"%''");一样的& quot ;和 ypm}sqlstatment+=& quot ;))由 medicines.ypbm ASC & quot ;;的命令//-------------------------------------------------------------矢量列表 =新建矢量();//================= 设置一页显示的数据条数(一次检索出的数据条数)===========stmt.setMaxRows(25);//----------------------------------------------------------------------//===================执行查询将结果放到ResultSet中================ResultSet rs = stmt.executeQuery(sqlstatment);ResultSet rs2= stmt.executeQuery(sql2);//------------------------------------------------if(rs==null){ //如果没有查询结果数据如果( stmt ! =null ) stmt.close();stmt.setMaxRows(0);connMgr.freeConnection("medicine",con);response.sendRedirect("/medicine/no_medicine.html");//定向到一个页面归还;}//====================将药品信息填入数据对象并存入Vector中================if(rs2.next()){all_count=rs2.getLong(1);//取得总条数}if(rs2!=null)rs2.close();while(rs.next()){medicinelist m =新建 medicinelist ();m.setYpbm(rs.getInt("ypbm"));m.se
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<html>
<head>
</head>
<body>
<form name="form1" action="xianShi.jsp"> <input type = "text" name="yaopinID"/>
<input type="submit" name="submit"/>
</body>
</html>
显示页面:<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head><body>
<table width="776" border="0" cellspacing="1" cellpadding="0">
<tr>
<td width="63" bgcolor="#99CCFF">药品编号</td>
<td width="56" bgcolor="#99CCFF">药品名称</td>
<td width="51" bgcolor="#99CCFF">方法</td>
<td width="56" bgcolor="#99CCFF">剂量</td>
<td width="51" bgcolor="#99CCFF">价格</td>
<td width="142" bgcolor="#99CCFF">功能</td>
<td width="284" bgcolor="#99CCFF">不良反应</td>
</tr>
<%@page import="com.microsoft.sqlserver.jdbc.*"%>
<%
Connection con=null;
Statement st =null;
ResultSet rs=null;
String yaopinID=null;
String yaopinname=null;
String fangfa=null;
String jiliang=null;
String price=null;
String funtion=null;
String buliangfanying=null;
int i=0;
String color=null;
try
{
SQLServerDataSource ds = new SQLServerDataSource();
ds.setUser("sa");
ds.setPassword("");
ds.setServerName("127.0.0.1");
ds.setPortNumber(1433);
ds.setDatabaseName("zhuyuan");
con= ds.getConnection();
st=con.createStatement(); //得到药品编号
String yaopinID= request.getParameter("yaopinID");
sql="select yaopinID,yaopinname,fangfa,jiliang,price,funtion,buliangfanying from yaopinbiao where yaopinId ='" + yaopinID +"'";
rs=st.executeQuery(sql);
if(rs.next())
{
color="#CCFFFF";
yaopinID=rs.getString("yaopinID");
yaopinname=rs.getString("yaopinname");
fangfa=rs.getString("fangfa");
jiliang=rs.getString("jiliang");
price=rs.getString("price");
funtion=rs.getString("funtion");
buliangfanying =rs.getString("buliangfanying");
%>
<tr bgcolor="<%=color%>">
<td><%=yaopinID%></td>
<td><%=yaopinname%></td>
<td><%=fangfa%></td>
<td><%=jiliang%></td>
<td><%=price%></td>
<td><%=funtion%></td>
<td><%=buliangfanying%></td>
</tr>
<%
}
}
catch(Exception e)
{
out.print("数据库操作出错:"+e);
}
finally
{
if(rs!=null)
rs.close();
if(st!=null)
st.close();
if(con!=null)
con.close();
}
%>
</table>
</body>
</html>就这样。应该没问题了!!!!!!
sql="select yaopinID,yaopinname,fangfa,jiliang,price,funtion,buliangfanying from yaopinbiao where yaopinId ='" + yaopinID +"'";
rs=st.executeQuery(sql);
if(rs.next())
{
这个还是运行不起来.......
说是:
An error occurred at line: 23 in the jsp file: /yaopinbiao/yaopinchaxun.jsp
Generated servlet error:
Duplicate local variable yaopinIDAn error occurred at line: 23 in the jsp file: /yaopinbiao/yaopinchaxun.jsp
Generated servlet error:
sql cannot be resolvedAn error occurred at line: 23 in the jsp file: /yaopinbiao/yaopinchaxun.jsp
Generated servlet error:
sql cannot be resolved
改成这样就可以了 yaopinID= request.getParameter("yaopinID");
String sql="select yaopinID,yaopinname,fangfa,jiliang,price,funtion,buliangfanying from yaopinbiao where yaopinID ='" + yaopinID +"'";
rs=st.executeQuery(sql);
if(rs.next())
{
谢谢huang_jin_gang 啊,问题解决拉~~~~~