项目简介
项目要实现一个管理设备信息的网站,网站部署在Tomcat中,数据库采用mysql,用eclipse进行开发。
环境搭建
- 安装jee版本的eclipse:http://www.eclipse.org/
- 下载好tomcat作为服务器:http://tomcat.apache.org/
- 下载并且安装好mysql :https://dev.mysql.com/downloads/mysql/
- 下载好jdbc:https://dev.mysql.com/downloads/connector/j/
数据库的安装过程:参见 x_smile 的博客:https://www.cnblogs.com/xsmile/p/7753984.html
jdbc推荐使用旧版本的。
代码Tree
src
bean
Device.java
User.java
dao
DeviceInfoDao.java
UserInfoDao.java
servlet
CheckLogin.java
DownLoadServlet.java
GetTimeServlet.java
SetPriceServlet.java
util
DBUtil.java
Referenced Libraies
mysql-connector-java-5.1.46.jar
servlet-api.jar(tomcat/lib)
WebContent
WEB-INF
files
AutoRunner.jar
lib
mysql-connector-java-5.1.46.jar
login.jsp
manager.jsp
setPrice.jsp
主要代码
1、Device.java
package bean;
import java.sql.Date;
public class Device {
private Integer id;
private String serialNum;
private Integer price;
private Date time;
public Device() {
}
public Integer getId() {
return id;
}
public void setId (Integer id) {
this.id = id;
}
public String getSerialNum() {
return serialNum;
}
public void setSerialNum (String serialNum) {
this.serialNum = serialNum;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
}
2、User.java
package bean;
public class User {
private Integer id;
private String name;
private String password;
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3、DeviceInfoDao.java
package dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bean.Device;
import util.DBUtil;
public class DeviceInfoDao {
public void addDeviceInfo(Device device) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
String sql = "insert into device(serialnum, price, time) values(?, ?, ?)";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, device.getSerialNum());
pstmt.setInt(2, device.getPrice());
pstmt.setDate(3, device.getTime());
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("Add device success!");
} else {
System.out.println("Add device fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, null);
}
}
public void deleteDeviceInfoBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn = DBUtil.getConnection();
String sql = "delete from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("delete device success!");
} else {
System.out.println("delete device fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
}
public void updateTimeBySerialNum(Date time, String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn = DBUtil.getConnection();
String sql = "update device set time=? where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setDate(1, time);
pstmt.setString(2, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device time success!");
} else {
System.out.println("update device time fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
}
public void updatePriceBySerialNum(int price, String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn = DBUtil.getConnection();
String sql = "update device set price=? where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, price);
pstmt.setString(2, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device price success!");
} else {
System.out.println("update device price fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
}
public List<Device> findDeviceBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
List<Device> deviceList= new ArrayList<Device>();
try {
conn = DBUtil.getConnection();
String sql = "select * from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String uid = rs.getString("serialnum");
int price = rs.getInt("price");
Date time = rs.getDate("time");
Device device = new Device();
device.setId(id);
device.setSerialNum(uid);
device.setPrice(price);
device.setTime(time);
deviceList.add(device);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return deviceList;
}
public int findPriceBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
int price = 0;
try {
conn = DBUtil.getConnection();
String sql = "select price from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
price = rs.getInt("price");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return price;
}
public Date findTimeBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
Date time = null;
try {
conn = DBUtil.getConnection();
String sql = "select time from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
time = rs.getDate("time");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return time;
}
public List<Device> findAllDevice() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
List<Device> deviceList= new ArrayList<Device>();
try {
conn = DBUtil.getConnection();
String sql = "select * from device";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String uid = rs.getString("serialnum");
int price = rs.getInt("price");
Date time = rs.getDate("time");
Device device = new Device();
device.setId(id);
device.setSerialNum(uid);
device.setPrice(price);
device.setTime(time);
deviceList.add(device);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return deviceList;
}
}
4、UserInfoDao.java
package dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bean.User;
import util.DBUtil;
public class UserInfoDao {
public List<User> findUserByNameAndPassword(String name, String password) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
List<User> userList= new ArrayList<User>();
try {
conn = DBUtil.getConnection();
String sql = "select * from manager where name=? and passward=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, password);
rs=pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
User user = new User();
user.setId(id);
user.setName(name);
user.setPassword(password);
userList.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return userList;
}
}
5、CheckLogin.java
package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.User;
import dao.UserInfoDao;
@WebServlet("/CheckLogin")
public class CheckLogin extends HttpServlet {
public CheckLogin() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
UserInfoDao userDao = new UserInfoDao();
List<User> users = userDao.findUserByNameAndPassword(request.getParameter("username"), request.getParameter("password"));
if (users.size() != 1) {
response.sendRedirect("login.jsp");
} else {
response.sendRedirect("manager.jsp");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
6、DownLoadServlet.java
package servlet;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URLEncoder;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class DownLoadServlet
*/
@WebServlet("/DownLoadServlet")
public class DownLoadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DownLoadServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String realUrl = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
String newUrl = realUrl.substring(0, realUrl.lastIndexOf("classes/")) + "files/AutoRunner.jar";
File f = new File(newUrl);
if(f.exists()){
//response.getWriter().append(newUrl);
FileInputStream fis = new FileInputStream(f);
String filename=URLEncoder.encode("AutoRunner.jar","utf-8"); //解决中文文件名下载后乱码的问题
byte[] b = new byte[fis.available()];
fis.read(b);
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition","attachment; filename="+filename+"");
ServletOutputStream out =response.getOutputStream();
out.write(b);
out.flush();
out.close();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
7、GetTimeServlet.java
package servlet;
import java.io.IOException;
import java.sql.Date;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.DeviceInfoDao;
/**
* Servlet implementation class GetTimeServlet
*/
@WebServlet("/GetTimeServlet")
public class GetTimeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public GetTimeServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String serialNum = request.getParameter("serialNum");
DeviceInfoDao deviceInfo = new DeviceInfoDao();
Date date = deviceInfo.findTimeBySerialNum(serialNum);
if (date != null) {
response.getWriter().append(date.toString());
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
8、SetPriceServlet.java
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import dao.DeviceInfoDao;
/**
* Servlet implementation class SetPriceServlet
*/
@WebServlet("/SetPriceServlet")
public class SetPriceServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SetPriceServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String serialNum = request.getParameter("serialNum");
String price = request.getParameter("price");
String message;
if (serialNum == null || price == null) {
return;
}
DeviceInfoDao deviceInfo = new DeviceInfoDao();
boolean ret = deviceInfo.updatePriceBySerialNum(Integer.parseInt(price), serialNum);
HttpSession session=request.getSession();
if (ret) {
message = "success";
} else {
message = "fail";
}
response.sendRedirect(request.getContextPath() + "/result.jsp?message=" + message);
//request.getRequestDispatcher("/result.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
9、login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="CheckLogin" method="post">
<center>
<tr>
<td> 用户名:</td>
<td> <input type="text" name="username"></td>
</tr>
<br>
<tr>
<td> 密码: </td>
<td><input type="text" name="password"></td>
</tr>
<br>
<tr>
<td><input type="submit" value="确定"></td>
</tr>
</center>
</from>
</body>
</html>
10、manager.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>manager page</title>
</head>
<body>
</body>
</html>
11、setPrice.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>set price</title>
</head>
<body>
<form action="SetPriceServlet" method="post">
<center>
<tr>
<td> 串号:</td>
<td> <input type="text" name="serialNum"></td>
</tr>
<br>
<tr>
<td> 价格设置: </td>
<input type="radio" name="price" value="0" checked> >0 <br>
<input type="radio" name="price" value="50"> >=50 <br>
<input type="radio" name="price" value="70"> >=70 <br>
<input type="radio" name="price" value="100"> >=100 <br>
<input type="radio" name="price" value="150"> >=150 <br>
<input type="radio" name="price" value="200"> >=200 <br>
<input type="radio" name="price" value="300"> >=300 <br>
</tr>
<br>
<tr>
<td><input type="submit" value="确定"></td>
</tr>
</center>
</from>
</body>
</html>
12、result.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String message = request.getParameter("message");
System.out.print(message);
if(message!=null && !"".equals(message)){
if (message.equals("success")) {
%>
设置抢单价格成功。
<%
} else {
%>
设置失败,请重新设置。
<%} %>
<%}%>
</body>
</html>
数据库代码
net start mysql
mysql -u root -p
create database grabOrder;
use grabOrder;
create table device
(
id int auto_increment primary key,
serialnum varchar(20),
price int,
time DATE
);
create table manager
(
id int auto_increment primary key,
name varchar(20),
passward varchar(20)
);
insert into manager(name, passward) values('moon', 'asd1234');
insert into device(serialnum, price, time) values('xxxyyyzzz', 50, 20190520);
数据库操作
修改root账户密码:./bin/mysqladmin -u root password '输入新密码'(mysql-5.5.43-linux2.6-x86_64目录下执行命令);
修改Mysql最大连接数:先将my.cnf文件拷贝到/etc目录下,命令:cp support-files/my-medium.cnf /etc/my.cnf(mysql-5.5.43-linux2.6-x86_64目录下执行命令);切换到etc目录下,使用vi修改my.cnf配置文件,命令为:sudo vi my.cnf,vi操作可查看博客:Ubuntu中vi命令的使用;在[mysqld]下增加max_connections=1024,按Esc,输入“:wq”保存并退出;
修改Mysql的大小写区分:在my.cnf文件中的[mysqld]下方增加一行:lower_case_table_name=1(1:不区分大小写;0:区分大小写);
启动Mysql服务:./bin/mysqld_safe --user=root&(mysql-5.5.43-linux2.6-x86_64目录下执行命令);
停止Mysql服务:./bin/mysqladmin -u root -p shutdown(mysql-5.5.43-linux2.6-x86_64目录下执行命令);
登录Mysql:./bin/mysql -u root -p(mysql-5.5.43-linux2.6-x86_64目录下执行命令),输入密码进入Mysql命令终端界面;
允许root用户远程登录:grant all privileges on *.* to root@"%" identified by "root用户密码" with grant option(Mysql命令终端);
允许root用户本地访问:grant all privileges on *.* to root@"localhost" identified by "root用户密码" with grant option(Mysql命令终端);
界面美化和添加手机号绑定功能:
1、jsp页面替换代码:
<meta name="viewport" content="width=device-width,
minimum-scale=1.0, maximum-scale=2.0; charset=UTF-8">
2、login.jsp修改:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width,
minimum-scale=1.0, maximum-scale=2.0; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="CheckLogin" method="post">
<table border="26" align="center">
<tr>
<td align="right"> 用户名:</td>
<td> <input type="text" name="username"></td>
</tr>
<tr>
<td align="right"> 密码: </td>
<td><input type="text" name="password"></td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="登录">
</td>
</tr>
</table>
</from>
</body>
</html>
3、setPrice.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width,
minimum-scale=1.0, maximum-scale=2.0; charset=UTF-8">
<title>set price</title>
</head>
<body>
<form action="SetPriceServlet" method="post">
<table border="26" align="center">
<tr>
<td align="right"> 串号/手机号:</td>
<td> <input type="text" name="serialNum" size="25"></td>
</tr>
<tr>
<td align="right"> 价格设置: </td>
<td>
<table>
<tr>
<td align="left"><input type="radio" name="price" value="0" checked> >0</td>
<td align="left"><input type="radio" name="price" value="50"> >=50</td>
</tr>
<tr>
<td align="left"><input type="radio" name="price" value="70"> >=70</td>
<td align="left"><input type="radio" name="price" value="100"> >=100</td>
</tr>
<tr>
<td align="left"><input type="radio" name="price" value="150"> >=150</td>
<td align="left"><input type="radio" name="price" value="200"> >=200</td>
</tr>
<tr>
<td align="left" colspan="2"><input type="radio" name="price" value="300"> >=300</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="确定">
</td>
</tr>
</table>
</from>
</body>
</html>
4、数据库添加phonenumber:
net start mysql
mysql -u root -p
create database grabOrder;
use grabOrder;
create table device
(
id int auto_increment primary key,
serialnum varchar(30),
price int,
time DATE,
phonenumber varchar(20)
);
create table manager
(
id int auto_increment primary key,
name varchar(20),
passward varchar(20)
);
insert into manager(name, passward) values('moon', 'asd1234');
insert into device(serialnum, price, time, phonenumber) values('xxxyyyzzz', 50, 20190520, "none");
5、Device.java
package bean;
import java.sql.Date;
public class Device {
private Integer id;
private String serialNum;
private Integer price;
private Date time;
private String phoneNum;
public Device() {
}
public Integer getId() {
return id;
}
public void setId (Integer id) {
this.id = id;
}
public String getSerialNum() {
return serialNum;
}
public void setSerialNum (String serialNum) {
this.serialNum = serialNum;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum (String phoneNum) {
this.phoneNum = phoneNum;
}
}
6、DeviceInfoDao.java 修改addDevieInfo、findDeviceBySerialNum、findAllDevice 添加方法:updatePhoneNumBySerialNum、 updatePriceByPhoneNum
package dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bean.Device;
import util.DBUtil;
public class DeviceInfoDao {
public void addDeviceInfo(Device device) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtil.getConnection();
String sql = "insert into device(serialnum, price, time, phonenumber) values(?, ?, ?, ?)";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, device.getSerialNum());
pstmt.setInt(2, device.getPrice());
pstmt.setDate(3, device.getTime());
pstmt.setString(4, device.getPhoneNum());
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("Add device success!");
} else {
System.out.println("Add device fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, null);
}
}
public void deleteDeviceInfoBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn = DBUtil.getConnection();
String sql = "delete from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("delete device success!");
} else {
System.out.println("delete device fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
}
public boolean updatePhoneNumBySerialNum(String phoneNum, String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
boolean ret = false;
try {
conn = DBUtil.getConnection();
String sql = "update device set phonenumber=? where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, phoneNum);
pstmt.setString(2, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device phone number success!");
ret = true;
} else {
System.out.println("update device phone number fail!");
ret = false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return ret;
}
public void updateTimeBySerialNum(Date time, String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn = DBUtil.getConnection();
String sql = "update device set time=? where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setDate(1, time);
pstmt.setString(2, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device time success!");
} else {
System.out.println("update device time fail!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
}
public boolean updatePriceBySerialNum(int price, String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
boolean ret = false;
try {
conn = DBUtil.getConnection();
String sql = "update device set price=? where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, price);
pstmt.setString(2, serialNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device price success!");
ret = true;
} else {
System.out.println("update device price fail!");
ret = false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return ret;
}
public boolean updatePriceByPhoneNum(int price, String phoneNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
boolean ret = false;
try {
conn = DBUtil.getConnection();
String sql = "update device set price=? where phonenumber=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, price);
pstmt.setString(2, phoneNum);
int row=pstmt.executeUpdate();
if (row == 1) {
System.out.println("update device price by phone success!");
ret = true;
} else {
System.out.println("update device price by phone fail!");
ret = false;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return ret;
}
public List<Device> findDeviceBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
List<Device> deviceList= new ArrayList<Device>();
try {
conn = DBUtil.getConnection();
String sql = "select * from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String uid = rs.getString("serialnum");
int price = rs.getInt("price");
Date time = rs.getDate("time");
String phoneNum = rs.getString("phonenumber");
Device device = new Device();
device.setId(id);
device.setSerialNum(uid);
device.setPrice(price);
device.setTime(time);
device.setPhoneNum(phoneNum);
deviceList.add(device);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return deviceList;
}
public int findPriceBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
int price = 0;
try {
conn = DBUtil.getConnection();
String sql = "select price from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
price = rs.getInt("price");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return price;
}
public Date findTimeBySerialNum(String serialNum) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
Date time = null;
try {
conn = DBUtil.getConnection();
String sql = "select time from device where serialnum=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, serialNum);
rs=pstmt.executeQuery();
while(rs.next()) {
time = rs.getDate("time");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return time;
}
public List<Device> findAllDevice() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
List<Device> deviceList= new ArrayList<Device>();
try {
conn = DBUtil.getConnection();
String sql = "select * from device";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String uid = rs.getString("serialnum");
int price = rs.getInt("price");
Date time = rs.getDate("time");
String phoneNum = rs.getString("phonenumber");
Device device = new Device();
device.setId(id);
device.setSerialNum(uid);
device.setPrice(price);
device.setTime(time);
device.setPhoneNum(phoneNum);
deviceList.add(device);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.release(conn, pstmt, rs);
}
return deviceList;
}
}
7、bindPhoneNumber.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width,
minimum-scale=1.0, maximum-scale=2.0; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="BindDeviceServlet" method="post">
<table border="26" align="center">
<tr>
<td align="right"> 串号:</td>
<td> <input type="text" name="serialNum" size="25"></td>
</tr>
<tr>
<td align="right"> 手机号码: </td>
<td><input type="text" name="phoneNumber"></td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="绑定">
</td>
</tr>
</table>
</from>
</body>
</html>
8、BindDeviceServlet
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import dao.DeviceInfoDao;
/**
* Servlet implementation class BindDeviceServlet
*/
@WebServlet("/BindDeviceServlet")
public class BindDeviceServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public BindDeviceServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String serialNum = request.getParameter("serialNum");
String phoneNumber = request.getParameter("phoneNumber");
String message;
if (serialNum == null || phoneNumber == null) {
return;
}
DeviceInfoDao deviceInfo = new DeviceInfoDao();
boolean ret = deviceInfo.updatePhoneNumBySerialNum(phoneNumber, serialNum);
if (ret) {
message = "success";
} else {
message = "fail";
}
response.sendRedirect(request.getContextPath() + "/result.jsp?message=" + message);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
9、 SetPriceServlet
package servlet;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import dao.DeviceInfoDao;
/**
* Servlet implementation class SetPriceServlet
*/
@WebServlet("/SetPriceServlet")
public class SetPriceServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public SetPriceServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String serialNum = request.getParameter("serialNum");
String price = request.getParameter("price");
String message;
boolean ret = false;
if (serialNum == null || price == null || "none".equals(serialNum)) {
return;
}
String regex = "^[0-9]+$";
Pattern pattern = Pattern.compile(regex);
Matcher match = pattern.matcher(serialNum);
DeviceInfoDao deviceInfo = new DeviceInfoDao();
if (match.matches()) {
ret = deviceInfo.updatePriceByPhoneNum(Integer.parseInt(price), serialNum);
} else {
ret = deviceInfo.updatePriceBySerialNum(Integer.parseInt(price), serialNum);
}
HttpSession session=request.getSession();
if (ret) {
message = "success";
} else {
message = "fail";
}
response.sendRedirect(request.getContextPath() + "/result.jsp?message=" + message);
//request.getRequestDispatcher("/result.jsp").forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
10、result.jsp要修改,两个servelet返回到这个界面