package com.hb.jdbcconnct;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Vector;

import javax.swing.JOptionPane;


/**
 * 定義一個類 JdbcConnct用于連接Oracle數(shù)據(jù)庫
 * @author Administrator *
 */
public class JdbcConnct {
    
 	/**
 	 * 定義一個方法fetchConnection() 獲取連接對象
 	 */
 	public static Connection fetchConnection() {
 		// 導(dǎo)入JDBC driver
 		try {
 			FileInputStream fin = new FileInputStream("config.properties");
 			Properties property = new Properties();
 			

 			property.load(fin);

 			String url = property.getProperty("url");
 			String user = property.getProperty("user");
 			String psw = property.getProperty("psw");
 			Class.forName("oracle.jdbc.driver.OracleDriver") ;
// 			String url = "jdbc:oracle:thin:@192.168.111.79:1521:orcl";
// 			String user = "zlb";
// 			String pwd = "hb2080";
 			return DriverManager.getConnection(url, user, psw);
 		} catch (Exception e) {
 			e.printStackTrace();
 			return null;
 		}

 	}
 	
 	/**
	 * 更新數(shù)據(jù)庫數(shù)據(jù),getData()
	 * @param strSQL  String類型，更新數(shù)據(jù)庫的SQL語句
	 * @return int updateData 更新受影響行數(shù)，否則返回-1，若發(fā)生異常，則返回0
	 * @throws SQLException
	 */
 	public int updateData(String strSQL) throws SQLException {
		// 建立一個JDBC對象
		JdbcConnct jdbcConnection = new JdbcConnct();
		// 建立一個數(shù)據(jù)庫連接對象
		Connection connection = null;
		// 建議一個SQLStatement狀態(tài)對象
		Statement statement = null;
		//定義一個int變量 updataLine 返回影響的行數(shù)
		int updataLine = 0;
		//獲得SQL語句
		String sql1 = strSQL;
		
		try {
			System.out.println("before");
			connection = jdbcConnection.fetchConnection();
			statement = connection.createStatement();
			updataLine = statement.executeUpdate(sql1);	
		} catch (SQLException e) {
			//e.printStackTrace();
			return 0;
		}catch(Exception e1){
			e1.printStackTrace();
			return 0;			
		}
		finally {
			connection.close();
			statement.close();
		}
		return updataLine;
	}
 	
	
	/** 得到數(shù)據(jù)庫數(shù)據(jù),getData()
	 * 
	 * @param strSQL String類型，更新數(shù)據(jù)庫的SQL語句
	 * @return Vector變量，返回數(shù)據(jù)集所有數(shù)據(jù)，Vector為二維變量，每一維存儲一行 String[]
	 * @throws SQLException
	 */
	public Vector getData(String strSQL) throws SQLException {
		// 建立一個JDBC對象
		JdbcConnct jdbcConnection = new JdbcConnct();
		// 建立一個數(shù)據(jù)庫連接對象
		Connection connection = null;
		// 建議一個SQLStatement狀態(tài)對象
		Statement statement = null;
		// 建立一個ResultSet結(jié)果集對象
		ResultSet resultSet = null;
		//定義一個Vector變量 vecData 存儲數(shù)據(jù)集
		Vector vecData = new Vector();
		//定義int變量 ncoloumn,數(shù)據(jù)庫表的列數(shù)
		int ncoloumn = 0;		
		//獲得SQL語句
		String sql1 = strSQL;
		
		try {
			System.out.println("before");
			connection = jdbcConnection.fetchConnection();
			statement = connection.createStatement(resultSet.TYPE_SCROLL_INSENSITIVE,
					resultSet.CONCUR_READ_ONLY);
			resultSet = statement.executeQuery(sql1);
			//獲取數(shù)據(jù)庫表的列數(shù)
			ncoloumn = resultSet.getMetaData().getColumnCount();
			//定義String變量 strrowData,一行數(shù)據(jù)
			ArrayList aryrowData = new ArrayList(ncoloumn);

			//數(shù)據(jù)集對象游標(biāo)定位到第一個個數(shù)據(jù)前
			resultSet.beforeFirst();
			while (resultSet.next()) {
				//定義一個一維數(shù)組變量 ,即時定義，即時增加
				String [] strTemp = new String[ncoloumn];
				for(int i=0; i<ncoloumn ;i++ ){
					strTemp[i] = resultSet.getString(i+1);					
				}
				vecData.add(strTemp);			
			}		
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}catch(Exception e1){
			e1.printStackTrace();
			return null;			
		}
		finally {
			connection.close();
			statement.close();
			resultSet.close();
		}
		return vecData;
	}
 	 	

 	/**
 	 * 入口方法
 	 */
 	public static void main(String [] args)throws Exception {
		// 建立一個Connection對象
		Connection connection = null;
		try{
			connection = fetchConnection();
			// 打印連接對象的指針值
			System.out.println("\tConnection: " + connection);
			
		}catch(Exception e){
			e.printStackTrace();	
			
		}finally{
			//關(guān)閉連接對象
			connection.close();			
		}			
 	}     

}