databaseoperator.java

这是我用eclipse+tomcat开发的一个很小的 管理系统,用到了jsp和javabean,其中有一个goods.sql文件是数据库的脚本.因为我刚学J2EE,做得不好的地方,请原谅

package Bean;

import java.sql.*;
import java.util.*;


// 数据库操作
public class DatabaseOperator
{
   Connection conn = null;
   Statement stmt = null; 
   ResultSet rtst = null;
   PreparedStatement pst = null;
   
   public DatabaseOperator()
   {
   	  try
   	  {
		conn = Database.getConnection();
		stmt = conn.createStatement();
   	  }
   	  catch(SQLException  e)
   	  {
   	  
   	  }
		
   }
   
   // 执行表更新
   public int executeSql(String sql)
   {
	
	 try
	 {
	   return stmt.executeUpdate(sql);
	 }
	 catch (SQLException se)
	 {
		 try
		 {
			conn.rollback();
			stmt.close();
		 	conn.close();
			
		 }
		 catch(Exception e)
		 {
		 }
		 
			return 0;
		
	 }
     
   }
   // 执行表查询
   public Vector executeSelectSql(String sql)
   {
     try
	 {
		 
		 rtst = stmt.executeQuery(sql);
         Vector v = new Vector();
		 while(rtst.next())
		 {
		   GoodsBean goods = new GoodsBean();
           
		   goods.setGoodsID(rtst.getString("goodsID"));
		   goods.setGoodsName(rtst.getString("goodsName"));
		   goods.setGoodsPrice(rtst.getFloat("goodsPrice"));
		   goods.setGoodsQuantity(rtst.getInt("goodsQuantity"));
		   goods.setGoodsType(rtst.getString("goodsType"));

		   v.add(goods);
		   
		 }
         
		 return v;
	 }
	 catch (SQLException se)
	 {
		 try
		 {
			stmt.close();
			rtst.close();
		 	conn.close();
			
		 }
		 catch(Exception e)
		 {
            
		 }
		
		 return null;
		
	 }
   }

   public boolean addGoods(GoodsBean goods)
   {
	  try
	  {
	  	  pst = conn.prepareStatement("insert into Goods values(?,?,?,?)");
		  pst.setString(1, goods.getGoodsName());
		  pst.setFloat(2, goods.getGoodsPrice());
		  pst.setInt(3, goods.getGoodsQuantity());
		  pst.setString(4, goods.getGoodsType());
		  pst.executeUpdate();
		  
		  return true;
	  }
	  catch(SQLException se)
	  {
			try
			{
				
				pst.close();
				conn.close();
				
			}
			catch(Exception e)
			{

			}
			
			  return false;
			
	  }
	
      
   }
   
   public boolean deleteGoodsByID(int ID)
   {
     if(executeSql("delete Goods where goodsID ="+ ID ) > 0)
     {
       return true;
     }
     else
     {
       return false;
     }
   }

   public Vector getGoodsByID(int ID)
   {
   	 return executeSelectSql("select * from Goods where goodsID =" + ID );
   }
   public Vector getAllGoods()
   {
	return executeSelectSql("select * from Goods order by goodsID");
   }
   
   public Vector getGoodsBy(GoodsBean goods )
   {
     String sql = "select * from Goods where 1=1 ";
	 if(goods.getGoodsID() != null && goods.getGoodsID().trim().length() > 0)
	 {
	   sql = sql + " and goodsID like " + goods.getGoodsID();
	 }
	 if(goods.getGoodsName() != null && goods.getGoodsName().trim().length() > 0)
	 {
	   sql = sql + " and goodsName like '" + goods.getGoodsName() + "'";
	 }
	 if(goods.getGoodsPrice() > 0)
	 {
	   sql = sql + " and goodsPrice =" + goods.getGoodsPrice() ;
	 }
	 if(goods.getGoodsQuantity() > 0)
	 {
	   sql = sql + " and goodsQuantity =" + goods.getGoodsQuantity() ;
	 }
     if(goods.getGoodsType() != null && goods.getGoodsType().trim().length() > 0)
	 {
	   sql = sql + " and goodsType like '" + goods.getGoodsType() + "'";
	 }
    
	 return executeSelectSql(sql);
   }
  
   public boolean updateGoods(GoodsBean goods)
   {
      try
	  {
	  	  
		  pst=conn.prepareStatement("update Goods set goodsName=?, goodsPrice=?, goodsQuantity=?, goodsType=? "
		                        + " where goodsID=?");

		  pst.setString(1, goods.getGoodsName());
		  pst.setFloat(2, goods.getGoodsPrice());
		  pst.setInt(3, goods.getGoodsQuantity());
		  pst.setString(4, goods.getGoodsType());
          pst.setString(5, goods.getGoodsID());
		  pst.executeUpdate();
		  return true;
	  }
	  catch(SQLException se)
	  {
			try
			{
				
				pst.close();
				conn.close();
				
			}
			catch(Exception e)
			{

			}
			
			  return false;
			
	  }
   }
   
   // 判断用户是否合法
	 public boolean isLeaglUser(String strUserName, String strPassword)
	 {
		 boolean blnIsLegal = false;
    	
		 try
		 {
		   rtst = stmt.executeQuery("select * from SysUser");
		   if(rtst != null)
		   { 
			 boolean blnIsExist = false;
			 while(rtst.next())
			 {
				
				if(rtst.getString("userName").equals(strUserName))
				{
				  blnIsExist = true;
				  break;
				}
			 }
			 if(blnIsExist)
			 {
			   
			   if(rtst.getString("userPwd").equals(strPassword))
			     blnIsLegal = true;
			 }
		   }
		   return blnIsLegal;
	  
		 }
		 catch(SQLException e)
		 {
		   e.printStackTrace();
		 }
	
		 return blnIsLegal;
		
      
	 }
}