JSP: 连接ORACLE数据库读取数据（含存储过程）

6w3i40s9b7

　　这个是JSP页面中IFRAME中的内容，单击主页面的SEARCH打开IFRAME
　　使用的数据库是ORACLE 10g,Tomcat 3.2


<tableclass="TBLSETTING"bordercolor="#9999FF"border="2"cellpadding="0"cellspacing="0">
<tr>
<tdclass="LOCKMOJI"width="90"align="middle">日付</td>
<tdclass="LOCKMOJI"width="70"align="middle">取引内容</td>
<tdclass="LOCKMOJI"width="70"align="middle">取引金額</td>
<tdclass="LOCKMOJI"width="130"align="middle">残高</td>
</tr>
<%...
Stringaccount=String.valueOf(request.getParameter("ID"));
Stringmonth=String.valueOf(request.getParameter("month"));

if(request.getParameter("search")==null)
{
}
else
{
StringoracleDriver="oracle.jdbc.driver.OracleDriver";
Stringurl="jdbc:oracle:thin:@172.16.100.10:1521:ATMDB";//addressofdatabase
Stringuid="ATMUser";//username
Stringpwd="ATMUser";//password
Connectionconn=null;
ResultSetrst=null;

try{
Class.forName(oracleDriver).newInstance();//adddrive

conn=DriverManager.getConnection(url,uid,pwd);
}catch(ClassNotFoundExceptionex){
out.print("DriverLoadError");
}catch(SQLExceptionex){
out.print("CannotConnecttoDataBase");
}
/*
//EXECUTEPROCEDURE
StringstrSql="{callPKG_I_SEARCH_HISTORY.PRC_MAIN(?,?,?,?,?)}";
CallableStatementstmt=conn.prepareCall(strSql);
stmt.registerOutParameter(1,Types.NUMERIC);
stmt.registerOutParameter(2,Types.VARCHAR);
stmt.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
stmt.setString(4,account);
stmt.setString(5,month);
stmt.execute();
rst=(ResultSet)stmt.getObject(3);
*/

//SQLで実行する
StringstrSQL="";
strSQL+="SELECT";
strSQL+="TO_CHAR(ENDTIME,'YYYY/MM/DDHH24:MI:SS')ENDTIME";
strSQL+=",(CASEACTION_TYPE";
strSQL+="WHEN'1'THEN'引出'";
strSQL+="WHEN'2'THEN'入金'";
strSQL+="WHEN'3'THEN'振込'";
strSQL+="ELSE''";
strSQL+="END)ACTION_TYPE";
strSQL+=",AMOUNT";
strSQL+=",BALANCE_NOW";
strSQL+="FROM";
strSQL+="ATM_HISTORY";
strSQL+="WHERE";
strSQL+="ACCOUNT_ID='"+account+"'";
strSQL+="ANDENDTIME>=TO_DATE('"+month+"'||'01000000','YYYYMMDDHH24MISS')";
strSQL+="ANDENDTIME<ADD_MONTHS(TO_DATE('"+month+"'||'01000000','YYYYMMDDHH24MISS'),1)";
strSQL+="ORDERBYENDTIME";

Statementstmt=conn.createStatement();
rst=stmt.executeQuery(strSQL);

while(rst.next())
{
out.println("<tr>");
out.println("<tdalign='center'width='90'>"+rst.getString("ENDTIME").substring(0,10)+"<br>"+rst.getString("ENDTIME").substring(10,19)+"</td>");
out.println("<tdalign='center'width='70'>"+rst.getString("ACTION_TYPE")+"</td>");
out.println("<tdalign='right'width='70'>"+rst.getString("AMOUNT")+"</td>");
out.println("<tdalign='right'width='130'>"+rst.getString("BALANCE_NOW")+"</td>");
out.println("</tr>");
}

rst.close();
stmt.close();
conn.close();
}
%>

</table>