Java项目超市管理系统（七）数据库的增、删、改、查操作

骑猪看日落 2022-12-29 02:11 156阅读 0赞

--------------------

首先是对goods数据库的操作。

package lyons.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    
    import lyons.db.DbClose;
    import lyons.db.DbConn;
    import lyons.entity.Goods;
    import lyons.tools.ScannerChoice;
    
    /** * 数据库goods表操作 * @author lyons(zhanglei) */
    public final class GoodsDao 
    { 
    		Connection        conn  = null;
    		PreparedStatement pstmt = null;
    		ResultSet 		  rs    = null;
    	
    	/** * 1.添加商品到数据库goods表 * @param goods 商品对象 * @return boolean */
    	public boolean addGoods(Goods goods)
    	{ 
    		boolean bool = false;
    		conn = DbConn.getconn();
    		String sql = "INSERT INTO GOODS(GNAME,GPRICE,GNUM) VALUES(?,?,?)";
    			
    			try
    			{ 
    				pstmt = conn.prepareStatement(sql);
    				pstmt.setString(1,goods.getGname());
    				pstmt.setDouble(2,goods.getGprice());
    				pstmt.setInt(3,goods.getGnum());
    				
    				int rs = pstmt.executeUpdate();
    				if (rs > 0)
    				{ 
    					bool = true;
    				}
    			} catch (SQLException e)
    			{ 
    				e.printStackTrace();
    			}finally
    					{ 
    						DbClose.addClose(pstmt,conn);
    					}
    	 return bool;
    	}
    
    	/** * 2.更改商品信息到数据库goods表 * @param key 选择要更改商品信息 * @param goods 商品对象 * @return boolean */
    	public boolean updateGoods(int key,Goods goods)
    	{ 
    		boolean bool = false;
    		conn = DbConn.getconn();
    			switch (key)
    			{ 
    			case 1:		// key=1,更改商品名称
    						String sqlName = "UPDATE GOODS SET GNAME=? WHERE GID=?";
    						
    					    try
    						{ 
    							pstmt = conn.prepareStatement(sqlName);
    							pstmt.setString(1, goods.getGname());
    							pstmt.setInt(2,goods.getGid());
    							
    							int rs = pstmt.executeUpdate();
    							if (rs > 0)
    							{ 
    								bool = true;
    							}
    						} catch (SQLException e)
    						{ 
    							e.printStackTrace();
    						}finally{ 
    									DbClose.addClose(pstmt,conn);
    								}
    				break;
    			case 2:		// key=2,更改商品价格
    						String sqlPrice = "UPDATE GOODS SET GPRICE=? WHERE GID=?";
    						
    						try
    						{ 
    							pstmt = conn.prepareStatement(sqlPrice);
    							pstmt.setDouble(1, goods.getGprice());
    							pstmt.setInt(2,goods.getGid());
    							
    							int rs = pstmt.executeUpdate();
    							if (rs > 0)
    							{ 
    								bool = true;
    							}
    						} catch (SQLException e)
    						{ 
    							e.printStackTrace();
    						}finally{ 
    									DbClose.addClose(pstmt,conn);
    								}
    				break;
    			case 3:		// key=3,更改商品数量
    						String sqlNum = "UPDATE GOODS SET GNUM=? WHERE GID=?";
    							
    						try
    						{ 
    							pstmt = conn.prepareStatement(sqlNum);
    							pstmt.setInt(1, goods.getGnum());
    							pstmt.setInt(2,goods.getGid());
    							
    							int rs = pstmt.executeUpdate();
    							if (rs > 0)
    							{ 
    								bool = true;
    							}
    						} catch (SQLException e)
    						{ 
    							e.printStackTrace();
    						}finally{ 
    									DbClose.addClose(pstmt,conn);
    								}
    				break;
    			default:
    				break;
    			}
    		return bool;
    	}
    	
    	/** * 3.从数据库goods表中-刪除商品 * @param gid 商品编号 * @return boolean */
    	public boolean deleteGoods(int gid)
    	{ 
    		boolean bool = false;
    		conn = DbConn.getconn();
    		String sql = "DELETE FROM GOODS WHERE GID=?";
    		
    			try
    			{ 
    				pstmt = conn.prepareStatement(sql);
    				pstmt.setInt(1,gid);
    				int rs = pstmt.executeUpdate();
    				if (rs > 0)
    				{ 
    					bool = true;
    				}
    			} catch (SQLException e)
    			{ 
    				e.printStackTrace();
    			}finally{ 
    						DbClose.addClose(pstmt,conn);
    					}
    		return bool;
    	}
    	
    	/** *4.查询商品信息 * @param key 查询方式 * @return ArrayList<Goods> */
    	public ArrayList<Goods> queryGoods(int key) 
    	{ 										
    		ArrayList<Goods> goodsList = new ArrayList<Goods>();
    		conn = DbConn.getconn();	
    
    		switch (key)
    		{ 
    			case 1:
    					// key=1商品 数量 升序查询
    					String sqlGnum = "SELECT * FROM GOODS ORDER BY GNUM ASC";
    					try
    					{ 
    						pstmt = conn.prepareStatement(sqlGnum);
    						rs = pstmt.executeQuery();
    						while (rs.next())
    						{ 
    							int gid = rs.getInt("gid");
    							String gname = rs.getString(2);
    							double gprice = rs.getDouble(3);
    							int gnum = rs.getInt(4);
    							
    							Goods goods = new Goods(gid,gname,gprice,gnum);
    							goodsList.add(goods);
    						}
    					} catch (SQLException e)
    					{ 
    						e.printStackTrace();
    					}finally
    							{ 
    								DbClose.queryClose(pstmt, rs, conn);
    							}
    				break;
    			case 2:
    				 	// key=2商品 价格 升序查询
    					String sqlGprice = "SELECT * FROM GOODS ORDER BY GPRICE ASC";
    					try
    					{ 
    						pstmt = conn.prepareStatement(sqlGprice);
    						rs = pstmt.executeQuery();
    						while (rs.next())
    						{ 
    							int gid = rs.getInt("gid");
    							String gname = rs.getString(2);
    							double gprice = rs.getDouble(3);
    							int gnum = rs.getInt(4);
    							
    							Goods goods = new Goods(gid,gname,gprice,gnum);
    							goodsList.add(goods);
    						}
    					} catch (SQLException e)
    					{ 
    						e.printStackTrace();
    					}finally
    							{ 
    								DbClose.queryClose(pstmt, rs, conn);
    							}
    				break;
    			case 3:
    					// key=3商品 关键字 查询
    					String nameGet = ScannerChoice.ScannerInfoString();
    					String sqlGname = "SELECT * FROM GOODS WHERE GNAME LIKE '%'||?||'%'";
    					try
    					{ 
    						pstmt = conn.prepareStatement(sqlGname);
    						pstmt.setString(1, nameGet);
    						rs = pstmt.executeQuery();
    						while (rs.next())
    						{ 
    							int gid = rs.getInt("gid");
    							String gname = rs.getString(2);
    							double gprice = rs.getDouble(3);
    							int gnum = rs.getInt(4);
    							
    							Goods goods = new Goods(gid,gname,gprice,gnum);
    							goodsList.add(goods);
    						}
    					} catch (SQLException e)
    					{ 
    						e.printStackTrace();
    					}finally
    					{ 
    						DbClose.queryClose(pstmt, rs, conn);
    					}
    					break;
    			default:
    				break;
    		}
    		return goodsList;
    	}
    
    	/** *5.显示所有商品信息 * @return ArrayList<Goods> */
    	public ArrayList<Goods> displayGoods()
    	{ 
    		ArrayList<Goods> goodsList = new ArrayList<Goods>(); 
    		conn = DbConn.getconn();
    		String sql = "SELECT * FROM GOODS";
    		
    		try
    		{ 
    			pstmt = conn.prepareStatement(sql);
    			rs 	  = pstmt.executeQuery();
    				
    				while (rs.next())
    				{ 
    					int gid = rs.getInt(1);
    					String gname = rs.getString(2);
    					double gprice = rs.getDouble("gprice"); 		//双引号+主键名,也可用数字表示.
    					int gnum = rs.getInt(4);
    					
    					Goods goods = new Goods(gid,gname,gprice,gnum);	//创建Goods对象，并赋值.
    					goodsList.add(goods);							//添加信息到动态数组中.
    				}
    		} catch (SQLException e)
    		{ 
    			e.printStackTrace();
    		}finally
    				{ 
    					DbClose.queryClose(pstmt, rs, conn);
    				}
    		return goodsList;
    		
    	}
    		
    }

--------------------

接下来是对gsales数据库的操作

--------------------

最后是对SalesMan数据库的操作

package lyons.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    
    import lyons.db.DbClose;
    import lyons.db.DbConn;
    import lyons.entity.SalesMan;
    
    /** * 数据库SalesMan表操作 * @author lyons(zhanglei) */
    public final class SalesManDao
    { 
    	 Connection        conn  = null;
    	 PreparedStatement pstmt = null;
    	 ResultSet 		rs 	 	 = null;
    	
    	/** * 1.前台收银登陆 * @param sName 用户名 * @return ArrayList<SalesMan> sPassWord,sId */
     	public ArrayList<SalesMan> checkstandLog(String sName)
    	{ 
     		ArrayList<SalesMan> salesManInfo = new ArrayList<SalesMan>();
    		conn = DbConn.getconn();
    		String sql = "SELECT SID,SPASSWORD FROM SALESMAN WHERE SNAME=?";
    				try
    				{ 
    					pstmt = conn.prepareStatement(sql);
    					pstmt.setString(1,sName);
    					
    					rs 	  = pstmt.executeQuery();
    					while (rs.next())
    					{ 
    						String sPassWord = rs.getString("spassword");
    						int sId = rs.getInt("sId");
    						SalesMan salesMan = new SalesMan(sId,sPassWord); 
    						salesManInfo.add(salesMan);						
    					}
    				} catch (SQLException e1)
    				{ 
    					e1.printStackTrace();
    				}finally
    				{ 
    					DbClose.queryClose(pstmt, rs, conn);
    				}
    	 return salesManInfo;
    	}
    
     	/** * 2.添加售货员 * @param sName 用户名 * @return boolean */
    		public boolean addSalesMan(SalesMan sName)
    		{ 
    			boolean bool = false;
    			conn = DbConn.getconn();
    			String sql = "INSERT INTO SALESMAN(SNAME,SPASSWORD) VALUES(?,?)";
    				
    				try
    				{ 
    					pstmt = conn.prepareStatement(sql);
    					pstmt.setString(1,sName.getSName());
    					pstmt.setString(2,sName.getSPassWord());
    					
    					int rs = pstmt.executeUpdate();
    					if (rs > 0)
    					{ 
    						bool = true;
    					}
    				} catch (SQLException e)
    				{ 
    					e.printStackTrace();
    				}finally
    						{ 
    							DbClose.addClose(pstmt,conn);
    						}
    		return bool;
    		}
    	
    	/** * 3.更改售货员信息 * @param key 要更改项 * @param sName 用户名 * @return boolean */
    	public  boolean updateSalesMan(int key,SalesMan sName)
    	{ 
    		
    		boolean bool = false;
    		conn = DbConn.getconn();
    			switch (key)
    			{ 
    			case 1:		// 3.1 更改售货员姓名
    						String sqlName = "UPDATE SALESMAN SET SNAME=? WHERE SID=?";
    						
    						try
    					{ 
    						pstmt = conn.prepareStatement(sqlName);
    						pstmt.setString(1, sName.getSName());
    						pstmt.setInt(2,sName.getSId());
    						
    						int rs = pstmt.executeUpdate();
    						if (rs > 0)
    						{ 
    							bool = true;
    						}
    					} catch (SQLException e)
    					{ 
    						e.printStackTrace();
    					}finally{ 
    								DbClose.addClose(pstmt,conn);
    							}
    				break;
    			case 2:		// 3.2 更改售货员密码
    						String sqlPrice = "UPDATE SALESMAN SET SPASSWORD=? WHERE SID=?";
    						
    						try
    					{ 
    						pstmt = conn.prepareStatement(sqlPrice);
    						pstmt.setString(1,sName.getSPassWord());
    						pstmt.setInt(2, sName.getSId());
    						
    						int rs = pstmt.executeUpdate();
    						if (rs > 0)
    						{ 
    							bool = true;
    						}
    					} catch (SQLException e)
    					{ 
    						e.printStackTrace();
    					}finally{ 
    								DbClose.addClose(pstmt,conn);
    							}
    				break;
    			default:
    				break;
    			}
    		return bool;
    	}
    
    	/** * 4.删除售货员 * @param sName 用户名 * @return boolean */
    	public boolean deleteSalesMan(String sName)
    	{ 
    		boolean bool = false;
    		conn = DbConn.getconn();
    		String sql = "DELETE FROM SALESMAN WHERE SNAME=?";
    		try
    		{ 
    			pstmt = conn.prepareStatement(sql);
    			pstmt.setString(1,sName);
    			int rs = pstmt.executeUpdate();
    			if (rs > 0)
    			{ 
    				bool = true;
    			}
    		} catch (SQLException e)
    		{ 
    			e.printStackTrace();
    		}finally{ 
    					DbClose.addClose(pstmt,conn);
    				}
    	 return bool;
    	}
    	
    	/** * 5.模糊查询售货员 * @param sName 用户名 * @return ArrayList<SalesMan> */
    	public ArrayList<SalesMan> querySalesMan(String sName)
    	{ 
    		ArrayList<SalesMan> SalesManList = new ArrayList<SalesMan>();
    		conn = DbConn.getconn();	
    	
    		sName = "%"+sName+"%";	//从用户处获取的字符串加上 % 符号，来达到模糊查询的目的.字符串 的连接还有更优秀的方式，待优化代码！
    		String sql = "SELECT * FROM SALESMAN WHERE SNAME LIKE ?";  //居然不能直接跟 % .只能用连接字符串的方式
    		   try
    		   { 
    				pstmt = conn.prepareStatement(sql);
    				pstmt.setString(1, sName);
    				rs = pstmt.executeQuery();
    				while (rs.next())
    				{ 
    					int sid = rs.getInt("sid");
    					String sname = rs.getString(2);
    					String sPassWord = rs.getString(3);
    					
    					SalesMan salesMan = new SalesMan(sid,sname,sPassWord);
    					SalesManList.add(salesMan);
    				}
    			} catch (SQLException e)
    			{ 
    				e.printStackTrace();
    			}finally
    					{ 
    						DbClose.queryClose(pstmt, rs, conn);
    					}
    		return SalesManList;
    	}
    	
    	/** * 6.显示所有售货员 * @return ArrayList<SalesMan> */
    	public  ArrayList<SalesMan> displaySalesMan()
    	{ 
    		ArrayList<SalesMan> salesManList = new ArrayList<SalesMan>();
    		conn = DbConn.getconn(); 
    		String sql = "SELECT * FROM SALESMAN";
    		
    		try
    		{ 
    			pstmt = conn.prepareStatement(sql);
    			rs =  pstmt.executeQuery();
    			while (rs.next())
    			{ 
    				int sId = rs.getInt(1);
    				String sName = rs.getString(2);
    				String sSpassWord = rs.getString(3);
    				
    				SalesMan salesMan = new SalesMan(sId,sName,sSpassWord);
    				salesManList.add(salesMan);
    			}
    		} catch (SQLException e)
    		{ 
    			e.printStackTrace();
    		}finally
    				{ 
    					DbClose.queryClose(pstmt, rs, conn);
    				}
    	 return salesManList;
    	}
    	
    }

至此功能已经全部实现。

接下来是分析学习心得：

1.向数据库发送sql语句的经典调用顺序

conn = DbConn.getconn();
    String sql = "INSERT INTO GOODS(gid,GNAME,GPRICE,GNUM) VALUES(?,?,?,?)";
    pstmt = conn.prepareStatement(sql);
    int rs = pstmt.executeUpdate();

2.查询语句的经典调用顺序

conn = DbConn.getconn();
    String sqlGnum = "SELECT * FROM GOODS ORDER BY GNUM ASC"
    pstmt = conn.prepareStatement(sqlGnum)
    rs = pstmt.executeQuery()
    while (rs.next()){ String str = rs.getString(column);}

3.sql语句的书写有两种方法，一种是直接用string“”+“”+“”的方法，另一种是带参数的，用？代替参数，然后用pstmt.setString(1,sth)来设置。

4.调用完连接后，要用Dbcolse相关方法关闭不用的资源，因为mysql中连接有上线，数据量大的时候不关闭连接会拖慢速度。

5.数据库中最基本的增删改查语法

增：INSERT INTO GOODS(gid,GNAME,GPRICE,GNUM) VALUES(?,?,?,?)
    删：DELETE FROM GOODS WHERE GID=?
    改：UPDATE GOODS SET GPRICE=? WHERE GID=?
    查：SELECT * FROM GOODS ORDER BY GNUM ASC
    SELECT * FROM GOODS WHERE GNAME=?

6.数据库中连接表的操作，添加日期

select* from gsales left join goods using(gid) where sdate=?

7.日期的添加

pstmt.setDate(1, new java.sql.Date(new Date().getTime()));