2015年4月13日 星期一

(溫故知新) jQuery 練習 easy

【jQuery 練習 easy】

以下是運動中心的使用記錄

USE_RECORD_BLOB (使用記錄)

USER_ID

(使用者編號)

USE_DATE_START

(使用開始日)

USE_TIME_START

(使用開始時間)

USE_DATE_END

(使用結束日)

USE_TIME_END

(使用結束時間)

MEMO

(備註)

2013000001

20131101

080500

20131101

121000

此使用者是白金客戶

2013000001

20131102

083300

20131102

121900

1

2013000001

20131102

130000

20131102

162300

1

2013000001

20131101

130550

20131101

235000

3

2013000005

20131102

130300

20131102

140000

此使用者是黑金客戶

2013000002

20131102

103000

20131102

162400

3

2013000002

20131102

182400

20131102

203000

2

2013000002

20131102

230130

20131103

033030

1

2013000003

20131101

090100

20131101

100600

2

2013000003

20131102

152000

20131102

202400

1

2013000003

20131102

100000

20131103

010000

2

JSP 畫面:

image

使用者編號:2013000001

使用開始日:20131101

使用地方:第一區


1. 按“查詢”時,若是有輸入資料查詢時,需要做

(1)資料驗證: 需要檢查”使用者編號”長度為10,並且只能輸入數字,”使用開始日”只能輸入日期,格式為 2014/01/01 。

(2)秀出查詢的條件在下方

開發環境:

MySQL Workbench MySQL Server

Tomcat 7

開發工具:

IntelliJ IDEA 14

使用 Maven 來管理 library

使用 quickstart 的方式建立 webapp project

使用 MySQL 建立相關資料

CREATE TABLE IF NOT EXISTS `USE_RECORD_BLOB` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `USER_ID` varchar(11) NOT NULL,
   `USE_DATE_START` varchar(10) NOT NULL,
   `USE_TIME_START` varchar(10) NOT NULL,
   `USE_DATE_END` varchar(10) NOT NULL,
   `USE_TIME_END` varchar(10) NOT NULL,
   `MEMO` blob,
   PRIMARY KEY (`ID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;



INSERT INTO `USE_RECORD_BLOB` (`USER_ID`, `USE_DATE_START`, `USE_TIME_START`,`USE_DATE_END`,`USE_TIME_END`,`MEMO`)
VALUES

('2013000001', '20131101', '080500','20131101','121000','此使用者是白金客戶'),

('2013000001', '20131102', '083300','20131102','121900','1'),

('2013000001', '20131102', '130000','20131102','162300','1'),

('2013000001', '20131101', '130550','20131101','235000','3'),

('2013000005', '20131102', '130300','20131102','140000','此使用者是黑金客戶'),

('2013000002', '20131102', '103000','20131102','162400','3'),

('2013000002', '20131102', '182400','20131102','203000','2'),

('2013000002', '20131102', '230130','20131103','033030','1'),

('2013000003', '20131101', '090100','20131101','100600','2'),

('2013000003', '20131102', '152000','20131102','202400','1'),

('2013000003', '20131102', '100000','20131103','010000','2');



select * from use_record_blob;



SELECT * FROM USE_RECORD_BLOB where 1 = 1 and USER_ID = '2013000001' and MEMO in ('1','3');

Maven 的 pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>JQUERY_easy_1</groupId>
   <artifactId>exam</artifactId>
   <packaging>war</packaging>
   <version>1.0-SNAPSHOT</version>
   <name>exam Maven Webapp</name>
   <url>http://maven.apache.org</url>


     <!--
設定各dependency 使用的版本 -->
    
<properties>
         <commons-logging.version>1.1.3</commons-logging.version>
         <junit.version>4.11</junit.version>
         <jdk.version>1.6</jdk.version>
         <struts2-core.version>2.3.16.3</struts2-core.version>
         <commons-lang3.version>3.3.2</commons-lang3.version>
         <commons-fileupload.version>1.3.1</commons-fileupload.version>
         <freemarker.version>2.3.9</freemarker.version>
         <javassist.version>3.18.1-GA</javassist.version>
         <ognl.version>3.0.8</ognl.version>
         <xwork-core.version>2.3.16.3</xwork-core.version>
         <mysql-connector-java.version>5.1.6</mysql-connector-java.version>
     </properties>


   <dependencies>
       <dependency>
           <groupId>junit</groupId>
           <artifactId>junit</artifactId>
           <version>${junit.version}</version>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>org.apache.struts</groupId>
           <artifactId>struts2-core</artifactId>
           <version>${struts2-core.version}</version>
       </dependency>
       <dependency>
           <groupId>commons-logging</groupId>
           <artifactId>commons-logging</artifactId>
           <version>${commons-logging.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.commons</groupId>
           <artifactId>commons-lang3</artifactId>
           <version>${commons-lang3.version}</version>
       </dependency>
       <dependency>
           <groupId>commons-fileupload</groupId>
           <artifactId>commons-fileupload</artifactId>
           <version>${commons-fileupload.version}</version>
       </dependency>
       <dependency>
           <groupId>freemarker</groupId>
           <artifactId>freemarker</artifactId>
           <version>${freemarker.version}</version>
       </dependency>
       <dependency>
           <groupId>org.javassist</groupId>
           <artifactId>javassist</artifactId>
           <version>${javassist.version}</version>
       </dependency>
       <dependency>
           <groupId>ognl</groupId>
           <artifactId>ognl</artifactId>
           <version>${ognl.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.struts.xwork</groupId>
           <artifactId>xwork-core</artifactId>
           <version>${xwork-core.version}</version>
       </dependency>
       <!-- 
引入 JDBC connector-->
      
<dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>${mysql-connector-java.version}</version>
       </dependency>
   </dependencies>
   <build>
     <finalName>exam</finalName>
       <plugins>
           <!--
強制專案編輯使用的compiler版本 -->
          
<plugin>
               <groupId>org.apache.maven.plugins</groupId>
               <artifactId>maven-compiler-plugin</artifactId>
               <version>3.0</version>
               <configuration>
                   <source>${jdk.version}</source>
                   <target>${jdk.version}</target>
               </configuration>
           </plugin>
       </plugins>
   </build>

</project>

Web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                       http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
          version="3.0"
          metadata-complete="true">
   <display-name>JQUERY_easy_1</display-name>
     <filter>
         <filter-name>struts2</filter-name>
         <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
     </filter>
     <filter-mapping>
         <filter-name>struts2</filter-name>
         <url-pattern>/*</url-pattern>
     </filter-mapping>
     <welcome-file-list>
         <welcome-file>index.jsp</welcome-file>
     </welcome-file-list>

</web-app>

整個程式架構使用struts2來完成,依package 分為 Action / Jdbc / model 三個部份

主要頁面Index.jsp

<%@ page pageEncoding="UTF-8" contentType="text/html; charset=utf-8" %>

<html>

<head>
     <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
     <style>
         .table1
         {
             border: 1px solid black;
         }
         .table1 td
         {
             border: 1px solid black;
         }
     </style>
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
     <script>
         $(function(){
             $('#submitData').click(function(){
                 var inputNumber = $('#number').val();
                 //alert('inputNumber: ' + inputNumber);
                 if(inputNumber != ''){
                     if(!isNaN(inputNumber)){
                         var length = $('#number').val().length;
                         //alert('length: ' + length);
                         if(length != 10){
                             alert('輸入數字必須為10位數 !!!');
                             return false;
                         }
                     }else{
                         alert('請輸入數字 !!!');
                         return false;
                     }
                 }
                 var inputstartDate = $('#startDate').val();
                 var inputendDate = $('#endDate').val();
                 if(inputstartDate !=''){
                     //alert('進入startDate');
                     if(!checkYMD(inputstartDate)){
                        alert('請輸入正確格式 YYYY/MM/DD !!!')
                         return false;
                     }
                 }
                 if(inputendDate !=''){
                     //alert('進入Enddate');
                     if(!checkYMD(inputendDate)){
                         alert('請輸入正確格式 YYYY/MM/DD !!!')
                         return false;
                     }
                 }
             });
         });
         function checkYMD(dateVal){
             var dateVal = dateVal;
             if (dateVal == null)
                 return false;
            // Ref: http://blog.roodo.com/rocksaying/archives/2670695.html
            var validatePattern = /^(\d{4})(\/|-)(\d{1,2})(\/|-)(\d{1,2})$/;
             dateValues = dateVal.match(validatePattern);
             if (dateValues == null)
                 return false;
             var dtYear = dateValues[1];
             dtMonth = dateValues[3];
             dtDay=  dateValues[5];
             if (dtMonth < 1 || dtMonth > 12)
                 return false;
             else if (dtDay < 1 || dtDay> 31)
                 return false;
             else if ((dtMonth==4 || dtMonth==6 || dtMonth==9 || dtMonth==11) && dtDay ==31)
                 return false;
             else if (dtMonth == 2){
                 var isleap = (dtYear % 4 == 0 && (dtYear % 100 != 0 || dtYear % 400 == 0));
                 if (dtDay> 29 || (dtDay ==29 && !isleap))
                     return false;
             }
             return true;
         }
     </script>

</head>

<body>

<h2>JQUERY_easy_1</h2>

<form action="searchAction" method="get">
     <table id="table1" class="table1">
         <tr>
             <td>使用者編號:</td>
             <td>
                 <input id="number" name="search.number" type="text">
             </td>
         </tr>
         <tr>
             <td>使用開始日:</td>
             <td>
                 <input id="startDate" name="search.startDate" type="text"> ~ <input id="endDate" name="search.endDate" type="text">
             </td>
         </tr>
         <tr>
             <td>使用地方:</td>
             <td>
                 <input id="area1" name="search.area1" type="checkbox">第一區
                 <input id="area2" name="search.area2" type="checkbox">第二區
                 <input id="area3" name="search.area3" type="checkbox">第三區
             </td>
         </tr>
         <tr align="center">
             <td colspan="2">
                 <input id="submitData" type="submit" value="查詢">
             </td>
         </tr>
     </table>

</form>



<hr>

${reMsg}




</body>

</html>

error.jsp 負責錯誤訊息的承接

<%--
   Created by William IntelliJ IDEA.
   Date: 2015/4/7
   Time: 下午 02:45
   To change this template use File | Settings | File Templates.

--%>

<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>

<html>

<head>
     <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
     <title>錯誤頁面</title>

</head>

<body>

<h1>發生錯誤</h1><br>

${reMsg}


</body>

</html>

Struts 配置檔部份(struts.xml)

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE struts PUBLIC
         "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
         "http://struts.apache.org/dtds/struts-2.3.dtd">



<struts>
     <!--
允許使用OGNL -->
    
<constant name="struts.ognl.allowStaticMethodAccess" value="true" />
     <package name="login" extends="struts-default">
         <action name="searchAction" class="Action.SearchAction">
             <result name="SearchSuccess">/index.jsp</result>
             <result name="SearchFailure">/error.jsp</result>
         </action>
     </package>

</struts>

當中設置的 SearchAction 這個 action,其中負責所有的動作

SearchAciton.java

package Action;

import Jdbc.ConnectFactory;

import com.opensymphony.xwork2.ActionSupport;

import model.Search;

import java.beans.Statement;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;



/**
  * Created by William on 2015/4/7.
  */

public class SearchAction extends ActionSupport {
     private Search search;
     private String reMsg;
     Connection conn = null;
     PreparedStatement psmt = null;
     ResultSet rs = null;


     public Search getSearch() {
         return search;
     }


     public void setSearch(Search search) {
         this.search = search;
     }


     public String getReMsg() {
         return reMsg;
     }


     public void setReMsg(String reMsg) {
         this.reMsg = reMsg;
     }


     @Override
     public String execute() throws Exception {
         conn = ConnectFactory.getInstance().getConnection();
         StringBuffer sqlsb = new StringBuffer();
         sqlsb.append("SELECT * FROM USE_RECORD_BLOB ");
         sqlsb.append("where 1 = 1 ");
         if(!"".equals(search.getNumber().trim())){
             sqlsb.append("and USER_ID = '" + search.getNumber() + "' ");
         }
         if(!"".equals(search.getStartDate().trim()) &&
                 !"".equals(search.getEndDate().trim())){
             sqlsb.append("and USE_DATE_START between '" + search.getStartDate() + "' and '" + search.getEndDate() + "' " );
         }else if("".equals(search.getStartDate().trim()) &&
                 !"".equals(search.getEndDate().trim())){
             sqlsb.append("and USE_DATE_START <= '" + search.getEndDate() + "' ");
         }else if(!"".equals(search.getStartDate().trim()) &&
                 "".equals(search.getEndDate().trim())){
             sqlsb.append("and USE_DATE_START >= '" + search.getStartDate() + "' ");
         }


         if(!"".equals(search.getArea1().trim()) && !"".equals(search.getArea2().trim())
                         && !"".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO in ('1','2','3') ");
         }else if("".equals(search.getArea1().trim()) && !"".equals(search.getArea2().trim())
                 && !"".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO in ('2','3') ");
         }else if(!"".equals(search.getArea1().trim()) && "".equals(search.getArea2().trim())
                 && !"".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO in ('1','3') ");
         }else if(!"".equals(search.getArea1().trim()) && !"".equals(search.getArea2().trim())
                 && "".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO in ('1','2') ");
         }else if(!"".equals(search.getArea1().trim()) && "".equals(search.getArea2().trim())
                 && "".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO ='1' ");
         }else if("".equals(search.getArea1().trim()) && !"".equals(search.getArea2().trim())
                 && "".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO ='2' ");
         }else if("".equals(search.getArea1().trim()) && "".equals(search.getArea2().trim())
                 && !"".equals(search.getArea3().trim())){
             sqlsb.append("and MEMO ='3' ");
         }


         //psmt = conn.prepareStatement("SELECT * FROM USE_RECORD_BLOB ");
         if(sqlsb.length() > 0){
             System.out.println(sqlsb.toString());
             System.out.println(search.toString());
         }
         psmt = conn.prepareStatement(sqlsb.toString());
         rs = psmt.executeQuery();
         StringBuffer sb = new StringBuffer();
         Integer counter = 0;
         while(rs.next()){
             sb.append("使用者編號: " + rs.getString("USER_ID") + "<BR>");
             sb.append("使用開始日: " + rs.getString("USE_DATE_START") + "<BR>");
             try{
                 Blob blob = rs.getBlob("MEMO");
                 byte[] blobData = blob.getBytes(1,(int)blob.length());
                 String blobWord = new String(blobData,"UTF-8");
                 if(!"".equals(blobWord) && blobWord.length() == 1){
                     sb.append("使用地方: 第 " + blobWord + " 區<BR>");
                 }else{
                     sb.append("備註:" + blobWord +" <BR>");
                 }
             }catch (Exception ex){
                 ex.printStackTrace();
                 this.reMsg = "發生錯誤:" + ex.toString();
                 return "SearchFailure";
             }
             sb.append("<BR>");
         }


         ConnectFactory.getInstance().release(rs,psmt);


         if(sb.length() > 0){
             //返回訊息
             this.reMsg = sb.toString();
             System.out.println(getReMsg());
             return "SearchSuccess";
         }else{
             this.reMsg = "查無資料";
             return "SearchSuccess";
         }


     }

}

Model部分

Search.java

package model;



/**
  * Created by Hsu on 2015/4/7.
  */

public class Search {
     String number ="";
     String startDate ="";
     String endDate ="";
     String area1 ="";
     String area2 ="";
     String area3 ="";


     public String getNumber() {
         return number;
     }


     public void setNumber(String number) {
         this.number = number;
     }


     public String getStartDate() {
         return startDate;
     }


     public void setStartDate(String startDate) {
         this.startDate = startDate.replaceAll("/","");
         //this.startDate = startDate;
     }


     public String getEndDate() {
         return endDate;
     }


     public void setEndDate(String endDate) {
         this.endDate = endDate.replaceAll("/","");
         //this.endDate = endDate;
     }


     public String getArea1() {
         return area1;
     }


     public void setArea1(String area1) {
         this.area1 = area1;
     }


     public String getArea2() {
         return area2;
     }


     public void setArea2(String area2) {
         this.area2 = area2;
     }


     public String getArea3() {
         return area3;
     }


     public void setArea3(String area3) {
         this.area3 = area3;
     }


     @Override
     public String toString() {
         StringBuffer sb = new StringBuffer();
         sb.append("number = " + number + "\n");
         sb.append("startDate = " + startDate + "\n");
         sb.append("endDate = " + endDate + "\n");
         sb.append("area1 = " + area1 + "\n");
         sb.append("area2 = " + area2 + "\n");
         sb.append("area3 = " + area3 );
         return sb.toString();
     }

}

Jdbc部份

ConnectFactory.java

package Jdbc;

import java.sql.*;

/**
  * Created by William on 2015/4/7.
  */

public class ConnectFactory {
     private static ConnectFactory instance = new ConnectFactory();
     //private static Connection conn = null;


     public static ConnectFactory getInstance(){
         return instance;
     }


     static {
         try{
             Class.forName("com.mysql.jdbc.Driver");
         }catch (ClassNotFoundException cex){
             throw new ExceptionInInitializerError(cex);
         }
     }


     public Connection getConnection() throws SQLException{
         return DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "test", "test");
     }


     public void release(ResultSet rs, PreparedStatement psmt){
         if(null != rs){
             try{
                 rs.close();
             }catch (SQLException sex){
                 sex.printStackTrace();
             }
         }


         if(null != psmt){
             try{
                 psmt.close();
             }catch (SQLException sex){
                 sex.printStackTrace();
             }
         }
     }

}

程式畫面:

【查無資料】

image

【查詢到資料】

image

原始碼下載:http://goo.gl/RpkOkk

沒有留言 :

張貼留言