JSP(5) - 修改客戶資訊模組

分三頁。先按顧客編號檢索出顧客資訊，修改顧客資訊後存入資料庫。

要點：
1) 從資料庫檢索出資料，用
ResultSet rs = null;
......
stmt = conn.createStatement();
rs = stmt.executeQuery(sQuery);
while(rs.next()) { ...... }

[@more@]

upt_cust1.jsp:

<!----&gt

請輸入要修改的客戶卡號

          type="button" value="取消" name="cancel">

upt_cust2.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");
String sCustName = null;
String sCustSex = null;
String sCustYear = null;
String sCustMonth = null;
String sCustAddr = null;
String sCustZip = null;
String sCustPhOff = null;
String sCustPhHom = null;
String sCustMobile = null;
String sCustEmail = null;
String sCustDis = null;
String sCustTtSum = null;
String sCustCrtDt = null;
String sCustUptDt = null;

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 {
sQuery = "select cst_id, cst_name, cst_sex, cst_year, to_number(cst_month) cst_month, cst_address, cst_zip_code, ";
sQuery = sQuery + "cst_ph_office, cst_ph_home, cst_mobile, cst_email, cst_discount, ";
sQuery = sQuery + "cst_trd_sum, to_char(cst_crt_date,'yyyy-mm-dd') cst_crt_dt 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="";
sCustAddr = rs.getString("cst_address");
if (sCustAddr==null) sCustAddr="";
sCustZip = rs.getString("cst_zip_code");
if (sCustZip==null) sCustZip="";
sCustPhOff = rs.getString("cst_ph_office");
if (sCustPhOff==null) sCustPhOff="";
sCustPhHom = rs.getString("cst_ph_home");
if (sCustPhHom==null) sCustPhHom="";
sCustMobile = rs.getString("cst_mobile");
if (sCustMobile==null) sCustMobile="";
sCustEmail = rs.getString("cst_email");
if (sCustEmail==null) sCustEmail="";
sCustDis = rs.getString("cst_discount");
sCustTtSum = rs.getString("cst_trd_sum");
sCustCrtDt = rs.getString("cst_crt_dt");
}
//out.println(sCustSex);
if (sCustSex.equals("M")) {
sCustSex="男";
}
else if (sCustSex.equals("F")){
sCustSex = "女";
}
else {
sCustSex ="";
}
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();
}

%>

修改顧客資訊

客戶卡號:     

客戶姓名:     

性別:         

出生年月:     年月

通訊地址:     

郵政編碼:     

辦公室電話:   

家庭電話:     

手機:         

Email:        

折扣:         %

累計購物金額：

增加日期:     

最後修改日起：

           

upt_cust3.jsp:

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

String sCustID = request.getParameter("cust_id");
String sCustName = request.getParameter("cust_name");
String sCustSex = request.getParameter("cust_sex");
String sCustYear = request.getParameter("cust_year");
String sCustMonth = request.getParameter("cust_month");
String sCustAddr = request.getParameter("cust_addr");
String sCustZip = request.getParameter("cust_zip");
String sCustPhOff = request.getParameter("cust_ph_office");
String sCustPhHom = request.getParameter("cust_ph_home");
String sCustMobile = request.getParameter("cust_mobile");
String sCustEmail = request.getParameter("cust_email");
Float fCustDis = Float.valueOf(request.getParameter("cust_discount"));
String sCustTtSum = request.getParameter("cust_trd_sum");
String sCustCrtDt = request.getParameter("cust_crt_date");
String sCustUptDt = request.getParameter("cust_upt_date");

String sCustID2 = new String(sCustID.getBytes("iso-8859-1"),"GBK");
String sCustName2 = new String(sCustName.getBytes("iso-8859-1"),"GBK");
String sCustAddr2 = new String(sCustAddr.getBytes("iso-8859-1"),"GBK");
String sCustPhOff2 = new String(sCustPhOff.getBytes("iso-8859-1"),"GBK");
String sCustPhHom2 = new String(sCustPhHom.getBytes("iso-8859-1"),"GBK");

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);
String sUpdate = null;
Statement stmt = null;

try{

sUpdate = "update customer set cst_Year='"+sCustYear+
"',cst_month='"+sCustMonth+
"',cst_address='"+sCustAddr2+
"',cst_zip_code='"+sCustZip+
"',cst_ph_office='"+sCustPhOff2+
"',cst_ph_home='"+sCustPhHom2+
"',cst_mobile='"+sCustMobile+
"',cst_email='"+sCustEmail+
"',cst_discount="+fCustDis+
",cst_crt_date=to_date('"+sCustCrtDt+
"','yyyy-mm-dd'),cst_upt_date=to_date('"+sCustUptDt+
"','yyyy-mm-dd') where cst_id='"+sCustID+"'";

//out.println(sUpdate+"
");
stmt = conn.createStatement();
stmt.execute(sUpdate);
conn.commit();
out.println("修改顧客"+sCustName2+"資訊完成");
}
catch(Exception e){
System.out.println("修改顧客資訊失敗！");
e.printStackTrace();
out.println("

修改顧客資訊失敗！

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

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

%>