Git地址： jdbcTest
项目简介
使用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("余额不足，请充值后重新支付！");
}
}
920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
