类的封装
package Test02;
public class Storage {
private int id; // 商品编号
private String name; // 商品名
private double price; // 价格
private String type; // 商品类型
private int number; // 商品数量
private String makeTime; // 生产日期
private int useDay; // 有效期
public Storage(){}
public Storage(int id, String name, double price, String type, int number, String makeTime, int useDay) {
super();
this.id = id;
this.name = name;
this.price = price;
this.type = type;
this.number = number;
this.makeTime = makeTime;
this.useDay = useDay;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getMakeTime() {
return makeTime;
}
public void setMakeTime(String makeTime) {
this.makeTime = makeTime;
}
public int getUseDay() {
return useDay;
}
public void setUseDay(int useDay) {
this.useDay = useDay;
}
}
数据库与java建立关系实现查询某一物品
package Test02;
import java.sql.*;
import java.util.*;
public class StorageDao2 {
public static final String WIRING ="电器";
public static final String FOOD ="食品";
public static final String CLOTHING ="服装";
public static final String COMMODITY ="日用品";
// public final static String DRIVER="com.mysql.jdbc.Driver";
// public final static String URL = "jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf-8&useSSL=false";
// public final static String DBNAME="root";
// public final static String DBPASS="root";
private Connection conn=null; //数据库连接
private PreparedStatement pstmt=null; //创建preparedStatement对象
private ResultSet rs =null;//创建结果集对象
//得到数据库链接
public Connection getConnection() {
// 加载了MySQL数据库的驱动程序,由驱动程序负责向DriverManager登记.在与数据库连接时,
try {
Class.forName("com.mysql.jdbc.Driver");
// 链接
String url = "jdbc:mysql://localhost:3306/store";
conn = DriverManager.getConnection(url, "root", "root");
} catch (Exception e) {
e.printStackTrace();
} finally {
return conn;
}
}
public List listOrderByItem (String Item){
List list = new ArrayList();
String sql = "select * from storage order by "+Item;
try {
conn = this.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
Storage stor = new Storage();
stor.setName(rs.getString("name"));
stor.setType(rs.getString("type"));
stor.setNumber(rs.getInt("number"));
stor.setPrice(rs.getDouble("price"));
stor.setUseDay(rs.getInt("useDay"));
stor.setMakeTime(rs.getString("makeTime").substring(0, 10));
list.add(stor);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
this.closeAll(conn, pstmt, rs);
}
return list;
}
//根据某一个类型,
public List listByType (String typeStr){
List li = new ArrayList();
String sql = "select * from storage WHERE type=?";
try {
conn = this.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, typeStr);
rs = pstmt.executeQuery();
while (rs.next()) {
Storage stor = new Storage();
stor.setName(rs.getString("name"));
stor.setType(rs.getString("type"));
stor.setPrice(rs.getFloat("price"));
stor.setNumber(rs.getInt("number"));
stor.setMakeTime(rs.getString("makeTime").substring(0,10));
stor.setUseDay(rs.getInt("useDay"));
li.add(stor);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
this.closeAll(conn, pstmt, rs);
}
return li;
}
public void closeAll(Connection conn,PreparedStatement pstmt,ResultSet rs){
if(conn !=null){
try {conn.close();} catch (SQLException e) {e.printStackTrace();}
}
if(pstmt !=null){
try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}
}
if(rs !=null){
try {rs.close();} catch (SQLException e) {e.printStackTrace();}
}
}
public static void main(String[] args) {
StorageDao2 sto = new StorageDao2();
List li = sto.listByType("食品");
// for (int i = 0; i < li.size(); i++) {
// Storage stor = (Storage)li.get(i);
// System.out.println(stor.);
// }
// for (int i = 0; i < li.size(); i++) {
// System.out.println((Storage)li.get(i));
// }
System.out.println(li.size());
}
}
login.jsp
<%@ page language="java" import="java.util.*" contentType="text/html; charset=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>
<style>
.p{ border:#93C solid 1px; width:90px; margin-right:300px; margin-top:10px;}
.p1{
border:#93C solid 1px;width:90px; margin-left:300px; margin-top:-20px;}
</style>
<body>
<div align="center" style="width:500px; height:500px; border:1px solid #09F; margin:0 auto">
<h1>查看库存</h1>
<h3>排序</h3>
<div style="width:500px; height:90px; border:2px solid #0FF;">
<div class="p"><a href="Storage/orderBy2.jsp?name=name">名称</a></div>
<div class="p1"><a href="Storage/orderBy2.jsp?name=price">价格</a></div>
<div class="p"><a href="Storage/orderBy2.jsp?name=number">数量</a></div>
<div class="p1"><a href="Storage/orderBy2.jsp?name=makeTime">生产日期</a></div>
</div>
<h3 style="margin-top:30px;">分类</h3>
<div style="width:500px; height:90px; border:2px solid #0FF;">
<div class="p"><a href="Storage/leibie.jsp?name=1">电器</a></div>
<div class="p1"><a href="Storage/leibie.jsp?name=2">食品</a></div>
<div class="p"><a href="Storage/leibie.jsp?name=3">服装</a></div>
<div class="p1"><a href="Storage/leibie.jsp?name=4">日用品</a></div>
</div>
</div>
</body>
</html>
根据类别查询
<%@ page language="java" import="java.util.*,Test02.*" contentType="text/html; charset=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 '??.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>
<style>
.p{
border-bottom-width:3px;
border-bottom-style:solid;
border-bottom-color:#000;
}
</style>
<body >
<%
request.setCharacterEncoding("utf-8");
String word=request.getParameter("name");
StorageDao2 sd=new StorageDao2();
if(word.equals("1"))word="电器";
if(word.equals("2"))word="食品";
if(word.equals("3"))word="服装";
if(word.equals("4"))word="日用品";
List<Storage> sts=sd.listByType(word);
%>
<h2 style="text-align: center;">查询结果(按商品<%=word %>分类)</h2>
<table style="border: 1px solid; margin: 0 auto; width: 900px;">
<tr>
<td class="p">商品名称</td>
<td class="p">单价(元)</td>
<td class="p"> 数量</td>
<td class="p">类型</td>
<td class="p">保质期(天)</td>
<td class="p">生产日期</td>
</tr>
<tr></tr>
<tr></tr>
<% for(Storage st:sts){%>
<tr>
<td><%= st.getName()%></td>
<td><%= st.getPrice()%></td>
<td><%= st.getNumber()%></td>
<td><%= st.getType()%></td>
<td><%= st.getUseDay()%></td>
<td><%= st.getMakeTime()%></td>
</tr>
<%} %>
</table>
<br>
<a href="Storage/index.jsp" style="text-align: center;"><p>重新选择</p></a>
</body>
</html>
查询结果
<%@ page language="java" import="java.util.*,Test02.*" 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 'orderBy.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">
-->
<link rel="stylesheet" type="text/css" href="./style/style.css" />
</head>
<style>
.tap{
border-bottom-width:3px;
border-bottom-style:solid;
border-bottom-color:#000;
}
</style>
<body>
<div align="center" style="width:800px;margin:auto">
<%
//获取传递参数
request.setCharacterEncoding("utf-8");
String byName=request.getParameter("name");
//out.print(byName);
String name="";
if(byName.equals("name"))
{
name="名称";
}
if(byName.equals("price"))
{
name="价格";
}
if(byName.equals("number"))
{
name="数量";
}
if(byName.equals("makeTime"))
{
name="生产日期";
}
%>
<div>查询结果(根据<%=name %>查询)</div>
<div>
<table width="800px">
<tr>
<td >商品名</td>
<td >价格</td>
<td >数量</td>
<td >类型</td>
<td >保质期</td>
<td >生产日期</td>
</tr>
<%
//数据库操作类
StorageDao2 stoDao=new StorageDao2();
List li=stoDao.listOrderByItem(byName);
for(int i=0;i<li.size();i++){
Storage sto=(Storage)li.get(i);
%>
<tr>
<td class="tab"><%=sto.getName() %></td>
<td class="tab"><%=sto.getPrice() %></td>
<td class="tab"><%=sto.getNumber() %></td>
<td class="tab"><%=sto.getType()%></td>
<td class="tab"><%=sto.getUseDay() %></td>
<td class="tab"><%=sto.getMakeTime()%></td>
</tr>
<%
}
%>
</table>
</div>
<div><a href="Storage/index.jsp">重新选择</a></div>
</div>
</body>
</html>