分兩頁。第一頁提示使用者輸入顧客卡號，第二頁根據卡號返回顧客詳細資訊。由於卡號位一確定某位顧客，故該顧客的交易資訊也同時返回。

要點：
1) 根據返回紀錄動態生成表格
out.println("");
out.println(""+sCustId+"");
out.println(""+sCustName+"");
......

2) 若某欄位結果為空，則應付值 &nbsp，以避免生成的表格缺邊框
sCustEmail = rs.getString("cst_email");
if (sCustEmail==null) sCustEmail=" ";[@more@]

源程式

qry_cust_id1.jsp:

String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

顧客資訊查詢

<!--

--&gt

按顧客卡號查詢

請輸入顧客卡號:

qry_cust_id2.jsp:

String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
java.util.Date d = new java.util.Date();
DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.CHINESE);
%>

顧客資訊查詢

String sCustId = request.getParameter("cust_id");

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url="jdbc:oracle:thin:@TCSCH074.tcsgdccn.com:1521:orcl";
//orcl???????SID
String user="store";
String password="store";
Connection conn= DriverManager.getConnection(url,user,password);
ResultSet rs = null;
String sQuery = null;
Statement stmt = null;

%>

顧客資訊

try {
String sCustName = "";
String sCustSex = "";
String sCustYear = "";
String sCustMonth = "";
String sCustAddress = "";
String sCustZipCode = "";
String sCustPhOffice = "";
String sCustPhHome = "";
String sCustMobile = "";
String sCustEmail = "";
String sCustDisc = "";
String sCustTtSum = "";
String sCustCrtDt = "";
String sCustUptDt = "";

sQuery = "select cst_id, cst_name,decode(cst_sex,'M','男','F','女',cst_sex) cst_sex, cst_year, cst_month, cst_address, cst_zip_code, ";
sQuery = sQuery + "cst_ph_office, cst_ph_home, cst_mobile, cst_email, cst_discount, cst_trd_sum, ";
sQuery = sQuery + "to_char(cst_crt_date,'yyyy-mm-dd') cst_crt_date, ";
sQuery = sQuery + "to_char(cst_upt_date,'yyyy-mm-dd') cst_upt_date from customer where cst_id = '"+sCustId+"'";

stmt = conn.createStatement();
rs = stmt.executeQuery(sQuery);

while(rs.next())
{
sCustId = rs.getString("cst_id");
sCustName = rs.getString("cst_name");
sCustSex = rs.getString("cst_sex");
sCustYear = rs.getString("cst_year");
if (sCustYear==null) sCustYear="";
sCustMonth = rs.getString("cst_month");
if (sCustMonth==null) sCustMonth="";
sCustAddress = rs.getString("cst_address");
if (sCustAddress==null) sCustAddress=" ";
sCustZipCode = rs.getString("cst_zip_code");
if (sCustZipCode==null) sCustZipCode=" ";
sCustPhOffice = rs.getString("cst_ph_office");
if (sCustPhOffice==null) sCustPhOffice=" ";
sCustPhHome = rs.getString("cst_ph_home");
if (sCustPhHome==null) sCustPhHome=" ";
sCustMobile = rs.getString("cst_mobile");
if (sCustMobile==null) sCustMobile=" ";
sCustEmail = rs.getString("cst_email");
if (sCustEmail==null) sCustEmail=" ";
sCustDisc = rs.getString("cst_discount");
if (sCustDisc==null) sCustDisc=" ";
sCustTtSum = rs.getString("cst_trd_sum");
sCustCrtDt = rs.getString("cst_crt_date");
sCustUptDt = rs.getString("cst_upt_date");

out.println("

");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
}

}

catch (Exception e){
System.out.println("顧客資訊查詢失敗！");
e.printStackTrace();
out.println("

顧客資訊查詢失敗！

");
out.println( e.getMessage() );
}

%>

卡號

姓名

性別

出生年月

折扣

購物總額

email

手機

辦公室電話

家庭電話

通訊地址

郵編

建立日期

最後修改日期

姓名

卡號

"+sCustId+"

"+sCustName+"

"+sCustSex+"

"+sCustYear+"年"+sCustMonth+"月

"+sCustDisc+"%

"+sCustTtSum+"元

"+sCustEmail+"

"+sCustMobile+"

"+sCustPhOffice+"

"+sCustPhHome+"

"+sCustAddress+"

"+sCustZipCode+"

"+sCustCrtDt+"

"+sCustUptDt+"

"+sCustName+"

"+sCustId+"

該顧客交易資訊

try {
String sExchDate = "";
String sExchSeq = "";
String sExchType = "";
String sExchCommName = "";
String sExchQutt = "";
String sExchCommUnit = "";
String sExchMon="";

sQuery = "select to_char(exch_date,'yyyy-mm-dd') exch_date, exch_seq, ";
sQuery = sQuery + "decode(exch_type,'A','總公司的發貨單','B','本店製作的退貨單','C','銷貨發票','D','購買','E','退貨','F','處理',exch_type) exch_type, ";
sQuery = sQuery + "comm_name, exch_nb, comm_unit, exch_mon from exchange e, commodity c ";
sQuery = sQuery + "where e.exch_comm_id=c.comm_id and exch_cst_id = '"+sCustId+"' order by exch_date desc, exch_seq asc";

stmt = conn.createStatement();
rs = stmt.executeQuery(sQuery);

while(rs.next())
{
sExchDate = rs.getString("exch_date");
sExchSeq = rs.getString("exch_seq");
sExchType = rs.getString("exch_type");
sExchCommName = rs.getString("comm_name");
if (sExchCommName==null) sExchCommName="";
sExchQutt = rs.getString("exch_nb");
sExchCommUnit = rs.getString("comm_unit");
sExchMon = rs.getString("exch_mon");

out.println("

");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
out.println("");
}

rs.close();
stmt.close();
conn.close();
}

catch (Exception e){
System.out.println("交易資訊查詢失敗！");
e.printStackTrace();
out.println("

交易資訊查詢失敗！

");
out.println( e.getMessage() );
}

finally{
if(stmt != null) stmt.close();
conn.close();
}

%>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/207/viewspace-800199/，如需轉載，請註明出處，否則將追究法律責任。

上一篇： JSP(7) - 銷售模組

下一篇： JSP(9) - 增加商品模組

請登入後發表評論登入

全部評論

zhyuh

博文量
233
訪問量

2038388

交易日期	當日序號	交易型別	商品	數量	單價	金額
"+sExchDate+"	"+sExchSeq+"	"+sExchType+"	"+sExchCommName+"	"+sExchQutt+"	"+sExchCommUnit+"元	"+sExchMon+"元

JSP(8) - 按卡號查詢顧客資訊

按顧客卡號查詢

顧客資訊

該顧客交易資訊

最新文章

相關文章