package  com.bean; 

import java.sql.*;
import java.util.*;
/**
 * <p>Title: JSP+Bean連接數(shù)據(jù)庫</p>
 * <p>Description: 本實(shí)例演示JSP+Bean框架實(shí)現(xiàn)數(shù)據(jù)庫查詢操作。</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Filename: JDBCConn.java</p>
 * @author 杜江
 * @version 1.0
 */
public class JDBCConn{
  public static String url="jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
  public static String username="test";
  public static String password="test";
/**
 *<br>方法說明：獲得數(shù)據(jù)連接
 *<br>輸入?yún)?shù)：
 *<br>返回類型：Connection 連接對象
 */  
  public Connection conn(){
     try {
     	//加載JDBC驅(qū)動
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //創(chuàng)建數(shù)據(jù)庫連接
        Connection con = DriverManager.getConnection(url, username, password);
        return con;
    }catch(SQLException sqle){
    	System.out.println("can't connection db:"+sqle);
    	return null;
    } catch (Exception e) {
        System.out.println("Failed to load JDBC/ODBC driver.");
        return null;
     }
  }
/**
 *<br>方法說明：執(zhí)行查詢SQL語句
 *<br>輸入?yún)?shù)：Connection con 數(shù)據(jù)庫連接
 *<br>輸入?yún)?shù)：String sql 要執(zhí)行的SQL語句
 *<br>返回類型：
 */
  public Vector query( String sql){
  	Connection con = conn();
    Vector vResult = new Vector();
    try{
     if(con==null){
       throw new Exception("database connection can't use!");
     }
     if(sql==null) throw new Exception("check your parameter: 'sql'! don't input null!");
     
     //聲明語句
     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     //執(zhí)行查詢
     ResultSet rs = stmt.executeQuery(sql); 
     //獲取記錄總數(shù)  
     rs.last();  
     int intRowCount = rs.getRow(); 
     ResultSetMetaData rmeta = rs.getMetaData();
     //獲得數(shù)據(jù)字段個數(shù)
     int numColumns = rmeta.getColumnCount();

     while(rs.next())
	 {
	   Vector vTemp = new Vector();
	   for(int i = 0;i< numColumns;i++)
	   {
		String sTemp = rs.getString(i+1);
		vTemp.addElement(sTemp);
	   }
	   vResult.addElement(vTemp);
	 }
	 rs.close();
    stmt.close();
    }catch(Exception e){
      System.out.println("query error:"+e);
    }finally{
     try{
     con.close();
    }catch(SQLException e){}
    }
    return vResult;
  }
/**
 *<br>方法說明：執(zhí)行查詢SQL語句
 *<br>輸入?yún)?shù)：Connection con 數(shù)據(jù)庫連接
 *<br>輸入?yún)?shù)：String sql 要執(zhí)行的SQL語句
 *<br>輸入?yún)?shù)：pageNo  頁碼數(shù)
 *<br>輸入?yún)?shù)：pageSize  記錄條數(shù)
 *<br>返回類型：
 */
  public Vector query(String sql,int pageNo,int pageSize){
    Vector vResult = new Vector();
    Connection con = conn();
    try{
     if(con==null){
       throw new Exception("database connection can't use!");
     }
     if(sql==null) throw new Exception("check your parameter: 'sql'! don't input null!");
     int intRowCount;//記錄總數(shù)
     int intPageCount;//總頁數(shù)
     //聲明語句
     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     //執(zhí)行查詢
     ResultSet rs = stmt.executeQuery(sql); 
     ResultSetMetaData rmeta = rs.getMetaData();
     //獲得數(shù)據(jù)字段個數(shù)
     int numColumns = rmeta.getColumnCount();
     //獲取記錄總數(shù)  
     rs.last();  
     intRowCount = rs.getRow(); 
     vResult.addElement(String.valueOf(intRowCount));
     //記算總頁數(shù)  
     intPageCount = (intRowCount+pageSize-1) / pageSize;
	 //調(diào)整待顯示的頁碼  
     if(pageNo>intPageCount) pageNo = intPageCount; 

	 if(intPageCount>0){
     //將記錄指針定位到待顯示頁的第一條記錄上  
     rs.absolute((pageNo-1) * pageSize + 1); 
     int i = 0;
     while(i<pageSize && !rs.isAfterLast())
	 {
	   Vector vTemp = new Vector();
	   for(int j = 0;j< numColumns;j++)
	   {
	   	
		String sTemp = rs.getString(j+1);
		vTemp.addElement(sTemp);
	   }
	   i++;
	   rs.next();
	  vResult.addElement(vTemp);	
	 }
	}
	rs.close();
    stmt.close();
    }catch(Exception e){
      System.out.println("query error:"+e);
    }finally{
    try{
      con.close();
    }catch(SQLException e){}
   }
    return vResult;
  }
/**
 *<br>方法說明：執(zhí)行插入、更新、刪除等沒有返回結(jié)果集的SQL語句
 *<br>輸入?yún)?shù)：Connection con 數(shù)據(jù)庫連接
 *<br>輸入?yún)?shù)：String sql 要執(zhí)行的SQL語句
 *<br>返回類型：
 */
   public int execute( String sql){
    Connection con = conn();
    try{
     if(con==null) return -2;
     Statement stmt = con.createStatement();
     return stmt.executeUpdate(sql); 
    }catch(Exception e){
      System.out.println("query error:"+e);
      return -1;
    }finally{
     try{
      con.close();
    }catch(SQLException e){}
    }
  }

}