数据库脚本:
CREATE TABLE `user`(
userId INT(32) PRIMARY KEY AUTO_INCREMENT,
userName VARCHAR(22) NOT NULL,
userPwd VARCHAR(32) NOT NULL,
userPhone VARCHAR(200) NOT NULL,
userSex INT(10) NOT NULL,
userEmail VARCHAR(100) NOT NULL,
userBirthday DATE NOT NULL
)CHARSET=utf8;
INSERT INTO `user`(userName,userPwd,userPhone,userSex,userEmail,userBirthday)
VALUES
('tom','132.com','18539365895',0,'1262614414@qq.com','2003-02-02'),
('jim','465.com','12345678984',1,'2438385915@qq.com','2002-10-05'),
('jerry','789.com','96385274185',0,'1367877957@qq.com','2000-08-12');
SELECT * FROM `user`;
实体类:entity:
private int userId;
private String userName;
private String userPwd;
private String userPhone;
private int userSex;
private String userEmail;
private Date userBirthday;
//+get/set 方法
dao:
UserDao:
public interface UserDao {
//登录
User login(String name,String pwd);
//查询全部
List<User> getAll();
//模糊查询
List<User>getUserByUserNameOrUserPhoneOrUserEmail(String name);
//根据id进行查询
User getUserById(int id);
//删除
int deleteUserById(int id);
//增加
int addUser(User user);
//修改
int updateUserById(User user);
}
Dao...IMpl:
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public User login(String name, String pwd) {
String sql="select * from user where userName=? and userPwd=?";
Object [] objects={name,pwd};
ResultSet rs = this.executeQuery(sql, objects);
User user=null;
try {
if(rs.next()){
user=new User();
user.setUserId(rs.getInt("userId"));
user.setUserName(rs.getString("userName"));
user.setUserPwd(rs.getString("userPwd"));
user.setUserSex(rs.getInt("userSex"));
user.setUserPhone(rs.getString("userPhone"));
user.setUserEmail(rs.getString("userEmail"));
user.setUserBirthday(rs.getDate("userBirthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
@Override
public List<User> getAll() {
String sql="select * from user";
List<User> userList=new ArrayList<>();
ResultSet rs = this.executeQuery(sql, null);
try {
while(rs.next()){
User user=new User();
user.setUserId(rs.getInt("userId"));
user.setUserName(rs.getString("userName"));
user.setUserPwd(rs.getString("userPwd"));
user.setUserSex(rs.getInt("userSex"));
user.setUserPhone(rs.getString("userPhone"));
user.setUserEmail(rs.getString("userEmail"));
user.setUserBirthday(rs.getDate("userBirthday"));
//添加到集合中
userList.add(user);
}
}catch (Exception ex){
ex.printStackTrace();
}
return userList;
}
@Override
public List<User> getUserByUserNameOrUserPhoneOrUserEmail(String name) {
String sql="SELECT * FROM `user` WHERE userName LIKE ? OR userPhone LIKE ? OR userEmail LIKE ?";
name="%"+name+"%";
Object [] objects={name,name,name};
List<User> userList=new ArrayList<>();
ResultSet resultSet = this.executeQuery(sql, objects);
try {
while(rs.next()){
User user=new User();
user.setUserId(rs.getInt("userId"));
user.setUserName(rs.getString("userName"));
user.setUserPwd(rs.getString("userPwd"));
user.setUserSex(rs.getInt("userSex"));
user.setUserPhone(rs.getString("userPhone"));
user.setUserEmail(rs.getString("userEmail"));
user.setUserBirthday(rs.getDate("userBirthday"));
//添加到集合中
userList.add(user);
}
}catch (Exception ex){
ex.printStackTrace();
}
return userList;
}
@Override
public User getUserById(int id) {
String sql="select * from user where userId=?";
Object [] objects={id};
User user=null;
ResultSet rs = this.executeQuery(sql, objects);
try {
if(rs.next()){
user=new User();
user.setUserId(this.rs.getInt("userId"));
user.setUserName(this.rs.getString("userName"));
user.setUserPwd(this.rs.getString("userPwd"));
user.setUserSex(this.rs.getInt("userSex"));
user.setUserPhone(this.rs.getString("userPhone"));
user.setUserEmail(this.rs.getString("userEmail"));
user.setUserBirthday(this.rs.getDate("userBirthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
@Override
public int deleteUserById(int id) {
String sql="delete from user where userId=?";
Object [] objects={id};
return this.executeUpdate(sql,objects);
}
@Override
public int addUser(User user) {
String sql="INSERT INTO `user`(userName,userPwd,userPhone,userSex,userEmail,userBirthday)VALUES(?,?,?,?,?,?)";
Object [] objects={user.getUserName(),user.getUserPwd(),user.getUserPhone(),user.getUserSex(),user.getUserEmail(),user.getUserBirthday()};
return this.executeUpdate(sql,objects);
}
@Override
public int updateUserById(User user) {
String sql="UPDATE USER SET userName=?,userPwd=?,userPhone=?,userSex=?,userEmail=?,userBirthday=? WHERE userId=?";
Object [] objects={user.getUserName(),user.getUserPwd(),user.getUserPhone(),user.getUserSex(),user.getUserEmail(),user.getUserBirthday(),user.getUserId()};
return this.executeUpdate(sql,objects);
}
}
Service:
userService:
public interface UserService {
//登录
User login(String name, String pwd);
//查询全部
List<User> getAll();
//模糊查询
List<User>getUserByUserNameOrUserPhoneOrUserEmail(String name);
//删除
boolean deleteUserById(int id);
//增加
boolean addUser(User user);
//修改
boolean updateUserById(User user);
//根据id进行查询
User getUserById(int id);
}
UserServiceImpl:
public class UserServiceImpl implements UserService {
//创建数据访问层对象
UserDao ud=new UserDaoImpl();
@Override
public User login(String name, String pwd) {
return ud.login(name,pwd);
}
@Override
public List<User> getAll() {
return ud.getAll();
}
@Override
public List<User> getUserByUserNameOrUserPhoneOrUserEmail(String name) {
return ud.getUserByUserNameOrUserPhoneOrUserEmail(name);
}
@Override
public boolean deleteUserById(int id) {
if(ud.deleteUserById(id)>0){
return true;
}
return false;
}
@Override
public boolean addUser(User user) {
if(ud.addUser(user)>0){
return true;
}
return false;
}
@Override
public boolean updateUserById(User user) {
if(ud.updateUserById(user)>0){
return true;
}
return false;
}
@Override
public User getUserById(int id) {
return ud.getUserById(id);
}
}
Servlet:
@WebServlet("/UserServlet")
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req,resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=UTF-8");
String path = req.getParameter("path");
HttpSession session = req.getSession();
UserService us=new UserServiceImpl();
if(path.equals("login")){
//获取用户名和密码
String userName = req.getParameter("userName");
String userPwd=req.getParameter("userPwd");
User user = us.login(userName, userPwd);
if(user!=null){
session.setAttribute("user",user);
resp.getWriter().print("<script>alert('登录成功!');window.location='UserServlet?path=showAll';</script>");
}else{
resp.getWriter().print("<script>alert('登录失败,用户名或密码错误');window.location='index.jsp'</script>");
}
}else if(path.equals("showAll")){
List<User> userList = us.getAll();
req.setAttribute("userList",userList);
req.getRequestDispatcher("show.jsp").forward(req,resp);
}else if(path.equals("toUpdate")){
String id = req.getParameter("id");
User user = us.getUserById(Integer.valueOf(id));
req.setAttribute("userUpdate",user);
req.getRequestDispatcher("updateOrAdd.jsp").forward(req,resp);
}else if(path.equals("updateOrAdd")){
String id = req.getParameter("id");
String userName = req.getParameter("userName");
String userPwd = req.getParameter("userPwd");
String userPhone = req.getParameter("userPhone");
String userSex = req.getParameter("userSex");
String userEmail = req.getParameter("userEmail");
String ub = req.getParameter("userBirthday");
Date userBirthday=null;
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
try {
userBirthday=sdf.parse(ub);
} catch (ParseException e) {
e.printStackTrace();
}
User user=new User();
user.setUserName(userName);
user.setUserPwd(userPwd);
user.setUserPhone(userPhone);
user.setUserSex(Integer.valueOf(userSex));
user.setUserEmail(userEmail);
user.setUserBirthday(userBirthday);
if(id.equals("") || id.equals(" ") || id==null){
//添加
boolean flag = us.addUser(user);
if(flag){
resp.getWriter().print("<script>alert('添加成功!');window.location='UserServlet?path=showAll';</script>");
}else{
resp.getWriter().print("<script>alert('添加失败');window.location='UserServlet?path=showAll';</script>");
}
}else{
user.setUserId(Integer.valueOf(id));
boolean flag = us.updateUserById(user);
if(flag){
resp.getWriter().print("<script>alert('修改成功!');window.location='UserServlet?path=showAll';</script>");
}else{
resp.getWriter().print("<script>alert('修改失败');window.location='UserServlet?path=showAll';</script>");
}
}
}else if(path.equals("del")){
String id = req.getParameter("id");
boolean flag = us.deleteUserById(Integer.valueOf(id));
resp.getWriter().print(flag);
}else if(path.equals("search")){
String search=req.getParameter("search");
List<User> userList = us.getUserByUserNameOrUserPhoneOrUserEmail(search);
req.setAttribute("userList",userList);
req.getRequestDispatcher("show.jsp").forward(req,resp);
}
}
}
JSP:
登录页面:
<html>
<head>
<title>登录</title>
<script>
</script>
</head>
<body>
<form action="UserServlet?path=login" method="post">
<table align="center">
<tr>
<th colspan="2" style="color: orange">趣买买购物</th>
</tr>
<tr>
<td>用户名:</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td>密码:</td>
<td><input type="password" name="userPwd"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="登录"></td>
</tr>
</table>
</form>
</body>
</html>
展示页面show.jsp:
<div class="mem_tit">我的订单 <a href="updateOrAdd.jsp">增加用户</a></div>
<form action="UserServlet?path=search" method="post">
<table border="0" class="order_tab" style="width:930px; text-align:center; margin-bottom:30px;"
cellspacing="0" cellpadding="0">
<tr>
<td colspan="8"><input type="text" name="search" placeholder="姓名,手机号,邮箱查询"> <input
type="submit" value="搜索"></td>
</tr>
<tr>
<td width="8%">序号</td>
<td width="10%">用户名</td>
<td width="15%">用户密码</td>
<td width="15%">手机号</td>
<td width="8%">性别</td>
<td width="15%">邮箱</td>
<td width="15%">生日</td>
<td width="25%">操作</td>
</tr>
<c:forEach items="${userList}" var="user">
<tr id="${user.userId}">
<td>${user.userId}</td>
<td>${user.userName}</td>
<td>${user.userPwd}</td>
<td>${user.userPhone}</td>
<td><c:if test="${user.userSex==0}">男</c:if><c:if test="${user.userSex==1}">女</c:if></td>
<td>${user.userEmail}</td>
<td>${user.userBirthday}</td>
<td><a href="#" onclick="delUser(${user.userId})">删除</a> <a
href="UserServlet?path=toUpdate&id=${user.userId}">修改</a></td>
</tr>
</c:forEach>
</table>
</form>
删除的关键脚本:
<script type="text/javascript" src="js/select.js"></script>
<script>
function delUser(id) {
var answer = confirm("确认删除吗");
if (answer) {
jq.post("UserServlet", "path=del&id=" + id, function (data) {
if (data == "true") {
jq("#" + id).remove();
alert("删除成功");
} else {
alert("删除失败");
}
})
}
}
</script>
修改页面的关键代码:
<form action="UserServlet?path=updateOrAdd" method="post">
<table border="0" class="add_tab" style="width:930px;" cellspacing="0" cellpadding="0">
<input type="text" value="${userUpdate.userId}" name="id" hidden >
<tr>
<td align="right">用户名</td>
<td style="font-family:'宋体';"><input type="text" value="${userUpdate.userName}"name="userName" class="add_ipt" />(必填)</td>
<td align="right">密码</td>
<td style="font-family:'宋体';"><input type="text" value="${userUpdate.userPwd}"name="userPwd" class="add_ipt" />(必填)</td>
</tr>
<tr>
<td align="right">手机</td>
<td style="font-family:'宋体';"><input type="text" value="${userUpdate.userPhone}" name="userPhone" class="add_ipt" />(必填)</td>
<td align="right">性别</td>
<td style="font-family:'宋体';"><input type="radio" name="userSex" value="0" <c:if test="${userUpdate.userSex==0}">checked</c:if> >男
<input type="radio" name="userSex" value="1" <c:if test="${userUpdate.userSex==1}">checked</c:if>>女
</td>
</tr>
<tr>
<td align="right">邮箱</td>
<td style="font-family:'宋体';"><input type="text" value="${userUpdate.userEmail}" name="userEmail" class="add_ipt" />(必填)</td>
<td align="right">生日</td>
<td style="font-family:'宋体';"><input type="text" value="${userUpdate.userBirthday}" name="userBirthday" class="add_ipt" /></td>
</tr>
</table>
<p align="right">
<input type="submit" class="add_b" value="确认修改"/>
</p>
</form>