package com.zuoye;

import java.sql.*;

import java.util.*;



public class Test {
    
    public static String uid;
    public static void main(String[] args)throws Exception{
        Scanner sc = new Scanner(System.in);
        System.out.println("用户名：");
        uid =sc.nextLine();
        System.out.println("密码：");
        String pwd = sc.nextLine();
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEconding=GBK","root","");
        String sql ="select * from login where UserName=? and Password=?";
        PreparedStatement p = conn.prepareStatement(sql);
        p.setString(1, uid);
        p.setString(2, pwd);
        ResultSet rs = p.executeQuery();
        
        boolean ok = rs.next();
        if(ok){
            System.out.println("欢迎您："+rs.getString(2));
            xuanxiang();
            
        }
        else{
            System.out.println("用户名或密码有误");
            
        }
        main(args);
        
        conn.close();
        
    }
    public static void xuanxiang() throws Exception{
        
        Scanner sc = new Scanner(System.in);
        System.out.println("*******选项*******");
        System.out.println("1.显示所有商品");
        System.out.println("2.添加商品");
        System.out.println("3.购买商品");
        System.out.println("4.退出");
        String a = sc.nextLine();
        if(a.equals("1")){
            liebiao();
            xuanxiang();
            
            
        }
        else if(a.equals("2")){
            tianjia();
            xuanxiang();
        }
        else if(a.equals("3")){
            goumai();
            xuanxiang();
            
        }
        else{
            return;
        }
        
                
    }
    
    public static void liebiao() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn1 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK","root","");
        String sql1 = "select * from fruit";
        Statement state1 = conn1.createStatement();
        ResultSet rs1 = state1.executeQuery(sql1);
        while(rs1.next()){
            System.out.print(rs1.getString(1)+"\t");
            System.out.print(rs1.getString(2)+"\t");
            System.out.print(rs1.getString(3)+"\t");
            System.out.print(rs1.getString(4)+"\t");
            System.out.print(rs1.getString(5)+"\t");
            System.out.print(rs1.getString(6)+"\n");
        }
        conn1.close();
    }
    public static void tianjia() throws Exception{
        Scanner sc =new Scanner(System.in);
        System.out.println("请输入编号：");
        String bh =sc.nextLine();
        System.out.println("请输入品种：");
        String pz =sc.nextLine();
        System.out.println("请输入价格：");
        double jg =Double.parseDouble(sc.nextLine());
        System.out.println("请输入产地：");
        String cd =sc.nextLine();
        System.out.println("请输入数量：");
        int sl=Integer.parseInt(sc.nextLine());
        System.out.println("请输入图片地址：");
        String dz =sc.nextLine();
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEconding=GBK","root","");
        String sql ="insert into fruit values(?,?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(sql);
        p.setString(1, bh);
        p.setString(2, pz);
        p.setDouble(3, jg);
        p.setString(4, cd);
        p.setInt(5, sl);
        p.setString(6, dz);
        p.executeUpdate();
        
        conn.close();
    }
    public static void goumai() throws Exception{
        
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK","root","");
        Scanner sc = new Scanner(System.in);
        System.out.println("购买什么水果");
        String pz = sc.nextLine();
        System.out.println("买多少");
        int gs = Integer.parseInt(sc.nextLine());
        String sql1 ="select * from fruit where Name=?";
        PreparedStatement p1 = conn.prepareStatement(sql1);
        p1.setString(1, pz);
        ResultSet rs2 = p1.executeQuery();
        double s = 0;
        while(rs2.next()){
              s =(rs2.getDouble(3)*gs);
            System.out.println(s);
        }
        String sql2="update fruit set Numbers = Numbers-? where Name =?";
        PreparedStatement p2 = conn.prepareStatement(sql2);
        p2.setInt(1, gs);
        p2.setString(2, pz);
        p2.executeUpdate();
        
        
        String sql3="update login set Account = Account-? where UserName =?";
        PreparedStatement p3 = conn.prepareStatement(sql3);
        p3.setDouble(1, s);
        p3.setString(2, uid);
        p3.executeUpdate();
        
        conn.close();
    }
}

用户名：
lisi
密码：
666666
欢迎您：李四
*******选项*******
1.显示所有商品
2.添加商品
3.购买商品
4.退出
1
k001    苹果    2.40    烟台    87    image/0.gif
k002    菠萝    1.40    广东    90    image/1.gif
k003    桔子    2.40    福州    90    image/2.gif
k004    葡萄    2.40    新缰    90    image/3.gif
k005    樱桃    2.40    青岛    90    image/4.gif
k006    桃子    2.40    花果山    90    image/5.gif
k007    香蕉    2.40    济南    90    image/6.gif
*******选项*******
1.显示所有商品
2.添加商品
3.购买商品
4.退出