package com.atlassian.user.generic;
   
   import org.springframework.dao.DataAccessException;
   import org.springframework.jdbc.core.ConnectionCallback;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.core.PreparedStatementCallback;
   
   import java.sql.*;
   import java.util.*;
  
  public class JdbcHelper
  {
  
  //    private void deleteAllTables(JdbcTemplate jdbcTemplate) throws Exception
  //    {
  //        List tables = getNonEmtpyTables(jdbcTemplate);
  //        while (tables.size() > 0)
  //        {
  //            for (Iterator i = tables.iterator(); i.hasNext();)
  //            {
  //                String name = (String) i.next();
  //                jdbcTemplate.execute("delete from " + name);
  //            }
  //            tables = getNonEmtpyTables(jdbcTemplate);
  //        }
  //    }
  
  
      @SuppressWarnings({"unchecked"})
      public List<HashMap<Object, Object>> executeQuery(JdbcTemplate jdbcTemplate, String sql)
      {
          return (List<HashMap<Object, Object>>) jdbcTemplate.execute(sql, new PreparedStatementCallback()
          {
              public Object doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException
              {
                  List<HashMap<Object, Object>> result = new ArrayList<HashMap<Object, Object>>();
                  ResultSet rs = preparedStatement.executeQuery();
                  ResultSetMetaData md = rs.getMetaData();
                  while (rs.next())
                  {
                      HashMap<Object, Object> row = new HashMap<Object, Object>();
                      for (int i = 1; i <= md.getColumnCount(); i++)
                      {
                          row.put(md.getColumnName(i), rs.getObject(i));
                      }
                      result.add(row);
                  }
                  return result;
              }
          });
      }
  
      public void deleteTable(JdbcTemplate jdbcTemplate, String table)
      {
          jdbcTemplate.execute("delete from " + table);
      }
  
      @SuppressWarnings({"unchecked"})
      public List<String> getNonEmtpyTables(JdbcTemplate jdbcTemplate)
      {
          final List<String> tables = (List) jdbcTemplate.execute(new ConnectionCallback()
          {
              public Object doInConnection(Connection con) throws SQLException, DataAccessException
              {
                  List<String> result = new ArrayList<String>();
                  DatabaseMetaData databaseMetaData = con.getMetaData();
                  ResultSet rs = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
                  while (rs.next())
                  {
                      result.add(rs.getString("TABLE_NAME"));
                  }
                  return result;
              }
          });
  
          List<String> result = new LinkedList<String>();
          for (Object table : tables)
          {
              String tableName = (String) table;
              int count = getTableCount(jdbcTemplate, tableName);
              if (count > 0)
              {
                  result.add(tableName);
              }
          }
          return result;
      }
  
      public int getTableCount(JdbcTemplate jdbcTemplate, String tableName)
      {
          return (Integer) jdbcTemplate.execute("select count(*) from " + tableName, new PreparedStatementCallback()
          {
              public Object doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException
              {
                  ResultSet rs = preparedStatement.executeQuery();
                  if (rs.next())
                  {
                      return rs.getInt(1);
                  }
                 else
                 {
                     throw new RuntimeException("unexpected empty result set");
                 }
 
             }
         });
     }
 
 }