1   package com.atlassian.user.generic;
2   
3   import javax.sql.DataSource;
4   import java.sql.*;
5   import java.util.ArrayList;
6   import java.util.HashMap;
7   import java.util.Iterator;
8   import java.util.List;
9   
10  public class DatabaseHelper
11  {
12  
13      public void deleteAllTables(DataSource dataSource) throws Exception
14      {
15          List<String> tables = getNonEmtpyTables(dataSource);
16          while (tables.size() > 0)
17          {
18              for (Iterator<String> i = tables.iterator(); i.hasNext();)
19              {
20                  String name = i.next();
21                  try
22                  {
23                      executeSQL(dataSource, "delete from " + name);
24                      System.out.println("deleted: " + name);
25                  }
26                  catch (SQLException e)
27                  {
28                      // ignore
29                  }
30              }
31              tables = getNonEmtpyTables(dataSource);
32          }
33  
34      }
35  
36      public void executeSQL(DataSource dataSource, String sql) throws Exception
37      {
38          Connection con = dataSource.getConnection();
39          Statement s = null;
40          try
41          {
42              s = con.createStatement();
43              s.execute(sql);
44          }
45          finally
46          {
47              if (s != null)
48                  s.close();
49              con.close();
50          }
51      }
52  
53      public List<String>/*String*/ getNonEmtpyTables(DataSource dataSource) throws Exception
54      {
55          List<String> result = new ArrayList<String>();
56          Connection con = dataSource.getConnection();
57          try
58          {
59              DatabaseMetaData databaseMetaData = con.getMetaData();
60              ResultSet rs = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
61              while (rs.next())
62              {
63                  String tableName = rs.getString("TABLE_NAME");
64                  List<HashMap<Object, Object>> l = executeQuery(dataSource, "select count(*) from " + tableName);
65                  int count = (Integer) (l.get(0)).get("");
66                  if (count > 0)
67                  {
68                      result.add(tableName);
69                  }
70              }
71          }
72          finally
73          {
74              con.close();
75          }
76          return result;
77      }
78  
79      public List<HashMap<Object, Object>>/*<HashMap>*/ executeQuery(DataSource dataSource, String sql) throws Exception
80      {
81          List<HashMap<Object, Object>> result = new ArrayList<HashMap<Object, Object>>();
82          Connection con = dataSource.getConnection();
83          Statement s = null;
84          try
85          {
86              s = con.createStatement();
87              ResultSet rs = s.executeQuery(sql);
88              ResultSetMetaData md = rs.getMetaData();
89  
90              while (rs.next())
91              {
92                  HashMap<Object, Object> row = new HashMap<Object, Object>();
93                  for (int i = 1; i <= md.getColumnCount(); i++)
94                  {
95                      row.put(md.getColumnName(i), rs.getObject(i));
96                  }
97                  result.add(row);
98              }
99          }
100         finally
101         {
102             if (s != null)
103                 s.close();
104             con.close();
105         }
106         return result;
107     }
108 
109 
110 }