鑫亿数码
一、背景说明:
为了方便用户购买网店商品,并且对用户购买的信息进行统一管理,可以把所有商品信息录入系统,通过数据库来保存相关录入的商品数据。
主要功能包括浏览商品信息模块;购买商品模块;查询模块,按编号或者名称查询物品;订单管理模块。
这里有一个更加基础的同类项目:你的第一个单机Java商城订单系统(鑫亿数码)
这里有JDBC的说明文档:
JDBC(上)
JDBC(下)
二、工具及环境:
1、eclipse-jee-2018
2、JDK11
3、tomcat9
三、文件目录:
四、配置文件:
#配置数据源
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/shop?serverTimezone=Asia/Shanghai
jdbc.username=root
jdbc.password=admin
#配置连接池参数
jdbc.initialSize=10
jdbc.maxTotal=5
jdbc.maxIdle=5
jdbc.maxWaitMillis=5000
五、工具类
1、DBCP数据库连接池:
代码演示:
/**
* @author 作者 Tony:
* 类说明
演示dbcp数据库连接池
* 使用Commons-DBCP组件
*/
public class Dbcp {
public static final String DB_CONFIG_FILEPATH="dbconfig.properties";
private static Properties properties;
private static Dbcp dbcp;
static {
try {
properties=new Properties();
InputStream is=Thread.currentThread().getContextClassLoader().getResourceAsStream(DB_CONFIG_FILEPATH);
properties.load(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
BasicDataSource dataSource=null;
private Dbcp() {
//创建Commons-DBCP连接池对象
dataSource=new BasicDataSource();
//设置数据源参数
dataSource.setDriverClassName(properties.getProperty("jdbc.driver"));
dataSource.setUrl(properties.getProperty("jdbc.url"));
dataSource.setUsername(properties.getProperty("jdbc.username"));
dataSource.setPassword(properties.getProperty("jdbc.password"));
//设置连接池参数
dataSource.setInitialSize(Integer.parseInt(properties.getProperty("jdbc.initialSize")));//初始化容量
dataSource.setMaxTotal(Integer.parseInt(properties.getProperty("jdbc.maxTotal")));//最大活动数
dataSource.setMaxIdle(Integer.parseInt(properties.getProperty("jdbc.maxIdle")));//最大空闲数
dataSource.setMaxWaitMillis(Long.parseLong(properties.getProperty("jdbc.maxWaitMillis")));//最长等待时间
}
public static Dbcp getInstance() {
if(dbcp==null) {
dbcp=new Dbcp();
}
return dbcp;
}
//返回数据源对象
public BasicDataSource getDataSource() {
return dataSource;
}
/**
* 连接池获取连接对象
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return dataSource==null?null:dataSource.getConnection();
}
/**
* 归还连接
*/
public void close(Connection conn,Statement psmt,ResultSet rs) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
close(conn, psmt);
}
public void close(Connection conn,Statement psmt) {
if(psmt!=null) {
try {
psmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2、JDBC的封装:
代码演示:
/**
* @author 作者 Tony:
* 类说明
JDBC的封装
*/
public class JDBCTemplate {
/**
* 封装更新操作
* @param psmtCreator 获取PreparedStatement对象
* @return
*/
public static final int update(PreparedStatementCreator psmtCreator) {
Connection conn=null;
PreparedStatement psmt=null;
int result=-1;
try {
//使用数据库连接池获取连接
conn=Dbcp.getInstance().getConnection();
conn.setAutoCommit(false);
psmt=psmtCreator.create(conn);
result=psmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
if(conn!=null) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
e.printStackTrace();
}finally {
Dbcp.getInstance().close(conn, psmt);
}
return result;
}
/**
* 封装查询操作
* @param psmtCreator 获取PreparedStatement对象
* @param rsExtractor 解析结果集,获取解析后的对象
* @return 返回解析后的查询结果
*/
public static final Object query(PreparedStatementCreator psmtCreator,ResultSetExtractor rsExtractor) {
Connection conn=null;
PreparedStatement psmt=null;
ResultSet rs=null;
Object result=null;
try {
conn=Dbcp.getInstance().getConnection();
conn.setAutoCommit(false);
psmt=psmtCreator.create(conn);
rs=psmt.executeQuery();
result=rsExtractor.extractor(rs);
conn.commit();
} catch (SQLException e) {
if(conn!=null) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
e.printStackTrace();
}finally {
Dbcp.getInstance().close(conn, psmt, rs);
}
return result;
}
}
3、获取预编译对象:
代码演示:
public interface PreparedStatementCreator {
/**
*
* 通过连接对象获取预编译对象
* @param conn 连接对象
* @return 预编译语句对象
* @throws SQLException 抛出SQL异常
*/
PreparedStatement create(Connection conn) throws SQLException;
}
4、处理结果集封装目标对象:
代码演示:
public interface ResultSetExtractor {
/**
*
*处理结果集封装目标对象
* @param rs 结果集对象
* @return 封装好的Object对象
* @throws SQLException 抛出SQL异常
*/
Object extractor(ResultSet rs) throws SQLException;
}
六、快速完成实体类编写的方法:
初学的小伙伴可能会觉得写实体类对象非常麻烦,万一碰上属性多的实体类更是头疼,其实是有快捷键让我们快速完成实体类的编写的。
(1)生成无参、有参构造函数:
鼠标单击右键,出现图示列表,按照步骤即可调出;
快捷键:
Alt+Shift+s+c 空参构造
Alt+Shift+s+o 实参构造
(2)生成get/set方法:
鼠标单击右键,出现图示列表,按照步骤即可调出;
快捷键:
Alt+Shift+s+r get/set方法
这里的勾选跟上一步是一致的,但一般都是全选的,真是方便快捷啊!
(3)生成toString方法:
鼠标单击右键,出现图示列表,按照步骤即可调出;
快捷键:
Alt+Shift+s+s toString快捷键
(4)总结:
一般实体类生成这几种就差不多就可以了,如果有特殊的需求,可以看到列表中还有许多快速生成的选项,这就不用我说了吧,小机灵鬼们~
七、实体类的定义
1、定义Goods实体类对象:
代码演示:
public class Goods {
private int id;
private String gno;//编号
private String gname;//商品名
private double price;//价格
private int num;//数量
private double sumPrice;//总价
public Goods() {
super();
}
public Goods(int id, String gno, String gname, double price) {
super();
this.id = id;
this.gno = gno;
this.gname = gname;
this.price = price;
}
public Goods(int id, String gno, int num, double sumPrice) {
super();
this.id = id;
this.gno = gno;
this.num = num;
this.sumPrice = sumPrice;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getGno() {
return gno;
}
public void setGno(String gno) {
this.gno = gno;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public double getSumPrice() {
return sumPrice;
}
public void setSumPrice(double sumPrice) {
this.sumPrice = sumPrice*num;
}
@Override
public String toString() {
return gno + "\t\t" + gname + "\t\t" + price;
}
}
2、定义Order实体类对象:
代码演示:
public class Order {
private int id;
private String gno;
private String username;//用户名
private String codes;//电话号码
private String ways;//邮寄方式
private String address;//地址
private Date datas;//下单时间
private String gname;//商品名
private int num;//购买数量
private double price;//单价
private double sumprice;//总价
public Order() {
super();
}
public Order(int id, String gno, Date datas, String username, String codes, String address, String ways, int num,Double price, Double sumprice) {
super();
this.id = id;
this.gno = gno;
this.datas = datas;
this.username = username;
this.codes = codes;
this.address = address;
this.ways = ways;
this.num = num;
this.price = price;
this.sumprice = sumprice;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getGno() {
return gno;
}
public void setGno(String gno) {
this.gno = gno;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getCodes() {
return codes;
}
public void setCodes(String codes) {
this.codes = codes;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getDatas() {
return datas;
}
public void setDatas(Date datas) {
this.datas = datas;
}
public String getWays() {
return ways;
}
public void setWays(String ways) {
this.ways = ways;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public double getSumprice() {
return sumprice;
}
public void setSumprice(double sumprice) {
this.sumprice = sumprice;
}
@Override
public String toString() {
//SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//获取时间
String way1;
if ("1".equalsIgnoreCase(ways)) {
way1="自提";
} else {
way1="邮寄";
}
String card = "10000";
card = card+id;
return card+"\t\t"+datas+"\t\t"+username+"\t\t"+codes+"\t\t"+address+"\t\t"+way1;
}
}
3、定义User实体类对象:
代码演示:
public class User {
private int id;//编号
private String username;//用户名
private String passward;//密码
public User() {
super();
}
public User(int id, String username, String passward) {
super();
this.id = id;
this.username = username;
this.passward = passward;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassward() {
return passward;
}
public void setPassward(String passward) {
this.passward = passward;
}
@Override
public String toString() {
return username + "\t" + passward;
}
}
八、接口的定义:
这里的接口我没有细分,都写在了同一个接口中,按照正常规范是需要每种实体类的实现都要单独编写接口的。
代码实现:
public interface GoodsDAO {
//1、商品
//查询所有
List<Goods> findAllGoods();
//通过编号查询
Goods findGoodsByGno(String gno);
//通过商品名查询
Goods findGoodsByGname(String gname);
//新增
boolean addGoods(Goods goods);
//删除
boolean deleteGoods(String gno);
//更新
boolean updateGoods(Goods goods);
//2、用户
//注册
boolean addUser(User user);
//登录
List<User> findUsers( String username,String passward);
//增加订单
boolean addOrder(Order order);
//查询订单
List<Order> findAllOrders();
}
九、接口实现类的定义:
public class GoodsDAOImpl implements GoodsDAO {
//重写抽象方法
//注册功能
@Override
public boolean addUser(User user) {
// TODO Auto-generated method stub
return JDBCTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="insert into user values(null,?,?)";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, user.getUsername());
psmt.setString(2, user.getPassward());
return psmt;
}
})>0?true:false;
}
//登录
@SuppressWarnings("unchecked")
@Override
public List<User> findUsers(String username,String passward) {
// TODO Auto-generated method stub
return (List<User>)JDBCTemplate.query(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="select * from user where username=? and passward=?";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, username);
psmt.setString(2, passward);
return psmt;
}
}, new ResultSetExtractor() {
@Override
public Object extractor(ResultSet rs) throws SQLException {
List<User> users=new ArrayList<User>();
while(rs.next()) {
int id=rs.getInt(1);
String username=rs.getString(2);
String passward=rs.getString(3);
User user=new User(0, username, passward);
users.add(user);
}
return users;
}
});
}
//商品预览
@SuppressWarnings("unchecked")
@Override
public List<Goods> findAllGoods() {
// TODO Auto-generated method stub
return (List<Goods>)JDBCTemplate.query(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="select * from goods";
PreparedStatement psmt=null;
psmt=conn.prepareStatement(sql);
return psmt;
}
}, new ResultSetExtractor() {
@Override
public Object extractor(ResultSet rs) throws SQLException {
List<Goods> goods=new ArrayList<Goods>();
while(rs.next()) {
int id=rs.getInt(1);
String gno=rs.getString(2);
String gname=rs.getString(3);
double price=rs.getDouble(4);
Goods good=new Goods(id, gno, gname, price);
goods.add(good);
}
return goods;
}
});
}
//编号查找商品
@Override
public Goods findGoodsByGno(String gno) {
// TODO Auto-generated method stub
return (Goods)JDBCTemplate.query(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="select * from goods where gno=?";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, gno);
return psmt;
}
}, new ResultSetExtractor() {
@Override
public Object extractor(ResultSet rs) throws SQLException {
Goods goods=null;
while (rs.next()) {
int id=rs.getInt(1);
String gno=rs.getString(2);
String gname=rs.getString(3);
Double price=rs.getDouble(4);
goods=new Goods(id, gno, gname, price);
}
return goods;
}
});
}
//增加订单
@Override
public boolean addOrder(Order order) {
// TODO Auto-generated method stub
return JDBCTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="insert into orders values(null,?,now(),?,?,?,?,?,?,?)";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, order.getGno());
psmt.setString(2, order.getUsername());
psmt.setString(3, order.getCodes());
psmt.setString(4, order.getAddress());
psmt.setString(5, order.getWays());
psmt.setInt(6,order.getNum());
psmt.setDouble(7, order.getPrice());
psmt.setDouble(8,order.getSumprice());
return psmt;
}
})>0?true:false;
}
//查询订单
@SuppressWarnings("unchecked")
@Override
public List<Order> findAllOrders() {
// TODO Auto-generated method stub
return (List<Order>)JDBCTemplate.query(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="select * from orders";
PreparedStatement psmt=null;
psmt=conn.prepareStatement(sql);
return psmt;
}
}, new ResultSetExtractor() {
@Override
public Object extractor(ResultSet rs) throws SQLException {
List<Order> orders=new ArrayList<Order>();
while(rs.next()) {
int id=rs.getInt(1);
String gno=rs.getString(2);
Date datas=rs.getDate(3);
String username=rs.getString(4);
String codes=rs.getString(5);
String address=rs.getString(6);
String ways=rs.getString(7);
Integer num=rs.getInt(8);
Double price=rs.getDouble(9);
Double sumprice=rs.getDouble(10);
Order order=new Order(id, gno, datas, username, codes, address, ways,num,price,sumprice);
orders.add(order);
}
return orders;
}
});
}
//通过商品名查询商品
@Override
public Goods findGoodsByGname(String gname) {
// TODO Auto-generated method stub
return (Goods)JDBCTemplate.query(new PreparedStatementCreator() {
@Override
public PreparedStatement create(Connection conn) throws SQLException {
String sql="select * from goods where gname=?";
PreparedStatement psmt=conn.prepareStatement(sql);
psmt.setString(1, gname);
return psmt;
}
}, new ResultSetExtractor() {
@Override
public Object extractor(ResultSet rs) throws SQLException {
Goods goods=null;
while (rs.next()) {
int id=rs.getInt(1);
String gno=rs.getString(2);
String gname=rs.getString(3);
Double price=rs.getDouble(4);
goods=new Goods(id, gno, gname, price);
}
return goods;
}
});
}
@Override
public boolean addGoods(Goods goods) {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean deleteGoods(String gno) {
// TODO Auto-generated method stub
return false;
}
@Override
public boolean updateGoods(Goods goods) {
// TODO Auto-generated method stub
return false;
}
}
十、其他类:
1、菜单类:
代码演示:
public class Menu {
static GoodsDAO goodsDAO = new GoodsDAOImpl();
private static Scanner input = new Scanner(System.in);
public Menu() {
}
//登录、注册选择页
public static void choiceMenu() {
System.out.println("请选择:");
System.out.println("[1、我要登录]");
System.out.println("[2、我要注册]");
}
//注册
public static void register() {
System.out.println("请输入账号:");
String username=input.next();
System.out.println("请输入密码:");
String passward=input.next();
User user=new User(0, username, passward);
if (goodsDAO.addUser(user)) {
System.out.println("注册成功!");
System.out.println();
} else {
System.out.println("注册失败!");
System.out.println();
}
}
//登录
public static String login() {
System.out.println("请输入账号:");
String username=input.next();
System.out.println("请输入密码:");
String passward=input.next();
String plag="true";
List<User> user=goodsDAO.findUsers(username,passward);
if (user!=null&&user.size()>0) {
System.out.println("登录成功!");
System.out.println();
plag="false";
} else {
System.out.println("登录失败!");
System.out.println();
}
return plag;
}
// 打印菜单
public static void printMenu() {
System.out.println("请选择:");
System.out.println("[1.我要下单]");
System.out.println("[2.我的订单]");
System.out.println("[3.查询商品]");
System.out.println("[4.退出]");
}
// 主菜单商品展示
public static void show() {
System.out.println("今日上架:");
System.out.println("编号\t\t商品\t\t价格");
}
/**
* 浏览功能 遍历数组 ,打印所有商品信息
*/
public void findAll() {
List<Goods> goods = goodsDAO.findAllGoods();
if (goods != null && goods.size() > 0) {
show();
for (Goods goods2 : goods) {
System.out.println(goods2);
}
}
}
//购买
public void shopping() {
System.out.println("********************欢迎订购********************");
System.out.println("请输入商品编号:");
String gno=input.next();
System.out.println("请输入购买数量:");
int num=input.nextInt();
Goods goods=goodsDAO.findGoodsByGno(gno);
Order order=new Order();
if (goods!=null) {
double sum=goods.getPrice()*num;
double price=goods.getPrice();
System.out.println("您当前购买的是:【"+goods.getGname().trim()+"】,单价是:【"+goods.getPrice()+"元】,购买数量:【"+num+"个】,合计:【"+sum+"元】");
System.out.println("是否确定下单(Y/N?)");
String result=input.next();
if("y".equalsIgnoreCase(result)) {
System.out.println("请输入姓名:") ;
String username=input.next();
System.out.println("请输入联系方式:");
String codes=input.next();
System.out.println("请选择派送方式:【1、自提 2、邮寄】");
String ways=input.next();
System.out.println("请输入收货地址:");
String address=input.next();
Order order2=new Order(0, gno,new Date(), username, codes,address, ways, num, price, sum);
if (goodsDAO.addOrder(order2)) {
System.out.println("恭喜下单成功,请耐心等待收货!");
System.out.println();
}else {
System.out.println("下单失败!");
System.out.println();
}
}else {
System.out.println("谢谢使用,欢迎下次再来!");
return;
}
} else {
System.out.println("您要购买的商品不存在");
}
}
//我的订单
public void history() {
System.out.println("当前位置>>我的订单");
System.out.println("订单编号\t\t创建时间\t\t\t创建人\t\t联系方式\t\t\t联系地址\t\t派送方式");
List<Order> orders = goodsDAO.findAllOrders();
if (orders != null && orders.size() > 0) {
for (Order order : orders) {
System.out.println(order);
}
}else {
System.out.println("还没有订单信息!");
}
}
//二级菜单
public void moreMenu() {
System.out.println("[1、查看订单][2、返回上一级][3、退出]");
int choice=input.nextInt();
switch (choice) {
case 1:
myOrder();
break;
case 2:
break;
case 3:
System.out.println("确认退出?Y/N");
String result=input.next();
//equalsIgnoreCase 忽略大小写 比较值
if("y".equalsIgnoreCase(result)) {
System.out.println("谢谢使用");
}
System.exit(0);
break;
default:
break;
}
}
//订单详情
public static void myOrder() {
List<Order> orders = goodsDAO.findAllOrders();
if (orders.size()>0) {
System.out.println("当前位置>>我的订单>>订单详情");
System.out.println("商品\t\t价格\t\t购买数量\t\t总价");
for (Order order : orders) {
Goods goods = goodsDAO.findGoodsByGno(order.getGno());
System.out.println(goods.getGname()+"\t\t"+order.getPrice()+"\t\t"+order.getNum()+"\t\t"+order.getSumprice());
}
} else {
System.out.println("还没购买商品!");
}
}
/**
* 查找商品
*/
public void find() {
System.out.println("请输入编号或商品名:");
String gno = input.next();
Goods goods=goodsDAO.findGoodsByGno(gno);
Goods goods2=goodsDAO.findGoodsByGname(gno);
if (goods != null) {
show();
System.out.println(goods);
} else if (goods2 != null) {
show();
System.out.println(goods2);
} else {
System.out.println("抱歉无此商品,我们会努力添加的哦!");
}
}
}
2、测试类:
代码演示:
public class Test {
private static Scanner scanner=new Scanner(System.in);
public static void main(String[] args) {
Menu menu=new Menu();
System.out.println("********************鑫亿数码********************");
//键盘输入选择登录、注册
boolean plag=true;
while (plag) {
menu.choiceMenu();
int choices1=scanner.nextInt();
switch (choices1) {
case 1:
//登录
if ("false".equalsIgnoreCase(menu.login())) {
plag=false;
}
break;
case 2:
menu.register();//注册
plag=false;
break;
default:
break;
}
}
//打印主菜单
menu.findAll();
//键盘输入选择
while (true) {
menu.printMenu();
int choices=scanner.nextInt();
//功能选项判断
switch (choices) {
case 1:
menu.shopping();//下单
break;
case 2:
menu.history();//查看订单
menu.moreMenu();//二级菜单
break;
case 3:
menu.find();//查询订单
//printMenu();
break;
case 4:
System.out.println("确认退出?Y/N");
String result=scanner.next();
//equalsIgnoreCase 忽略大小写 比较值
if("y".equalsIgnoreCase(result)) {
System.out.println("谢谢使用");
}
System.exit(0);
break;
default:
break;
}
}
}
}
十一、数据库表:
1、初始用户表:
2、商品表:
十二、程序运行
1、程序的启动:
程序的主入口main方法是在Test类中的,我们可以进入到Test类的窗口界面中,通过图示的两种方法进行程序的启动。
进入程序运行窗口后发现位置不太友好,可以通过那个小按钮将窗口移至正下方,更多小技巧可以自行网上搜索的,记住遇到困难不要慌,先问问身边的大佬或者网上搜索,谁还没有过出错的小白日常呢?你说是吧!
2、程序的演示:
2.1主界面:
2.2登录界面:
2.3下单:
2.4我的订单:
2.5订单详情:
2.6按商品编号查询商品信息:
成功:
失败:
2.7按商品名称查询商品信息:
2.8退出程序:
还不快去开动你充满智慧的脑袋去写出一个属于自己的系统~
注:能力有限,目前基本无干货,还请谅解,争取早日能够写出有质量的文章!
我是皮蛋布丁,一位爱吃皮蛋的热爱运动的废铁程序猿。