title: accessing excel as database with jdbc
body: <p>I have to design an application for accessing excel application as database. My problem is I have to create multiple connection for each transaction and if I miss any of closing it, the excel is not being update. </p>
  
  <p>I want to design pattern where i am able to access the excel. Any one help me in designing a common pattern through which i wont be having problem. I want something like <a href="http://code.google.com/p/jdbcutil/" rel="nofollow">this</a>, but we are not able to use it to access excel. </p>
  
  <p>Thanks in advance!</p>
  
  <p>i have this method in utility class </p>
  
  <pre><code>static ResultSet  getExcelData(String filePath,String sqlQuery){
      ResultSet rs=null;
  
  
      try{
          conn    = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="+filePath+";READONLY=false");
          stmt=    conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
          rs=stmt.executeQuery( sqlQuery ); 
      }catch (Exception e) {
          e.printStackTrace();
          return null;
          // TODO: handle exception
      }finally{
  
  
      }
      return rs;
  
  }
  </code></pre>
  
  <p>and i am calling it this way    </p>
  
  <pre><code>ResultSet rs=JdbcUtil.getExcelData("D:\\AB_demo\\AB_demo\\test.xls", "Select max(int(ID)) from [MAIN$] where HEADER_IND is not Null AND int(ID)&lt;"+excelId);
          int databaseId = 0;
          if(rs.next())
          {
              databaseId=rs.getInt(1);    
          }
  
  
          ResultSet rs1=JdbcUtil.getExcelData("D:\\AB_demo\\AB_demo\\test.xls", "SELECT * from [MAIN$]  where   id= '"+databaseId+"'or id='"+excelId+"'");
  </code></pre>
  
  <p>i am calling this method twice after which im updating the excel file by using </p>
  
  <pre><code>stmt.executeUpdate(sql);
  </code></pre>
  
  <p>its returning the integer 1 but its not reflecting in excel.when i use process explorer the file is still in use.i need a design pattern or come code to overcome his kind of problem.</p>