前言
介绍:本项目用于刚刚入门Javaweb人员学习使用,主要功能是通过Java连接jdbc MySQL在web页面实现增删改查效果
开发环境
开发工具:IDEA2023专业版,Tomcat9.0.62,navicat
jar包:mysql-connector-java-8.0.82.jar
语言:Java+SQL+HTML
代码实现
1.环境搭建
使用Navicat建立数据库
IDEA搭建:
项目基本类首先搭建好,前期还需要配置Tomcat服务器和web框架
2.代码实现:
首先连接数据库JDBC
// Dao.java
package Dao;
import popj.Goods;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Dao {
static final String URL = "jdbc:mysql://localhost:3306/goods_db?serverTimezone=UTC";
static final String USER = "root";
static final String PWD = "1234";
static Connection connection;
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
}catch (ClassNotFoundException e){
e.printStackTrace();
}
}
// 连接数据库
public static Connection getConnection() throws SQLException {
// System.out.println("连接成功");
return DriverManager.getConnection(URL, USER, PWD);
}
// 关闭连接 ,释放资源
public static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
}
}
注意的是,这里需要根据个人来确定数据库名和用户名,密码
然后就需要写入方法(get和set,构造方法)
package popj;
public class Goods {
private int goodId;
private String goodsName;
private int num;
private double price;
public Goods() {
super();
}
public Goods(int goodId, String goodsName, int num, double price) {
this.goodId = goodId;
this.goodsName = goodsName;
this.num = num;
this.price = price;
}
public int getGoodId() {
return goodId;
}
public void setGoodId(int goodId) {
this.goodId = goodId;
}
public String getGoodsName() {
return goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@Override
public String toString() {
return "Goods{" +
"goodId=" + goodId +
", goodsName='" + goodsName + '\'' +
", num=" + num +
", price=" + price +
'}';
}
}
写入数据逻辑
package popj;
import Dao.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class SqlConn {
//查询数据库中的数据显示
public static List<Goods> GetSource(String sql){
Connection coon = null;
PreparedStatement stat = null;
ResultSet rs = null;
List<Goods> list = new ArrayList<Goods>();
try {
coon = Dao.getConnection();
stat = coon.prepareStatement(sql);
rs=stat.executeQuery();
//循环数据库表
while (rs.next()){
Goods goods = new Goods();
goods.setGoodId(rs.getInt("goodId"));
goods.setGoodsName(rs.getString("goodsName"));
goods.setNum(rs.getInt("num"));
goods.setPrice(rs.getDouble("price"));
list.add(goods);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//增删改
public static boolean updateGoods(String sql) {
Connection conn = null;
PreparedStatement ps = null;
boolean flag = false;
try {
conn = Dao.getConnection();
ps = conn.prepareStatement(sql);
flag = ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
Dao.close(null, ps, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
}
然后再写入SQL语句
package Dao;
import popj.Goods;
import popj.SqlConn;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class GoodsDao {
public static List<Goods> select(){
String sql = "select * from tb_goods";
return SqlConn.GetSource(sql);
}
//添加
public static boolean add(Goods g){
String sql = "insert into tb_goods values(null,'"+g.getGoodsName()+"','"+g.getNum()+"','"+g.getPrice()+"')";
System.out.println(sql);
return SqlConn.updateGoods(sql);
}
// 删除
public static boolean delete(int id) {
String sql="delete from tb_goods where goodId="+id;
return SqlConn.updateGoods(sql);
}
//修改
public static boolean update(Goods g){
String sql = "update tb_goods set goodsName='"+g.getGoodsName()+"', num='"+g.getNum()+"', price='"+g.getPrice()+"' where goodId="+g.getGoodId();
return SqlConn.updateGoods(sql);
}
}
数据的基本代码已经完成,接下来就是前端页面的代码
首先显示主页,加入主页代码,这里我用的是JSP
<%@ page import="java.util.List" %>
<%@ page import="popj.Goods" %>
<%@ page import="Dao.GoodsDao" %><%--
Created by IntelliJ IDEA.
User: Kobe
Date: 2024/3/19
Time: 21:02
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css"
integrity="sha384-xOolHFLEh07PJGoPkLv1IbcEPTNtaed2xpHsD9ESMhqIYd0nLMwNLD69Npy4HI+N" crossorigin="anonymous">
<title>首页</title>
<style>
.container {
text-align: center;
margin: 0 auto;
height: 50%;
}
.container table {
text-align: center;
overflow-y: scroll;
white-space: nowrap;
border-radius: 10px;
box-shadow: inset 0 0 0px rgba(240, 240, 240, 0.5);
background-color: rgba(240, 240, 240, 0.5);
}
p a{
width: 50%;
}
</style>
</head>
<body>
<div class="container">
<h2>超市管理系统</h2>
<table border="1" class="table table-bordered">
<tr>
<th scope="col">序号</th>
<th scope="col">名称</th>
<th scope="col">数量</th>
<th scope="col">单价</th>
<th scope="col">操作</th>
</tr>
<% List<Goods> list = GoodsDao.select(); %>
<% for (Goods goods : list) { %>
<tr>
<td><%= goods.getGoodId() %>
</td>
<td><%= goods.getGoodsName() %>
</td>
<td><%= goods.getNum() %>
</td>
<td><%= goods.getPrice() %>
</td>
<td><a href="DeleteServlet?id=<%=goods.getGoodId()%>"
onclick="return confirm('是否删除该商品')" class="btn btn-danger">删除</a>
<a href="update.jsp?goodId=<%=goods.getGoodId()%>" class="btn btn-success">修改</a>
<%-- 后台获取前端的id--%>
</td>
</tr>
<%}%>
<tr>
<td colspan="8" style="text-align:center;" >
共为您查询出<b style="color: pink"><%=list.size() %>
</b> 条数据
</td>
</tr>
</table>
<p><a href="add.jsp" class="btn btn-success">添加</a>
</p>
</div>
</body>
</html>
效果图
新增(添加)
当我们点击添加,就跳转到添加页面,所以还需要写入添加界面 ,Add.jsp
<%--
Created by IntelliJ IDEA.
User: Kobe
Date: 2024/3/19
Time: 21:38
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="Servlet.AddServlet" %>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css"
integrity="sha384-xOolHFLEh07PJGoPkLv1IbcEPTNtaed2xpHsD9ESMhqIYd0nLMwNLD69Npy4HI+N" crossorigin="anonymous">
<html>
<head>
<title>商品添加</title>
<style>
.container {
width: 50%;
}
.container .btn{
width: 80%;
margin: 0 auto;
text-align: center;
}
h2{
width: 50%;
}
</style>
</head>
<body>
<div class="container">
<h2>商品录入</h2>
<form method="post" action="AddServlet">
<p>商品名称:<input type="text" name="name" required="required" class="form-control"></p>
<p>商品数量:<input type="text" name="num" required="required" class="form-control"></p>
<p>商品价格:<input type="text" name="price" required="required" class="form-control"></p>
<p class="btn"><input type="submit" value="添加" class="btn btn-success">
<input type="reset" value="取消" class="btn btn-danger">
</p>
</form>
</div>
</body>
</html>
效果图
如果需要正式的添加数据还需要写Servlet请求
有了这一步,才能添加数据到数据库中
package Servlet;
import Dao.GoodsDao;
import popj.Goods;
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 java.io.IOException;
import java.util.Arrays;
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Goods g = new Goods();
String name = new String(req.getParameter("name").getBytes("ISO-8859-1"),"UTF-8");
System.out.println("商品名称"+name);
String num =req.getParameter("num");
String price = req.getParameter("price");
g.setGoodsName(name);
g.setNum(Integer.parseInt(num));
g.setPrice(Double.parseDouble(price));
//添加成功跳转首页
if(GoodsDao.add(g)){
resp.sendRedirect("index.jsp");
}
}
}
修改:
修改功能同上,写入JSP页面和Servlet代码
Servlet:
package Servlet;
import Dao.GoodsDao;
import popj.Goods;
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 java.io.IOException;
import java.sql.SQLException;
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
//获取前端传递修改后的数据
Goods g = new Goods();
String name = req.getParameter("name");
System.out.println("商品名称"+name);
String num =req.getParameter("num");
String price = req.getParameter("price");
String goodId = req.getParameter("goodId");
if (goodId != null && !goodId.isEmpty()) {
g.setGoodId(Integer.parseInt(goodId));
}
g.setGoodsName(name);
g.setNum(Integer.parseInt(num));
g.setPrice(Double.parseDouble(price));
if (GoodsDao.update(g)){
resp.sendRedirect("index.jsp");
}else {
resp.getWriter().println("更新失败");
}
}
}
JSP:
<%--
Created by IntelliJ IDEA.
User: Kobe
Date: 2024/3/19
Time: 21:38
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@page import="Servlet.AddServlet" %>
<%@ page import="popj.Goods" %>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css"
integrity="sha384-xOolHFLEh07PJGoPkLv1IbcEPTNtaed2xpHsD9ESMhqIYd0nLMwNLD69Npy4HI+N" crossorigin="anonymous">
<html>
<head>
<title>商品添加</title>
<style>
.container {
width: 50%;
}
.container .btn{
width: 80%;
margin: 0 auto;
text-align: center;
}
h2{
width: 50%;
}
</style>
</head>
<body>
<div class="container">
<h2>商品修改</h2>
<form method="post" action="UpdateServlet">
<p>商品名称:<input type="text" name="name" required="required" class="form-control"></p>
<p>商品数量:<input type="text" name="num" required="required" class="form-control"></p>
<p>商品价格:<input type="text" name="price" required="required" class="form-control"></p>
<input type="hidden" name="goodId" value="<%= request.getParameter("goodId") %>"/>
<%-- --%>
<p class="btn"><input type="submit" value="修改" class="btn btn-success">
<input type="reset" value="取消" class="btn btn-danger">
</p>
</form>
</div>
</body>
</html>
有了JSP前端和Servlet代码,就能够实现修改功能,修改功能和新增功能都差不多,主要是SQL语句
删除
删除只需要写入Servlet就可以,具体代码如下
package Servlet;
import Dao.GoodsDao;
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;
/**
* Servlet implementation class DeleteServlet
*/
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DeleteServlet() {
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
int id = Integer.parseInt(request.getParameter("id"));
System.out.println("商品编号:"+id);
if(GoodsDao.delete(id)) {
// 删除成功之后 跳转到首页 刷新数据
response.sendRedirect("index.jsp");
}else {
response.getWriter().println("删除失败");
}
}
/**
* @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);
}
}
以上全部代码,关注博主既可领取项目全套代码