vo包的Sales類：
package com.vo;
public class Sales {
public String salestime;
public float salesnum;
public String getSalestime() {
return salestime;
}
public void setSalestime(String salestime) {
this.salestime = salestime;
}
public float getSalesnum() {
return salesnum;
}
public void setSalesnum(float salesnum) {
this.salesnum = salesnum;
}
}
dao包中的DBManager類：
package com.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DbManager {
private static String URL = "jdbc:mysql://localhost:3306/sales";
private static String USER = "root";
private static String PWD = "root";
public static Connection getConn(){
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL,USER,PWD);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Connection conn,Statement ste,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ste !=null){
try {
ste.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
dao包中的SalesDao類：
package com.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.vo.Sales;
public class SalesDao {
public List<Sales> find(String month) {
Connection con = null;
Statement state = null;
ResultSet resultSet = null;
List<Sales> list = null;
String sql = "select * from sales where salestime like '" + month + "-%'" ;
con = DbManager.getConn();
try {
state = con.createStatement();
resultSet = state.executeQuery(sql);
while(resultSet.next()){
if(null == list){
list = new ArrayList<Sales>();
}
Sales sales = new Sales();
sales.setSalestime(resultSet.getString("salestime"));
sales.setSalesnum(resultSet.getFloat("salesnum"));
list.add(sales);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbManager.closeAll(con, state, resultSet);
}
return list;
}
}
index.jsp頁面：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form action="show.jsp" method="post">
請選擇查詢的月份:
<select name="month">
<option value="1">1月</option>
<option value="2">2月</option>
<option value="3">3月</option>
<option value="4">4月</option>
<option value="5">5月</option>
<option value="6">6月</option>
<option value="7">7月</option>
<option value="8">8月</option>
<option value="9">9月</option>
<option value="10">10月</option>
<option value="11">11月</option>
<option value="12">12月</option>
</select>
<input type="submit" value="查詢"/>
</form>
</body>
</html>
show.jsp頁面：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page language="java" import="com.dao.*" %>
<%@ page language="java" import="com.vo.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'show.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<table>
<tr>
<td>銷售日期</td>
<td>銷售額</td>
</tr>
<%
String month = request.getParameter("month");
SalesDao dao = new SalesDao();
List<Sales> list = dao.find(month);
if(list != null){
for(Sales sales : list){
%>
<tr>
<td><%=sales.getSalestime() %></td>
<td><%=sales.getSalesnum() %></td>
</tr>
<%
}
}else{
%>
<tr>
<td colspan="2">暫時沒有數據</td>
</tr>
<%
}
%>
</table>
</body>
</html>
