购物商城

约 4633 字大约 15 分钟

2026-04-04

购物商城

数据库设计

初始化表

-- 用户信息表（账号、密码、姓名、角色id、状态）
#userInformation（{4}user、password、uname、[1]roleid、status）
drop table if exists userInformation;
create table userInformation(
  `user` varchar(18) primary key,
  `password` varchar(255) not null,
  uname varchar(12) not null unique,
  roleid int not null,
  `status` int not null
)default charset=utf8;


-- 用户角色表（角色id、角色名称、菜单列表）
#userRole（[1]roleid、rolename、menulist）
drop table if exists userRole;
create table userRole(
  roleid int primary key,
  rolename varchar(12) not null unique,
  menulist varchar(255) not null
)default charset=utf8;

-- 商品信息表（{5}商品id、名称、类型id、品牌、价格、生产日期、失效日期）
#commodityInformation（commodityid、commodityname、[2]typeid、brand、price、productiondate、expirationdate）
drop table if exists commodityInformation;
create table commodityInformation(
  commodityid int auto_increment primary key,
  commodityname varchar(255) not null unique,
  typeid int not null,
  brand varchar(255) not null,
  price double(12,2) not null,
  productiondate date not null,
  expirationdate date not null
)default charset=utf8;


-- 商品类型表（类型id、类型名称）
#commodityTypes（[2]typeid、commoditypename）
drop table if exists commodityTypes;
create table commodityTypes(
  typeid int primary key,
  commoditypename varchar(255) not null unique
)default charset=utf8;

-- 购物车表（自编号、用户账号、商品id、商品名称、价格、数量、金额）
#shopCar（shopcarid、{4}user、{5}commodityid、commodityname、price、amount、money）
drop table if exists shopCar;
create table shopCar(
  shopcarid int auto_increment primary key,
  `user` varchar(18) not null unique,
  commodityid int not null,
  commodityname varchar(255) not null,
  price double(12,2) not null,
  amount int not null,
  money double(12,2) not null
)default charset=utf8;


-- 订单表（订单编号、总金额、下单日期、用户账号）
#order（[3]orderid、summoney、orderdate、{4}user）
drop table if exists `order`;
create table `order`(
  orderid int primary key,
  summoney double(12,2) not null,
  orderdate date not null,
  `user` varchar(18) not null
)default charset=utf8;

-- 订单明细表（自编号、订单编号、商品id、商品名称、价格、数量、金额）
#orderDetails（orderDetailsid、[3]orderid、{5}commodityid、commodityname、price、amount、money）
drop table if exists orderDetails;
create table orderDetails(
  orderDetailsid int auto_increment primary key,
  orderid int not null,
  commodityid int not null,
  commodityname varchar(255) not null,
  price double(12,2) not null,
  amount int not null,
  money double(12,2) not null
)default charset=utf8;

/*
# 关联用户角色表的角色id
alter table userInformation add constraint fk_roleid foreign key(roleid) references userRole(userRole);

# 关联商品类型表的类型id
alter table commodityInformation add constraint fk_typeid foreign key(typeid) references commodityTypes(typeid);

# 关联用户信息表的用户名
alter table shopCar add constraint fk_user foreign key(`user`) references userInformation(`user`);
# 关联商品信息表的商品id
alter table shopCar add constraint fk_commodityid foreign key(commodityid) references commodityInformation(commodityid);


# 关联购物车表的用户名
alter table `order` add constraint fk_user foreign key(`user`) references shopCar(`user`);

# 关联购物车表的商品id
alter table orderDetails add constraint fk_commodityid foreign key(commodityid) references shopCar(commodityid);
*/

初始化数据

/*用户角色表（角色id、角色名称、菜单列表）
0 超级管理员
1 卖家
2 买家
3 黑名单
userRole（[1]roleid、rolename、menulist）*/
insert into userRole values(0,'超级管理员','商品管理|订单管理|用户管理|商品列表|购物|个人信息管理');
insert into userRole values(1,'商家','商品管理|订单管理|商品列表|个人信息管理');
insert into userRole values(2,'买家','商品列表|购物|个人信息管理');

/*用户信息表（账号、密码、姓名、角色id、状态）
userInformation（[4]user、password、uname、[1]roleid、status）*/
insert into userInformation values('root','root','马化腾',0,1);
insert into userInformation values('admin1','123456','苹果',1,1);
insert into userInformation values('admin2','123456','鸿星尔克',1,1);
insert into userInformation values('admin3','123456','妮维雅',1,1);
insert into userInformation values('buyer1','123','张三',2,1);
insert into userInformation values('buyer2','123','李四',2,1);
insert into userInformation values('buyer3','123','王五',2,1);

/*商品类型表（类型id、类型名称）
commodityTypes（[2]typeid、commoditypename）*/
insert into commodityTypes values(1001,'数码');
insert into commodityTypes values(1002,'服装');
insert into commodityTypes values(1003,'洗护');

/*商品信息表（商品id、名称、类型id、品牌、价格、生产日期、失效日期）
commodityInformation（[3]commodityid、commodityname、[2]typeid、brand、price、productiondate、expirationdate）*/
insert into commodityInformation values(default,'iPhoneX',1001,'苹果',8900,date_sub(now(),interval 1 year),date_add(now(),interval 99 year));
insert into commodityInformation values(default,'iPad2021',1001,'苹果',2890,date_sub(now(),interval 90 day),date_add(now(),interval 90 year));
insert into commodityInformation values(default,'AirPods',1001,'苹果',1200,date_sub(now(),interval 2 year),date_add(now(),interval 20 year));

insert into commodityInformation values(default,'t恤',1002,'鸿星尔克',120.5,date_sub(now(),interval 60 day),date_add(now(),interval 10 year));
insert into commodityInformation values(default,'黑裤子',1002,'鸿星尔克',200,date_sub(now(),interval 70 day),date_add(now(),interval 10 year));
insert into commodityInformation values(default,'鞋子',1002,'鸿星尔克',230,date_sub(now(),interval 80 day),date_add(now(),interval 10 year));

insert into commodityInformation values(default,'洗面奶',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year));
insert into commodityInformation values(default,'护发素',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year));
insert into commodityInformation values(default,'眼霜',1001,'妮维雅',8900,date_sub(now(),interval 90 day),date_add(now(),interval 1 year));

select * from userRole;
select * from userInformation;
select * from commodityTypes
select * from commodityInformation;

具体实现

pojo 数据库映射表

package com.onlinestore.pojo;

import lombok.*;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 数据库映射表-用户表 类描述
 * @create 2021-09-01 16:59
 */
@Data
@NoArgsConstructor
@AllArgsConstructor

public class UserInformation {
    private String user;
    private String password;
    private String uname;
    private Integer roleid;
    private Integer status;
}
package com.onlinestore.pojo;

import lombok.*;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 用户角色表 类描述
 * @create 2021-09-01 17:06
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserRole {
    private Integer roleid;
    private String rolename;
    private String menulist;
}
package com.onlinestore.pojo;

import lombok.*;

import java.util.Date;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 商品信息映射 类描述
 * @create 2021-09-01 17:07
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class CommodityInformation {
    private Integer commodityid;
    private String commodityname;
    private Integer typeid;
    private String brand;
    private Double price;
    private Date productiondate;
    private Date expirationdate;


    @Override
    public String toString() {
        return
                "商品id=" + commodityid +
                ", 名称='" + commodityname + '\'' +
                ", 类型id=" + typeid +
                ", 品牌='" + brand + '\'' +
                ", 价格=" + price +
                ", 生产日期=" + productiondate +
                ", 失效日期=" + expirationdate;
    }
}
package com.onlinestore.pojo;

import lombok.*;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 商品类型映射 类描述
 * @create 2021-09-01 17:09
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class CommodityTypes {
    private Integer typeid;
    private String commoditypename;
}
package com.onlinestore.pojo;

import lombok.*;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 购物车映射 类描述
 * @create 2021-09-01 17:10
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ShopCar {
    private Integer shopcarid;
    private String user;
    private Integer commodityid;
    private String commodityname;
    private Double price;
    private Integer amount;
    private Double money;
    @Override
    public String toString() {
        return
                "编号=" + shopcarid +
                ", 用户账号='" + user + '\'' +
                ", 商品id=" + commodityid +
                ", 商品名称='" + commodityname + '\'' +
                ", 价格=" + price +
                ", 数量=" + amount +
                ", 金额=" + money;
    }
}
package com.onlinestore.pojo;

import lombok.*;

import java.util.Date;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 订单表映射 类描述
 * @create 2021-09-01 17:13
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Order {
    private String orderid;
    private Double summoney;
    private Date orderdate;
    private String user;



    @Override
    public String toString() {
        return
                "订单编号=" + orderid +
                ", 总金额=" + summoney +
                ", 下单日期=" + orderdate +
                ", 用户账号='" + user + '\'' ;
    }
}
package com.onlinestore.pojo;

import lombok.*;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 订单明细表映射 类描述
 * @create 2021-09-01 17:14
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class OrderDetails {
    private Integer orderDetailsid;
    private String orderid;
    private Integer commodityid;
    private String commodityname;
    private Double price;
    private Integer amount;
    private Double money;


    @Override
    public String toString() {
        return
                "订单明细编号=" + orderDetailsid +
                        ", 订单编号=" + orderid +
                        ", 商品id=" + commodityid +
                        ", 商品名称='" + commodityname + '\'' +
                        ", 价格=" + price +
                        ", 数量=" + amount +
                        ", 金额=" + money;
    }
}

dao接口数据访问层

package com.onlinestore.dao;

import com.onlinestore.pojo.UserInformation;
import com.onlinestore.pojo.UserRole;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;
import java.util.List;

public interface UserInformationDao {
    // 注册
    public void register(UserInformation userInformation) throws SQLException;
    // 登录
    public Integer login(String user,String password) throws SQLException;
    // 获取用户状态
    public Integer getState(String user) throws SQLException;
    // 获取用户操作菜单
    public String[] getMenu(String account) throws SQLException;
}
package com.onlinestore.dao;

import com.onlinestore.pojo.ShopCar;

import java.sql.SQLException;
import java.util.List;

public interface ShopCarDao {
    // 加入购物车
    public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException;

    // 展示购物车
    public List<ShopCar> queryUserShopCar(String account) throws SQLException;

    // 提交订单后，删除指定用户的购物车记录
    public Integer deleteUserShopCar(String account) throws SQLException;
}
package com.onlinestore.dao;

import com.onlinestore.pojo.OrderDetails;

import java.sql.SQLException;
import java.util.List;

public interface OrderDetailsDao {
    public Integer addOrderDetails(String account) throws SQLException;
    public List<OrderDetails> queryAllOrderDetails(String account) throws SQLException;
}
package com.onlinestore.dao;

import com.onlinestore.pojo.Order;

import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;

public interface OrderDao {
    // 提交订单
    public Integer  updateOrder(String user) throws SQLException, ParseException;
    // 查看用户订单
    public List<Order> queryUserOrder(String user) throws SQLException;
}
package com.onlinestore.dao;

import com.onlinestore.pojo.CommodityInformation;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public interface CommodityInformationDao {
    // 查询所有商品
    public List<CommodityInformation> queryAll() throws SQLException;
    // 查询所有商品编号
    public Map<String,Integer> quaryAllCommodityId(Integer commodityid) throws SQLException;
}

impl dao接口实现类

package com.onlinestore.dao.impl;

import com.onlinestore.dao.UserInformationDao;
import com.onlinestore.pojo.UserInformation;
import com.onlinestore.pojo.UserRole;
import com.onlinestore.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 用户注册和登录 类描述
 * @create 2021-09-01 17:26
 */
public class UserInformationDaoImpl implements UserInformationDao {

    private QueryRunner runner;

    public UserInformationDaoImpl() {
        runner = JDBCUtils.getQueryRunner();
    }


    // 注册
    @Override
    public void register(UserInformation userInformation) throws SQLException {
        String sql = "insert into userInformation values(?,?,?,?,?)";
        Object[] params = {
                userInformation.getUser(),
                userInformation.getPassword(),
                userInformation.getUname(),
                userInformation.getRoleid(),
                userInformation.getStatus()
        };
        runner.update(sql, params);
    }

    @Override
    public Integer login(String user, String password) throws SQLException {
        Integer result = 0;

        String sql = "select count(*) from userInformation where `user`=? and `password`=?";
        Object[] params = {user, password};
        ScalarHandler<Long> handler = new ScalarHandler<>();
        Long count = runner.query(sql, handler, params);
        Integer state = this.getState(user);
        if (count == 1) {
            result = 1; // 成功
        } else if (state == 0) {
            result = 0; // 失败
        } else {
            result = -1;// 禁用-1 成功1 失败0
        }
        return result;
    }

    // 查询用户状态 1启用 0禁用
    @Override
    public Integer getState(String account) throws SQLException {
        String sql = "select `status` from userInformation where user=?";
        Object[] prams = {account};
        ScalarHandler<Integer> handler = new ScalarHandler<>();
        Integer state = runner.query(sql, handler, prams);
        if (state == null) {
            return 0;
        }
        return state;
    }
    // 获取用户菜单
    @Override
    public String[] getMenu(String account) throws SQLException {
        String sql = "select `menulist` from userrole where `roleid`=(select `roleid` from userInformation where `user`=?)";
        Object[] params = {account};
        ScalarHandler<String> handler = new ScalarHandler();
        String query = runner.query(sql, handler, params);
        return query.split("\\|");
    }
}
package com.onlinestore.dao.impl;

import com.onlinestore.dao.ShopCarDao;
import com.onlinestore.pojo.CommodityInformation;
import com.onlinestore.pojo.ShopCar;
import com.onlinestore.pojo.UserInformation;
import com.onlinestore.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 购物车 类描述
 * @create 2021-09-02 15:40
 */
public class ShopCarDaoImpl implements ShopCarDao {
    private QueryRunner runner;

    public ShopCarDaoImpl() {
        runner = JDBCUtils.getQueryRunner();
    }
    // 购物车添加
    @Override
    public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException {
        String sql = "select * from commodityInformation where commodityid=?";
        Object[] params = {no};
        BeanHandler<CommodityInformation> handler = new BeanHandler<>(CommodityInformation.class);
        CommodityInformation query = runner.query(sql, handler, params);

        String updatesql = "insert into `shopCar` values(default,?,?,?,?,?,?)";
        Object[] updateParams = {
                account,
                no,
                query.getCommodityname(),
                query.getPrice(),
                count,
                query.getPrice() * count
        };
        int updateFlag = runner.update(updatesql, updateParams);
        return updateFlag;
    }
    // 查询用户购物车
    @Override
    public List<ShopCar> queryUserShopCar(String account) throws SQLException {
        String sql = "select * from shopCar where `user`=?";
        Object[] params={account};
        BeanListHandler<ShopCar> handler = new BeanListHandler<>(ShopCar.class);
        List<ShopCar> query = runner.query(sql, handler,params);
        return query;
    }
    // 提交订单后删除购物车记录
    @Override
    public Integer deleteUserShopCar(String account) throws SQLException {
        String sql="delete from shopCar where `user`=?";
        Object[] params={account};
        int updateFlag = runner.update(sql, params);
        return updateFlag;
    }
}
package com.onlinestore.dao.impl;

import com.onlinestore.dao.OrderDetailsDao;
import com.onlinestore.pojo.Order;
import com.onlinestore.pojo.OrderDetails;
import com.onlinestore.pojo.ShopCar;
import com.onlinestore.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Array;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: TODO 类描述
 * @create 2021-09-03 11:05
 */
public class OrderDetailsDaoImpl implements OrderDetailsDao {
    private QueryRunner runner;

    public OrderDetailsDaoImpl() {
        runner = JDBCUtils.getQueryRunner();
    }

    // 生成订单详细表
    @Override
    public Integer addOrderDetails(String account) throws SQLException {
        String polymericQuerySql = "select o.orderid,s.commodityid,s.commodityname,s.price,s.amount,s.money from `shopCar` s inner join `order` o on s.`user`=o.`user` where o.`user`=?";
        Object[] polymericQueryParams = {account};
        BeanListHandler<OrderDetails> handler = new BeanListHandler<>(OrderDetails.class);
        // 获取订单详细信息映射
        List<OrderDetails> queryDetails = runner.query(polymericQuerySql, handler, polymericQueryParams);

        String insertSql = null;
        Object[] insertSqlParams = null;
        int updateFlag = 0;
        // 循环插入数据给订单详细表
        for (OrderDetails queryDetail : queryDetails) {
            insertSql = "insert into `orderdetails` values(default,?,?,?,?,?,?)";
            insertSqlParams = new Object[]{
                    queryDetail.getOrderid(),
                    queryDetail.getCommodityid(),
                    queryDetail.getCommodityname(),
                    queryDetail.getPrice(),
                    queryDetail.getAmount(),
                    queryDetail.getMoney()
            };
            updateFlag += runner.update(insertSql, insertSqlParams);
        }
        return updateFlag;
    }

    // 查询订单明细 error
    @Override
    public List<OrderDetails> queryAllOrderDetails(String account) throws SQLException {
//        List<Order> orders = new OrderDaoImpl().queryUserOrder(account);
//        String sql = "";
//        Object[] params={};
//        List<List<OrderDetails>> lists= new ArrayList<>();
//        BeanListHandler<OrderDetails> handler=new BeanListHandler<>(OrderDetails.class);
//        for (Order order : orders) {
//            sql = "select * from `order` where `orderid`=?";
//            params= new Object[]{order.getOrderid()};
//            System.out.println(params); // 空
//            System.out.println(order);
//            List<OrderDetails> query = runner.query(sql, handler, params);
//            lists.add(query);
//        }
        String sql= "select *from `order` o inner join `orderdetails` ord on o.orderid=ord.orderid";
        BeanListHandler<OrderDetails> handler=new BeanListHandler<>(OrderDetails.class);
        List<OrderDetails> query = runner.query(sql, handler);
        return query;
    }
}
package com.onlinestore.dao.impl;

import com.onlinestore.dao.OrderDao;
import com.onlinestore.pojo.Order;
import com.onlinestore.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.naming.spi.ObjectFactoryBuilder;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.logging.SimpleFormatter;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 订单表 类描述
 * @create 2021-09-02 17:19
 */
public class OrderDaoImpl implements OrderDao {
    private QueryRunner runner;

    public OrderDaoImpl() {
        runner = JDBCUtils.getQueryRunner();
    }

    // 根据用户信息,提交订单
    @Override
    public Integer updateOrder(String user) throws SQLException, ParseException {
        String sumMoneysql = "select sum(`money`) from shopCar where `user`=?";
        ScalarHandler<Double> handler = new ScalarHandler<>();
        Object[] params_sum = {
                user
        };
        Double sumMoney = runner.query(sumMoneysql, handler, params_sum);
        // 查找不到记录，返回0
        if (sumMoney == null || sumMoney == 0.0) {
            return 0;
        }

        String orederDateTime = (new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())).concat(String.valueOf(Math.round(new Random().nextInt(900)+100)));
        Long orederDateTimeLong=Long.parseLong(orederDateTime);
        Object[] params_insert = {
                orederDateTimeLong, sumMoney, new SimpleDateFormat("yy-MM-dd HH:mm:ss").format(new Date()), user // 订单日期
        };
        String insertSql = "insert into `order` values(?,?,?,?)";
        int updateFlag = runner.update(insertSql, params_insert);
        return updateFlag;
    }

    // 查询订单信息
    @Override
    public List<Order> queryUserOrder(String user) throws SQLException {
        String sql = "select * from `order` where `user`=?";
        BeanListHandler<Order> handler = new BeanListHandler<>(Order.class);
        Object[] params = {user};
        List<Order> query = runner.query(sql, handler, params);
        return query;
    }
}
package com.onlinestore.dao.impl;

import com.onlinestore.dao.CommodityInformationDao;
import com.onlinestore.pojo.CommodityInformation;
import com.onlinestore.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 展示商品列表 类描述
 *  删除 商品id 自编号 使用truncate
 * @create 2021-09-02 14:55
 */
public class CommodityInformationDaoImpl implements CommodityInformationDao {
    private QueryRunner runner;

    public CommodityInformationDaoImpl() {
        runner = JDBCUtils.getQueryRunner();
    }

    @Override
    public List<CommodityInformation> queryAll() throws SQLException {
        String sql = "select * from commodityInformation";
        BeanListHandler<CommodityInformation> handler = new BeanListHandler<>(CommodityInformation.class);
        List<CommodityInformation> list = runner.query(sql, handler);
        return list;
    }

    @Override
    public Map<String, Integer> quaryAllCommodityId(Integer orderid) throws SQLException {
        String sql = "select `commodityid` from commodityInformation";
        MapListHandler handler = new MapListHandler();
        List<Map<String, Object>> query = runner.query(sql, handler);
        Map<String, Integer> returnMap = new HashMap<>();
        for (int i = 0; i < query.size(); i++) {
            for (String s : query.get(i).keySet()) {
                // 查找key加上随机数，value为商品编号
                returnMap.put(s.concat(String.valueOf(new Random().nextInt())), Integer.parseInt(query.get(i).get(s).toString()));
            }
        }
        return returnMap;
    }
}

service接口业务逻辑层

package com.onlinestore.service;

import com.onlinestore.pojo.UserInformation;

import java.sql.SQLException;
import java.util.List;

public interface UserInformationService {
    public void register(UserInformation userInformation) throws SQLException;
    public Integer login(String user,String password) throws SQLException;
    public String[] getMenu(String account) throws SQLException;
}
package com.onlinestore.service;

import com.onlinestore.pojo.ShopCar;

import java.sql.SQLException;
import java.util.List;

public interface ShopCarService {
    public Integer updateShopCar(Integer no,String account,Integer count) throws SQLException;
    public List<ShopCar> queryUserShopCar(String account) throws SQLException;
    public Integer deleteUserShopCar(String account) throws SQLException;

}
package com.onlinestore.service;

import com.onlinestore.pojo.Order;

import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;

public interface OrderService {
    public Integer updateOrder(String user) throws SQLException, ParseException;
    public List<Order> queryUserOrder(String user) throws SQLException;
}
package com.onlinestore.service;

import com.onlinestore.pojo.OrderDetails;

import java.sql.SQLException;
import java.util.List;

public interface OrderDetailsService {
    public Integer addOrderDetails(String account) throws SQLException;
    public List<OrderDetails> queryAllOrderDetails(String account) throws SQLException;
}
package com.onlinestore.service;

import com.onlinestore.pojo.CommodityInformation;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public interface CommodityInformationService {
    public List<CommodityInformation> queryAll() throws SQLException;
    public Map<String, Integer> quaryAllCommodityId(Integer commodityid) throws SQLException;
}

impl service接口实现类

package com.onlinestore.service.impl;

import com.onlinestore.dao.UserInformationDao;
import com.onlinestore.dao.impl.UserInformationDaoImpl;
import com.onlinestore.pojo.UserInformation;
import com.onlinestore.service.UserInformationService;

import java.sql.SQLException;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 用户逻辑层 用户登录和注册 类描述
 * @create 2021-09-01 17:27
 */
public class UserInformationServiceImpl implements UserInformationService {
    private UserInformationDao userInformationDao;

    public UserInformationServiceImpl() {
        userInformationDao = new UserInformationDaoImpl();
    }

    @Override
    public void register(UserInformation ui) throws SQLException {
        userInformationDao.register(ui);
    }

    @Override
    public Integer login(String user, String password) throws SQLException {
        Integer loginFlag = userInformationDao.login(user, password);
        return loginFlag;
    }

    @Override
    public String[] getMenu(String account) throws SQLException {
        return userInformationDao.getMenu(account);
    }
}
package com.onlinestore.service.impl;

import com.onlinestore.dao.ShopCarDao;
import com.onlinestore.dao.impl.ShopCarDaoImpl;
import com.onlinestore.pojo.ShopCar;
import com.onlinestore.service.ShopCarService;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: TODO 类描述
 * @create 2021-09-02 15:56
 */
public class ShopCarServiceImpl implements ShopCarService {
    private ShopCarDao shopCarDao;

    public ShopCarServiceImpl() {
        shopCarDao = new ShopCarDaoImpl();
    }

    @Override
    public Integer updateShopCar(Integer no, String account, Integer count) throws SQLException {
        return shopCarDao.updateShopCar(no, account, count);
    }

    @Override
    public List<ShopCar> queryUserShopCar(String account) throws SQLException {
        return shopCarDao.queryUserShopCar(account);
    }

    @Override
    public Integer deleteUserShopCar(String account) throws SQLException {
        return shopCarDao.deleteUserShopCar(account);
    }
}
package com.onlinestore.service.impl;

import com.onlinestore.dao.OrderDao;
import com.onlinestore.dao.impl.OrderDaoImpl;
import com.onlinestore.pojo.Order;
import com.onlinestore.service.OrderService;

import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: TODO 类描述
 * @create 2021-09-02 19:02
 */
public class OrderServiceImpl implements OrderService {
    private OrderDao orderDao;

    public OrderServiceImpl() {
        orderDao = new OrderDaoImpl();
    }

    @Override
    public Integer updateOrder(String user) throws SQLException, ParseException {
        return orderDao.updateOrder(user);
    }

    @Override
    public List<Order> queryUserOrder(String user) throws SQLException {
        return orderDao.queryUserOrder(user);
    }
}
package com.onlinestore.service.impl;

import com.onlinestore.dao.OrderDetailsDao;
import com.onlinestore.dao.impl.OrderDetailsDaoImpl;
import com.onlinestore.pojo.OrderDetails;
import com.onlinestore.service.OrderDetailsService;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: TODO 类描述
 * @create 2021-09-03 11:44
 */
public class OrderDetailsServiceImpl implements OrderDetailsService {
    private OrderDetailsDao orderDetails;

    public OrderDetailsServiceImpl() {
        orderDetails = new OrderDetailsDaoImpl();
    }

    @Override
    public Integer addOrderDetails(String account) throws SQLException {
        return orderDetails.addOrderDetails(account);
    }

    @Override
    public List<OrderDetails> queryAllOrderDetails(String account) throws SQLException {
        return orderDetails.queryAllOrderDetails(account);
    }
}
package com.onlinestore.service.impl;

import com.onlinestore.dao.CommodityInformationDao;
import com.onlinestore.dao.impl.CommodityInformationDaoImpl;
import com.onlinestore.pojo.CommodityInformation;
import com.onlinestore.service.CommodityInformationService;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: TODO 类描述
 * @create 2021-09-02 15:00
 */
public class CommodityInformationServiceImpl implements CommodityInformationService {
    private CommodityInformationDao commodityInformationDao;

    public CommodityInformationServiceImpl() {
        commodityInformationDao = new CommodityInformationDaoImpl();
    }

    @Override
    public List<CommodityInformation> queryAll() throws SQLException {
        return commodityInformationDao.queryAll();
    }

    @Override
    public Map<String, Integer> quaryAllCommodityId(Integer commodityid) throws SQLException {
        return commodityInformationDao.quaryAllCommodityId(commodityid);
    }
}

utils 数据库工具类

package com.onlinestore.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Properties;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 数据库操作工具类 类描述
 * @create 2021-09-01 17:18
 */
public class JDBCUtils {
    private static DataSource ds;
    static {
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        }
        catch (Exception e){
            ds = null;
        }
    }

    private static Connection getConnection(){
        Connection con;
        try {
            con = ds.getConnection();
        }
        catch (Exception ex){
            con = null;
        }
        return con;
    }
    /* 获取 QueryRunner */
    public static QueryRunner getQueryRunner(){
        QueryRunner runner = new QueryRunner(ds);
        return runner;
    }

    public static CallableStatement getStatement(String sql) throws Exception{
        CallableStatement call = getConnection().prepareCall(sql);
        return call;
    }

}

ui 测试类

package com.onlinestore.ui;

import com.onlinestore.dao.UserInformationDao;
import com.onlinestore.pojo.*;
import com.onlinestore.service.*;
import com.onlinestore.service.impl.*;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import org.junit.Test;

import javax.management.relation.InvalidRoleValueException;

/**
 * @author 涂鏊飞tu_aofei@163.com
 * @description: 测试类 类描述
 * @create 2021-09-01 17:27
 */
public class TestDemo {
    private static Scanner in;
    private static UserInformationService service;
    private static CommodityInformationService service_commodity;
    private static ShopCarService shopCarService;
    private static OrderService orderService;
    private static OrderDetailsService orderDetailsService;

    static {
        in = new Scanner(System.in);
        service = new UserInformationServiceImpl();
        service_commodity = new CommodityInformationServiceImpl();
        shopCarService = new ShopCarServiceImpl();
        orderService = new OrderServiceImpl();
        orderDetailsService = new OrderDetailsServiceImpl();
    }

    @Test
    public void testDemo() {
        new TestDemo().start();
    }

    private void start() {
        System.out.println("1. 登录 | 2. 注册");
        System.out.println("请选择：");
        String choose = in.nextLine();
        if (choose.equals("1")) {
            login();
        } else if (choose.equals("2")) {
            System.out.println("1. 注册买家");
            System.out.println("2. 注册商家");
            String chooseTwoMenu = in.nextLine();
            if (chooseTwoMenu.equals("1")) {
                registerBuyer();
            } else if (chooseTwoMenu.equals("2")) {
                registerBusiness();
            } else if (!choose.equals("1") || !choose.equals("2")) {
                throw new InputMismatchException("输入类型不匹配，请输入正确的数字！");
            }
        } else if (!choose.equals("1") || !choose.equals("2")) {
            throw new InputMismatchException("输入类型不匹配，请输入正确的数字！");
        }
    }

    private static void registerBuyer() {
        try {
            System.out.println("请输入新账号：");
            String account = in.next();
            System.out.println("请输入密码：");
            String pwd = in.next();
            System.out.println("请输入姓名：");
            String uname = in.next();
            service.register(new UserInformation(account, pwd, uname, 1, 1));
            System.out.println("买家注册成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void registerBusiness() {
        try {
            System.out.println("请输入新账号：");
            String account = in.next();
            System.out.println("请输入密码：");
            String pwd = in.next();
            System.out.println("请输入姓名：");
            String uname = in.next();
            service.register(new UserInformation(account, pwd, uname, 2, 1));
            System.out.println("商家注册成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void login() {
        try {
            System.out.println("请输入账号：");
            String account = in.next();
            System.out.println("请输入密码：");
            String pwd = in.next();
            Integer loginFlag = service.login(account, pwd);
            if (loginFlag == 1) {
                System.out.println("登陆成功!");
                flow(account);
            } else if (loginFlag == 0) {
                System.out.println("账号或密码错误!");
            } else {
                System.out.println("您已被禁用!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //执行流程
    private static void flow(String account) throws Exception {
        showHomeMenu(account);
        System.out.println("请选择菜单项[1 退出]：");
        String commChoose = in.next();
        switch (commChoose) {
            case "商品列表":
                showCommodityList();
                shopping(account);
                // 购物
                System.out.println("选购完毕！，您的购物车的商品如下：");
                queryShopCar(account);
                System.out.println("是否结算?[y/n]");
                commitOrder(account); //提交订单
                break;
            case "查看购物车":
                System.out.println("您的购物车的商品如下：");
                queryShopCar(account);
                System.out.println("是否结算?[y/n]");
                commitOrder(account); //提交订单
                break;
            case "个人信息管理":
                break;
            case "1":
                System.exit(1);
            default:
                throw new InputMismatchException("输入类型不匹配，请输入正确的字符串！");
        }
    }

    // 提交订单
    private static void commitOrder(String account) throws Exception {
        String settleAccounts = in.next();
        if (settleAccounts.equals("y") || settleAccounts.equals("Y")) {
            //结算
            Integer orderInsertFlag = orderService.updateOrder(account);//提交订单
            if (orderInsertFlag > 0) {
                System.out.println("订单已提交，订单数目:" + orderInsertFlag);
            } else {
                System.out.println("您的购物车为空！，无法生成订单！");
            }
            // 生成订单明细表
            Integer orderDetailsUpdateFlag = orderDetailsService.addOrderDetails(account);
            if (orderDetailsUpdateFlag > 0) {
                System.out.println("订单明细已生成，数目:" + orderDetailsUpdateFlag);
            }
            // 删除购物车记录
            Integer deleteUserShopCarFlag = shopCarService.deleteUserShopCar(account);
            if (deleteUserShopCarFlag > 0) {
                System.out.println("用户" + account + "购物车记录已删除，更新记录条数:" + deleteUserShopCarFlag);
            }
            // 查看订单
            queryOrder(account);

        } else {
            flow(account);
        }
    }

    // 查看订单，明细表
    private static void queryOrder(String account) throws Exception {
        System.out.println("1. 查看您的所有订单");
        System.out.println("2. 查看您的所有订单明细");
        System.out.println("3. 返回主界面");
        Integer orderChoose = in.nextInt();
        switch (orderChoose) {
            case 1:
                System.out.println("已提交的订单如下：");
                List<Order> orderList = orderService.queryUserOrder(account);
                for (Order order : orderList) {
                    System.out.println(order);
                }
                queryOrder(account);// 回调
                break;
            case 2:
                System.out.println("已提交的订单详情如下：");
                List<OrderDetails> lists = orderDetailsService.queryAllOrderDetails(account);
                for (OrderDetails list : lists) {
                    System.out.println(list);
                }
                queryOrder(account);// 回调
                break;
            case 3:
                flow(account); // 返回主页面
                break;
            default:
                System.out.println("输入错误！，请重新输入：");
                queryOrder(account);
        }


    }

    // 查看购物车
    private static void queryShopCar(String account) throws SQLException {
        List<ShopCar> shopCars = shopCarService.queryUserShopCar(account);
        shopCars.forEach(System.out::println);
    }

    // 购买商品
    private static void shopping(String account) throws Exception {
        while (true) {
            System.out.println("请选择中意的商品编号...");
            Integer no = in.nextInt();
            Map<String, Integer> maps = service_commodity.quaryAllCommodityId(no);
            if (maps.isEmpty() || !maps.containsValue(no)) {
//                throw new NullPointerException("商品编号不存在！");
                System.out.println("商品编号不存在！");
                shopping(account); // 继续购物
            }
            System.out.println("请选择要购买的商品数量...");
            Integer count = in.nextInt();
            Integer flag = shopCarService.updateShopCar(no, account, count);
            System.out.println("加入购物车" + flag + "件商品");
            System.out.println("是否继续购物[y/n]");
            String continues = in.next();
            if (!(continues.equals("y") || continues.equals("Y"))) {
                break;
            }
            showCommodityList();
        }
    }

    // 展示首页
    private static void showHomeMenu(String account) throws SQLException {
        String[] menu = service.getMenu(account);
        System.out.println("---------------------------------------");
        System.out.println("-----------欢迎来到网购系统首页----------");
        for (int i = 0; i < menu.length; i++) {
            System.out.println((i + 1) + "." + menu[i]);
        }
        System.out.println("---------------------------------------");
    }

    // 商品列表
    private static void showCommodityList() throws SQLException {
        List<CommodityInformation> commodityInformations = service_commodity.queryAll();
        for (CommodityInformation ci : commodityInformations) {
            System.out.println("------------------------------------------------------------------------------------------------------------------");
            System.out.println(ci);
        }
    }
}

贡献者

xiaofei

更新日志

2026/4/5 03:39

查看所有更新日志

fb8bc-更新为vuepress于 2026/4/5