Java任务：商品库的增删改查

来自CloudWiki
跳转至： 导航， 搜索

任务描述：商品库的增删查改

如图7-2所示，管理员可以实现对商品库的查询 和商品信息的修改。

Java-20-10-1.png

图7-2 查看和修改商品

定义接口

商品数据接口GoodsDao

package dao;

import entity.Goods;

public interface GoodsDao {
	  public int getNum();//查询商品总类别数
	  
	  public int getNum(int id);//查询某商品的数量
	  
	  public void add(Goods g);//增加商品
	  
	  public void delete(int id);//删除商品
	  
	  public String findAll();//查询所有商品
	  
	  public String find(String name);//查询某种商品是否存在
	  
	  public void modify(int id,float p);//修改商品
}

实现思路

  • 我们现在已经定义了一个GoodsDao接口。
  • 接下来我们用MYSQL数据库来实现这个接口。

任务实现

创建测试数据

接下来我们在 MySQL 中创建 cloud 数据库，并创建 goods 数据表，表结构如下： 

mysql> create database cloud;
    
Query OK, 1 row affected (0.00 sec)

mysql> use cloud;

 Database changed

数据表 goods:

 CREATE TABLE IF NOT EXISTS `goods` (    
       `id` INT unsigned AUTO_INCREMENT,      
       `name` VARCHAR(100) NOT NULL,    
        `price`  FLOAT NOT NULL,     
        `num` INT(11) NOT NULL,       
        PRIMARY KEY (`id`),    
         UNIQUE KEY `gname` (`name`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


desc goods;

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)     | NO   | UNI | NULL    |                |
| price | float            | NO   |     | NULL    |                |
| num   | int(11)          | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

创建实现类GoodsDaoSQL

UserDaoSQL： 

package dao;

import java.sql.*;

import entity.*;

public class GoodsDaoSQL implements GoodsDao{
	// JDBC 驱动名及数据库 URL
		static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
		static final String DB_URL = "jdbc:mysql://10.0.0.30:3306/cloud?characterEncoding=utf8";
		//	static final String DB_URL = "jdbc:mysql://localhost:3306/cloud?characterEncoding=utf8";
		
		// 数据库的用户名与密码，需要根据自己的设置
	    static final String USER = "root";
	    static final String PASS = "000000";
	    Connection conn;
		
	    public GoodsDaoSQL(){
	    	 
	    }

建立连接函数

 
private boolean getConn() {     
	     
	    	//Connection conn = null;       
	        try {
	            Class.forName(JDBC_DRIVER); //classLoader,加载对应驱动
	            this.conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
	            System.out.println("MySQL连接成功！");
	        } catch (ClassNotFoundException e) {
	        	System.out.println(e.getMessage());
	        	return false;
	        } catch (SQLException e) {
	        	System.out.println(e.getMessage());
	        	return false;
	        }
	        return true;
	   }

在类的构造函数仲添加此函数的调用： 

    public GoodsDaoSQL(){
	    	 this.getConn();
    }

编写getNum方法

返回现在的商品总数。 

public int getNum() {
		 
		 int rowCount = 0;
	     try { 
		    		ResultSet rs = null;   //建立一个空的结果集
		            //1.创建PreparedStatement 对象，让它与一条SQL模板绑定；
		    		String sql = "select count(*) record from  goods";
		    		PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);                           
	                 //2.执行查询 
		            rs = pstmt.executeQuery();//执行查询		           
		            if(rs.next()){
		               rowCount=rs.getInt("record");
		            }
	                pstmt.close();	    	     		
		            
		     }catch (SQLException e) {//捕捉、处理异常
		             System.out.println(e.getMessage());
		     }
		  return rowCount;
	  }

编写getNum(int id)方法

按id查询某一商品数量 

public int getNum(int id) {//按id查询某一商品
		  int rowCount = 0;
		  try { 
			    		ResultSet rs = null;   //建立一个空的结果集
			            //1.创建PreparedStatement 对象，让它与一条SQL模板绑定；
			    		String sql = "select num from  goods where id = ?";
			    		PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
			    		pstmt.setInt(1, id);
		                 //2.执行查询 
			            rs = pstmt.executeQuery();//执行查询		           
			            if(rs.next()){
			               rowCount=rs.getInt("num");
			            }
		                pstmt.close();	    	     		
			            
			     }catch (SQLException e) {//捕捉、处理异常
			             System.out.println(e.getMessage());
			     }
			  return rowCount;
		}

编写add方法

功能：向商品库中添加商品 

 public void add(Goods g) {//增加商品
		  Connection conn =  this.conn;
	      int i = 0;
	      String sql = "insert into goods (name,price,num) values(?,?,?)";
	      PreparedStatement pstmt;
	      try {
	            pstmt = (PreparedStatement) conn.prepareStatement(sql);
	            pstmt.setString(1, g.getName());
	            pstmt.setFloat(2, g.getPrice());  //pstmt.setInt(2, user.getAge()); pstmt.setDate  
	            pstmt.setInt(3, g.getNum());  
	            
	            i = pstmt.executeUpdate();
	            if(i!=0){
	            	System.out.println(g.getName()+"插入成功!");
		        }else {
		        	System.out.println(g.getName()+"插入失败!");
		        }
	            pstmt.close();
	            
	        } catch (SQLException e) {
	        	System.out.println(e.getMessage());
	        }
	        
	  }

编写delete方法

功能：删除商品方法。 

public void delete(int id) {//删除商品
		    Connection conn = this.conn;
	        int i = 0;
			String sql = "DELETE FROM goods WHERE id = ?"; 
			
	    	try {   		
	          
	    		PreparedStatement pstmt = conn.prepareStatement(sql);  
	    		pstmt.setInt(1, id);            
	    		i = pstmt.executeUpdate();
	                if(i!=0){    	    System.out.println("商品"+id+"删除成功!");
		            }else {
		            	System.out.println("商品"+id+"删除失败!");
		            }
	    		pstmt.close();
	            
	    	}catch (SQLException e) {
	        	System.out.println(e.getMessage());
	        }	       
	  }

编写findAll()方法

功能：查询所有商品 

  	  public String findAll() {//查询所有商品
		  String sql = "select * from goods";
		  String total ="";
	      PreparedStatement pstmt;
	      try {
	            pstmt = (PreparedStatement)conn.prepareStatement(sql);
	            
	            //3.执行查询 
	            ResultSet  rs = pstmt.executeQuery();//执行查询
	            int col = rs.getMetaData().getColumnCount();//获取字段数
	            
	            
	            //4. 打印数据表表头	  
	            
	            for (int i = 1; i <= col; i++) {
	            	String col_name = rs.getMetaData().getColumnName(i);
	            	total +=(col_name+"\t");
	            	if (i == 2 ) {//此行仅仅是为了显示更好看，可以删去
                    	total +=("\t");
                    }
	            	
	            }
	            total +=("\r\n");
	            total +=("==================================\r\n");          
	            
	            
	            while (rs.next()) {
	                for (int i = 1; i <= col; i++) {
	                	total +=(rs.getString(i) + "\t");
	                    if ((i == 2) && (rs.getString(i).length() < 8)) {//此行仅仅是为了显示更好看，可以删去
	                    	total +=("\t");
	                    }
	                 }
	                total +=("\r\n");
	            }
	            
	        } catch (SQLException e) {
	        	total =(e.getMessage());
	        }
	        return total;
	  }

编写find(String name) 方法

功能：查询某种商品是否存在 

public String find(String name) {//查询某种商品是否存在
		  String total = "";
		  try { 
	    		ResultSet rs = null;   //建立一个空的结果集

	            //1.创建PreparedStatement 对象，让它与一条SQL模板绑定；
	    		String sql = "select * from goods where name LIKE ?";
	    		PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
	    		
	            //使用2. setXXX()系列方法为sql命令设置值
	    		pstmt.setString(1, "%"+name+"%");//                        

	            //3.执行查询 
	            rs = pstmt.executeQuery();//执行查询
	            int col = rs.getMetaData().getColumnCount();//获取字段数
	                       
	            
	            //4. 打印数据表表头	           
	            
	            for (int i = 1; i <= col; i++) {
	            	String col_name = rs.getMetaData().getColumnName(i);
	            	total +=(col_name+"\t");
	            	if (i == 2 ) {//此行仅仅是为了显示更好看，可以删去
                    	total +=("\t");
                    }
	            }
	            total +=("\r\n");
	            total +=("==================================\r\n");
	            
	           while (rs.next()) {
	                for (int i = 1; i <= col; i++) {
	                	total +=(rs.getString(i) + "\t");
	                    if ((i == 2) && (rs.getString(i).length() < 8)) {//此行仅仅是为了显示更好看，可以删去
	                    	total +=("\t");
	                    }
	                 }
	                total +=("\r\n");
	            }
	           
	    	   pstmt.close();
	    		
	            
	    	 }catch (SQLException e) {//捕捉、处理异常
	             total =(e.getMessage());
	        }
		  return total;
	  }

编写modify方法

功能：修改商品价格 

public void modify(int id,float p) {
		  //修改商品
		  int i = 0;
	      String sql = "UPDATE goods SET  price=?"  
	                 + " WHERE id=?";  
	      PreparedStatement pstmt;
	      try {
	             pstmt = (PreparedStatement) conn.prepareStatement(sql);
	             pstmt.setFloat(1, p);
	             pstmt.setInt(2,id);            
	             i = pstmt.executeUpdate();
	             if(i!=0){ 
	            	 System.out.println("商品"+id+"修改成功!");
		         }else {
		        	 System.out.println("商品"+id+"修改失败!");
		         }
	             pstmt.close();
	             
	         } catch (SQLException e) {
	         	System.out.println(e.getMessage());
	         }
	                 
	  }

编写toString方法

功能：以字符串形式 返回商品库信息。 

public String toString() {		
	        return findAll();
	  }

</nowiki>

测试验证

编写测试类

main/TestGoods: 

package main;


import java.util.Scanner;

import dao.*;
import entity.*;

public class TestGoods {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		GoodsDao s = new GoodsDaoSQL(); 	
		
		
		/* 向商品库中添加商品 */
		Goods g1= new Goods("1","山西刀削面",105.0f,30);
		Goods g2= new Noodles("2","兰州牛肉面",15.0f,20);
		Goods g3= new Rice("3","黄焖鸡米饭",12.0f,30,false);
		
		//添加元素		
		System.out.println("执行添加餐品操作...");
		System.out.println("添加餐品结果：");s.add(g1);
		System.out.println("添加餐品结果：");s.add(g2);
		System.out.println("添加餐品结果：");s.add(g3);
		
		
		/* 展示商品库中所有商品 */
		System.out.println("共找到商品？件："+s.getNum());
		System.out.println("以下为商品详情信息：");
		System.out.println(s.findAll());
		
		/* 删除商品库中某件商品 */
		System.out.println("执行删除餐品操作...");
		Scanner sc = new Scanner(System.in);
		System.out.println("请问您想删除什么餐品？输入餐品id号");
		int id = Integer.valueOf(sc.next());
		int num = s.getNum(id);
		System.out.println("id号为"+id+"餐品现在的商品数为："+num);
		if( num>0 ){ //如果被删除产品数量大于0
			System.out.println("删除餐品结果：");s.delete(id);
		}
		//查询所有商品
		System.out.println(s.findAll());
		
		/*修改商品库中某件商品  */
		System.out.println("请问您想修改什么餐品？输入餐品id");
	    id = Integer.valueOf(sc.next());
	    System.out.println("餐品修改后的价格为？");
	    float price= Float.valueOf(sc.next());
		System.out.println("修改商品'"+id+"'的价格为"+price+":");
		System.out.println("修改餐品结果：");s.modify(id, price);
		System.out.println(s.findAll());
		
		/* 根据商品名称查找商品  */
		System.out.println("请问您想查询什么餐品？输入餐品关键词：");
	    String name = sc.next();
		System.out.println(s.find(name));
		
		
	}

}

}

运行效果

Java-20-10-1.png

用到的其他类

Goods: 

package entity;

public class Goods {
	protected String id;
	protected  String name;//名称
	protected  float price;//价格
	protected  int num;//数量
	
	public Goods() {
		
	}
	public Goods(String n,float p,int num) {
		this.name = n;
		this.price = p;
		this.num = num;
	}
	public Goods(String id,String n,float p,int num) {
		this.id = id;
		this.name = n;
		this.price = p;
		this.num = num;
	}
	public String getId() {
		return this.id;
	}
    public String getName() {//name的读方法
		
	    return this.name;
	}
	public void setName(String newName) {//name的写方法
	     this.name = newName;
	}
	public float getPrice( ){//价格的读方法
	    return this.price;
	}
	
	public void setPrice(float p) {//价格的写方法
		if(p<=0) {
			System.out.println("价格必须为正！");
		}else {
		    this.price =p;
		}
	}
	public int getNum() {//num的读方法
	    return this.num; 
	}
	public void setNum(int num) {
		if(num<0) {
			System.out.println("数量不能为负！");
		}else {
			this.num=num;
		}
	}
	public String toString() {
		String s="";
		s= "名称："+this.name+"; 价格："+this.price 
		  +"； 数量："+this.num+";";
		return s;
		
	}
	public float getProfit() {
		float p =this.price *0.15f;
		return p;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
        Goods g1= new Goods("黄焖鸡米饭",15,1);
        System.out.println("商品信息为："+g1.toString());
        
	}

}
取自“http://www.openbrains.net/mediawiki/index.php?title=Java任务：商品库的增删改查&oldid=19047”