首先在Mysql中有两个表fruit和login：
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.退出