package com.windy.fileupload;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

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

public class DBConnection {

 private Connection conn = null;

 private Statement stmt = null;

 private PreparedStatement prepstmt = null;

 public DBConnection() {
  try {
   getConnection();

   if (conn != null) {
    stmt = conn.createStatement();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }

 }
 /**
  * Constructor: DBConnection()
  * @param sql  執行的SQL
  */
 public DBConnection(String sql) {
  try {
   getConnection();

   if (conn != null) {
    prepstmt = conn.prepareStatement(sql);
   }
  } catch (SQLException e) {
      e.printStackTrace();
  }
 }
 
 /**
  * getConnection()
  * 初始化 數據源
  * @return Connection
  */
 public Connection getConnection() {

  // try {
  // Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
  // conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs","sa","sa");
  // } catch (Exception e) {
  // }
  try {
   Context ctx = new InitialContext();

   if (ctx == null)
    throw new Exception("SQL Server 2000 - No Context");
   DataSource ds = (DataSource) ctx
     .lookup("java:comp/env/jdbc/fileUploadDown");
   if (ds != null) {
    conn = ds.getConnection();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 }

 /**
  * executeQuery(String sql)
  *
  * @param sql
  * @return Result
  * @throws SQLException
  */
 public ResultSet executeQuery(String sql) throws SQLException {
  if (stmt != null) {
   return stmt.executeQuery(sql);
  } else
   return null;
 }

 /**
  * executeQuery()
  *
  * @return ResultSet
  * @throws SQLException
  */
 public ResultSet executeQuery() throws SQLException {
  if (prepstmt != null) {
   return prepstmt.executeQuery();
  } else
   return null;
 }

 /**
  * executeUpdate(String sql)
  *
  * @param sql
  * @throws SQLException
  */
 public void executeUpdate(String sql) throws SQLException {
  if (stmt != null)
   stmt.executeUpdate(sql);
 }

 /**
  * executeUpdate()
  *
  * @throws SQLException
  */
 public void executeUpdate() throws SQLException {
  if (prepstmt != null)
   prepstmt.executeUpdate();
 }
   
 
 /**
    ResultSet對應的方法

*/
 
 

 public void setString(int index, String value) throws SQLException {
  prepstmt.setString(index, value);
 }

 public void setInt(int index, int value) throws SQLException {
  prepstmt.setInt(index, value);
 }
 
 public void setBlob(int i, Blob value) throws SQLException {
     prepstmt.setBlob(i, value);    
 }
 
 public void setBoolean(int index, boolean value) throws SQLException {
  prepstmt.setBoolean(index, value);
 }
 
 public void setClob(int i, Clob value) throws SQLException {
     prepstmt.setClob(i, value);
 }

 public void setDate(int index, Date value) throws SQLException {
  prepstmt.setDate(index, value);
 }

 public void setLong(int index, long value) throws SQLException {
  prepstmt.setLong(index, value);
 }

 public void setFloat(int index, float value) throws SQLException {
  prepstmt.setFloat(index, value);
 }

 public void setTimestamp(int index, Timestamp value) throws SQLException {
  prepstmt.setTimestamp(index, value);
 }
 
 public void setObject(int index, Object x) throws SQLException {
  prepstmt.setObject(index, x);
 }

 public void setObject(int index, Object x, int targetSqlType) throws SQLException {
  prepstmt.setObject(index, x, targetSqlType);
 } 

 public void setObject(int index, Object x, int targetSqlType, int scale) throws SQLException {
  prepstmt.setObject(index, x, targetSqlType, scale);
 } 
 public void setBinaryStream(int index, InputStream in, int length) throws
  SQLException {
  prepstmt.setBinaryStream(index, in, length);
 }

 /**
  * Close database connection
  */
 public void close() {
  try {
   if (stmt != null) {
    stmt.close();
    stmt = null;
   }

   if (prepstmt != null) {
    prepstmt.close();
    prepstmt = null;
   }

   conn.close();
   conn = null;
  } catch (Exception e) {
   System.err.println("Database close error: " + e);
  }
 }
}