项目简介

使用Java分层和JDBC功能实现命令行简易购物车功能，
管理员登录对所有用户、商品信息、商品类型的操作，管理员操作时会有管理员日志表来存储管理员的所有操作行为，管理员对该表无法操作
普通用户可注册或登录，登录后可添加指定商品到购物车，对购物车的商品进行增删改查操作，结账功能，结账后将明细存在订单表里

环境需要

1.运行环境：最好是java jdk 1.8，我们在这个平台上运行的。其他版本理论上也可以。
2.IDE环境：IDEA，Eclipse,Myeclipse都可以。推荐IDEA，这里使用IDEA作为演示;
3.硬件环境：windows 7/8/10 1G内存以上；或者 Mac OS； 
4.数据库：MySql 8.0版本；
5. JDBC8.0的jar包
下载地址：https://dev.mysql.com/downloads/connector/j/

在这里插入图片描述

数据库文件

-- 管理员表
CREATE TABLE `admin` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(50),
  `password` varchar(50)
);

-- 管理员日志表
create table adminlog(
	id int primary key auto_increment,
	aid int,
	`desc` varchar(255),
	time date,
	constraint FK_Adminlog_Admin foreign key(aid) references admin(id)
);

-- 用户表
CREATE TABLE `users`  (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `uname` varchar(50),
  `upwd` varchar(50),
  `ustatus` int(11),
  `uscore` int(11)
);

-- 商品类型表
CREATE TABLE `goodstype`  (
  `typeid` int(11) PRIMARY KEY AUTO_INCREMENT,
  `typename` varchar(50)
);

-- 商品表
CREATE TABLE `goods`  (
  `gid` int(11) PRIMARY KEY AUTO_INCREMENT,
  `gname` varchar(50),
  `gnum` int(11),
  `gprice` double,
  `typeid` int(11),
  CONSTRAINT `FK_Goods_GoodsType` FOREIGN KEY (`typeid`) REFERENCES `goodstype`(`typeid`)
);

-- 购物车表
CREATE TABLE `cart`  (
  `cid` int(11) PRIMARY KEY AUTO_INCREMENT,
  `goodsid` int(11),
  `cnum` int(11),
  `userid` int(11),
  CONSTRAINT `FK_Cart_Goods` FOREIGN KEY (`goodsid`) REFERENCES `goods` (`gid`),
  CONSTRAINT `FK_Cart_Users` FOREIGN KEY (`userid`) REFERENCES `users` (`id`)
);

-- 订单表
create table shopdetail(
	id int primary key auto_increment,
	uid int,
	cost DOUBLE,
	time date,
	constraint FK_Shopdetail_Users foreign key(uid) references users(id) 
);

Java文件

Benas

注意：为了方便操作，在定义实体类时属性名称应和数据库列名相同
Beans主要就是get和set方法

1、管理员类

public class Admin {
    private Integer id;
    private String username;
    private String password;

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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;
    }
}

其余和第一个类似，保持数据库的列名和成员变量名称一致即可

2、用户类
3、商品类型类
4、商品类
5、购物车类
6、管理员日志类
7、订单明细类

工具类

1、 JDBCUtils
通过properties文件来获取jdbc连接所需的信息

public class JDBCUtils {
    public static String driver;
    public static String url;
    public static String username;
    public static String password;

    static {
        try {
            InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    public String change(String label) {
        String newLabel = label;
        int index = label.indexOf('_');
        if (index != -1) {
            newLabel = label.substring(0, index) + label.substring(index + 1, index + 2).toUpperCase() + label.substring(index + 2);
        }
        return newLabel;
    }

    public int executeUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = -1;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            setParams(pstmt, params);
            rows = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, null);
        }
        return rows;
    }

    public void setParams(PreparedStatement pstmt, Object... params) {
        try {
            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public <T> T findOneInstance(Class<T> clazz, String sql, Object...params) {
        List<T> list = executeQuery(clazz, sql, params);
        if (list != null && list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    public <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) {
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            setParams(pstmt, params);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                T entity = clazz.newInstance();
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String label = rsmd.getColumnLabel(i);
                    Object val = rs.getObject(label);
                    String type = rsmd.getColumnClassName(i);
                    String newLabel = change(label);
                    Field f = clazz.getDeclaredField(newLabel);
                    f.setAccessible(true);
                    // 对日期类型进行特殊判断
                    if (type.contains("Date")) {
                        Class type2 = f.getType();
                        // 如果类中的成员变量也为Date则直接赋值 否则转换为String再赋值
                        if (type2.toString().contains("Date")) {
                            f.set(entity, val);
                        } else {
                            if (null != val) {
                                Date date = (java.sql.Date) val;
                                f.set(entity, date.toString());
                            }
                        }
                    } else {
                        f.set(entity, val);
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, rs);
        }
        return list;
    }
}

properties文件内容

driver:com.mysql.cj.jdbc.Driver
url:jdbc:mysql:///你的数据库名称?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
username:你的用户名
password:你的密码

2、常量类
用来保存一些常量，避免赋值时出错

Dao层

1、 UserDao接口

public interface UsersDao {
    Users findUserByNameAndPwd(String name, String pwd);
    Users findUserByName(String name);
    Users findUserById(int id);
    int deleteUsers(int id);
    int updateUsers(Users users);
    List<Users> findAll();
    int addUsers(Users users);
}

2、 UserDaoImpl类

public class UsersDaoImpl implements UsersDao {
    JDBCUtils jdbcUtils = new JDBCUtils();
    @Override
    public Users findUserByNameAndPwd(String name, String pwd) {
        return jdbcUtils.findOneInstance(Users.class, "select * from users where uname = ? and upwd = ?", name, pwd);
    }

    @Override
    public Users findUserByName(String name) {
        return jdbcUtils.findOneInstance(Users.class, "select * from users where uname = ?", name);
    }

    @Override
    public Users findUserById(int id) {
        return jdbcUtils.findOneInstance(Users.class, "select * from users where id = ?", id);
    }

    @Override
    public int deleteUsers(int id) {
        return jdbcUtils.executeUpdate("delete from users where id = ?", id);
    }

    @Override
    public int updateUsers(Users users) {
        return jdbcUtils.executeUpdate("update users set uname = ?, upwd = ?, ustatus = ?, uscore = ? where id = ?", users.getUname(), users.getUpwd(), users.getUstatus(), users.getUscore(), users.getId());
    }

    @Override
    public List<Users> findAll() {
        return jdbcUtils.executeQuery(Users.class, "select * from users");
    }

    @Override
    public int addUsers(Users users) {
        return jdbcUtils.executeUpdate("insert into users values(null,?,?,?,0)", users.getUname(), users.getUpwd(), users.getUstatus());
    }
}

3、 AdminDao接口
4、 UserDaoImpl类
5、 GoodsDao接口
6、 GoodsDaoImpl类
7、 …
都是类似的代码，对数据库的数据进行增删改查等

View层

为了减少空间仅展示部分代码

	// 管理员添加商品类型
    private void addGoodsType() {
        System.out.println("请输入新的类型名称：");
        String typeName = in.next();
        Type type = new Type();
        type.setTypename(typeName);
        int rows = typeDao.addType(type);
        if (rows != -1) {
            System.out.println("添加成功！");
            AdminLog adminLog = new AdminLog();
            adminLog.setAid(admin.getId());
            adminLog.setDesc("添加一个新商品类型：" + typeName);
            adminLogDao.addDesc(adminLog);
        } else {
            System.out.println("添加失败！");
        }
    }

	// 普通用户注册
	private void register() {
        System.out.println("请输入用户名：");
        String usm = in.next();
        Users users = usersDao.findUserByName(usm);
        if (users == null) {
            System.out.println("请输入密码：");
            String pwd = in.next();
            if (pwd.length() < 6 || pwd.length() > 18) {
                System.out.println("密码长度有误，请重新输入！");
                System.out.println("请输入密码：");
                pwd = in.next();
                users = new Users();
                users.setUname(usm);
                users.setUpwd(pwd);
                users.setUstatus(Const.USERON);
                int rows = usersDao.addUsers(users);
                System.out.println(rows != -1 ? "注册成功！" : "注册失败！");
            } else {
                users = new Users();
                users.setUname(usm);
                users.setUpwd(pwd);
                users.setUstatus(Const.USERON);
                int rows = usersDao.addUsers(users);
                System.out.println(rows != -1 ? "注册成功！" : "注册失败！");
            }
        } else {
            System.out.println("此用户名已存在，请重新注册！");
            register();
        }
    }

	// 普通用户结账，这里可以进一步完善，添加手动提交事务
	private void pay() {
        double sum = showCard();
        double money = users.getUscore();
        boolean status = true;
        // 付款成功后，减少用户账户余额，清空购物车，减少商品信息中商品的数量，添加购物明细
        if (money > sum) {
            System.out.println("付款成功！找您：" + (money - sum));
            // 清空购物车，判断商品数量是否足够
            List<Cart> list = cartDao.findAllByUid(users.getId());
            for (Cart cart : list) {
                int goodsId = cart.getGoodsid();
                int goodsNum = cart.getCnum();
                Goods goods = goodsDao.findById(goodsId);
                if (goods.getGnum() > goodsNum) {
                    cartDao.delete(cart.getCid());
                    goods.setGnum(goods.getGnum() - goodsNum);
                    // 减少商品信息中的商品数量
                    goodsDao.updateGoods(goods);
                } else {
                    System.out.println("库存不足请联系管理员");
                    status = false;
                    break;
                }
            }
            if (status) {
                users.setUscore(money - sum);
                // 更新账户余额
                usersDao.updateUsers(users);
                ShopDetail shopDetail = new ShopDetail();
                shopDetail.setCost(sum);
                shopDetail.setUid(users.getId());
                int rows = shopDetailDao.addShopDetail(shopDetail);
                System.out.println(rows != -1 ? "结算成功！" : "结算失败！");
            }
        } else {
            System.out.println("余额不足，请充值后重新支付！");
        }
    }