|
目录
1.前言
2.项目基础：
3.项目实现内容：
4.代码介绍：
1、MainPage.java
2、GoodsDao.java:
3、SalesManDao.java
4、MysqlData.java
1.前言
记录一下我的第一个java实战小项目，零零星星的一个星期完成了（2020.9.16—9.24）。但是好像没有整体框架的思维，还有好多代码实现的方法没有达到最优，这样做项目估计会被大佬骂吧，如有大佬无意间看到，还请批评指正，提出建议。
菜鸟很菜，继续学习呀！
2.项目基础：
JDBC基础。学会使用jdbc连接数据库，访问数据库，修改数据库。(本项目使用mysql)基础的sql语句，查询，修改，删除，真的很基础。java基础（我的java真的太基础了，好多还没学会）。
3.项目实现内容：
需求文档：链接：https://pan.baidu.com/s/1jsCTDi_6gbMgemOYinbedw 提取码：nyts
  
4.代码介绍：
代码简单的分为四个类，以下的代码能全部实现，且没有bug（自己测过很多次没发现bug）。
MainPage.java: 实现主界面的功能
GoodsDao.java: 实现与商品有关的操作
SalesManDao.java: 实现与售货员有关的操作
MysqlData.java: 实现数据库的操作
数据库表: goods, salesman, gsales
goods salesman gsales
  
1、MainPage.java
import java.util.Scanner;
public class MainPage {
public static void main(String[] args) {
Scanner s = new Scanner(System.in);
Boolean flag = true;
while (flag){
System.out.println("**********************************");
System.out.println(" 1.商品维护");
System.out.println(" 2.前台收银");
System.out.println(" 3.商品管理");
System.out.println("**********************************");
System.out.println("请选择，输入数字或者按0退出：");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
switch (select){
case 1:
GoodMaintenance();
break;
case 2:
QianTaiLogin();
break;
case 3:
GoodsMagLogin();
break;
case 0:
flag = false;
System.out.println("已退出系统！");
break;
default:
System.out.println("请输入正确的数字！");
}
}
}
/****************part1*****************/
public static void GoodMaintenance(){
System.out.println("执行显示商品维护菜单");
System.out.println("商超购物管理系统>>商品维护");
Scanner s = new Scanner(System.in);
GoodsDao operate = new GoodsDao();
while (true){
System.out.println("**********************************");
System.out.println(" 1.添加商品");
System.out.println(" 2.更改商品");
System.out.println(" 3.删除商品");
System.out.println(" 4.显示所有商品");
System.out.println(" 5.查询商品");
System.out.println("**********************************");
System.out.println("请选择，输入数字或按0返回上一级菜单：");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if (select == 1){
operate.addGoods();
} else if (select == 2){
operate.alterGoods();
} else if (select == 3){
operate.deleteGoods();
} else if (select == 4){
operate.showGoods(1);
} else if (select == 5){
operate.quaryGoods();
} else if (select == 0){
//flag = false;
break;
} else {
System.out.println("请输入正确的数字！");
}
}
System.out.println("已退出，返回上一级菜单。");
}
/****************part2*****************/
//登录界面
public static void QianTaiLogin(){
Scanner s = new Scanner(System.in);
MysqlData md = new MysqlData();
GoodsDao gd = new GoodsDao();
int select = 3;
while (true) {
System.out.println("**********************************");
System.out.println(" 欢迎使用bala超市购物管理系统");
System.out.println(" 1.登录系统");
System.out.println(" 2.退出");
System.out.println("**********************************");
System.out.print("请选择，输入数字：");
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
//System.out.println("select:" + select);
if(select == 1){
int i;
for(i = 2; i >= 0; i--){
System.out.print("请输入用户名：");
String name = s.nextLine();
System.out.print("请输入密码：");
String pass = s.nextLine();
//检查用户密码,count=1,正确，count=0，用户名密码错误。
int[] arr = md.quaryData(name,pass,1);
//返回售货员ID
int count = arr[0];
int sid = arr[1];
if(count == 1){
//登录成功
System.out.println("登录成功");
gd.shopping(sid);
break;
}else {
System.out.println("用户名和密码不匹配！");
if(i != 0)
System.out.println("您还有"+i+"次登录机会，请重新输入：");
if(i == 0)
System.out.println("密码错误次数超过3次，已退出！");
}
}
}else if(select == 2){
break;
}else {
System.out.println("输入错误！");
}
}
}
/****************part3*****************/
public static void GoodsMagLogin(){
System.out.println("执行商品管理！");
System.out.println();
System.out.println("商超购物管理系统>>商品管理");
GoodsDao gd = new GoodsDao();
SalesManDao sm = new SalesManDao();
Scanner s = new Scanner(System.in);
boolean flag = true; //退出当前系统标志
while (flag){
System.out.println("***************************************");
System.out.println(" 1、列出当日卖出商品列表");
System.out.println(" 2、售货员管理");
System.out.println("***************************************");
System.out.println("请选择，输入数字或者按0返回上一级菜单：");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if (select == 1){
gd.showTodaySales();
} else if (select == 2){
sm.jieMian();
} else if (select == 0){
flag = false;
} else {
System.out.println("输入错误！");
}
}
System.out.println("已退出，返回上一级菜单。");
}
}
2、GoodsDao.java:
import java.util.Scanner;
public class GoodsDao {
//goods
private String gName;
private float gPrice;
private int gNum;
//goods sales
java.util.Date date = new java.util.Date();
java.sql.Date sData = new java.sql.Date(date.getTime());
public String getgName() {
return gName;
}
public void setgName(String gName) {
this.gName = gName;
}
public float getgPrice() {
return gPrice;
}
public void setgPrice(float gPrice) {
this.gPrice = gPrice;
}
public int getgNum() {
return gNum;
}
public void setgNum(int gNum) {
this.gNum = gNum;
}
//添加商品
public void addGoods(){
MysqlData igd = new MysqlData();
System.out.println("执行添加商品操作：");
Scanner s = new Scanner(System.in);
char flag = 'y';
while (flag == 'y'){
while (true){
System.out.println("添加商品名称：");//不能为空
this.setgName(s.nextLine());
if(this.getgName().equals("")) //or this.getgName().length() == 0
System.out.println("输入不能为空！");
else
break;
}
while (true){
System.out.println("添加商品价格：");
try{
this.setgPrice(s.nextFloat());
break;
}catch (Exception e){
System.out.println("输入错误！");
String huanchong = s.next();
}
}
while (true){
System.out.println("添加商品数量：");
try{
this.setgNum(s.nextInt());
break;
}catch (Exception e){
System.out.println("输入错误！");
String huanchong = s.next();
}
}
System.out.println("输入的商品信息为：");
System.out.printf(this.getgName() + " " + this.getgPrice() + " " + this.getgNum() + "\n");
//存入数据库
igd.InsertData(this.getgName(),this.getgPrice(),this.getgNum());
//继续添加商品
System.out.print("是否继续商品(y/n): ");
while (true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
}
}
//更改商品,有个Scanner第二次循环，输入为空的问题没想明白
public void alterGoods(){
MysqlData md = new MysqlData();
System.out.println("执行更改商品操作");
//为什么在外面定义scann类，在while循环里，循环第二次的时候默认是空的，不能从键盘输入?
//Scanner s = new Scanner(System.in);
//String alterName = "";
char flag = 'y';
while (flag == 'y'){
System.out.println("输入更改商品名称：");
Scanner s = new Scanner(System.in);
String alterName = s.nextLine();
int count = md.quaryData(alterName,"acc_goods");
System.out.println("查询商品返回的值：" + count);
if(count > 0){
System.out.println("选择你要更改的内容:");
System.out.println("1、更改商品名称");
System.out.println("2、更改商品价格");
System.out.println("3、更改商品数量");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if(select == 1){
while (true){
System.out.println("请输入要更改商品名称:");//不能为空
try{
this.setgName(s.nextLine());
if(this.getgName().equals("")) //or this.getgName().length() == 0
System.out.println("输入不能为空！");
else {
md.alterData(alterName, this.getgName(), 1);
break;
}
}catch (Exception e){
System.out.println("输入错误！");
}
}
}else if(select == 2){
System.out.println("请输入要更改商品价格");
float alprice;
while (true){
try{
alprice = Float.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
md.alterData(alterName,alprice);
}else if(select ==3){
System.out.println("请输入要更改商品数量");
int alnum;
while (true){
try{
alnum = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
md.alterData(alterName,alnum,1);
}else {
System.out.println("输入错误！");
}
}
while (true){
System.out.println("是否继续(y/n)");
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
}
}
//删除商品
public void deleteGoods(){
System.out.println("执行删除商品操作！");
MysqlData igd = new MysqlData();
char flag = 'y';
while(flag == 'y'){
System.out.println("输入删除的商品名称：");
Scanner s = new Scanner(System.in);
String delname = s.nextLine();
//按照名字检查商品是否存在
int count = igd.quaryData(delname,"acc_goods");
if(count > 0){
System.out.println("是否确定要删除(y/n)?:");
if(s.next().charAt(0) == 'y'){
//执行删除商品
igd.deleteData(delname,1);
}else {
System.out.println("未删除。");
}
}
System.out.println("是否继续(y/n):");
while (true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
}
}
//显示所有商品信息
public void showGoods(int method){
System.out.println("显示所有商品");
MysqlData md = new MysqlData();
md.showData(method);
}
public void quaryGoods(){
System.out.println("执行查询商品操作！");
MysqlData md = new MysqlData();
Scanner s = new Scanner(System.in);
char flag = 'y';
while(flag == 'y'){
System.out.println("1、按商品价格升序查询");
System.out.println("2、按商品数量升序查询");
System.out.println("3、输入关键字查询商品");
System.out.println("请选择，输入数字或0返回上一级菜单：");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if(select == 1){
md.showData(2);
}else if(select == 2){
md.showData(3);
}else if(select == 3){
System.out.println("请输入商品关键字：");
Scanner sc = new Scanner(System.in);
String name = sc.nextLine();
md.quaryData(name,"inacc_goods"); //按照模糊方法查询商品是否存在
}else if(select == 0){
break;
}
else {
System.out.println("输入错误！");
}
System.out.println("是否继续(y/n):");
while (true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
}
}
//购物结算
public void shopping(int sid){
System.out.println(" 1.购物结算");
Scanner sc = new Scanner(System.in);
MysqlData md = new MysqlData();
float[] arr = {0,0};
int gid = 0;
float gprice = 0;
int gnum = 0;
float money = 0;
int num = 0;
System.out.println("输入商品关键字：");
while(true){
String name = sc.nextLine();
int count = md.quaryData(name, "inacc_goods");
if(count == 1){
while (true){
System.out.print("请选择商品：");
String selname = sc.nextLine();
//有输入就会有错误！
int count2 = md.quaryData(selname,"acc_goods");
//返回商品ID,价格,数量
if(count2 == 1){
arr = md.quaryData(selname);
gid = (int)arr[0];
gprice = arr[1];
gnum = (int)arr[2];
while (true){
System.out.print("请输入购买数量：");
try{
num = Integer.valueOf(sc.nextLine());
if(num >= 0 & num gnum)
System.out.println("库存不足！请调整购买数量！");
}
money = gprice * num;
System.out.printf("总价：%.1f",money);
System.out.println();
System.out.println("请输入实际交费金额：");
while (true){
try{
int jiaofei = Integer.valueOf(sc.nextLine());
if(jiaofei < 0){
System.out.println("输入错误！");
}else if(jiaofei < money){
System.out.println("您的钱不够买呀！请重新输入：");
}else {
System.out.println("找钱：" + (jiaofei - money));
break;
}
}catch (Exception e){
System.out.println("输入错误！");
}
}
System.out.println("谢谢光临！");
//修改数据库商品数量
int alternum = (int) (gnum - num);
md.alterData(selname,alternum,2);
//将sid,gid,sdate,snum 信息存到gsales表中
md.insertGsales(gid,sid,this.sData,num);
break;
}
}
break;
}else {
System.out.println("请重新输入商品关键字！");
}
}
}
//列出当日卖出商品列表
public void showTodaySales(){
System.out.println("执行列出当日卖出商品列表操作！");
System.out.println("今日售出商品：");
MysqlData md = new MysqlData();
md.showgoodsales(this.sData);
}
}
3、SalesManDao.java
import java.util.Scanner;
public class SalesManDao {
private String sName;
private String sPass;
public String getsPass() {
return sPass;
}
public void setsPass(String sPass) {
this.sPass = sPass;
}
public String getsName() {
return sName;
}
public void setsName(String sName) {
this.sName = sName;
}
public void jieMian(){
System.out.println("商超购物管理系统>>商品管理>>售货员管理");
Scanner s = new Scanner(System.in);
while (true) {
System.out.println("*****************************************");
System.out.println(" 1、添加售货员");
System.out.println(" 2、更改售货员");
System.out.println(" 3、删除售货员");
System.out.println(" 4、显示所有售货员");
System.out.println(" 5、查询售货员");
System.out.println("*****************************************");
System.out.println("请选择，输入数字或者按0返回上一级菜单：");
int select = 0;
while (true){
try{
select = Integer.valueOf(s.nextLine());
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if (select == 1) {
this.addSalesMan();
} else if (select == 2) {
this.alterSalesMan();
} else if (select == 3) {
this.deleteSalesMan();
} else if (select == 4) {
this.showSalesMan();
} else if (select == 5) {
this.quarySalesMan();
} else if (select == 0) {
break;
} else {
System.out.println("输入错误！");
}
}
}
//添加售货员信息
public void addSalesMan(){
MysqlData md = new MysqlData();
System.out.println("执行添加售货员操作：");
Scanner s = new Scanner(System.in);
char flag = 'y';
while (true){
System.out.println("添加售货员姓名：");
while (true){
this.setsName(s.nextLine());
if(this.getsName().equals(""))
System.out.println("输入不能为空！");
else
break;
}
System.out.println("添加售货员密码：");
while (true){
this.setsPass(s.nextLine());
if(this.getsPass().equals(""))
System.out.println("输入不能为空！");
else
break;
}
//存入数据库
md.InsertData(this.getsName(),this.getsPass());
System.out.println("是否继续(y/n)?");
while (true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
if(flag == 'n')
break;
}
}
//更改售货员信息
public void alterSalesMan(){
System.out.println("执行更改售货员操作");
Scanner s = new Scanner(System.in);
MysqlData md = new MysqlData();
String sname;
String alname;
String alpass;
while (true){
System.out.println("输入更改的售货员姓名：");
while (true){
sname = s.nextLine();
if(sname.equals(""))
System.out.println("输入不能为空！");
else
break;
}
int count = 0;
count = md.quaryData(sname,"acc_salesman"); // 准确查询
if(count > 0){
System.out.println("选择您要更改的内容");
System.out.println("1、更改售货员姓名");
System.out.println("2、更改售货员密码");
int select;
while(true){
try{
select = s.nextInt();
break;
}catch (Exception e){
System.out.println("输入错误！");
}
}
if(select == 1){
System.out.println("请输入新的名字：");
while (true){
alname = s.nextLine();
if(alname.length() == 0)
System.out.println("不能为空!");
else {
md.alterData(sname,alname,2);
break;
}
}
}else if(select == 2){
System.out.println("请输入新的密码：");
while (true){
alpass = s.nextLine();
if(alpass.equals(""))
System.out.println("不能为空！");
else {
md.alterData(sname,alpass,3);
break;
}
}
}else {
System.out.println("输入错误！");
}
}
System.out.println("是否继续(y/n)?");
char flag = 'y';
while(true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'y' || flag == 'n')
break;
else
System.out.println("输入错误！请重新输入：");
}
if(flag == 'n')
break;
}
}
public void deleteSalesMan() {
System.out.println("执行删除售货员操作");
MysqlData md = new MysqlData();
Scanner s = new Scanner(System.in);
char flag = 'y';
while (true) {
System.out.println("输入删除的售货员姓名：");
String sname = s.nextLine();
int count = md.quaryData(sname, "acc_salesman");
if (count > 0) {
System.out.println("是否确定要删除(y/n)?");
while (true){
try{
flag = s.nextLine().charAt(0);
}catch (Exception e){
System.out.println("输入不能为空！");
}
if(flag == 'n' || flag == 'y')
break;
else
System.out.println("输入错误！请重新输入：");
}
if (flag == 'y') {
//delete
md.deleteData(sname,2);
}
}
while (true){
System.out.println("是否继续(y/n)?");
try{
flag = s.nextLine().charAt(0);
if(flag == 'y' || flag == 'n')
break;
System.out.println("请正确输入");
}catch (Exception e){
System.out.println("请正确输入");
}
}
if(flag == 'n')
break;
}
}
public void showSalesMan(){
System.out.println("执行显示所有售货员操作");
MysqlData md = new MysqlData();
md.showData(4);
}
public void quarySalesMan(){
System.out.println("执行查询售货员操作");
MysqlData md = new MysqlData();
Scanner s = new Scanner(System.in);
char flag = 'y';
while (true){
System.out.println("输入要查询的售货员姓名关键字：");
String sname = s.nextLine();
md.quaryData(sname,"inacc_salesman");
System.out.println("是够继续(y/n)?");
while (true){
try{
flag = s.nextLine().charAt(0);
if(flag == 'y' || flag == 'n')
break;
System.out.println("请正确输入");
}catch (Exception e){
System.out.println("请正确输入");
}
}
if(flag == 'n')
break;
}
}
}
4、MysqlData.java
import java.sql.*;
public class MysqlData {
private String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private String DB_URL = "jdbc:mysql://localhost/long";
private String USER = "root";
private String PASS = "123456";
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
/**************商品维护**********************/
//添加商品信息
public void InsertData(String name, Float price, int num){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//注意：第一个ID自动编号，不用添加
String sql = "insert into goods(GName,GPrice,GNum) values(?,?,?) ";
pstmt = conn.prepareStatement(sql);
//System.out.println("name:" + name);
pstmt.setString(1, name);
pstmt.setFloat(2, price);
pstmt.setInt(3, num);
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("商品添加成功！");
}
pstmt.close();
conn.close();
}catch (SQLException se){
se.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//添加售货员信息
public void InsertData(String sname, String spass){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "insert into salesman(SName,SPassword) values(?,?) ";
pstmt = conn.prepareStatement(sql);
System.out.println("sqlname:" + sname);
pstmt.setString(1, sname);
pstmt.setString(2, spass);
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("添加售货员成功！");
}
pstmt.close();
conn.close();
}catch (SQLException se){
se.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
/* method 方法调用
* method = "acc_goods",商品信息精确查询；
* method = "inacc_goods",商品信息模糊查询；
* method = "acc_salesman",售货员信息精确查询；
* method = "inacc_salesman",售货员信息模糊查询；
* 用int count>0判断查询存在，否则不存在*/
//查询数据(准确查，模糊查）
public int quaryData(String name, String method){
int count = 0;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//查询数据是否存在数据库,用count>0判断是否存在
//准确查询-商品
String sqlgood = "SELECT * FROM goods WHERE GName = ?;";
//模糊查询-商品
//方法1
String sqlgood1 = "SELECT * FROM goods WHERE GName like \"%\"?\"%\"";
//方法2
/* String sql3 = "SELECT * FROM goods WHERE ";
sql1 += "GName like concat('%',?,'%')";*/
//准确查询-售货员
String sqlsales = "SELECT * FROM salesman WHERE SName = ?;";
//模糊查询-售货员
String sqlsales1 = "SELECT * FROM salesman WHERE SName like \"%\"?\"%\"";
String sql = "";
if(method == "acc_goods"){
sql = sqlgood;
}else if(method == "inacc_goods"){
sql = sqlgood1;
}else if (method == "acc_salesman"){
sql = sqlsales;
}else if (method == "inacc_salesman"){
sql = sqlsales1;
}else {
System.out.println("方法调用错误！");
}
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
if(method == "acc_goods" || method == "inacc_goods"){
if(!rs.next()){
System.out.println("该商品不存在！");
count = 0;
} else {
System.out.print("商品名称 " + "商品价格 " + "商品数量" + "\t" + "\n");
String gname = rs.getString("GName");
Float gprice = rs.getFloat("GPrice");
int gnum = rs.getInt("GNum");
System.out.print(gname + " " + gprice +" " + gnum + "\t" + "\n");
while (rs.next()){
gname = rs.getString("GName");
gprice = rs.getFloat("GPrice");
gnum = rs.getInt("GNum");
System.out.print(gname + " " + gprice +" " + gnum + "\t" + "\n");
}
count = 1;
}
}else {
if(!rs.next()){
System.out.println("该售货员不存在！");
count = 0;
} else {
System.out.print("售货员姓名 " + "售货员密码" + "\t" + "\n");
String sname = rs.getString("SName");
String spass = rs.getString("spassword");
System.out.print(sname + " " + spass + "\t" + "\n");
while (rs.next()){
sname = rs.getString("SName");
spass = rs.getString("spassword");
System.out.print(sname + " " + spass + "\t" + "\n");
}
count = 1;
}
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
/*System.out.println("count:" + count);*/
return count;
}
//售货员用户名和密码匹配,返回售货员ID
public int[] quaryData(String name, String spass, int i){
int count = 0;
int sid = 0;
try{
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "SELECT * FROM salesman WHERE SName = ? AND SPassword = ?;";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, spass);
rs = pstmt.executeQuery();
while (rs.next()){
sid = rs.getInt("SID");
count = 1;
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
int[] arr = {count, sid};
return arr;
}
//返回商品价格(float)、数量(int),id
public float[] quaryData(String name){
float price = 0;
float num = 0;
float gid = 0;
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "SELECT * FROM goods WHERE GName = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
while (rs.next()){
price = rs.getFloat("gprice");
num = rs.getFloat("gnum");
gid = rs.getFloat("gid");
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
float[] arr = {gid, price, num};
return arr;
}
//method=1：更改商品名称；method=2：改售货员姓名;method=3,改售货员密码,
public void alterData(String name, String alname, int method){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql1 = "UPDATE goods SET GName = ? WHERE GName = ?";
String sql2 = "UPDATE salesman SET SName = ? WHERE SName = ?";
String sql3 = "UPDATE salesman SET SPassword = ? WHERE SName = ?";
String sql = "";
if(method == 1){
sql = sql1;
}else if(method == 2){
sql = sql2;
}else if(method == 3){
sql = sql3;
}else{
System.out.println("方法调用错误!");
}
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, alname);
pstmt.setString(2, name);
if(method == 4){
rs = pstmt.executeQuery();
if(!rs.next()){
System.out.println("用户名和密码不匹配！！");
int count = 0;
}
}else {
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("修改成功！");
}
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//method=1：更改商品数量,并显示修改成功;method=2:更改商品数量,不显示修改成功
public void alterData(String name, int alnum, int method){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "UPDATE goods SET GNum = ? WHERE GName = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, alnum);
pstmt.setString(2, name);
//rs = pstmt.executeQuery();
int i = pstmt.executeUpdate();
if(i > 0 & method == 1){
System.out.println("修改成功！");
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//更改商品价格
public void alterData(String name, Float alprice){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "UPDATE goods SET GPrice = ? WHERE GName = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1, alprice);
pstmt.setString(2, name);
//rs = pstmt.executeQuery();
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("商品价格修改成功！");
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//method=1：删除商品；method=2：删除售货员
public void deleteData(String name, int method){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql1 = "DELETE FROM goods WHERE GName = ?";
String sql2 = "DELETE FROM salesman WHERE SName = ?";
String sql = "";
if(method == 1){
sql = sql1;
}else if(method == 2){
sql = sql2;
}else{
System.out.println("方法调用失败。");
}
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("删除成功！");
}else {
System.out.println("删除失败！");
}
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
/*method = 1:显示所有商品信息；
* method = 2:价格升序显示商品信息；
* method = 3:数量升序显示商品信息；
* method = 4:显示所有售货员*/
//显示所有商品信息（普通显示，价格升序显示，数量升序显示）
public void showData(int method){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//method=1,显示所有商品信息
String sql1 = "SELECT *, (CASE WHEN gnum >= 10 THEN NULL WHEN gnum < 10 THEN \"*该商品已不足10件!\" END) 'note' FROM goods;";
//method=2,根据价格升序显示商品信息
String sql2 = "SELECT GName,GPrice,GNum FROM goods GROUP BY GPrice";
//method=3,根据数量升序显示商品信息
String sql3 = "SELECT GName,GPrice,GNum FROM goods GROUP BY GNum";
String sql4 = "SELECT SName,SPassword FROM salesman";
String sql = "";
if(method == 1){
sql = sql1;
}else if(method == 2){
sql = sql2;
}else if(method == 3){
sql = sql3;
}else if(method == 4){
sql = sql4;
}else {
System.out.println("方法调用错误！");
}
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
//普通查询比其他查询多了一个备注
if(method == 1){
System.out.printf("%-10s","商品名称");
System.out.printf("%-10s","商品价格");
System.out.printf("%-10s","商品数量");
System.out.printf("%-10s","备注");
System.out.println();
while (rs.next()){
//int gid = rs.getInt("GID");
String gname = rs.getString("GName");
Float gprice = rs.getFloat("GPrice");
int gnum = rs.getInt("GNum");
String note = rs.getString("note");
System.out.printf("%-14s",gname);
System.out.printf("%-13.1f",gprice);
System.out.printf("%-10d",gnum);
System.out.printf("%-10s",note);
System.out.println();
/* System.out.printf("商品名称"+"\t \t \t \t ");
System.out.printf("商品价格"+"\t \t \t \t ");
System.out.printf("商品数量"+"\t \t \t \t ");
System.out.printf("备注"+"\t \t \t \t ");
System.out.println();
System.out.printf(gname +"\t \t \t \t ");
System.out.printf(gprice+"\t \t \t \t ");
System.out.printf(gnum+"\t \t \t \t ");
System.out.printf(note+"\t \t \t \t ");
System.out.println();*/
}
}else if(method == 4) {
System.out.print("售货员姓名 " + "售货员密码" + "\t" + "\n");
while (rs.next()){
String sname = rs.getString("SName");
String spass = rs.getString("spassword");
System.out.print(sname + " " + spass + "\t" + "\n");
}
} else {
System.out.printf("商品名称"+"\t \t \t \t ");
System.out.printf("商品价格"+"\t \t \t \t ");
System.out.printf("商品数量"+"\t \t \t \t ");
System.out.println();
while (rs.next()){
String gname = rs.getString("GName");
Float gprice = rs.getFloat("GPrice");
int gnum = rs.getInt("GNum");
System.out.printf(gname +"\t \t \t \t ");
System.out.printf(gprice+"\t \t \t \t ");
System.out.printf(gnum+"\t \t \t \t ");
System.out.println();
}
}
stmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(stmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//销售信息存入gsales表
public void insertGsales(int gid, int sid, Date sdate, int snum){
try {
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "insert into gsales(GID,SID,SDate,SNum) values(?,?,?,?) ";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, gid);
pstmt.setInt(2, sid);
pstmt.setDate(3, sdate);
pstmt.setInt(4, snum);
pstmt.executeUpdate();
/*int i = pstmt.executeUpdate();
if(i > 0){
System.out.println("添加成功！");
}else {
System.out.println("添加失败！");
}*/
pstmt.close();
conn.close();
} catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
//显示今日销售的商品
public void showgoodsales(Date sdate){
try{
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "SELECT a.gid,a.GName,a.GPrice,a.GNum, b.snum,b.SDate," +
"(CASE WHEN a.gnum >= 10 THEN NULL WHEN a.gnum < 10 THEN\"*该商品已不足10件!\" END) 'note' " +
"FROM\tgoods a right JOIN gsales b on a.gid = b.gid where b.SDate = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setDate(1, sdate);
rs = pstmt.executeQuery();
System.out.print("商品名称 "+"商品价格 "+"商品数量 "+"销量 "+"备注"+"\t"+"\n");
while (rs.next()){
String gname = rs.getString("gname");
float gprice = rs.getFloat("gprice");
int gnum = rs.getInt("gnum");
int snum = rs.getInt("snum");
String note = rs.getString("note");
System.out.println(gname+" "+gprice+" "+gnum+" "+snum+" "+note);
}
pstmt.close();
conn.close();
}catch (SQLException se){
se.printStackTrace();
} catch (Exception e){
e.printStackTrace();
}finally {
try{
if(pstmt != null)
conn.close();
}catch (SQLException se){
}
try {
if(conn != null)
conn.close();
}catch (SQLException se){
se.printStackTrace();
}
}
}
}
|