|
🙈作者简介：练习时长两年半的Java up主 🙉个人主页：老茶icon 🙊 ps:点赞👍是免费的，却可以让写博客的作者开兴好久好久😎 📚系列专栏：Java全栈，计算机系列（火速更新中） 💭 格言：种一棵树最好的时间是十年前，其次是现在 🏡动动小手，点个关注不迷路，感谢宝子们一键三连
目录
题目：基于数据库的商品购物系统Java实现内容/作用：知识点/设计/实验/作业/练习学习：Java和数据库的综合应用
一、前言数据库表Java包实现后界面
二、环境与设备三、数据库部分1、数据库建表顾客表customer管理员表sys_manager商品表goods表中添加数据顾客表管理员表商品表
四、Java部分dao：entity：service：util：CustomerDaoGoodsDaoSysManagerDaoCustomerGoodsSysManagerMainDBUtil
五、总结与分析
题目：基于数据库的商品购物系统Java实现
内容/作用：知识点/设计/实验/作业/练习
学习：Java和数据库的综合应用
一、前言
基于数据库，用Java实现对模拟一个商品购物系统的增加、删除、查找、修改功能。
数据库表
Java包
实现后界面
二、环境与设备
1.软件：IntelliJ IDEA 2022.3.2 2.环境：Windows 11 and JDK 11
三、数据库部分
1、数据库建表
上面已经说了，需要建3个表。首先先先建一个数据库：shopdb。在其数据库中创建表，顾客表customer，管理员表sys_manager，商品表goods。
顾客表customer
create table customer(
id int not null primary key auto_increment,
phone varchar(20) not null,
password varchar(20) not null,
balance double default '0',
name varchar(20) ,
address text
)
管理员表sys_manager
create table sys_manager(
id int not null primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null
)
商品表goods
create table goods(
id int not null primary key auto_increment,
brand varchar(20) not null,
name varchar(20) not null,
price double not null,
num int not null,
make_date date not null
)
表中添加数据
随便添加几行，为了方便测试，例如：
顾客表
管理员表
商品表
 好的到这里，数据库部分就完成编写。下面就是Java编写代码！！ 好的到这里，数据库部分就完成编写。下面就是Java编写代码！！ 好的到这里，数据库部分就完成编写。下面就是Java编写代码！！ (！！三遍！！？？)
四、Java部分
首先： 我们先创建一个包shopsys，包中，再创建4个包，分别为：dao,entity,service,util。
作用分别如下：
dao：
* DAO层 数据访问层，用于访问数据库的一个类
* 命名通常为表名+DAO
* 在其中定义某张表的CURD操作
entity：
* entity 实体层，对应某张表，属性就是表的字段
* 类名与表名一致
* 标准的实体类也称为JavaBean，是组成Java程序的最小的类
* JavaBean需要满足：
* 1.这个类必须是公共的
* 2.提供无参数的构造方法
* 3.对属性进行封装
service：
* 业务层，调用数据访问层的方法
util：
* 定义数据库工具类，简化JDBC操作
* 可以简化的操作
* 1.加载驱动
* 2.连接数据库
* 6.关闭
创建下图文件，重命名如下：
 开始编写代码： 开始编写代码： 开始编写代码： 模板已经建好了，代码我就直接放上了。 每个java文件导包是否正确！！！ 每个java文件导包是否正确！！！ 每个java文件导包是否正确！！！
CustomerDao
public class CustomerDao {
private Connection conn;
private PreparedStatement pst;
private ResultSet rs;
public Customer login(String phone, String password) {
conn = DBUtil.getConn();
String sql = "select * from customer where phone=? and password=?";
try {
pst = conn.prepareStatement(sql);
pst.setString(1, phone);
pst.setString(2, password);
rs = pst.executeQuery();
if (rs.next()) {
int id = rs.getInt(1);
double balance = rs.getDouble(4);
String name = rs.getString(5);
String address = rs.getString(6);
return new Customer(id, phone, password, balance, name, address);
}
} catch (SQLException e) {
System.out.println("顾客登录异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return null;
}
public boolean register(String phone, String password) {
conn = DBUtil.getConn();
try {
//判断用户是否存在
String sql = "select * from customer where phone=? ";
pst = conn.prepareStatement(sql);
pst.setString(1, phone);
rs = pst.executeQuery();
if (rs.next()) {
return false;
}
//真实注册
String sql2 = "insert into customer(phone,password) values(?,?)";
pst = conn.prepareStatement(sql2);
pst.setString(1, phone);
pst.setString(2, password);
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("注册异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
public boolean updateInfo(String name, String address, int id) {
conn = DBUtil.getConn();
try {
String sql = "update customer set name=?,address=? where id=?";
pst = conn.prepareStatement(sql);
pst.setString(1, name);
pst.setString(2, address);
pst.setInt(3, id);
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("修改信息异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
public boolean changeBalance(double balance, int id) {
conn = DBUtil.getConn();
try {
String sql = "update customer set balance=? where id=?";
pst = conn.prepareStatement(sql);
pst.setDouble(1, balance);
pst.setInt(2, id);
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("修改金额信息异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
}
GoodsDao
public class GoodsDao {
private Connection conn;
private PreparedStatement pst;
private ResultSet rs;
public List queryAll() {
ArrayList list = new ArrayList();
conn = DBUtil.getConn();
String sql = "select * from goods";
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
int id = rs.getInt(1);
String brand = rs.getString(2);
String name = rs.getString(3);
double price = rs.getDouble(4);
int num = rs.getInt(5);
String makeDate = rs.getString(6);
Goods goods = new Goods(id, brand, name, price, num, makeDate);
list.add(goods);
}
} catch (SQLException e) {
System.out.println("查询所有商品异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return list;
}
public Goods queryById(int id) {
conn = DBUtil.getConn();
String sql = "select * from goods where id=?";
try {
pst = conn.prepareStatement(sql);
pst.setInt(1, id);
rs = pst.executeQuery();
while (rs.next()) {
String brand = rs.getString(2);
String name = rs.getString(3);
double price = rs.getDouble(4);
int num = rs.getInt(5);
String makeDate = rs.getString(6);
return new Goods(id, brand, name, price, num, makeDate);
}
} catch (SQLException e) {
System.out.println("根据id查询商品异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return null;
}
public boolean insert(Goods goods) {
conn = DBUtil.getConn();
try {
String sql = "insert into goods values(null,?,?,?,?,?)";
pst = conn.prepareStatement(sql);
pst.setString(1,goods.getBrand());
pst.setString(2,goods.getName());
pst.setDouble(3, goods.getPrice());
pst.setInt(4, goods.getNum());
pst.setString(5,goods.getMakeDate().toString());
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("修改商品信息异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
public boolean updateGoods(double price, int num, int id) {
conn = DBUtil.getConn();
try {
String sql = "update goods set price=?,num=? where id=?";
pst = conn.prepareStatement(sql);
pst.setDouble(1, price);
pst.setInt(2, num);
pst.setInt(3, id);
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("修改商品信息异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
public boolean deleteById(int id) {
conn = DBUtil.getConn();
try {
String sql = "delete from goods where id=?";
pst = conn.prepareStatement(sql);
pst.setInt(1, id);
return pst.executeUpdate() > 0;
} catch (SQLException e) {
System.out.println("删除商品异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return false;
}
}
SysManagerDao
public class SysManagerDao {
private Connection conn;
private PreparedStatement pst;
private ResultSet rs;
public SysManager login(String username, String password) {
conn = DBUtil.getConn();
String sql = "select * from sys_manager where username=? and password=?";
try {
pst = conn.prepareStatement(sql);
pst.setString(1, username);
pst.setString(2, password);
rs = pst.executeQuery();
if (rs.next()) {
return new SysManager(rs.getInt(1), username, password);
}
} catch (SQLException e) {
System.out.println("管理员登录异常" + e);
} finally {
DBUtil.release(conn, pst, rs);
}
return null;
}
}
Customer
public class Customer {
private int id;
private String phone;
private String password;
private double balance;
private String name;
private String address;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", phone='" + phone + '\'' +
", password='" + password + '\'' +
", balance=" + balance +
", name='" + name + '\'' +
", address='" + address + '\'' +
'}';
}
public Customer(String phone, String password) {
this.phone = phone;
this.password = password;
}
public Customer() {
}
public Customer(String phone, String password, double balance, String name, String address) {
this.phone = phone;
this.password = password;
this.balance = balance;
this.name = name;
this.address = address;
}
public Customer(int id, String phone, String password, double balance, String name, String address) {
this.id = id;
this.phone = phone;
this.password = password;
this.balance = balance;
this.name = name;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
Goods
public class Goods {
private int id;
private String brand;
private String name;
private double price;
private int num;
private String makeDate;
@Override
public String toString() {
return "Goods{" +
"id=" + id +
", brand='" + brand + '\'' +
", name='" + name + '\'' +
", price=" + price +
", num=" + num +
", makeDate=" + makeDate +
'}';
}
public Goods() {
}
public Goods(String brand, String name, double price, int num, String makeDate) {
this.brand = brand;
this.name = name;
this.price = price;
this.num = num;
this.makeDate = makeDate;
}
public Goods(int id, String brand, String name, double price, int num, String makeDate) {
this.id = id;
this.brand = brand;
this.name = name;
this.price = price;
this.num = num;
this.makeDate = makeDate;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
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 int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getMakeDate() {
return makeDate;
}
public void setMakeDate(String makeDate) {
this.makeDate = makeDate;
}
}
SysManager
public class SysManager {
private int id;
private String username;
private String password;
@Override
public String toString() {
return "SysManager{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public SysManager() {
}
public SysManager(String username, String password) {
this.username = username;
this.password = password;
}
public SysManager(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
}
Main
public class Main {
CustomerDao customerDao = new CustomerDao();
GoodsDao goodsDao = new GoodsDao();
SysManagerDao sysManagerDao = new SysManagerDao();
Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
new Main().menu();
}
public void cusMenu(Customer customer) {
System.out.println("1.查看个人信息");
System.out.println("2.修改个人信息");
System.out.println("3.充值");
System.out.println("4.购买");
System.out.println("5.返回");
switch (sc.nextInt()) {
case 1:
System.out.println(customer);
break;
case 2:
System.out.println("请输入用户名");
String newName = sc.next();
System.out.println("请输入地址");
String newAddress = sc.next();
if (customerDao.updateInfo(newName, newAddress, customer.getId())) {
//既改动数据库，又改动当前方法的参数
customer.setName(newName);
customer.setAddress(newAddress);
System.out.println("修改成功");
}
break;
case 3:
System.out.println("请输入充值金额");
double money = sc.nextDouble();
double newBalance = customer.getBalance() + money;
customer.setBalance(newBalance);
if (customerDao.changeBalance(newBalance, customer.getId())) {
System.out.println("充值成功");
}
break;
case 4:
//所有商品
goodsDao.queryAll().forEach(System.out::println);
System.out.println("请输入要购买的商品编号");
Goods goods = goodsDao.queryById(sc.nextInt());
System.out.println("请输入要购买的数量");
int buyNum = sc.nextInt();
//判断库存和余额
if (goods.getNum()
System.out.println("余额不足");
return;
}
//同时执行两张表的修改
boolean b = goodsDao.updateGoods(goods.getPrice(), goods.getNum() - buyNum, goods.getId());
boolean b1 = customerDao.changeBalance(customer.getBalance() - buyNum * goods.getPrice(), customer.getId());
//更新方法变量的值
customer.setBalance(customer.getBalance() - buyNum * goods.getPrice());
if (b && b1) {
System.out.println("===============");
System.out.println("商品：" + goods.getBrand() + "\t" + goods.getName());
System.out.println("单价：" + goods.getPrice());
System.out.println("购买数量：" + buyNum);
System.out.println("总价：" + buyNum * goods.getPrice());
System.out.println("收货人：" + customer.getName());
System.out.println("收货人电话：" + customer.getPhone());
System.out.println("收货地址：" + customer.getAddress());
System.out.println("===============");
}
break;
case 5:
menu();
break;
}
cusMenu(customer);
}
public void sysMenu() {
System.out.println("1.查看所有商品");
System.out.println("2.添加商品");
System.out.println("3.修改商品");
System.out.println("4.删除商品");
System.out.println("5.返回");
switch (sc.nextInt()) {
case 1:
goodsDao.queryAll().forEach(System.out::println);
break;
case 2:
Goods g1 = new Goods("娃哈哈", "纯净水", 2, 50, new Date().toString());
Goods g4 = new Goods("农夫山泉", "纯净水", 2, 50, new Date().toString());
Goods g3 = new Goods("康师傅", "冰红茶", 3, 50, new Date().toString());
Goods g2 = new Goods("康师傅", "红烧牛肉面", 4.5, 50, new Date().toString());
goodsDao.insert(g1);
goodsDao.insert(g2);
goodsDao.insert(g3);
goodsDao.insert(g4);
break;
case 3:
System.out.println("请输入商品编号");
int id = sc.nextInt();
System.out.println("请输入新价格");
double newPrice = sc.nextDouble();
System.out.println("请输入新库存");
int newNum = sc.nextInt();
if (goodsDao.updateGoods(newPrice, newNum, id)) {
System.out.println("修改成功");
}
break;
case 4:
System.out.println("请输入商品编号");
int delId = sc.nextInt();
if (goodsDao.deleteById(delId)) {
System.out.println("删除成功");
}
break;
}
sysMenu();
}
public void menu() {
System.out.println("1.管理员登录");
System.out.println("2.顾客登录");
System.out.println("3.注册顾客");
switch (sc.nextInt()) {
case 1:
System.out.println("请输入用户名");
String username = sc.next();
System.out.println("请输入密码");
String password = sc.next();
SysManager sysManager = sysManagerDao.login(username, password);
if (sysManager != null) {
//管理员界面
sysMenu();
} else {
System.out.println("用户名或密码错误");
}
break;
case 2:
System.out.println("请输入电话");
String phone = sc.next();
System.out.println("请输入密码");
String pwd = sc.next();
Customer customer = customerDao.login(phone, pwd);
if (customer != null) {
cusMenu(customer);
} else {
System.out.println("用户名或密码错误");
}
break;
case 3:
System.out.println("请输入电话");
String regPhone = sc.next();
System.out.println("请输入密码");
String regPwd = sc.next();
if (customerDao.register(regPhone, regPwd)) {
System.out.println("顾客界面");
} else {
System.out.println("该用户已注册");
}
break;
}
menu();
}
}
DBUtil
public class DBUtil {
/*
* 定义静态代码块用于加载驱动
* */
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动不存在" + e);
}
}
//定义静态常量保存连接数据库的字符串
private final static String URL = "jdbc:mysql://localhost:3306/shopdb?serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "root";
/*
* 定义静态方法获取连接对象
* */
public static Connection getConn() {
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
System.out.println("连接数据库信息异常" + e);
}
return connection;
}
/*
* 定义静态方法释放资源
* */
public static void release(Connection conn, PreparedStatement pst, ResultSet rs) {
//使用了哪个接口就关闭那个接口
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("关闭异常" + e);
}
}
}
DBUtil中有一个地方需要改动！！！ DBUtil中有一个地方需要改动！！！ DBUtil中有一个地方需要改动！！！  端口号，修改为自己的电脑端口就行，默认是3306. 数据库名称，就是创建数据库的名字，前面也收过了。  上面的名字和密码，改为自己安装数据库时设置的账户和密码就行。 改完之后，导包正确，就能运行。 代码就不详细讲解，通俗易懂，都加以注释。
五、总结与分析
到这里，虽然有些地方不足，但学完数据库知识，能和Java综合，已经很不错了。宝子们，咋们下期见。。
往期专栏Java全栈开发数据结构与算法计算机组成原理操作系统数据库系统物联网控制原理与技术
|