这个是JSP页面中IFRAME中的内容,单击主页面的SEARCH打开IFRAME
使用的数据库是ORACLE 10g,Tomcat 3.2
< table class ="TBLSETTING" bordercolor ="#9999FF" border ="2" cellpadding ="0" cellspacing ="0" >
< tr >
< td class ="LOCKMOJI" width ="90" align ="middle" > 日付 </ td >
< td class ="LOCKMOJI" width ="70" align ="middle" > 取引内容 </ td >
< td class ="LOCKMOJI" width ="70" align ="middle" > 取引金額 </ td >
< td class ="LOCKMOJI" width ="130" align ="middle" > 残高 </ td >
</ tr >
<% ...
String account = String.valueOf(request.getParameter("ID"));
String month = String.valueOf(request.getParameter("month"));
if (request.getParameter("search") == null)
{
}
else
{
String oracleDriver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@172.16.100.10:1521:ATMDB"; // address of database
String uid = "ATMUser"; // username
String pwd = "ATMUser"; // password
Connection conn = null;
ResultSet rst = null;
try {
Class.forName(oracleDriver).newInstance(); // add drive
conn = DriverManager.getConnection(url , uid, pwd);
} catch(ClassNotFoundException ex) {
out.print("Driver Load Error");
} catch(SQLException ex) {
out.print("Cannot Connect to DataBase");
}
/*
// EXECUTE PROCEDURE
String strSql = "{call PKG_I_SEARCH_HISTORY.PRC_MAIN(?, ?, ?, ?, ?)}";
CallableStatement stmt = 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で実行する
String strSQL = "";
strSQL += " SELECT ";
strSQL += " TO_CHAR(ENDTIME,'YYYY/MM/DD HH24:MI:SS') ENDTIME ";
strSQL += " ,(CASE ACTION_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 += " AND ENDTIME >= TO_DATE('" + month + "' || '01000000', 'YYYYMMDDHH24MISS') ";
strSQL += " AND ENDTIME < ADD_MONTHS(TO_DATE('" + month + "' || '01000000', 'YYYYMMDDHH24MISS'), 1) ";
strSQL += " ORDER BY ENDTIME ";
Statement stmt=conn.createStatement();
rst=stmt.executeQuery(strSQL);
while(rst.next())
{
out.println("<tr>");
out.println("<td align='center' width='90'>" + rst.getString("ENDTIME").substring(0,10) + "<br>" + rst.getString("ENDTIME").substring(10,19) + "</td>");
out.println("<td align='center' width='70'>" + rst.getString("ACTION_TYPE") + "</td>");
out.println("<td align='right' width='70'>" + rst.getString("AMOUNT") + "</td>");
out.println("<td align='right' width='130'>" + rst.getString("BALANCE_NOW") + "</td>");
out.println("</tr>");
}
rst.close();
stmt.close();
conn.close();
}
%>
</ table >