001package csheets.ext.database.core;
002import java.sql.Connection;
003import java.sql.DriverManager;
004import java.sql.ResultSet;
005import java.sql.SQLException;
006import java.sql.Statement;
007import java.sql.ResultSetMetaData;
008import java.util.ArrayList;
009
010
011public class ConsoleDerbyTest
012{
013    private static final String driverPath = "org.apache.derby.jdbc.EmbeddedDriver";
014    private static Connection connection;
015    private static String url = "jdbc:derby:src-resources/csheets/ext/database/embebbed_db/derby_db";
016    
017    private static String tableName = "recent";
018    private static String insertSt = "insert into " + tableName + " VALUES('1', 'SLB', '32')";
019    private static String insertSt2 = "insert into " + tableName + " VALUES('2', 'FCP', '27')";
020    
021    private static ArrayList allTables = new ArrayList();
022    private static ArrayList query = new ArrayList();
023    
024    private static int[] rowsCols;
025    
026
027    public static void main(String[] args) throws SQLException
028    {
029        createConnection();
030//        createTable(tableName);
031//        insertRestaurants(insertSt);
032//        insertRestaurants(insertSt2);
033//        selectRestaurants();
034//        showAllTables();
035        allTables = saveAllTableNamesToArrayList();
036//        printArrayList(allTables);
037//        query = queryToArray(tableName);
038//        printArrayList(query);
039        
040//        rowsCols = countsRowsAndCols(tableName);
041//        System.out.println("ROWS = " + rowsCols[0]);
042//        System.out.println("COLS = " + rowsCols[1]);
043        
044        dropAllTables(allTables);
045        //update("UPDATE TITLES SET TITLES = 'f' WHERE TITLES = 'all bought'");
046        
047        shutdown();
048    }
049    
050    private static void createConnection()
051    {
052        try 
053        {
054            Class.forName(driverPath);
055            connection = DriverManager.getConnection(url + ";create=true","user","pass");
056            System.out.println("CONNECTED");
057        } 
058        catch (ClassNotFoundException e) 
059        {
060            System.out.println("Error: class not found!");
061        } 
062        catch (SQLException e) 
063        {
064            System.out.println("Error: connection to database!");
065            e.printStackTrace();
066        }
067    }
068    
069    
070    private static void createTable(String tableName) throws SQLException
071    {
072        String stat = "CREATE TABLE " + tableName + "(id VARCHAR(50), CLUB VARCHAR(50), TITLES VARCHAR(50))";
073        Statement st = null;
074        st = connection.createStatement();
075        int i = st.executeUpdate(stat);
076        System.out.println("TABLE CREATED: " + tableName);
077    }
078    
079    private static void insertRestaurants(String insertSt)
080    {
081        System.out.println(insertSt);
082        try
083        {
084            Statement st = null;
085            st = connection.createStatement();
086            st.executeUpdate(insertSt);
087            st.close();
088            System.out.println("DATA INSERTED");
089        }
090        catch (SQLException sqlExcept)
091        {
092            sqlExcept.printStackTrace();
093        }
094    }
095    
096    private static void selectRestaurants()
097    {
098        try
099        {
100            Statement st = null;
101            st = connection.createStatement();
102            ResultSet results = st.executeQuery("select * from " + tableName);
103            ResultSetMetaData rsmd = results.getMetaData();
104            int numberCols = rsmd.getColumnCount();
105            for (int i=1; i<=numberCols; i++)
106            {
107                //print Column Names
108                System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
109            }
110
111            System.out.println("\n-------------------------------------------------");
112
113            while(results.next())
114            {
115                int id = results.getInt(1);
116                String restName = results.getString(2);
117                String cityName = results.getString(3);
118                System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
119            }
120            results.close();
121            st.close();
122        }
123        catch (SQLException sqlExcept)
124        {
125            sqlExcept.printStackTrace();
126        }
127    }
128    
129    private static void showAllTables()
130    {
131        try
132        {
133            Statement st = null;
134            st = connection.createStatement();
135            ResultSet results = st.executeQuery("SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='T'");
136            ResultSetMetaData rsmd = results.getMetaData();
137            int numberCols = rsmd.getColumnCount();
138            for (int i=1; i<=numberCols; i++)
139            {
140                //print Column Names
141                System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
142            }
143
144            System.out.println("\n-------------------------------------------------");
145
146            while(results.next())
147            {
148                String tableName = results.getString(1);
149                System.out.println(tableName);
150            }
151            results.close();
152            st.close();
153        }
154        catch (SQLException sqlExcept)
155        {
156            sqlExcept.printStackTrace();
157        }
158    }
159    
160    private static ArrayList saveAllTableNamesToArrayList()
161    {
162        ArrayList temp = new ArrayList();
163        try
164        {
165            Statement st = null;
166            st = connection.createStatement();
167            ResultSet results = st.executeQuery("SELECT TABLENAME FROM SYS.SYSTABLES WHERE TABLETYPE='T'");
168            ResultSetMetaData rsmd = results.getMetaData();
169            int numberCols = rsmd.getColumnCount();
170            for (int i=1; i<=numberCols; i++)
171            {
172                //print Column Names
173                System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
174            }
175
176            
177            Object obj = null;
178            for (; results.next();) 
179            {
180                for (int i = 0; i < numberCols; i++) 
181                {
182                    obj = results.getObject(i + 1);
183                    temp.add(obj);
184                }
185            }
186            results.close();
187            st.close();
188        }
189        catch (SQLException sqlExcept)
190        {
191            sqlExcept.printStackTrace();
192        }
193        
194        return temp;
195    }
196    
197    private static void printArrayList(ArrayList a)
198    {
199        for(int i = 0; i < a.size(); i++)
200        {
201            System.out.println(a.get(i).toString());
202        }
203    }
204    
205    public static synchronized ArrayList queryToArray(String tableName) throws SQLException 
206    {
207        Statement st = null;
208        ResultSet rs = null;
209        ArrayList temp = new ArrayList();
210        Object obj = null;
211        
212        String expression = "SELECT * FROM " + tableName ;
213        
214        st = connection.createStatement();         
215        rs = st.executeQuery(expression);   
216       
217        ResultSetMetaData meta = rs.getMetaData();
218        int cols = meta.getColumnCount();
219        int rows = countRows(tableName);
220        
221        for(int i = 1; i <= cols; i++)
222        {
223            obj = meta.getColumnName(i);
224            temp.add(obj);
225        }
226        
227        for(; rs.next(); )
228        {
229            for(int i = 0; i < cols; i ++)
230            {
231                obj = rs.getObject(i + 1);
232                temp.add(obj);
233            }
234        }
235        st.close();
236        return temp;
237    }
238     
239    
240    public static synchronized int countRows(String tableName) throws SQLException
241    {
242        Statement st = null;
243        ResultSet rs = null;
244        Object obj = null;
245        String sqlExpression = "SELECT COUNT(*) FROM " + tableName;
246       
247        st = connection.createStatement();
248        rs = st.executeQuery(sqlExpression);
249        
250        ResultSetMetaData meta = rs.getMetaData();
251        rs.next();
252        
253        obj = rs.getObject(1);
254        
255        st.close();
256        
257        return Integer.parseInt(obj.toString()) + 1;
258    }
259    
260    public static int[] countsRowsAndCols(String tableName) throws SQLException 
261        {
262            int []result = new int[2];
263            int rows, cols;
264            Statement st = null;
265            ResultSet rs = null;
266            Object obj = null;
267            String sqlExpression = "SELECT * FROM " + tableName;
268       
269            st = connection.createStatement();
270            rs = st.executeQuery(sqlExpression);
271            ResultSetMetaData meta = rs.getMetaData();
272            rs.next();
273        
274            obj = rs.getObject(1);
275            result[0] = countRows(tableName);
276            result[1] = meta.getColumnCount();
277            
278            st.close();
279                  
280            return result;
281        }
282    
283    
284    private static void dropAllTables(ArrayList allTables) throws SQLException
285    {
286        for(int i = 0; i < allTables.size(); i++)
287        {
288            String stat = "DROP TABLE  " + allTables.get(i).toString();
289            Statement st = null;
290            st = connection.createStatement();
291            int j = st.executeUpdate(stat);
292            System.out.println("TABLE DELETED: " + allTables.get(i).toString());
293        }
294        
295    }
296    
297
298    private static void shutdown() throws SQLException
299    {
300         Statement st = null;
301         st = connection.createStatement();
302        try
303        {
304            if (st != null)
305            {
306                st.close();
307            }
308            if (st != null)
309            {
310                DriverManager.getConnection(url + ";shutdown=true");
311                connection.close();
312            }           
313        }
314        catch (SQLException sqlExcept)
315        {
316            
317        }
318
319    }
320}