import java.awt.image.Raster;

import java.sql.*;

import java.util.*;

public class ZuoYe {

   public static String zh;

    public static void main(String [] args) throws Exception {

        Scanner sc=new Scanner(System.in);

        System.out.println("请输入账号");

         zh=sc.nextLine();

        System.out.println("请输入密码");

        String mm=sc.nextLine();

        Class.forName("com.mysql.jdbc.Driver");

        String jdbc="jdbc:mysql://127.0.0.1:3306/mydb";

        Connection conn=DriverManager.getConnection(jdbc, "root", "");

        String sql="select * from login where UserName=? and Password=? ";

        PreparedStatement state= conn.prepareStatement(sql);

        state.setString(1, zh);

        state.setString(2, mm);

        ResultSet re=state.executeQuery();

        if(re.next()){

            System.out.println("登陆成功！"+re.getString(2)+",欢迎你！");

            jm();

        }

        else{

            System.out.println("账号或密码输入错误");

        }

            conn.close();

    }

    public static void jm() 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 xz=sc.nextLine();

        if(xz.equals("1")){

            xianshi();

            jm();

        }

        else if(xz.equals("2")){

            tianjia();

            xianshi();

            jm();

        }

        else if(xz.equals("3")){

            goumai();

            xianshi();

        }

        else if(xz.equals("4")){

            return;

        }

        else{

            System.out.println("输入有误");

        }

    }

    public static void xianshi() throws Exception{

        Class.forName("com.mysql.jdbc.Driver");

        String jdbc="jdbc:mysql://127.0.0.1:3306/mydb";

        Connection conn=DriverManager.getConnection(jdbc, "root", "");

        String sql="select * from fruit";

        PreparedStatement state=conn.prepareStatement(sql);

        ResultSet re=state.executeQuery();

        System.out.println("*********************************************");

        System.out.println("商品编号"+"\t"+"商品名称"+"\t"+"单价"+"\t"+"原产地"+"\t"+"剩余商品数量");

        while(re.next()){

            System.out.println(re.getString(1)+"\t"+re.getString(2)+"\t"+re.getDouble(3)+"\t"+re.getString(4)+"\t"+re.getInt(5));

        }

        System.out.println("*********************************************");

        conn.close();

    }

    public static void tianjia() throws Exception{

        Scanner sc=new Scanner(System.in);

        System.out.println("请输入商品属性:商品编号-商品名称-单价-原产地-添加数量，属性之间用-隔开");

        String sp=sc.nextLine();

        String [] str=sp.split("-");

        double qz=Double.parseDouble(str[2]) ;

        int sl=Integer.parseInt(str[4]);

        Class.forName("com.mysql.jdbc.Driver");

        String jdbc="jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK";

        Connection conn=DriverManager.getConnection(jdbc, "root", "");

        String sql="insert into fruit values(?,?,?,?,?,NULL)";

        PreparedStatement state=conn.prepareStatement(sql);

        state.setString(1, str[0]);

        state.setString(2, str[1]);

        state.setDouble(3, qz);

        state.setString(4, str[3]);

        state.setInt(5, sl);

        state.executeUpdate();

        System.out.println("添加成功！！！");

        conn.close();

    }

    public static void goumai() throws Exception{

        Scanner sc=new Scanner(System.in);

        System.out.println("请输入购买的水果种类或代号");

        String mz=sc.nextLine();

        System.out.println("请输入购买数量");

        int sl=Integer.parseInt(sc.nextLine());

        Class.forName("com.mysql.jdbc.Driver");

        String jdbc="jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK";

        Connection conn=DriverManager.getConnection(jdbc,"root","");

        conn.setAutoCommit(false);

        //查询苹果单价

        String sql2="select * from fruit where Name=? or Ids=?";

        PreparedStatement state=conn.prepareStatement(sql2);

        state.setString(1, mz);

        state.setString(2, mz);

        ResultSet re=state.executeQuery();

        re.next();

        double zj=re.getDouble(3)*sl;//总价格

        //修改商品数量

        String sl_sql="update fruit set Numbers=? where Name=? or Ids=?";

        PreparedStatement state_sl=conn.prepareStatement(sl_sql);

        state_sl.setInt(1, (re.getInt(5)-sl));

        state_sl.setString(2, mz);

        state_sl.setString(3, mz);

        state_sl.executeUpdate();

        //修改用户剩余钱数

        String sy="update login set Account=Account-? where UserName=?";

        PreparedStatement dstate=conn.prepareStatement(sy);

        dstate.setDouble(1, zj);

        dstate.setString(2, zh);

        dstate.executeUpdate();

        //最后查询剩余钱数

        String zhye="select * from login where UserName=?";

        PreparedStatement state_ye=conn.prepareStatement(zhye);

        state_ye.setString(1, zh);

        ResultSet re1 =state_ye.executeQuery();

        re1.next();

        System.out.println("购买成功!!!您当前账户余额为"+re1.getDouble(4)+"元");

        conn.commit();

        conn.close();

    }

}