文章目录
eclipse项目创建目录
PS:注意文件的位置
一、设计mysql数据库
- 数据库名和表名
- phone表
- user表
- 填入数据
二、代码
1.dao层
PhoneDao:
package cn.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.entity.Phone;
import cn.util.C3p0Utils;
public class PhoneDao {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
// 获取所有数据
public List<Phone> findPhone() throws SQLException {
String sql = "select * from phone";
List<Phone> phones = runner.query(sql, new BeanListHandler<Phone>(Phone.class));
return phones;
}
// 增加数据
public int adddataPhone(Phone phone) throws SQLException {
String sql = "insert into phone (id,sjname,sjprice) values(?,?,?)";
int row = runner.update(sql, phone.getId(), phone.getSjname(), phone.getSjprice());
return row;
}
// 删除数据,通过id
public int deletePhoneById(int id) throws SQLException {
String sql = "delete from phone where id = ?";
int row = runner.update(sql, id);
return row;
}
// 修改数据,通过id
public int updatePhone(Phone phone) throws SQLException {
String sql = "update phone set sjname = ?,sjprice = ? where id=?";
int row = runner.update(sql, phone.getSjname(), phone.getSjprice(), phone.getId());
return row;
}
// 查询商品,通过id
public Phone findPhoneById(int id) throws SQLException {
String sql = "select * from phone where id = ?";
Phone phone = runner.query(sql, new BeanHandler<Phone>(Phone.class), id);
return phone;
}
}
UserDao
package cn.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.util.C3p0Utils;
import cn.dao.*;
import cn.entity.User;
public class UserDao {
public User findUandUpsw(String username, String password) throws SQLException {
QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user where username = ? and password = ?";
User user = runner.query(sql, new BeanHandler<User>(User.class), username, password);
return user;
}
}
2.entity类
Phone
package cn.entity;
public class Phone {
private int id;
private String sjname;
private String sjprice;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSjname() {
return sjname;
}
public void setSjname(String sjname) {
this.sjname = sjname;
}
public String getSjprice() {
return sjprice;
}
public void setSjprice(String sjprice) {
this.sjprice = sjprice;
}
}
User
package cn.entity;
public class User {
private String username;
private String password;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3.service层
BothService
package cn.service;
import java.sql.SQLException;
import java.util.List;
import cn.dao.PhoneDao;
import cn.dao.UserDao;
import cn.entity.Phone;
import cn.entity.User;
public class BothService {
UserDao userDao = new UserDao();
PhoneDao phoneDao = new PhoneDao();
public User login(String username, String password) throws SQLException {
return userDao.findUandUpsw(username, password);
}
public List<Phone> findPhone() throws SQLException {
return phoneDao.findPhone();
}
public int add(Phone phone) throws SQLException {
return phoneDao.adddataPhone(phone);
}
public int deletePhoneById(int id) throws SQLException {
return phoneDao.deletePhoneById(id);
}
public int updatePhone(Phone phone) throws SQLException {
return phoneDao.updatePhone(phone);
}
public Phone findPhoneById(int id) throws SQLException {
return phoneDao.findPhoneById(id);
}
}
4.servlet层
AddServlet
package cn.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 com.sun.org.apache.bcel.internal.generic.I2D;
import cn.entity.Phone;
import cn.service.BothService;
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
int id = Integer.parseInt(request.getParameter("id"));
Phone phone = new Phone();
phone.setId(id);
phone.setSjname(request.getParameter("sjname"));
phone.setSjprice(request.getParameter("sjprice"));
BothService service = new BothService();
try {
Phone num = service.findPhoneById(id);
if (num != null) {
response.sendRedirect("error.jsp");
}
int row = service.add(phone);
if (row != 0) {
response.sendRedirect("FindAllServlet");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
DeleteServet
package cn.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 cn.service.BothService;
/**
* Servlet implementation class DeletePhoneByIdServlet
*/
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
BothService service = new BothService();
int id = Integer.parseInt(request.getParameter("id"));
try {
int row = service.deletePhoneById(id);
if (row != 0) {
response.sendRedirect("FindAllServlet");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
FindAllServlet
package cn.servlet;
import java.io.IOException;
import java.util.List;
import javax.security.auth.message.callback.PrivateKeyCallback.Request;
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 cn.entity.Phone;
import cn.service.BothService;
@WebServlet("/FindAllServlet")
public class FindAllServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
BothService service = new BothService();
try {
List<Phone> phones = service.findPhone();
if (phones != null) {
request.getSession().setAttribute("list", phones);
response.sendRedirect("show.jsp");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
FindOneServlet
package cn.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 cn.entity.Phone;
import cn.service.BothService;
@WebServlet("/FindOneServlet")
public class FindOneServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
BothService service = new BothService();
int id = Integer.parseInt(request.getParameter("id"));
try {
Phone phone = service.findPhoneById(id);
if (phone != null) {
request.getSession().setAttribute("phone", phone);
response.sendRedirect("one.jsp");
} else {
response.sendRedirect("error.jsp");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
LoginServlet
package cn.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 cn.entity.User;
import cn.service.BothService;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;chatset=utf-8");
BothService service = new BothService();
String username = request.getParameter("username");
String password = request.getParameter("password");
try {
User user = service.login(username, password);
if (user != null) {
response.sendRedirect("FindAllServlet");
} else {
System.out.println("登录失败");
response.sendRedirect("login.jsp");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
UpdateServlet
package cn.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 cn.entity.Phone;
import cn.entity.User;
import cn.service.BothService;
/**
* Servlet implementation class UpdatePhoneServlet
*/
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//-------------------------------------------------------------------
BothService service = new BothService();
Phone phone = new Phone();
int id = Integer.parseInt(request.getParameter("id"));
phone.setId(id);
phone.setSjname(request.getParameter("sjname"));
phone.setSjprice(request.getParameter("sjprice"));
try {
int row = service.updatePhone(phone);
if (row != 0) {
response.sendRedirect("FindAllServlet");
}
} catch (Exception e) {
e.printStackTrace();
}
//-------------------------------------------------------------------
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
5.util方法
C3p0Utils.java
package cn.util;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Utils {
private static DataSource ds;
static {
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource() {
return ds;
}
}
6.c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///zxc?3useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false</property>
<property name="user">root</property>
<property name="password">123456</property>
</default-config>
</c3p0-config>
注意:该xml名称是固定的,其他的名字会报错
7.jsp文件
add.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="AddServlet" method="post">
编号:<input type="text" name="id"><br>
<br> 品牌:<input type="text" name="sjname"><br>
<br> 价格:<input type="text" name="sjprice"><br>
<br> <input type="submit" value="添加" onclick="return confirm('确认添加')">
</form>
</body>
</html>
error.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>
你的操作不规范且我没写此功能!
以下功能没有完善:
1、增加的编号已经存在
2、查询id不存在
还有其他报错功能没转到此页面,是因为我懒得写了
</body>
</html>
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="LoginServlet" method="post">
账号:<input type="text" name="username" value="1"> <br /><br />
密码:<input type="password" name="password" value="1"><br />
<input type="submit" value="登录">
</form>
</body>
</html>
one.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="cn.entity.Phone"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table border="1">
<tr>
<td>编号</td>
<td>手机</td>
<td>价格</td>
</tr>
<tr>
<td>${phone.id }</td>
<td>${phone.sjname }</td>
<td>${phone.sjprice }</td>
</tr>
</table>
<a href="show.jsp">返回主界面</a>
<form action="FindAllServlet" method="post">
<input type="submit" value="查询全部"><br /> <br />
</form>
</body>
</html>
show.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="cn.entity.Phone"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE >
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- 显示用户的所有信息页面 -->
<form action="FindOneServlet" method="post">
<input type="text" name="id">
<input type="submit" value="查询"><br />
</form>
<table border="1">
<tr>
<td>编号</td>
<td>名称</td>
<td>价格</td>
<td>操作</td>
</tr>
<c:forEach items="${list}" var="sj">
<tr>
<td>${sj.id }</td>
<td>${sj.sjname }</td>
<td>${sj.sjprice }</td>
<td><a href="DeleteServlet?id=${sj.id}" onclick="return confirm('确认删除')">删除</a>
<a href="update.jsp?id=${sj.id }">修改</a>
</td>
</tr>
</c:forEach>
</table>
<a href="add.jsp"><input type="submit" value="添加手机信息"></a>
</body>
</html>
update.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>
<h1>修改信息</h1>
<form action="UpdateServlet" method="post">
<%String str = request.getParameter("id"); %>
<input type="hidden" name="id" value="<%=str%>">
<br /> 品牌:<input type="text" name="sjname"><br />
<br /> 价格:<input type="text" name="sjprice"><br />
<br /> <input type="submit" value="确定修改">
</form>
</body>
</html>
三、运行结果
- 登录界面
- show界面
- 修改信息界面
- 其它界面这里不再展示了
四、补充
1、jar包资源
链接:https://share.weiyun.com/d73F11NW
密码:b5fxe2
2、我的配置
JDK :1.8.0_202
mysql :mysql-connector-java-8.0.12
tomcat :apache-tomcat-9.0.29
eclipse:eclipse-jee-2020-06-R-win32-x86_64
3、注意事项
不能运行,一般都是mysql的连接的问题,mysql8.0以上不稳定或者需要配置很多东西,建议用mysql5.7的吧,遇到运行失败的问题不要着急,上网搜搜错误的地方,我的mysql搞了两三天都弄不好,很耽误时间,还是需要耐心。其他的错误,应该就是我们的配置不一样的问题了,还有导包要导对地方(bin目录下右键要进行Build Path)。