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.退出