我用的jdbc去访问数据库的,在jsp上显示的,我的sql是嵌套循环的,用一个主sql,查询出来的id,作为循环里种其他的sql的条件,下面是我的部分代码。String strSQL="select ProcessID,NodeID from TTA_ProcessNode where FormID=? order by NodeID ";
//String strSQL="select ProcessID,NodeID from (select ProcessID,NodeID from TTA_ProcessNode where FormID=? order by NodeID desc) where rownum<3";
System.out.println(strSQL);
ResultSet rs=JspAdmJdbc.strSQLSet(formid,strSQL);
ResultSet rss;
while (rs.next()){
out.print("<tr bgcolor='#ffffff'>");
String territory_r="";
String territory_p="";
String territory_c="";
String territory_a="";
String territory="";
String modalityname="";
String productname="";
String functionname="";
String account="";
String titlename="";
String FreeCondition="";
String IsEnd="";
String Channel="";
String IsDirect="";
//rs.getInt("ProcessID");
String strSQL_r="select RegionID,RegionName from TTG_Region where ltrim(rtrim(RegionID)) in" +
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'R%')";
rss=JspAdmJdbc.strSQLSet(strSQL_r);
while(rss.next()){
territory_r=territory_r+rss.getString("RegionName")+",";
}
rss.close();
String strSQL_p = "select ProvinceID,ProvinceName from TTG_Province where ltrim(rtrim(ProvinceID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'P%')";
rss=JspAdmJdbc.strSQLSet(strSQL_p);
while(rss.next()){
territory_p=territory_p+rss.getString("ProvinceName")+",";
}
rss.close();
String strSQL_c = "select CityID,CityName from TTG_City where ltrim(rtrim(CityID)) in "+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'C%')";
rss=JspAdmJdbc.strSQLSet(strSQL_c);
while(rss.next()){
territory_c=territory_c+rss.getString("CityName")+",";
}
rss.close();
String strSQL_a = "select AccountID,EnglishName from TTG_Account where ltrim(rtrim(AccountID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'H%')";
rss=JspAdmJdbc.strSQLSet(strSQL_a);
while(rss.next()){
territory_a=territory_a+rss.getString("EnglishName")+",";
}
rss.close();
territory=territory_r+territory_p+territory_c+territory_a;
if ("".equals(territory)){
territory="All";
}
String strSQL_f="select FunctionID,FunctionName from TTG_Function where FunctionID in "+
"(select FunctionID from TTA_ProNodeFunction where ProcessID ="+rs.getInt("ProcessID")+")";
rss=JspAdmJdbc.strSQLSet(strSQL_f);
while(rss.next()){
functionname=functionname+rss.getString("FunctionName")+",";
}
rss.close();
if ("".equals(functionname)) {
functionname="All";
}
String strSQL_m = "select modalityID,ModalityName from ttg_modality where modalityID in"
+"(select Replace(product,'m','') as modalityid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'm%')";
rss=JspAdmJdbc.strSQLSet(strSQL_m);
while(rss.next()){
modalityname=modalityname+rss.getString("ModalityName")+",";
}
rss.close();
if ("".equals(modalityname)) {
modalityname="All";
}
String strSQL_pd = "select productID,productName from TTG_Product where productid in"
+"(select Replace(product,'p','') as productid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'p%')";
rss=JspAdmJdbc.strSQLSet(strSQL_pd);
while(rss.next()){
productname=productname+rss.getString("ProductName")+",";
}
rss.close();
if ("".equals(productname)) {
productname="All";
}
String strSQL_ac ="select ProcessID,AccountProperty from TTA_ProNodeAccount where processid ="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ac);
System.out.println(strSQL_ac);
if(!rss.next()){
account="All";
}else{
if ("1".equals(rss.getString("AccountProperty")))
account="Key Account";
if ("2".equals(rss.getString("AccountProperty")))
account="General Account";
if ("3".equals(rss.getString("AccountProperty")))
account="Value Account";
if ("0".equals(rss.getString("AccountProperty")))
account="All";
}
System.out.println("acount: "+account);
rss.close();
String strSQL_t="select TitleID,TitleName from TTA_Title where TitleID in (select TitleID from TTA_ProcessToEmp where ProcessID ="+rs.getInt("ProcessID")+") ";
rss=JspAdmJdbc.strSQLSet(strSQL_t);
if(rss.next()){
titlename=rss.getString("TitleName");
}
if ("".equals(titlename)){
titlename="None";
}
rss.close();
String strSQL_ch="select ProcessID,IsEnd,Channel from TTA_ProNodeCondition where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ch);
if(rss.next()){
if(rss.getInt("IsEnd")==0){
IsEnd="No";
}else{
IsEnd="Yes";
}
if(rss.getInt("Channel")==1){
Channel="Direct";
}
if(rss.getInt("Channel")==2){
Channel="InDirect";
}
if(rss.getInt("Channel")==0){
Channel="All";
}
}
rss.close();
strSQL_ch="select IsDirect from TTA_ProcessToEmp where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ch);
if(rss.next()){
if(rss.getInt("IsDirect")==1){
IsDirect="Yes";
}else{
IsDirect="No";
}
}
rss.close();
String strSQL_con="select FreeCondition from TTA_ProNodeCondition where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_con);
if(rss.next()){
if(rss.getString("FreeCondition")==null){
FreeCondition="None";
}else{
FreeCondition=(rss.getString("FreeCondition")).trim();
}
}
rss.close();
String ChangeIndex_3="";
out.print("<td><div align='center' class='style1'>"+rs.getInt("NodeID")+"</div></TD>"); out.print("<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Territory.jsp?ProcessID="+rs.getInt("ProcessID")+">"+territory+"</a></div></TD>"); out.print("<td><div align='center' class='style1'><A HREF=Edit_Channel.jsp?ProcessID="+rs.getInt("ProcessID")+">"+Channel+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_function.jsp?ProcessID="+rs.getInt("ProcessID")+">"+functionname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:175px'><div align='center' class='style1'><A HREF=Edit_Modality.jsp?ProcessID="+rs.getInt("ProcessID")+">"+modalityname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Product.jsp?ProcessID="+rs.getInt("ProcessID")+"&actionIndication="+ChangeIndex_3+"&showTableIndication=3>"+productname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Account.jsp?ProcessID="+rs.getInt("ProcessID")+">"+account+"</a></div></TD>");
out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'>" + FreeCondition + "</div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_IsEnd.jsp?ProcessID="+rs.getInt("ProcessID")+">"+IsEnd+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_IsDirect.jsp?ProcessID="+rs.getInt("ProcessID")+">"+IsDirect+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:80px'><div align='center' class='style10'><A HREF=Edit_Title.jsp?ProcessID="+rs.getInt("ProcessID")+">"+titlename+"</a></div></TD>");
out.print("<td align='center' style='word-wrap:break-word;width:25px'><img src='../../images/button_del.gif' hspace=0 vspace=0 border=0 style='cursor:hand'onMouseUp=ConfirmDel("+rs.getInt("ProcessID")+")></td>");
}
rs.close();
现在我领导给我个方案,让我把每个sql写在数据库中,用plsql,我试了下,还是要重复的连接数据库,效果不好,速度还是很慢,后来我想能不能把上面的java代码全放在plsql中,最后从plsql中传个集合到jsp页面,这样只需连接一次数据库。
我还需要做个查询功能,因为加载速度很慢,查询也一定很慢,请问高手们能不能把这些在页面上显示得值保存的数据库中,通过创建一个view,或者表来,然后再去查询的时候直接去访问这个表或view
有人遇到加载页面响应速度很慢的问题吗?大家能帮帮忙帮我解决吗?真是非常感谢大家了
//String strSQL="select ProcessID,NodeID from (select ProcessID,NodeID from TTA_ProcessNode where FormID=? order by NodeID desc) where rownum<3";
System.out.println(strSQL);
ResultSet rs=JspAdmJdbc.strSQLSet(formid,strSQL);
ResultSet rss;
while (rs.next()){
out.print("<tr bgcolor='#ffffff'>");
String territory_r="";
String territory_p="";
String territory_c="";
String territory_a="";
String territory="";
String modalityname="";
String productname="";
String functionname="";
String account="";
String titlename="";
String FreeCondition="";
String IsEnd="";
String Channel="";
String IsDirect="";
//rs.getInt("ProcessID");
String strSQL_r="select RegionID,RegionName from TTG_Region where ltrim(rtrim(RegionID)) in" +
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'R%')";
rss=JspAdmJdbc.strSQLSet(strSQL_r);
while(rss.next()){
territory_r=territory_r+rss.getString("RegionName")+",";
}
rss.close();
String strSQL_p = "select ProvinceID,ProvinceName from TTG_Province where ltrim(rtrim(ProvinceID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'P%')";
rss=JspAdmJdbc.strSQLSet(strSQL_p);
while(rss.next()){
territory_p=territory_p+rss.getString("ProvinceName")+",";
}
rss.close();
String strSQL_c = "select CityID,CityName from TTG_City where ltrim(rtrim(CityID)) in "+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'C%')";
rss=JspAdmJdbc.strSQLSet(strSQL_c);
while(rss.next()){
territory_c=territory_c+rss.getString("CityName")+",";
}
rss.close();
String strSQL_a = "select AccountID,EnglishName from TTG_Account where ltrim(rtrim(AccountID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'H%')";
rss=JspAdmJdbc.strSQLSet(strSQL_a);
while(rss.next()){
territory_a=territory_a+rss.getString("EnglishName")+",";
}
rss.close();
territory=territory_r+territory_p+territory_c+territory_a;
if ("".equals(territory)){
territory="All";
}
String strSQL_f="select FunctionID,FunctionName from TTG_Function where FunctionID in "+
"(select FunctionID from TTA_ProNodeFunction where ProcessID ="+rs.getInt("ProcessID")+")";
rss=JspAdmJdbc.strSQLSet(strSQL_f);
while(rss.next()){
functionname=functionname+rss.getString("FunctionName")+",";
}
rss.close();
if ("".equals(functionname)) {
functionname="All";
}
String strSQL_m = "select modalityID,ModalityName from ttg_modality where modalityID in"
+"(select Replace(product,'m','') as modalityid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'm%')";
rss=JspAdmJdbc.strSQLSet(strSQL_m);
while(rss.next()){
modalityname=modalityname+rss.getString("ModalityName")+",";
}
rss.close();
if ("".equals(modalityname)) {
modalityname="All";
}
String strSQL_pd = "select productID,productName from TTG_Product where productid in"
+"(select Replace(product,'p','') as productid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'p%')";
rss=JspAdmJdbc.strSQLSet(strSQL_pd);
while(rss.next()){
productname=productname+rss.getString("ProductName")+",";
}
rss.close();
if ("".equals(productname)) {
productname="All";
}
String strSQL_ac ="select ProcessID,AccountProperty from TTA_ProNodeAccount where processid ="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ac);
System.out.println(strSQL_ac);
if(!rss.next()){
account="All";
}else{
if ("1".equals(rss.getString("AccountProperty")))
account="Key Account";
if ("2".equals(rss.getString("AccountProperty")))
account="General Account";
if ("3".equals(rss.getString("AccountProperty")))
account="Value Account";
if ("0".equals(rss.getString("AccountProperty")))
account="All";
}
System.out.println("acount: "+account);
rss.close();
String strSQL_t="select TitleID,TitleName from TTA_Title where TitleID in (select TitleID from TTA_ProcessToEmp where ProcessID ="+rs.getInt("ProcessID")+") ";
rss=JspAdmJdbc.strSQLSet(strSQL_t);
if(rss.next()){
titlename=rss.getString("TitleName");
}
if ("".equals(titlename)){
titlename="None";
}
rss.close();
String strSQL_ch="select ProcessID,IsEnd,Channel from TTA_ProNodeCondition where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ch);
if(rss.next()){
if(rss.getInt("IsEnd")==0){
IsEnd="No";
}else{
IsEnd="Yes";
}
if(rss.getInt("Channel")==1){
Channel="Direct";
}
if(rss.getInt("Channel")==2){
Channel="InDirect";
}
if(rss.getInt("Channel")==0){
Channel="All";
}
}
rss.close();
strSQL_ch="select IsDirect from TTA_ProcessToEmp where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ch);
if(rss.next()){
if(rss.getInt("IsDirect")==1){
IsDirect="Yes";
}else{
IsDirect="No";
}
}
rss.close();
String strSQL_con="select FreeCondition from TTA_ProNodeCondition where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_con);
if(rss.next()){
if(rss.getString("FreeCondition")==null){
FreeCondition="None";
}else{
FreeCondition=(rss.getString("FreeCondition")).trim();
}
}
rss.close();
String ChangeIndex_3="";
out.print("<td><div align='center' class='style1'>"+rs.getInt("NodeID")+"</div></TD>"); out.print("<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Territory.jsp?ProcessID="+rs.getInt("ProcessID")+">"+territory+"</a></div></TD>"); out.print("<td><div align='center' class='style1'><A HREF=Edit_Channel.jsp?ProcessID="+rs.getInt("ProcessID")+">"+Channel+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_function.jsp?ProcessID="+rs.getInt("ProcessID")+">"+functionname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:175px'><div align='center' class='style1'><A HREF=Edit_Modality.jsp?ProcessID="+rs.getInt("ProcessID")+">"+modalityname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Product.jsp?ProcessID="+rs.getInt("ProcessID")+"&actionIndication="+ChangeIndex_3+"&showTableIndication=3>"+productname+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_Account.jsp?ProcessID="+rs.getInt("ProcessID")+">"+account+"</a></div></TD>");
out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'>" + FreeCondition + "</div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_IsEnd.jsp?ProcessID="+rs.getInt("ProcessID")+">"+IsEnd+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:50px'><div align='center' class='style1'><A HREF=Edit_IsDirect.jsp?ProcessID="+rs.getInt("ProcessID")+">"+IsDirect+"</a></div></TD>"); out.print( "<td style='word-wrap:break-word;width:80px'><div align='center' class='style10'><A HREF=Edit_Title.jsp?ProcessID="+rs.getInt("ProcessID")+">"+titlename+"</a></div></TD>");
out.print("<td align='center' style='word-wrap:break-word;width:25px'><img src='../../images/button_del.gif' hspace=0 vspace=0 border=0 style='cursor:hand'onMouseUp=ConfirmDel("+rs.getInt("ProcessID")+")></td>");
}
rs.close();
现在我领导给我个方案,让我把每个sql写在数据库中,用plsql,我试了下,还是要重复的连接数据库,效果不好,速度还是很慢,后来我想能不能把上面的java代码全放在plsql中,最后从plsql中传个集合到jsp页面,这样只需连接一次数据库。
我还需要做个查询功能,因为加载速度很慢,查询也一定很慢,请问高手们能不能把这些在页面上显示得值保存的数据库中,通过创建一个view,或者表来,然后再去查询的时候直接去访问这个表或view
有人遇到加载页面响应速度很慢的问题吗?大家能帮帮忙帮我解决吗?真是非常感谢大家了
解决方案 »
- 问一个偏门的问题,关于spring声明式事务
- jsp连接数据库的问题! 奋斗了一天一夜!真不知道哪里出问题了!各位高手帮个忙!
- java web开发的学习路线
- 请教struts开发中首页和公共页面的问题
- struts+hibernate 遇到的问题!
- java中如何判断页面输入的是否为合法的时间
- 新手 超简单问题~!bean 怎么 调用 的?看看我的代码对吗?
- 急用:JSP如何实现让打印机打印特定目录中的所有文件
- Struts校验配置问题
- 一个jsp计数器的错误!(怎么回事)
- java.sql.SQLException: Io 异常:The Network Adapter could not establish the connect
- jfreechart热点图传参问题
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'R%')";
rss=JspAdmJdbc.strSQLSet(strSQL_r);
while(rss.next()){
territory_r=territory_r+rss.getString("RegionName")+",";
}
rss.close(); //这句是不是可以去掉啊。
String strSQL_p = "select ProvinceID,ProvinceName from TTG_Province where ltrim(rtrim(ProvinceID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'P%')";
rss=JspAdmJdbc.strSQLSet(strSQL_p);
while(rss.next()){
territory_p=territory_p+rss.getString("ProvinceName")+",";
}
rss.close();
还有,你查询了一次,只需要RegionName。你是不是可以把这两个SQL语句用一次查询呢?
其他的情况类似,你看能不能把那些级联查询用一个SQL语句完成.可以一试。
//String strSQL="select ProcessID,NodeID from (select ProcessID,NodeID from TTA_ProcessNode where FormID=? order by NodeID desc) where rownum<3";
System.out.println(strSQL);
ResultSet rs=JspAdmJdbc.strSQLSet(formid,strSQL);
ResultSet rss;
while (rs.next()){
out.print("<tr bgcolor='#ffffff'>");
String territory_r="";
String territory_p="";
String territory_c="";
String territory_a="";
String territory="";
String modalityname="";
String productname="";
String functionname="";
String account="";
String titlename="";
String FreeCondition="";
String IsEnd="";
String Channel="";
String IsDirect="";
//rs.getInt("ProcessID");
String strSQL_r="select RegionID,RegionName from TTG_Region where ltrim(rtrim(RegionID)) in" +
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'R%')"+
"union select ProvinceID,ProvinceName from TTG_Province where ltrim(rtrim(ProvinceID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'P%')"+
"union select CityID,CityName from TTG_City where ltrim(rtrim(CityID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID = "+rs.getInt("ProcessID")+" and territory like 'C%')"+
"union select AccountID,EnglishName from TTG_Account where ltrim(rtrim(AccountID)) in"+
"(select ltrim(rtrim(territory)) from TTA_ProNodeTerritory where ProcessID ="+rs.getInt("ProcessID")+" and territory like 'H%')"; rss=JspAdmJdbc.strSQLSet(strSQL_r);
while(rss.next()){
territory_r=territory_r+rss.getString("RegionName")+",";
}
rss.close();
territory=territory_r;
if ("".equals(territory)){
territory="All";
}
System.out.println("territory:"+territory);
String strSQL_ac ="select ProcessID,AccountProperty from TTA_ProNodeAccount where processid ="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ac);
System.out.println(strSQL_ac);
if(!rss.next()){
account="All";
}else{
if ("1".equals(rss.getString("AccountProperty")))
account="Key Account";
if ("2".equals(rss.getString("AccountProperty")))
account="General Account";
if ("3".equals(rss.getString("AccountProperty")))
account="Value Account";
if ("0".equals(rss.getString("AccountProperty")))
account="All";
}
System.out.println("acount: "+account);
rss.close();
String strSQL_m = "select modalityID,ModalityName from ttg_modality where modalityID in"
+"(select Replace(product,'m','') as modalityid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'm%')";
rss=JspAdmJdbc.strSQLSet(strSQL_m);
System.out.println(strSQL_m);
while(rss.next()){
modalityname=modalityname+rss.getString("ModalityName")+",";
}
rss.close();
if ("".equals(modalityname)) {
modalityname="All";
}
String strSQL_pd = "select productID,productName from TTG_Product where productid in"
+"(select Replace(product,'p','') as productid from tta_ProNodeProduct where processid ="+rs.getInt("ProcessID")+" and product like 'p%')";
rss=JspAdmJdbc.strSQLSet(strSQL_pd);
System.out.println(strSQL_pd);
while(rss.next()){
productname=productname+rss.getString("ProductName")+",";
}
rss.close();
if ("".equals(productname)) {
productname="All";
}
String strSQL_f="select FunctionID,FunctionName from TTG_Function where FunctionID in "+
"(select FunctionID from TTA_ProNodeFunction where ProcessID ="+rs.getInt("ProcessID")+")";
rss=JspAdmJdbc.strSQLSet(strSQL_f);
while(rss.next()){
functionname=functionname+rss.getString("FunctionName")+",";
}
rss.close();
if ("".equals(functionname)) {
functionname="All";
}
String strSQL_ch="select ProcessID,IsEnd,Channel,FreeCondition from TTA_ProNodeCondition where ProcessID="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_ch);
System.out.println(strSQL_ch);
if(rss.next()){
if(rss.getInt("IsEnd")==0){
IsEnd="No";
}else{
IsEnd="Yes";
}
if(rss.getInt("Channel")==1){
Channel="Direct";
}
if(rss.getInt("Channel")==2){
Channel="InDirect";
}
if(rss.getInt("Channel")==0){
Channel="All";
}
if(rss.getString("FreeCondition")==null){
FreeCondition="None";
}else{
FreeCondition=(rss.getString("FreeCondition")).trim();
}
}
rss.close();
String strSQL_t="select a.TitleID,a.TitleName,b.IsDirect from (select TitleID,TitleName from TTA_Title where TitleID in (select TitleID from TTA_ProcessToEmp )) a ,TTA_ProcessToEmp b,TTA_ProNodeAccount c where a.TitleID=b.TitleID and b.processid="+rs.getInt("ProcessID")+"";
rss=JspAdmJdbc.strSQLSet(strSQL_t);
System.out.println(strSQL_t);
if(rss.next()){
titlename=rss.getString("TitleName");
if(rss.getInt("IsDirect")==1){
IsDirect="Yes";
}else{
IsDirect="No";
}
}
if ("".equals(titlename)){
titlename="None";
}
rss.close();
rss=JspAdmJdbc.strSQLSet(strSQL_con);
试试优化一下,或者直接拿一个连接出来,那么这么一个页面就只需要一个连接就OK了~~
还有同一个语句你会执行很多次,所以请使用预编译,参数用?来设置.
这样的话应该会有一定的提高了.
jsp+javabean,当然你要是懂servlet更好,加入servlet。
看着语句不是很多.
2 ltrim(rtrim(RegionID)) in ... 类似这样的是用不到索引的.
首先每个sql都自己执行一下,看下效率,如果慢的,就需要换种写法,能用索引的最好用到索引,或者看看是不是有些查询没建立索引.
3 如楼上所说rs不需要关闭,我的看法是必须关闭.
4 使用preparedstatement来执行sql和传入参数,因为preparedstatement可以执行一个预编译.
首先每个sql都自己执行一下,看下效率,如果慢的,就需要换种写法,能用索引的最好用到索引,或者看看是不是有些查询没建立索引.建立索引,他们都不是不同的表,你的意思是说在表中就建立索引,还是在新查询出来的结果中建立索引,能说的具体一点吗?谢谢
如果你的JspAdmJdbc.strSQLSet()里面是直接打开Connect的话,
不可能会快起来。给你想个办法,
反正你是跑jsp,肯定用的tomcat之类的吧
如果使用tomcat,可以参照下面的连接
http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html在tomcat级别建立一个连接池,
然后重写一下那个JspAdmJdbc.strSQLSet(),
让它得到Connect的方法变为从tomcat里面“捞”,这样即满足了你头“简单的jsp”的需求,
也使用上了连接池。good luck
也可以用Ibatis
如果不会 可以单独联系
QQ:275080310
ResultSet rs =null;
Statement stmt = null;
try{
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/postgres");
Connection conn = ds.getConnection();
if(ds!=null){
conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(str);
}
}
catch(Exception ex){
ex.printStackTrace();
} finally{
}
return rs;
}
配置文件位置是不一样的,
一定要区分开,我之前给的是最新的7.0的,需要配置的文件的信息在下面这个连接,
http://tomcat.apache.org/tomcat-7.0-doc/config/context.html其中这段话一定要看清楚
Context elements may be explicitly defined: 之前的版本lz自己去找吧,就在tomcat.apache.org左面的documentation下面有相应版本的连接good luck
<description>postgreSQL Datasource example</description>
<res-ref-name>jdbc/postgres</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> 这是在web.xml
<Resource name="jdbc/postgres" auth="Container"
type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@3.36.234.24:1521:DEPOP3"
username="epop3dbo" password="gtmepop3" maxActive="20" maxIdle="10"
maxWait="-1"/>
这是server.xml, <ResourceLink global="jdbc/postgres" name="jdbc/postgres" type="javax.sql.DataSource"/> 这是context.xml