7 import java.util.HashMap;
8 import java.util.Scanner;
9 import java.util.Properties;
11 import iotruntime.master.RuntimeOutput;
13 /** A class that extends Table class to do table operations on IoTSet
15 * @author Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
19 public class TableSet extends Table {
22 * TableSet class properties
24 protected String strWhere;
27 * Class constructor - for IoTSet (only one table is needed)
29 * @param strTblName String table name that this Table object operates on
31 public TableSet(String strTblName, boolean _bVerbose) {
33 super(strTblName, _bVerbose);
37 * A method to set table name and select entry from a SQL query config file
39 * @param strCfgFileName String config file name for device/entity
41 public void setTableSetFromQueryFile(String strQueryFileName, String strObjectID) {
44 // Parse configuration file
45 // Assumption here is that .config file is written with the correct syntax (need typechecking)
46 File file = new File(strQueryFileName);
47 Scanner scanFile = new Scanner(new FileReader(file));
48 // String for scanning the file
50 while (scanFile.hasNext()) {
51 strScan = scanFile.next();
52 // if this is for IoTSet table
53 if (strScan.equals("SELECT FROM")) {
54 // The next token is definitely the table name
55 strScan = scanFile.next();
56 this.setTableName(strScan);
58 // Scan WHERE for either IoTSet or IoTRelation
59 if (strScan.equals("WHERE")) {
60 // The next token is definitely the WHERE statement
62 String strWhere = scanFile.next();
63 while (!strWhere.equals(";")) {
64 strScan = strScan + " " + strWhere;
65 strWhere = scanFile.next();
67 RuntimeOutput.print("strScan: " + strScan, bVerbose);
69 if (strObjectID != null) {
70 // Object ID for IoTDeviceAddress address selection
71 strScan = strScan + " AND ID='" + strObjectID + "'";
73 this.setWhereCondition(strScan);
77 } catch (FileNotFoundException ex) {
79 System.out.println("Table: Exception: ");
86 * A method to set the String WHERE for a more complex query
88 * @param strWhr String WHERE for a more complex query
91 public void setWhereCondition(String strWhr) {
98 * A method to select entries by giving more complex WHERE in SQL query for IoTSet
100 * @param strTableName String table name to create device table
101 * @param strWhere String WHERE part of the query
104 public void selectSetEntry() {
106 // Creating SQL command
107 String strCommand = "SELECT * FROM " + strTableName;
108 if (strWhere == null) {
109 // No condition for query
110 strCommand = strCommand + ";";
112 // Condition for query
113 strCommand = strCommand + " WHERE " + strWhere + ";";
115 // Execute SQL command
116 RuntimeOutput.print("Executing: " + strCommand, bVerbose);
117 rs = sqlInterface.sqlCommandQuery(strCommand);
119 rsmd = rs.getMetaData();
120 } catch (SQLException ex) {
121 System.out.println("Table: Exception: ");
122 ex.printStackTrace();
127 * A method to get number of columns in the table
129 * @param iIndex Row number in the ResultSet
132 public int getNumOfCols(int iIndex) {
140 // Break when reaching the desired location
144 // Get the specific class table name and table ID
145 // e.g. ProximitySensorBrandC + PS1
146 String strClassImplTableID = rs.getString(1);
147 String strClassImplTableName = rs.getString(2);
148 String strSQLCommand = "SELECT * FROM " + strClassImplTableName +
149 strClassImplTableID + ";";
150 ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strSQLCommand);
151 if(rsClassImplementation.next()) {
152 // Get the column type name
153 rsmd = rsClassImplementation.getMetaData();
154 iCols = rsmd.getColumnCount();
157 } catch (SQLException ex) {
158 System.out.println("Table: Exception: ");
159 ex.printStackTrace();
166 * A method to get column data type
168 * @param iCol Column number
169 * @param iIndex Row number in the ResultSet
172 public String getFieldType(int iCol, int iIndex) {
174 String strColumnTypeName = "";
180 // Break when reaching the desired location
184 // Get the specific class table name and table ID
185 // e.g. ProximitySensorBrandC + PS1
186 String strClassImplTableID = rs.getString(1);
187 String strClassImplTableName = rs.getString(2);
188 String strCommand = "SELECT * FROM " + strClassImplTableName +
189 strClassImplTableID + ";";
190 RuntimeOutput.print(strCommand, bVerbose);
191 ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strCommand);
192 // Get the column type name
193 rsmd = rsClassImplementation.getMetaData();
194 strColumnTypeName = rsmd.getColumnTypeName(iCol);
196 } catch (SQLException ex) {
197 System.out.println("Table: Exception: ");
198 ex.printStackTrace();
201 return strColumnTypeName;
205 * A method to return a array of String data structure that
206 * contains the list of ID field values of objects
210 public String[] getFieldObjectIDs() {
212 String[] arrFieldObjectIDs = new String[getNumOfRows()];
217 arrFieldObjectIDs[iCnt] = new String(rs.getString(1));
221 } catch (SQLException ex) {
222 System.out.println("Table: Exception: ");
223 ex.printStackTrace();
225 return arrFieldObjectIDs;
229 * A method to return a HashMap data structure that contains the list
232 * It matches the device ID in the specific table device, e.g. ProximitySensor
233 * with the name of that device/entry in the main IoTMain table, e.g.
234 * AtmelProximitySensor, GEProximitySensor, etc. These also represent the
235 * class names of these objects
237 * @return HashMap<String, String>
239 public HashMap<String, String> getEntryTypes() {
241 HashMap<String, String> hmEntryTypes = new HashMap<String, String>();
245 hmEntryTypes.put(rs.getString(1), rs.getString(2));
248 } catch (SQLException ex) {
249 System.out.println("Table: Exception: ");
250 ex.printStackTrace();
256 * A method to return an array data structure representative for DB table
258 * The outer array structure indexes the inner array structure that
259 * represents a single database entry.
263 //public HashMap<Integer, HashMap<Integer, String>> getDBTable() {
264 public String[][] getDBTable() {
268 String[] arrTblElement;
269 String[][] arrTbl = new String[getNumOfRows()][];
273 // Get the class implementation table name from the second column
274 // and we compound it with the ID so that we will get a unique name
275 // This is to allow a case where we have more than one instance
277 // e.g. ProximitySensorImplPS1 from the table below
278 // +------+-----------------------+
280 // +------+-----------------------+
281 // | PS1 | ProximitySensorImpl |
282 // | PS2 | ProximitySensorBrandC |
283 // | PS3 | ProximitySensorBrandD |
284 // +------+-----------------------+
285 String strClassImplTableID = rs.getString(1);
286 String strClassImplTableName = rs.getString(2);
287 // We just select everything because there is only one entry
288 // to store all the necessary constructor values (if any)
289 // If constructor is empty then it returns nothing
290 // e.g. ProximitySensorImplPS1
296 String strCommand = "SELECT * FROM " + strClassImplTableName +
297 strClassImplTableID + ";";
298 RuntimeOutput.print(strCommand, bVerbose);
299 ResultSet rsClassImplementation = sqlInterface.sqlCommandQuery(strCommand);
300 rsmd = rsClassImplementation.getMetaData();
301 iCols = rsmd.getColumnCount();
302 arrTblElement = new String[iCols];
303 if(rsClassImplementation.next()) {
304 for(int i=0; i<iCols; i++) {
305 // Extract field information - columns start from 1
306 // Store each field value into one table element
307 arrTblElement[i] = new String(rsClassImplementation.getString(i+1));
310 // Insert one row into the table
311 arrTbl[iCnt++] = arrTblElement;
314 } catch (SQLException ex) {
315 System.out.println("Table: Exception: ");
316 ex.printStackTrace();
323 * A method to close statement manually
325 public void closeStmt() {
327 sqlInterface.closeStatement();
332 * A method to close connection manually
334 public void closeConn() {
336 sqlInterface.closeConnection();
341 * A method to close ResultSet manually
343 public void closeRS() {
347 } catch (SQLException ex) {
348 System.out.println("Table: Exception: ");
349 ex.printStackTrace();