seedatabean.java

基于struts开发的网上书店系统。希望对大家有帮助。JSP JavaBean Struts

package book;

import java.util.ArrayList;
import java.util.Collection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class SeeDataBean {
	   //这是专门用于处理数据库的Bean，处于控制器的角色
	   //牵涉到数据库的问题，尽量都由这个类来完成
	   //一般来说，业务逻辑尽可能都由控制器来完成   

	   java.sql.Connection conn= null;
	   java.sql.Statement stmt =null;
	   java.sql.ResultSet rs=null;
	  
	   public SeeDataBean()
	   {    
	   }
	  
	  void myconn()
	  {
	      try
	    {
	     Context initCtx = new InitialContext(); 
	     Context envCtx = (Context) initCtx.lookup("java:comp/env"); 
	     DataSource ds = (DataSource)envCtx.lookup("jdbc/book"); 
	     conn=ds.getConnection();    
	     stmt = conn.createStatement();
	     }
	     catch(Exception e)
	     {}
		
	  	
	  }
	  
	  
	  //查询书籍表
	  public String[][] selectrow(String Sql)
	  {

	     myconn();
	     String[][] data;

	     try {

	    
	    	//执行SQL语句
	      stmt.execute(Sql);
	      //取得结果集

	      rs = stmt.getResultSet();

	      Collection c1=new ArrayList();

	     while (rs.next()) {
	      String[] d =new String[5];
	       
	      d[0]= rs.getString("id");
	      d[1]= rs.getString("num");
	      d[2]= rs.getString("title");
	      d[3]= rs.getString("bookconcern");
	      d[4]= rs.getString("price");
	      
	      c1.add(d);
	     }
	     data=new String[c1.size()][5];
	     c1.toArray(data);
	     return data;  
	  } 
	  
	  catch (Exception e) {
	  	return new String[0][0];
	  }
	  finally {

	    if (rs != null)
	     {
	      try{rs.close();}catch(Exception ignore){};
	      }

	     if (stmt != null)
	     {
	      try{stmt.close();}catch(Exception ignore){};
	      }
	     if (conn != null)
	     {
	      try{conn.close();}catch(Exception ignore){};
	      }

	    }         
	              
	       
	  }



	  //查询登录表，返回的是权限码

	  public String selectlogin(String muser,String mpass)
	  {

	     myconn();
	     
	     String right="";
	     
	     try {

	      String Sql="select * from userpass where muser='"+muser+"' and mpass='"+mpass+"'";
	    
	    	//执行SQL语句
	      stmt.execute(Sql);
	      //取得结果集

	      rs = stmt.getResultSet();

	     
	     while (rs.next()) {
	       
	      right= rs.getString("mright");
	      
	     }
	       
	  } 
	  
	  catch (Exception e) {
	  	
	  }
	  finally {

	    if (rs != null)
	     {
	      try{rs.close();}catch(Exception ignore){};
	      }

	     if (stmt != null)
	     {
	      try{stmt.close();}catch(Exception ignore){};
	      }
	     if (conn != null)
	     {
	      try{conn.close();}catch(Exception ignore){};
	      }

	    }
	    
	    return right;         
	              
	  }





	  //建立该页码的查询，通过数组返回两组数据
	  //一个是最大页码数，另一个是查询条件

	  public String[] selectpage(int pageno,int pagesize,String query,String sort)
	  {

	     myconn();
	     String[] mout=new String[2];
	     String[] data;

	     try {

	    
	    	//执行SQL语句
	      stmt.execute("select id from booktable where "+query+" Order By "+sort);
	      //取得结果集

	      rs = stmt.getResultSet();

	      Collection c1=new ArrayList();

	     while (rs.next()) {
	       
	       String d= rs.getString("id");
	       c1.add(d);
	       
	     }
	     
	     data=new String[c1.size()];
	     c1.toArray(data);
	     int idbegin;
	     
	     mout[0]=String.valueOf(Math.round(data.length/pagesize));
	     idbegin=pagesize*pageno;
	     
	     String mywhere=" id < 0 ";
	  
	     for (int k=idbegin;k<idbegin+pagesize && k<data.length;k++)
	        mywhere+=" or id="+data[k];
	        
	        mout[1]=mywhere;
	     return mout;
	  } 
	  
	  catch (Exception e) {
	  	mout[0]="1";
	  	mout[1]="id>0";
	  	return mout;
	  }
	  finally {

	    if (rs != null)
	     {
	      try{rs.close();}catch(Exception ignore){};
	      }

	     if (stmt != null)
	     {
	      try{stmt.close();}catch(Exception ignore){};
	      }
	     if (conn != null)
	     {
	      try{conn.close();}catch(Exception ignore){};
	      }

	    }         
	              
	       
	  }


	  
	   //发送sql语句
	  public boolean postsql(String Sql) {

	     myconn();
	     try
	     {

	       stmt.executeUpdate(Sql);
	       return true;
	      }
	      catch (Exception e)
	      {
	          e.printStackTrace();
	          return false;
	       }
	      finally
	      {
	         if (stmt != null)
	             {
	               try{stmt.close();}catch(Exception ignore){};
	              }
	        if (conn != null)
	           {
	               try{conn.close();}catch(Exception ignore){};
	           }
	       }
	   }


	   //插入购买记录
	   public boolean insertnote(int bid,String nname,String ndwelling,String nemail,int namount)
	   {
	     try
	     {
	         //根据bid调入书的数据
	         Object[][] data=selectrow("select * from booktable where id="+bid);
	                  
	         String id=data[0][0].toString();
	         String num=data[0][1].toString();
	         String title=data[0][2].toString();
	         String bookconcern=data[0][3].toString();
	         String price=data[0][4].toString();

	         //计算价格合计         
	         float fprice=Float.parseFloat(price);
	         float ntotal=namount* fprice;

	        //组织购买记录插入的查询语句
		String sql="INSERT INTO note (name,dwelling,email,num,"+
			   "title,bookconcern,price,amount,total)"+
			   " values ( '"+nname+"',"+
			   "'"+ndwelling+"','"+nemail+"',"+ 
			   num+",'"+title+"','"+bookconcern+"',"+
			   price+","+namount+","+ntotal+")";

	         //提交         
	         if (postsql(sql))           
	             return true;
	          else
	             return false;
	      }
	      catch (Exception e)
	      {
	          e.printStackTrace();
	          return false;
	       }

	    }
	    

	   //修改书籍数据
	   public boolean updatebook(String mid,String mnum,String mtitle,String mbookconcern,String mprice)
	   {
	     try
	     {
	        
		String sql="Update booktable set num='" + mnum
						+"',title='"+mtitle
						+"',bookconcern='"+mbookconcern
						+"',price='"+mprice
					    +"' where id ="+mid;

	         //提交         
	         if (postsql(sql))           
	             return true;
	          else
	             return false;
	      }
	      catch (Exception e)
	      {
	          e.printStackTrace();
	          return false;
	       }

	    }
	}