【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 畫面：
使用者編號：2013000001
使用開始日：20131101
使用地方：第一區
1. 按“查詢”時，若是有輸入資料查詢時，需要做
(1)資料驗證: 需要檢查”使用者編號”長度為10,並且只能輸入數字，”使用開始日”只能輸入日期，格式為 2014/01/01 。
(2)秀出查詢的條件在下方
開發環境：
MySQL Workbench MySQL Server
開發工具：
使用 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();
}
}
}
}
程式畫面：
【查無資料】
【查詢到資料】
原始碼下載：http://goo.gl/RpkOkk
沒有留言 :
張貼留言