package  com.bean; 

import java.sql.*;
import java.util.*;
/**
 * <p>Title: JSP+Bean連接數據庫</p>
 * <p>Description: 本實例演示JSP+Bean框架實現數據庫查詢操作。</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>方法說明：獲得數據連接
 *<br>輸入參數：
 *<br>返回類型：Connection 連接對象
 */  
  public Connection conn(){
     try {
     	//加載JDBC驅動
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //創建數據庫連接
        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>方法說明：執行查詢SQL語句
 *<br>輸入參數：Connection con 數據庫連接
 *<br>輸入參數：String sql 要執行的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);
     //執行查詢
     ResultSet rs = stmt.executeQuery(sql); 
     //獲取記錄總數  
     rs.last();  
     int intRowCount = rs.getRow(); 
     ResultSetMetaData rmeta = rs.getMetaData();
     //獲得數據字段個數
     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>方法說明：執行查詢SQL語句
 *<br>輸入參數：Connection con 數據庫連接
 *<br>輸入參數：String sql 要執行的SQL語句
 *<br>輸入參數：pageNo  頁碼數
 *<br>輸入參數：pageSize  記錄條數
 *<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;//記錄總數
     int intPageCount;//總頁數
     //聲明語句
     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     //執行查詢
     ResultSet rs = stmt.executeQuery(sql); 
     ResultSetMetaData rmeta = rs.getMetaData();
     //獲得數據字段個數
     int numColumns = rmeta.getColumnCount();
     //獲取記錄總數  
     rs.last();  
     intRowCount = rs.getRow(); 
     vResult.addElement(String.valueOf(intRowCount));
     //記算總頁數  
     intPageCount = (intRowCount+pageSize-1) / pageSize;
	 //調整待顯示的頁碼  
     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>方法說明：執行插入、更新、刪除等沒有返回結果集的SQL語句
 *<br>輸入參數：Connection con 數據庫連接
 *<br>輸入參數：String sql 要執行的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){}
    }
  }

}