总览:没有技术含量的增删改查,最多连了一下数据库,用时四天半,每一天也至少敲了六小时,很赶,代码写得很烂。作品等级为D。
数据库设计:
#create database xiaoMi;
use xiaoMi;
#select * from phone;
#drop table phone;
select * from phone;
create table phone(
phoneId int primary key,
phoneName varchar(20) not null,
phoneDescribe varchar(100),
phonePng varchar(200) not null
);
insert into phone values(1,'Redmi K50','天玑8100|67W快充|5500mAh大电池',"D:\JAVA项目\phone1.png"),
(2,'Redmi K40S','骁龙870|三星 E4直屏|67W旗舰快充',"D:\JAVA项目\phone3.png")
;
#drop table phoneMp;
create table phoneMp(
phoneId int not null,
phoneMemory varchar(20) not null,
phonePrice double not null,
primary key(phoneId,phoneMemory),
foreign key (phoneId) references phone(phoneId)
on delete cascade
on update cascade
);
insert into phoneMp values
(1,'8GB+128GB',2199.00)
,(1,'12GB+512GB',2899.00)
,(1,'8GB+256GB',2399.00)
,(1,'12GB+256GB',2599.00),
(2,'8GB+128GB',1899.00)
,(2,'6GB+128GB',1799.00)
,(2,'8GB+256GB',2099.00)
,(2,'12GB+256GB',2199.00);
#drop table phoneCs;
create table phoneCs(
phoneId int,
phoneColour varchar(10),
phoneStock int not null,
phoneColorPng varchar(200) not null,
primary key(phoneId,phoneColour),
foreign key (phoneId) references phone(phoneId)
on delete cascade
on update cascade
);
select * from phoneCs;
insert into phoneCs values
(1,'墨羽',10,"D:\JAVA项目\phone1.2.png")
,(1,'幻境',10,'D:\JAVA项目\phone1.3.png')
,(1,'幽芒',10,"D:\JAVA项目\phone1.4.png")
,(1,'银迹',10,"D:\JAVA项目\phone1.1.png"),
(2,'亮黑',10,"D:\JAVA项目\phone3.1.png")
,(2,'幻境',10,'D:\JAVA项目\phone3.2.png')
,(2,'幽芒',10,"D:\JAVA项目\phone3.3.png")
,(2,'银迹',10,"D:\JAVA项目\phone3.4.png");
select * from cartUser;
update cartUser set userBalance=100000.0 where userId=1;
create table cartUser(
userId int primary key,
userName varchar(15) not null,
userPassword char(6) not null,
userBalance double not null
);
insert into cartUser values(1,'detail','12345',10000.00);
create table userAddress(
userAddressId int primary key,
userId int ,
address varchar(50),
foreign key (userId) references cartUser(userId)
on delete cascade
on update cascade
);
drop table shoppingCart;
set SQL_SAFE_UPDATES=0;
select * from shoppingCart;
create table shoppingCart(
shoppingCartId int primary key AUTO_INCREMENT,
userId int not null,
phoneId int not null,
phoneMemory varchar(20) not null,
phoneColour varchar(10) not null,
phoneCount int not null
);
select * from shoppingCart;
update shoppingCart set phoneCount=2 where shoppingCartId=18;
delete from shoppingCart where shoppingCartId=5;
insert into shoppingCart values(1,6,1," "," ",5);
insert into Goods values(1,1,3);
select * from Goods;
drop table Goods;
create table Goods(
goodsId int primary key AUTO_INCREMENT,
userId int,
shoppingCartId int,
foreign key (userId) references cartUser(userId)
on delete cascade
on update cascade,
foreign key (shoppingCartId) references shoppingCart(shoppingCartId)
on delete cascade
on update cascade
);
drop table orderForm;
select * from saleGoods;
drop table saleGoods;
create table saleGoods(
saleGoodsId int primary key AUTO_INCREMENT,
userId int,
shoppingCartId int
);
#drop table phoneSales;
select * from phoneSales;
create table phoneSales(
phoneId int primary key,
saleAmount int,
foreign key (phoneId) references phone(phoneId)
on update cascade
);
功能实现:
1.购物车简单登录
2.菜单组件显示商品,事件触发为双击,中间空着的地方加了个不同型号手机销量表
3.商品详情页面,单击选择内存和加入购物车(选购完全相同商品,数量加一)
4.购物车商品界面,单击结算和清空购物车,只有默认选择所有商品的设计。
5.用户有余额字段,会判断其余额够不够再予以结算。
预实现而未实现的功能:
商品库存字段有,但未使用,为了快,也因为懒,购物车商品就一次结算了,还有订单界面直接放弃了,有用户地址信息字段也没用。
总体项目jar包
http://链接:https://pan.baidu.com/s/123tMJQGupc8S8CISFPy-pg?pwd=1234 提取码:1234http://xn--https-bl8js66z7n7i//pan.baidu.com/s/123tMJQGupc8S8CISFPy-pg?pwd=1234%20%20%E6%8F%90%E5%8F%96%E7%A0%81%EF%BC%9A1234部分代码截图:
package com.cart.dao.entity; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.cart.dao.err.DaoException; import com.cart.util.ErrorCodeEnum; import com.cart.util.ErrorLevel; public abstract class BaseDao{ private Connection conn; // private static Logger logger=Logger.getLogger(BaseDao.class); public BaseDao() { this.conn=Get.getConnectionXiaoMi(); } // public void setConnection(Connection conn) { // this.conn=conn; // } protected void close( Statement stat, ResultSet rs) throws DaoException{ if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block throw new DaoException(ErrorCodeEnum.SQL_CLOSE_RS_ERR,e,ErrorLevel.CRITICAL); } } if (stat != null) { try { stat.close(); } catch (SQLException e) { // TODO Auto-generated catch block throw new DaoException(ErrorCodeEnum.SQL_CLOSE_STAT_ERR,e,ErrorLevel.CRITICAL); } } } protected void bindParameters(PreparedStatement stmt, Object... params)throws DaoException{ if (params == null) { return; } for (int i = 0; i < params.length; i++) { try { stmt.setObject(i + 1, params[i]); } catch (SQLException e) { // TODO Auto-generated catch block throw new DaoException(ErrorCodeEnum.SQL_ASSEMBLE_PARAMS_ERR, e,ErrorLevel.CRITICAL); } } } protected List executeQueryList(String sql, Object... params)throws DaoException { PreparedStatement stmt = null; ResultSet rs = null; List list = null; try { stmt = conn.prepareStatement(sql); bindParameters(stmt, params); System.out.println(stmt); rs = stmt.executeQuery(); list = new ArrayList (); while (rs.next()) { T t = recordToEntity(rs); list.add(t); } } catch (SQLException e) { //e.printStackTrace(); throw new DaoException(ErrorCodeEnum.SQL_QUERY_ENTITY_ERR, e,ErrorLevel.CRITICAL); } finally { close( stmt, rs); } return list; } protected Map executeQueryMap(String sql, Object... params)throws DaoException { PreparedStatement stmt = null; ResultSet rs = null; Map map = null; try { stmt = conn.prepareStatement(sql); bindParameters(stmt, params); rs = stmt.executeQuery(); map = new HashMap (); while (rs.next()) { Map.Entry entry = recordToEntry(rs); map.put(entry.getKey(), entry.getValue()); } } catch (SQLException e) { //e.printStackTrace(); throw new DaoException(ErrorCodeEnum.SQL_QUERY_ENTITY_ERR, e,ErrorLevel.CRITICAL); }finally { close( stmt, rs); } return map; } protected T executeQueryEntity(String sql, Object... params)throws DaoException { PreparedStatement stmt = null; ResultSet rs = null; T t=null; try { stmt = conn.prepareStatement(sql); bindParameters(stmt, params); rs = stmt.executeQuery(); while (rs.next()) { t = recordToEntity(rs); } } catch (SQLException e) { //e.printStackTrace(); throw new DaoException(ErrorCodeEnum.SQL_QUERY_ENTITY_ERR, e,ErrorLevel.CRITICAL); } finally { close( stmt, rs); } return t; } protected Map executeQueryColToMap(String sql, Object... params)throws DaoException{ PreparedStatement stmt = null; ResultSet rs = null; Map map = null; try { stmt = conn.prepareStatement(sql); bindParameters(stmt, params); rs = stmt.executeQuery(); map = new HashMap (); while (rs.next()) { String colName=rs.getMetaData().getColumnName(1); map.put(colName, rs.getObject(1)); } System.out.println(map); } catch (SQLException e) { //e.printStackTrace(); throw new DaoException(ErrorCodeEnum.SQL_QUERY_MAPPING_ERR, e,ErrorLevel.CRITICAL); }catch(Exception e){ throw new DaoException(ErrorCodeEnum.OTHER_ERROR, e,ErrorLevel.CRITICAL); }finally { close( stmt, rs); } return map; } protected int executeUpdate(String sql, Object... params) throws DaoException{ int updataRows = 0; PreparedStatement pstm = null; ResultSet rs = null; try { pstm = conn.prepareStatement(sql); bindParameters(pstm,params); updataRows = pstm.executeUpdate(); } catch (SQLException e) { throw new DaoException(ErrorCodeEnum.SQL_UPDATE_ERR, e,ErrorLevel.CRITICAL); }finally { close( pstm, rs); } if(updataRows==0) { throw new DaoException(ErrorCodeEnum.SQL_NO_UPDATED_ERR,ErrorLevel.NORMAL); } return updataRows; } protected int executeUpdateBatch(String sql, Object[][] params) throws DaoException{ int []updataRows = null; PreparedStatement pstm = null; ResultSet rs = null; try { pstm = conn.prepareStatement(sql); for(int i=0;i Map.Entry recordToEntry(ResultSet rs)throws DaoException; protected Integer count(String sql) throws DaoException{ Integer count = 0; try { Map resultMap = this.executeQueryColToMap(sql, null); count = Integer.parseInt(resultMap.get("count").toString()); } catch (DaoException e) { throw e; } return count; } } } //继承BaseDaoz之后的操作 @Override protected CartUser recordToEntity(ResultSet rs) throws DaoException { // TODO Auto-generated method stub CartUser cu= new CartUser(); try { cu.setUserId(rs.getInt("userId")); cu.setUserName(rs.getString("userName")); cu.setUserPassword(rs.getString("userPassword")); cu.setUserBalance(rs.getDouble("userBalance")); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return cu; } @Override protected Entry recordToEntry(ResultSet rs) throws DaoException { // TODO Auto-generated method stub return null; } } public void mouseClicked(MouseEvent e) { // TODO Auto-generated method stub if(op !=null) { //加入购物车执行操作 if(op.equals("in")) { // System.out.println("ooo"+memory); // new ShoppingCartView(memory,350,250,540,250); JOptionPane.showMessageDialog(null, phoneName+" "+memory+" "+color+" 已成功加入购物车"); ShoppingCart sc= new ShoppingCart(0, userId, phoneId, memory, color, 1); ShoppingCartDao scd= new ShoppingCartDao(); GoodsDao gd= new GoodsDao(); //Goods表 int ShoppingCartId=scd.addShoppingCart(sc); if(sc.getShoppingCartId()!=ShoppingCartId) { gd.addGoods(sc.getUserId(), ShoppingCartId); } } //结算执行操作 else if(op.equals("settle")) { GoodsDao gd= new GoodsDao(); SaleGoodsDao sgd= new SaleGoodsDao(); ShoppingCartDao scd =new ShoppingCartDao(); // PhoneMpDao pmd= new PhoneMpDao(); PhoneSalesDao psd=new PhoneSalesDao(); CartUserDao cud= new CartUserDao(); double sum=scd.calculateSum(userId); if(cud.consume(userId, sum)>0) { List gList=gd.findAllGoodsById(userId); for (int i=0;i gList=gd.findAllGoodsById(userId); for(int i=0;i =2) { //进入购买界面 if(op.equals("buy")) { new PhoneDetailView((IndexView) view,"购买",pc,350,250,560,250,userId); } //进入购物车界面 else if(op.equals("cart")) { CartUserDao cud= new CartUserDao(); String userName=cud.findUserNameById(userId); new ShoppingCartView((IndexView) view,"用户:"+userName,350,250,550,230,userId); } ((Component) view).setEnabled(false); } } } public class PhoneSalesDao extends BaseDao { public int addSaleRecord( int phoneId,int saleAmount) { String sql="insert into phoneSales(phoneId,saleAmount) values (?,?)"; int id=0; try { id=this.executeUpdate(sql, phoneId,saleAmount); }catch (Exception e) { // TODO: handle exception sql="update phoneSales set saleAmount=? where phoneId=?"; int s=this.findOnePhoneSalesCount(phoneId)+saleAmount; id=this.executeUpdate(sql,s,phoneId); } return id; } public class ShoppingCartDao extends BaseDao { public int addShoppingCart(ShoppingCart sc) { int i=0; String sql="insert into shoppingCart(userId,phoneId,phoneMemory,phoneColour,phoneCount) values (?,?,?,?,?)"; String sql2="select * from shoppingCart where shoppingCartId=?"; GoodsDao gd= new GoodsDao(); List gList=gd.findAllGoodsById(sc.getUserId()); if(gList.isEmpty()) { i=this.insertUseAutoIncrement(sql, new Object[] {sc.getUserId(),sc.getPhoneId(),sc.getPhoneMemory(),sc.getPhoneColour(),sc.getPhoneCount()}); }else { for(int o=0;o scList=this.executeQueryList(sql2, shoppingCartId); // System.out.println("1"+scList.toString()); int count=sc.getPhoneCount(); int shoppingCartId2=-1; for(int j=0;j sc.getPhoneCount()) { System.out.println("count::::::"); sql="update shoppingCart set phoneCount=? where shoppingCartId=? "; this.executeUpdate(sql, new Object[] {count,shoppingCartId2}); i=shoppingCartId2; break; }else { i=this.insertUseAutoIncrement(sql, new Object[] {sc.getUserId(),sc.getPhoneId(),sc.getPhoneMemory(),sc.getPhoneColour(),sc.getPhoneCount()}); break; } } } return i; } PhoneCsDao pcd= new PhoneCsDao(); List pList=pd.findAllPhone(); //菜单商品显示 for(int i=0;i pcList=pcd.finaAllColourByPhoneId(pList.get(i).getPhoneId()); menuPhone.add(phone); menuPhone.addSeparator(); for(int j=0;j mpList=pmd.findAllMemoryById(pc.getPhoneId()); MousePolice mp= new MousePolice(); for(int i=0;i (); // jt= new JTable(sList,columnNames); List gList=gd.findAllGoodsById(userId); Object[][] heros= new Object[gList.size()][columnNames.length]; for(int i=0;i cuList=cud.finadAllUser(); for(int i=0;i
个人总结:
所有图片的显示和数据都是我循环输出在视图上。我针对手机这一个单品作出了一些设计,一台手机会有多种颜色、多种内存和与之对应的不同价格,所以为了较完整体现其基本信息,我建了三个表来体现,于是每一次对一台手机的操作都会牵扯三个表。还有我觉得有所创新的设计为准备购买商品表和已经购买商品表,这两个表共用了一个购物车商品表,所以它们两个表存储的不过是购物商品表的id,一个订单的生成有可能有多种商品,一个用户的购物车也存在多种商品,所以我用了这三个表得以实现。最后我简单做了个销售统计,当一个品种的商品成功卖出时,那个品种的销量就加1。也是由于我要通过三个表得到一个完整的手机信息,四个表来实现用户商品的加入、结算、删除。所有窗口的响应和查询、修改操作,虽说也就增删改,但是一个数据的变动是几个表的交叉,还是算有工作量吧!视图显示所有数据和图片(除了一个小标识符)皆从数据库来,数据改变,显示就变。还在手机的库存和每个用户的余额,都有在其中涉及。画界面应是最为简单的操作,可是关于JAVA awt的组件,我真是一个一个地学,然后在用上,还有事件的处理,也少许考虑了用户的交互(放屁)。



