基于该文章进行改进的项目:增加了连接数据库,和GUI增删改
address
项目效果展示
流程图
客户:登录页面
客户:购物页面
进入客户页面,并对其产品展示
客户:加入购物车页面
选择产品并点击加入购物车,弹出输入框
输入数量和购物车ID,点击提交就可以把数据加入数据库中的购物车明细表中;
(注意:这里的购物车ID是购物车明细表的外键,所以需要输入数据库有的数据,否则数据插入失败,所以这里是可以优化的地方)
客户:查看购物车页面
点击进入查看购物车
进入购物车页面,并点击下单(这里可以优化,增加删除功能,价格统计)
店主:登录页面
店主:管理页面
店主:添加商品分类页面
店主:商品进货页面
店主:用户管理页面
一、数据库部分
(1)数据库ER图
(2)建表
1.创建test 数据库
我采用mysql数据库,记得在创建的时候设置为 utf-8 ,否则默认格式不支持插入中文。
我的mysql登录账户:root,密码:admin(后面的jdbc连接需要提供账户密码)
create database test
2. 商品类型表
create table 商品类型表(
商品类型ID int primary key auto_increment,
类型名称 varchar(30)
);
3.商品表
create table 商品表(
商品ID int primary key auto_increment,
商品名称 varchar(30),
商品型号 varchar(30),
商品价格 int,
商品类型 int,
constraint PK_商品_商品类型 foreign key(商品类型) references 商品类型表(商品类型ID)
);
4.客户表
create table 客户表(
客户ID int primary key auto_increment,
姓名 varchar(30),
电话 varchar(30),
Emall varchar(30),
地址 varchar(30),
账户 varchar(30),
密码 varchar(30)
);
5.购物车表
在后面的功能实现中,我并没有用到这个表,本来要求去掉的,但嫌麻烦,建议可以增添些字段在这个表中
create table 购物车表(
购物车ID int primary key auto_increment,
日期 varchar(30)
);
6.购物车明细表
主要是用这个表
create table 购物车明细表(
购物车明细ID int primary key auto_increment,
数量 int,
商品ID int,
购物车ID int,
constraint PK_购物车明细_商品 foreign key(商品ID) references 商品表(商品ID),
constraint PK_购物车明细_购物车 foreign key(购物车ID) references 购物车表(购物车ID)
);
7.效果预览
(1)数据库test
(2)商品类型表
创建所有表后再去插入数据
(3)商品表
(4)客户表
(5)购物车表
(6)购物车明细表
创建项目:homework
添加包
二、创建类部分:bean包
bean包下放 对应数据库表字段的java文件
商品表
1.Product.java
package bean;
public class Product {
public int id;
public String name;
public String type;
public int price;
public int categoryId;
public int getCategoryId() {
return categoryId;
}
public void setCategoryId(int categoryId) {
this.categoryId = categoryId;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
}
2.Category.java
package bean;
public class Category {
public int id;
public String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
3.User.java
package bean;
public class User {
public int id;
public String zh;
public String mm;
public String getZh() {
return zh;
}
public void setZh(String zh) {
this.zh = zh;
}
public String getMm() {
return mm;
}
public void setMm(String mm) {
this.mm = mm;
}
public String name;
public String phoneNumber;
public String emall;
public String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getEmall() {
return emall;
}
public void setEmall(String emall) {
this.emall = emall;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
4.Order.java
package bean;
public class Order {
public int id;
public String date;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
}
5.OrderItem.java
package bean;
public class OrderItem {
public int id;
public int number;
public int productId;
public int orderId;
public int getProductId() {
return productId;
}
public void setProductId(int productId) {
this.productId = productId;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
}
三、连接数据库部分:dao包
dao 实现连接数据库,对数据库中的表进行操作
这里我对每个表都实现了增删改查,但是有些操作是本项目是没用到的,但最好也加上吧,方便拓展
1.ProductDAO.java
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.*;
public class ProductDAO {
public ProductDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8", "root",
"admin");
}
public int getTotal() {
int total = 0;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from 商品表";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Product product) {
String sql = "insert into 商品表 values(null,?,?,?,?)";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, product.name);
ps.setString(2, product.type);
ps.setInt(3, product.price);
ps.setInt(4,product.categoryId);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
product.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库语句执行出错");
}
}
public void update(Product product) {
//, 商品价格 = ?,商品类型=?
String sql = "update 商品表 set 商品名称= ?,商品型号=?, 商品价格 = ?,商品类型=? where 商品ID = ?";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1,product.name);
ps.setString(2, product.type);
ps.setInt(3,product.price);
ps.setInt(4, product.categoryId);
ps.setInt(5,product.id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库语句执行出错,可能是插入数据的类型字段有误");
}
}
public void delete(int id) {
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "delete from 商品表 where 商品id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库语句执行出错");
}
}
public Product get(int id) {
Product product = null;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select * from 商品表 where 商品id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
int id1 = rs.getInt(1);
String name = rs.getString(2);
String type=rs.getString(3);
int price=rs.getInt(4);
int categoryId=rs.getInt(5);
product.name = name;
product.type=type;
product.id = id1;
product.price = price;
product.categoryId = categoryId;
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库语句执行出错");
}
return product;
}
public List< Product> list() {
return list(0, Short.MAX_VALUE);
}
public List< Product> list(int start, int count) {
List< Product> products = new ArrayList< Product>();
String sql = "select * from 商品表 order by 商品id desc limit ?,? ";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Product product = new Product();
product.name = rs.getString(2);
product.type=rs.getString(3);
product.id = rs.getInt(1);
product.price = rs.getInt(4);
product.categoryId = rs.getInt(5);
products.add( product);
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("数据库语句执行出错");
}
return products;
}
}
2.CategoryDAO.java
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.Category;
public class CategoryDAO {
public CategoryDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8", "root",
"admin");
}
public int getTotal() {
int total = 0;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from 商品类型表";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Category category) {
String sql = "insert into 商品类型表 values(null,?)";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, category.name);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
category.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Category category) {
String sql = "update 商品类型表 set 类型名称= ? where 商品类型id = ?";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, category.name);
ps.setInt(2, category.id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "delete from 商品类型表 where 商品类型id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public Category get(int id) {
Category category = null;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select * from 商品类型表 where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
int id1 = rs.getInt(1);
String name = rs.getString(2);
category.name = name;
category.id = id1;
}
} catch (SQLException e) {
e.printStackTrace();
}
return category;
}
public List<Category> list() {
return list(0, Short.MAX_VALUE);
}
public List<Category> list(int start, int count) {
List<Category> categories = new ArrayList<Category>();
String sql = "select * from 商品类型表 order by 商品类型id desc limit ?,? ";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Category category = new Category();
int id = rs.getInt(1);
String name = rs.getString(2);
category.id = id;
category.name = name;
categories.add(category);
}
} catch (SQLException e) {
e.printStackTrace();
}
return categories;
}
}
3.UserDAO.java
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.*;
public class UserDAO {
public UserDAO() {