NbTdBLFK

1package com.bimmr.classtracker.Database;
2
3import android.util.Log;
4
5import java.sql.*;
6import java.util.*;
7
8
9public class SQLManager {
10
11    private MySQL mysql;
12
13    /**
14     * Create a mysql connection
15     */
16    public SQLManager(String database) {
17        this.mysql = new MySQL(database);
18    }
19
20
21    public SQLManager(String hostname, String port, String database, String username, String password) {
22        this.mysql = new MySQL(hostname, port, database, username, password);
23    }
24
25    /**
26     * Closes connections
27     */
28    public void unload() {
29        mysql.closeConnection();
30    }
31
32    public MySQL getMySQL() {
33        return mysql;
34    }
35
36    public void createTableIfDoesntExist(String tableName, Column... columns) throws SQLException {
37        String mySQLData = "";
38        for (int i = 0; i != columns.length; i++) {
39            Column c = columns[i];
40            mySQLData += c.toString() + (i + 1 != columns.length ? ", " : "");
41        }
42        mysql.updateSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (" + mySQLData + ");");
43    }
44
45
46    public Object get(String tableName, String keyCol, String keyVal, String columnName) {
47        Object obj = 0;
48
49        if (!mysql.hasOpenConnection())
50            mysql.openConnection();
51
52        Connection con = mysql.getConnection();
53
54        ResultSet set = mysql.querySQL("SELECT " + columnName + " FROM " + tableName + " WHERE " + keyCol + " = '" + keyVal + "';");
55        if (set != null) {
56            try {
57                if (set.next())
58                    obj = set.getObject(columnName);
59
60            } catch (SQLException e) {
61                Log.d("info", "Error getting " + keyCol + " " + keyVal + " from column " + columnName + " from table " + tableName);
62                e.printStackTrace();
63            }
64
65        }
66        if (obj == null)
67            obj = 0;
68
69        mysql.closeConnection();
70        return obj;
71
72    }
73
74
75    public ArrayList<String> getAllKey(String tableName, String keyCol) {
76        ArrayList<String> players = new ArrayList<String>();
77        if (!mysql.hasOpenConnection())
78            mysql.openConnection();
79
80        Connection con = mysql.getConnection();
81        try {
82
83            ResultSet set = mysql.querySQL("SELECT " + keyCol + " FROM `" + tableName + "`");
84
85            while (set.next()) {
86                if (set.getString(1) != null)
87                    players.add(set.getString(1));
88            }
89        } catch (SQLException e) {
90            Log.d("info", "Error getting " + keyCol + " from table " + tableName);
91            e.printStackTrace();
92        }
93        //mysql.closeConnection();
94        return players;
95    }
96
97    private void set(String tableName, String keyCol, String keyVal, String columnName, Object value) {
98
99        mysql.updateSQL("INSERT INTO " + tableName + " (`" + keyCol + "`, `" + columnName + "`) VALUES ('" + keyVal + "', '" + value + "');");
100    }
101
102    public void update(String tableName, String keyCol, String keyVal, String columnName, Object value) {
103
104        if (getAllKey(tableName, keyCol).contains(keyVal))
105            mysql.updateSQL("UPDATE " + tableName + " SET " + columnName + "='" + value + "' WHERE " + keyCol + " ='" + keyVal + "';");
106        else set(tableName, keyCol, keyVal, columnName, value);
107
108    }
109
110    public void alterTable(String table, Column column) throws SQLException {
111
112        Connection c = null;
113        PreparedStatement s = null;
114
115        if (!mysql.hasOpenConnection())
116            mysql.openConnection();
117
118        c = mysql.getConnection();
119
120        s = c.prepareStatement("ALTER TABLE " + table + " ADD " + column.getName() + " " + column.getDataType().toString() + "(" + column.getLength() + ");");
121
122        s.executeUpdate();
123    }
124
125    public static enum DataType {
126        INT, CHAR, VARCHAR, TINYINT, SMALLINT, BOOLEAN, BIGINT, FLOAT, DOUBLE;
127    }
128
129    public static class Column {
130
131        private Integer length;
132        private DataType type;
133        private String name;
134
135        public Column(String name, DataType type, Integer length) {
136            this.name = name;
137            this.type = type;
138            this.length = length;
139        }
140
141        public Column(String name, DataType type, int length) {
142            this.name = name;
143            this.type = type;
144            this.length = length;
145        }
146
147        /**
148         * @return the type
149         */
150        public DataType getDataType() {
151            return type;
152        }
153
154        /**
155         * @return the length
156         */
157        public Integer getLength() {
158            return length;
159        }
160
161        /**
162         * @return the name
163         */
164        public String getName() {
165            return name;
166        }
167
168        @Override
169        public String toString() {
170            if (length == null) return name + " " + type.toString();
171            return name + " " + type.toString() + "(" + length + ")";
172        }
173    }
174
175    public class MySQL {
176
177        private final String hostname, port, database, username, password;
178        private Connection connection;
179
180        public MySQL(String database) {
181            this.hostname = null;
182            this.port = null;
183            this.database = database;
184            this.username = null;
185            this.password = null;
186            this.connection = null;
187
188        }
189
190        public MySQL(String hostname, String port, String database, String username, String password) {
191            this.hostname = hostname;
192            this.port = port;
193            this.database = database;
194            this.username = username;
195            this.password = password;
196            this.connection = null;
197        }
198
199        /**
200         * Checks if there is an open connection
201         *
202         * @return
203         */
204        public boolean hasOpenConnection() {
205            try {
206                return this.connection != null && !this.connection.isClosed();
207            } catch (SQLException e) {
208                Log.d("info",  "Error checking the MySQL Connection!");
209                e.printStackTrace();
210                return false;
211            }
212        }
213
214        /**
215         * Opens a connection
216         */
217        public void openConnection() {
218            try {
219                if (hostname == null) {
220                    if (!hasOpenConnection()) {
221                        Class.forName("org.sqlite.JDBC");
222                        this.connection = DriverManager.getConnection("jdbc:sqlite:" + database + ".db");
223                    }
224                } else {
225                    Class.forName("com.mysql.jdbc.Driver");
226                    this.connection = DriverManager.getConnection("jdbc:mysql://" + this.hostname + ":" + this.port + "/" + this.database, this.username, this.password);
227                }
228            } catch (SQLException e) {
229                Log.d("info", "Could not connect to MySQL server! because: " + e.getMessage());
230            } catch (ClassNotFoundException e) {
231                Log.d("info", "JDBC Driver not found!");
232            }
233        }
234
235        /**
236         * Closes the connection
237         */
238        public void closeConnection() {
239            if (hasOpenConnection())
240                try {
241                    this.connection.close();
242                    this.connection = null;
243                } catch (SQLException e) {
244                    Log.d("info", "Error closing the MySQL Connection!");
245                    e.printStackTrace();
246                }
247        }
248
249        /**
250         * Gets the connection
251         *
252         * @return
253         */
254        public Connection getConnection() {
255            return this.connection;
256        }
257
258        /**
259         * Querys the SQL
260         *
261         * @param query
262         * @return
263         */
264        public ResultSet querySQL(String query) {
265
266            Connection c = null;
267            PreparedStatement state = null;
268            ResultSet set = null;
269
270            if (!hasOpenConnection())
271                openConnection();
272
273            c = getConnection();
274
275            try {
276                state = c.prepareStatement(query);
277            } catch (SQLException e) {
278                Log.d("info", "Error creating the query statement!");
279                e.printStackTrace();
280            }
281
282            try {
283                set = state.executeQuery();
284            } catch (SQLException e) {
285                Log.d("info", "Error getting the ResultSet from the query!");
286                e.printStackTrace();
287            }
288
289            return set;
290        }
291
292        /**
293         * Updates the SQL
294         */
295        public void updateSQL(String update) {
296
297            Connection c = null;
298            PreparedStatement s = null;
299
300            if (!hasOpenConnection())
301                openConnection();
302
303            c = getConnection();
304
305            try {
306                s = c.prepareStatement(update);
307            } catch (SQLException e) {
308                Log.d("info", "Error creating the update statement!");
309                e.printStackTrace();
310            }
311            try {
312                s.executeUpdate();
313            } catch (SQLException e) {
314                Log.d("info", "Error executing the update statement!");
315                e.printStackTrace();
316            }
317            //closeConnection();
318        }
319    }
320}