3 import iotinstaller.MySQLInterface;
4 import iotinstaller.TableProperty;
5 import iotruntime.master.RuntimeOutput;
11 import java.util.HashMap;
12 import java.util.HashSet;
13 import java.util.Scanner;
14 import java.util.Properties;
16 /** A class that does table related operations in a Table object
18 * @author Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
25 * Table class properties
27 protected MySQLInterface sqlInterface;
28 protected String strTableName;
29 protected String strWhere;
30 protected ResultSet rs;
31 protected ResultSetMetaData rsmd;
32 protected boolean bVerbose;
35 * Table class constants
37 protected final static String STR_COMM_TABLE_NAME = "IoTComm";
38 protected final static String STR_MAIN_TABLE_NAME = "IoTMain";
41 * Class constructor #1
43 public Table(boolean _bVerbose) {
45 sqlInterface = new MySQLInterface(_bVerbose);
54 * Class constructor #2 - with table name specified
56 * @param strTblName String table name that this Table object operates on
58 public Table(String strTblName, boolean _bVerbose) {
61 sqlInterface = new MySQLInterface(_bVerbose);
62 strTableName = strTblName;
64 rs = sqlInterface.sqlCommandQuery("SELECT * FROM " + strTableName + ";");
65 rsmd = rs.getMetaData();
67 } catch(SQLException ex) {
68 System.out.println("Table: Exception: ");
74 * A method to set table name
76 * @param strTableName String table name that this Table object operates on
79 public void setTableName(String strTblName) {
81 strTableName = strTblName;
86 * A method to get table name
90 public String getTableName() {
99 * A method to create a new table (Table object)
101 * @param tp array of TableProperty class to construct query
102 * @param strUniqueField field that is unique in this table
105 public void createTable(TableProperty[] tp, String strUniqueField) {
107 // Creating SQL command
108 String strCommand = "CREATE TABLE " + strTableName + " (";
109 // Iterate along the array tp to construct '<field> VARCHAR(<length>)' string
110 for(int i=0; i<tp.length; i++) {
111 strCommand = strCommand + tp[i].getField() +
112 " " + tp[i].getType() + "(" + tp[i].getLength() + ")";
113 // Add ', ' except for the last entry in the array
115 strCommand = strCommand + ", ";
118 strCommand = strCommand + ");";
119 // Execute SQL command
120 sqlInterface.sqlCommand(strCommand);
121 // Assuming that there is always a PK column for each table
122 // This has to be made unique
123 if (strUniqueField != null) {
124 sqlInterface.sqlCommand("ALTER IGNORE TABLE " + strTableName + " ADD UNIQUE(" + strUniqueField +");");
126 RuntimeOutput.print("Table: Creating a new entity/device table", bVerbose);
130 * A method to insert a record into a table for a specific device
132 * @param strFieldVals array of String that contains field values of a table
135 public void insertEntry(String[] strFieldVals) {
137 // Creating SQL command
138 String strCommand = "INSERT INTO " + strTableName + " VALUES (";
139 // Iterate along the array strFields to construct '<field>' string
140 for(int i=0; i<strFieldVals.length; i++) {
141 strCommand = strCommand + "'" + strFieldVals[i] + "'";
143 // Add ', ' except for the last entry in the array
144 if (i<strFieldVals.length-1) {
145 strCommand = strCommand + ", ";
148 strCommand = strCommand + ");";
149 // Execute SQL command
150 sqlInterface.sqlCommand(strCommand);
151 RuntimeOutput.print("Table: Inserting a new entry into " + strTableName + "..", bVerbose);
155 * A method to delete a record into a table for a specific device
157 * @param strWhere String WHERE part of the query
160 public void deleteEntry(String strWhere) {
162 // Creating SQL command
163 String strCommand = "DELETE FROM " + strTableName;
164 if (strWhere == null) {
165 // No condition for query
166 strCommand = strCommand + ";";
168 // Condition for query
169 strCommand = strCommand + " WHERE " + strWhere + ";";
171 // Execute SQL command
172 sqlInterface.sqlCommand(strCommand);
173 RuntimeOutput.print("Table: Deleting entry from " + strTableName + "..", bVerbose);
177 * A method to drop a table
181 public void dropTable() {
183 // Creating SQL command
184 String strCommand = "DROP TABLE " + strTableName;
185 // Execute SQL command
186 sqlInterface.sqlCommand(strCommand);
187 RuntimeOutput.print("Table: Dropping table " + strTableName + "..", bVerbose);
191 * A method to check table existence in the database
195 public boolean isTableExisting() {
197 // Assume table does not exist
198 boolean bExist = false;
199 // Creating SQL command
200 String strCommand = "SHOW TABLES LIKE '" + strTableName + "';";
201 // Execute SQL command
202 rs = sqlInterface.sqlCommandQuery(strCommand);
212 } catch (SQLException ex) {
213 System.out.println("Table: Exception: ");
214 ex.printStackTrace();
221 * A method to return ResultSet
225 public ResultSet getResultSet() {
232 * A method to check if table is empty
236 public boolean isTableEmpty() {
238 // Check if this table has any entries
239 String strCommand = "SELECT * FROM " + strTableName;
240 rs = sqlInterface.sqlCommandQuery(strCommand);
245 } catch(SQLException ex) {
246 System.out.println("Table: Exception: ");
247 ex.printStackTrace();
253 * A method to get number of rows in the table
257 public int getNumOfRows() {
266 } catch (SQLException ex) {
267 System.out.println("Table: Exception: ");
268 ex.printStackTrace();
274 * A method to get number of columns in general table
276 * This doesn't do 2-round lookup as it does for device driver table
280 public int getGeneralNumOfCols() {
284 rsmd = rs.getMetaData();
285 iCols = rsmd.getColumnCount();
286 } catch (SQLException ex) {
287 System.out.println("Table: Exception: ");
288 ex.printStackTrace();
294 * A method to return a narray data structure representative for DB table
296 * This works just like getDBTable() but for other tables in general
297 * It does not do 2-round process as it does for device driver table lookup
301 public String[][] getGeneralDBTable() {
304 int iCols = getGeneralNumOfCols();
305 String[] arrTblElement = new String[iCols];
306 String[][] arrTbl = new String[getNumOfRows()][];
311 arrTblElement = new String[iCols];
312 for(int i=0; i<iCols; i++) {
313 // Extract field information - columns start from 1
314 // Store each field value into one table element
315 arrTblElement[i] = new String(rs.getString(i+1));
317 // Insert one row into the table
318 arrTbl[iCnt++] = arrTblElement;
321 } catch (SQLException ex) {
322 System.out.println("Table: Exception: ");
323 ex.printStackTrace();
330 * A method to get the column names of a specified table
332 * @return HashSet<String> : holds all column information for specific tables
333 * @author Kevin Truong<kctruon1@uci.edu>
335 public HashSet<String> getColumnNames(){
336 int iCols = getGeneralNumOfCols();
337 String[] arrTbleColNames = new String[iCols];
338 HashSet<String> arrTblColNames = new HashSet<String>();
341 rsmd = rs.getMetaData();
343 for(int i = 0;i < iCols; i++){
344 arrTblColNames.add(rsmd.getColumnName(i+1));
348 } catch (SQLException ex){
349 System.out.println("Table: Exception: ");
350 ex.printStackTrace();
353 return arrTblColNames;
358 * A method to close statement manually
360 public void closeStmt() {
362 sqlInterface.closeStatement();
367 * A method to close connection manually
369 public void closeConn() {
371 sqlInterface.closeConnection();
376 * A method to close ResultSet manually
378 public void closeRS() {
382 } catch (SQLException ex) {
383 System.out.println("Table: Exception: ");
384 ex.printStackTrace();