3 import iotinstaller.MySQLInterface;
4 import iotinstaller.TableProperty;
5 import iotruntime.master.RuntimeOutput;
11 import java.util.HashMap;
12 import java.util.Scanner;
13 import java.util.Properties;
15 /** A class that does table related operations in a Table object
17 * @author Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
24 * Table class properties
26 protected MySQLInterface sqlInterface;
27 protected String strTableName;
28 protected String strWhere;
29 protected ResultSet rs;
30 protected ResultSetMetaData rsmd;
31 protected boolean bVerbose;
34 * Table class constants
36 protected final static String STR_COMM_TABLE_NAME = "IoTComm";
37 protected final static String STR_MAIN_TABLE_NAME = "IoTMain";
40 * Class constructor #1
42 public Table(boolean _bVerbose) {
44 sqlInterface = new MySQLInterface(_bVerbose);
53 * Class constructor #2 - with table name specified
55 * @param strTblName String table name that this Table object operates on
57 public Table(String strTblName, boolean _bVerbose) {
60 sqlInterface = new MySQLInterface(_bVerbose);
61 strTableName = strTblName;
63 rs = sqlInterface.sqlCommandQuery("SELECT * FROM " + strTableName + ";");
64 rsmd = rs.getMetaData();
66 } catch(SQLException ex) {
67 System.out.println("Table: Exception: ");
73 * A method to set table name
75 * @param strTableName String table name that this Table object operates on
78 public void setTableName(String strTblName) {
80 strTableName = strTblName;
85 * A method to get table name
89 public String getTableName() {
98 * A method to create a new table (Table object)
100 * @param tp array of TableProperty class to construct query
101 * @param strUniqueField field that is unique in this table
104 public void createTable(TableProperty[] tp, String strUniqueField) {
106 // Creating SQL command
107 String strCommand = "CREATE TABLE " + strTableName + " (";
108 // Iterate along the array tp to construct '<field> VARCHAR(<length>)' string
109 for(int i=0; i<tp.length; i++) {
110 strCommand = strCommand + tp[i].getField() +
111 " " + tp[i].getType() + "(" + tp[i].getLength() + ")";
112 // Add ', ' except for the last entry in the array
114 strCommand = strCommand + ", ";
117 strCommand = strCommand + ");";
118 // Execute SQL command
119 sqlInterface.sqlCommand(strCommand);
120 // Assuming that there is always a PK column for each table
121 // This has to be made unique
122 if (strUniqueField != null) {
123 sqlInterface.sqlCommand("ALTER IGNORE TABLE " + strTableName + " ADD UNIQUE(" + strUniqueField +");");
125 RuntimeOutput.print("Table: Creating a new entity/device table", bVerbose);
129 * A method to insert a record into a table for a specific device
131 * @param strFieldVals array of String that contains field values of a table
134 public void insertEntry(String[] strFieldVals) {
136 // Creating SQL command
137 String strCommand = "INSERT INTO " + strTableName + " VALUES (";
138 // Iterate along the array strFields to construct '<field>' string
139 for(int i=0; i<strFieldVals.length; i++) {
140 strCommand = strCommand + "'" + strFieldVals[i] + "'";
142 // Add ', ' except for the last entry in the array
143 if (i<strFieldVals.length-1) {
144 strCommand = strCommand + ", ";
147 strCommand = strCommand + ");";
148 // Execute SQL command
149 sqlInterface.sqlCommand(strCommand);
150 RuntimeOutput.print("Table: Inserting a new entry into " + strTableName + "..", bVerbose);
154 * A method to delete a record into a table for a specific device
156 * @param strWhere String WHERE part of the query
159 public void deleteEntry(String strWhere) {
161 // Creating SQL command
162 String strCommand = "DELETE FROM " + strTableName;
163 if (strWhere == null) {
164 // No condition for query
165 strCommand = strCommand + ";";
167 // Condition for query
168 strCommand = strCommand + " WHERE " + strWhere + ";";
170 // Execute SQL command
171 sqlInterface.sqlCommand(strCommand);
172 RuntimeOutput.print("Table: Deleting entry from " + strTableName + "..", bVerbose);
176 * A method to drop a table
180 public void dropTable() {
182 // Creating SQL command
183 String strCommand = "DROP TABLE " + strTableName;
184 // Execute SQL command
185 sqlInterface.sqlCommand(strCommand);
186 RuntimeOutput.print("Table: Dropping table " + strTableName + "..", bVerbose);
190 * A method to check table existence in the database
194 public boolean isTableExisting() {
196 // Assume table does not exist
197 boolean bExist = false;
198 // Creating SQL command
199 String strCommand = "SHOW TABLES LIKE '" + strTableName + "';";
200 // Execute SQL command
201 rs = sqlInterface.sqlCommandQuery(strCommand);
211 } catch (SQLException ex) {
212 System.out.println("Table: Exception: ");
213 ex.printStackTrace();
220 * A method to return ResultSet
224 public ResultSet getResultSet() {
231 * A method to check if table is empty
235 public boolean isTableEmpty() {
237 // Check if this table has any entries
238 String strCommand = "SELECT * FROM " + strTableName;
239 rs = sqlInterface.sqlCommandQuery(strCommand);
244 } catch(SQLException ex) {
245 System.out.println("Table: Exception: ");
246 ex.printStackTrace();
252 * A method to get number of rows in the table
256 public int getNumOfRows() {
265 } catch (SQLException ex) {
266 System.out.println("Table: Exception: ");
267 ex.printStackTrace();
273 * A method to get number of columns in general table
275 * This doesn't do 2-round lookup as it does for device driver table
279 public int getGeneralNumOfCols() {
283 rsmd = rs.getMetaData();
284 iCols = rsmd.getColumnCount();
285 } catch (SQLException ex) {
286 System.out.println("Table: Exception: ");
287 ex.printStackTrace();
293 * A method to return a narray data structure representative for DB table
295 * This works just like getDBTable() but for other tables in general
296 * It does not do 2-round process as it does for device driver table lookup
300 public String[][] getGeneralDBTable() {
303 int iCols = getGeneralNumOfCols();
304 String[] arrTblElement = new String[iCols];
305 String[][] arrTbl = new String[getNumOfRows()][];
310 arrTblElement = new String[iCols];
311 for(int i=0; i<iCols; i++) {
312 // Extract field information - columns start from 1
313 // Store each field value into one table element
314 arrTblElement[i] = new String(rs.getString(i+1));
316 // Insert one row into the table
317 arrTbl[iCnt++] = arrTblElement;
320 } catch (SQLException ex) {
321 System.out.println("Table: Exception: ");
322 ex.printStackTrace();
329 * A method to close statement manually
331 public void closeStmt() {
333 sqlInterface.closeStatement();
338 * A method to close connection manually
340 public void closeConn() {
342 sqlInterface.closeConnection();
347 * A method to close ResultSet manually
349 public void closeRS() {
353 } catch (SQLException ex) {
354 System.out.println("Table: Exception: ");
355 ex.printStackTrace();