JSP中显示不同表的字段，怎么用bean处理啊?

[Richie2000]

我现在正在使用JSP编写一个页面,但是现在要在JSP页面的同一行上显示来自不同表的信息，请问各位高手如何实现啊？
最好能有源代码，多谢了

[qdcomn]

什么样的数据库？
用视图吧，很简单
sql server 2000视图建立方法：点击视图－新建视图－添加你需要调用的那多个表－选中多个表中需要调用的字段－链接各个表的关联字段
视图和表的访问方式相同

• 打赏
• 举报

回复 赞

[lboss_li]

用视图，或者用 jfy3d(剑事) 所说的那类sql查询就ok了！

• 打赏
• 举报

回复 赞

[kingmaxno1]

要是实在没办法就这样：
public void getSQL(String sql)
{
try
{
....
//就是把sql语句的全句打进去
ResultSet rs=ps.execute(sql);
.....
}
catch(Exception e)
{
...
}
}

• 打赏
• 举报

回复 赞

[jfy3d]

select t1.字段，t2字段.... from table1 t1,table2 t2

• 打赏
• 举报

回复 赞

[Vicky020923]

import java.sql.ResultSetMetaData
然后自己找帮助看吧

• 打赏
• 举报

回复 赞

[Richie2000]

谢谢大家，可是在其中还有一些SQL的Count之类的，怎么办啊

• 打赏
• 举报

回复 赞

[玄玥]

用数组怎样？写两个sql语句。
先把count()的东西找出来，
然后用别的sql语句找别的字段。
数组很好玩的

• 打赏
• 举报

回复 赞

[tianruoyou]

先写一个javabean,把sql语句写到里面

package database;
import java.sql.*;
import java.util.*;
import javax.sql.*;

public class databaseBean {
public Connection connect() throws ClassNotFoundException,
SQLException,
Exception {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(
"jdbc:oracle:thin:@10.3.8.8:1521:ora", "ycdb", "ycdb ");
} catch (ClassNotFoundException cnfe) {
error = "ClassNotFoundException: Could not locate DB driver.";
throw new ClassNotFoundException(error);
} catch (SQLException cnfe) {
error = "SQLException: Could not connect to database.";
throw new SQLException(error);
} catch (Exception e) {
error = "Exception: An unknown error occurred while connecting " +
"to database.";
throw new Exception(error);
}
return con;
}

public ResultSet viewData_dbdDetail(String staff_id,String oper_type,String start_time,String end_time,Connection con)
throws SQLException, Exception {
ResultSet rs = null;
String sqlstr=null;
String sql_opertype=null;
if (oper_type.equals("ALL")){
sql_opertype=" b.type_id in('1001','1021','2001') ";
}else{
sql_opertype=" b.type_id='"+oper_type+"' ";
}


try {
sqlstr="select a.order_no as order_no,b.name as name,a.staff_id as staff_id,-a.charge/100 as charge,a.created_date"
+" from prepay_used a,(select distinct type_id,name from explain_type where flag='O' ) b "
+" where a.oper_type=b.type_id and to_char(a.created_date,'yyyy-mm-dd') between ? and ? "
+" and "+sql_opertype+" and a.staff_id=?"
+" order by a.order_no ";

pstmt=con.prepareStatement(sqlstr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstmt.setString(1,start_time);
pstmt.setString(2,end_time);
//pstmt.setString(3,sql_opertype);
pstmt.setString(3,staff_id);
rs=pstmt.executeQuery();

} catch (SQLException sqle) {
error = "SQLException: Could not execute the query."+sqlstr+"";
throw new SQLException(error);
} catch (Exception e) {
error = "An exception occured while retrieving books.";
throw new Exception(error);
}
return rs;
}
}

• 打赏
• 举报

回复 赞

[tianruoyou]

然后在jsp里面调用,count之类的在sql语句里面sum一把不就出来了？
<jsp:useBean id="dbBean" class="database.databaseBean" />
<%
Connection con=dbBean.connect();
ResultSet rs=null;
....
rs=dbBean.viewData_dbcDetail(site_id,oper_type,start_time,end_time,con);
%>

<!----------------------------------------------------------------------------------------
显示区域
--------------------------------------------------------------------------------------->
<p align="center"><font color="#000099" size="3"><strong><%=site_name%><%=oper_name%>揽机详单</strong></font></p>

<TABLE border="1" width="98%" bgcolor="#F2F2F2" bordercolorlight="#808080" cellspacing="0" cellpadding="0" bordercolordark="#FFFFFF" id="data">
<TR>
<TD width="20%" height="18" bgcolor="#64B221"><p align="center">订单号</TD>
<TD width="25%" height="18" bgcolor="#64B221"><p align="center">业务类型</TD>
<TD width="15%" height="18" bgcolor="#64B221"><p align="center">员工号</TD>
<TD width="15%" height="18" bgcolor="#64B221"><p align="center">金额</TD>
<TD width="25%" height="18" bgcolor="#64B221"><p align="center">办理时间</TD>
</TR>
<%
if (rs!=null){
String charge=null;
double charge1=0;
while (rs.next()){
java.text.DecimalFormat df1 = new java.text.DecimalFormat("##0.00");
charge1=rs.getDouble("charge");
charge=df1.format(charge1);
%>
<TR>
<TD width="20%" height="18" bgcolor="white" align="center"><%=rs.getString("order_no")%></TD>
<TD width="25%" height="18" bgcolor="white" align="center"><%=rs.getString("name")%></TD>
<TD width="15%" height="18" bgcolor="white"><p align="center"><%=rs.getString("staff_id")%></TD>
<TD width="15%" height="18" bgcolor="white"><p align="center"><%=charge%></TD>
<TD width="25%" height="18" bgcolor="white"><p align="center"><%=rs.getString("created_date")%></TD>

</TR>

• 打赏
• 举报

回复 赞

[joy19]

tianruoyou() ：已经写的非常详细了！我这有一个直接在页面同时调用多个表的代码！你看看吧！
<HTML>

<HTML>

<script language=JavaScript >
<!--
function toPhoneDemandResult()
{
document.FORM_WatchDemandResult.action = "PhoneDemandResult.jsp";
}
//-->
</script>

<HEAD>
<TITLE>电话管理子系统查询电话记录返回界面</TITLE>
<script language=JavaScript src="JSLib.js" type=text/javascript>
</script>
<LINK REL=stylesheet HREF="../Styles/MyStyles.css" TYPE="text/css">
</HEAD>

<%@ page contentType="text/html;charset=gb2312" %>
<%@ page language="java" errorPage="../Styles/ErrorPage.jsp" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<jsp:useBean id="javaWatchDemandResult" scope="session" class="InfoDatabase.OpenDatabase"/>

<BODY>

<FORM id=FORM_WatchDemandResult name=FORM_WatchDemandResult action="PhoneDemandResult.jsp" method=post style="POSITION: absolute; TOP: 20px">
<%
String strUserName = (String) session.getValue("strUserName");
String strEngineerID = (String) session.getValue("strEngineerID");
String strPriorityOfEngineer = (String) session.getValue("strPriorityOfEngineer");
String strPriorityOfParts = (String) session.getValue("strPriorityOfParts");
// System.err.println("\n" + strEngineerName +"_" + strPriorityOfEngineer + ": create new Phone record");
if (strEngineerID == null)
{
%>
<P ><font size="5" color=Red>由于非法访问本网页，你的信息将记录在案。</font></P>
<%
}
else
{
%>

<P align=left>
<font size="2" color="#000080">首页>>客户管理>>巡访记录查询</font>
</P>
<%
// int nPriorityOfEngineer = new Integer(strPriorityOfEngineer).intValue();

//...
//提取查询条件
byte[] b = new byte[128];
String sql,strCustomerName="",strMachineType="";
ResultSet res;
String strCustomerId = request.getParameter("CustomerId");
String dateSetupMachine="";
//...

b = strCustomerId.getBytes("8859_1");
strCustomerId = new String(b,"gb2312");
//...
String strMachineId = request.getParameter("MachineId");
//...
sql = "select m_strCustomerName from infodb_CustomerDoc where m_strCustomerId ='"+strCustomerId+"' ";
res = javaWatchDemandResult.executeQuery( sql );
if(res.next())
{
strCustomerName = res.getString(1);
}
res.close();

sql = "select m_strMachineType,m_dateSetupMachine from infodb_SetupMachine where m_strMachineId ='"+strMachineId+"' ";
res = javaWatchDemandResult.executeQuery( sql );
if(res.next())
{
strMachineType = res.getString(1);
dateSetupMachine = res.getString(2);
}
res.close();


%>
<table name=TaskQueryTable1 width="800" border="1" bordercolor="#FF6600" align="center" cellPadding=1 cellSpacing=1>
<TR>
<TD align=center valign="middle" align=center width="15%">
<font size="3" color="#000080"><b>客户名称</b></font>
</TD>
<TD valign="middle" align=center width="35%">
<INPUT id=strCustomerName name=strCustomerName style="WIDTH: 80%" value="<% out.print(strCustomerName); %>" readonly>
</TD>
<TD align=center valign="middle" align=center width="15%">
<font size="3" color="#000080"><b>设备类型</b></font>
</TD>
<TD valign="middle" align=center width="35%">
<INPUT id=strMachineType name=strMachineType style="WIDTH: 80%" value="<% out.print(strMachineType); %>" readonly>
</TD>
</TR>
<TR>
<TD align=center valign="middle" align=center width="15%">
<font size="3" color="#000080"><b>设备编号</b></font>
</TD>
<TD valign="middle" align=center width="35%">
<INPUT id=datePhoneDate1 name=datePhoneDate1 style="WIDTH: 80%" value="<% out.print(strMachineId); %>" readonly>
</TD>
<TD align=center valign="middle" align=center width="15%">
<font size="3" color="#000080"><b>装机时间</b></font>
</TD>
<TD valign="middle" align=center width="35%">
<INPUT id=datePhoneDate2 name=datePhoneDate2 style="WIDTH: 80%" value="<% out.print(dateSetupMachine); %>" readonly>
</TD>
</TR>
</table>

<TABLE WIDTH=800 align=center border="1" bordercolor=#FF6600>
<tr valign=middle align=center>
<td width="20%" height=25><font size="2">巡访时间</font></td>
<td width="15%" height=25><font size="2">巡访工程师</font></td>
<td width="15%" height=25><font size="2">填写人</font></td>
<td width="20%" height=25><font size="2">填写日期</font></td>
<td width="30%" height=25><font size="2">备注</font></td>
</tr>
<%
sql = "select m_nNumber,m_dataWatch, m_strWatchEngineer,m_strWatchMan,m_dataWatchfill,m_strWatch from infodb_Watch where m_strMachineId ='" +strMachineId+ "'" ;
res = javaWatchDemandResult.executeQuery( sql );
while (res.next())
{
int nNumber = res.getInt(1);
String dataWatch =res.getString(2);
String strWatchEngineer = res.getString(3);
String strWatchMan = res.getString(4);
String dataWatchfill = res.getString(5);
String strWatch = res.getString(6);

%>
<tr valign=middle align=center>
<td width="20%" height="36" ><font size=2 color=#000080><%out.println(dataWatch);%></font></td>
<td width="15%"height="36"><font size=2 color=#000080><%out.println(strWatchEngineer);%></font></td>
<td width="15%" height="36"><font size=2 color=#000080><%out.println(strWatchMan);%></font></td>
<td width="20%" height="36"><font size=2 color=#000080><%out.println(dataWatchfill);%></font></td>
<td width="30%" height="36"><font size=2 color=#000080><%out.println(strWatch);%></font></td>
</tr>
<%
}
res.close();
}
}
%>
</table>


</form>

</body>

</html>

• 打赏
• 举报

回复 赞

[Richie2000]

谢谢大家，我先好好学习一下

• 打赏
• 举报

回复 赞