iotjava/iotinstaller/TableSet.java

   1 package iotinstaller;
   2
   3 // Java libraries
   4 import java.io.*;
   5 import java.sql.*;
   6 import java.util.Map;
   7 import java.util.HashMap;
   8 import java.util.Scanner;
   9 import java.util.Properties;
  10
  11 import iotruntime.master.RuntimeOutput;
  12
  13 /** A class that extends Table class to do table operations on IoTSet
  14  *
  15  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
  16  * @version     1.0
  17  * @since       2016-02-29
  18  */
  19 public class TableSet extends Table {
  20
  21         /**
  22          * TableSet class properties
  23          */
  24         protected String strWhere;
  25
  26         /**
  27          * Class constructor - for IoTSet (only one table is needed)
  28          *
  29          * @param     strTblName  String table name that this Table object operates on
  30          */
  31         public TableSet(String strTblName, boolean _bVerbose) {
  32
  33                 super(strTblName, _bVerbose);
  34         }
  35
  36         /**
  37          * A method to set table name and select entry from a SQL query config file
  38          *
  39          * @param  strCfgFileName String config file name for device/entity
  40          */
  41         public void setTableSetFromQueryFile(String strQueryFileName, String strObjectID) {
  42
  43                 try {
  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
  49                         String strScan = "";
  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);
  57                                 }
  58                                 // Scan WHERE for either IoTSet or IoTRelation
  59                                 if (strScan.equals("WHERE")) {
  60                                         // The next token is definitely the WHERE statement
  61                                         strScan = "";
  62                                         String strWhere = scanFile.next();
  63                                         while (!strWhere.equals(";")) {
  64                                                 strScan = strScan + " " + strWhere;
  65                                                 strWhere = scanFile.next();
  66                                         }
  67                                         RuntimeOutput.print("strScan: " + strScan, bVerbose);
  68
  69                                         if (strObjectID != null) {
  70                                                 // Object ID for IoTDeviceAddress address selection
  71                                                 strScan = strScan + " AND ID='" + strObjectID + "'";
  72                                         }
  73                                         this.setWhereCondition(strScan);
  74                                 }
  75                         }
  76
  77                 } catch (FileNotFoundException ex) {
  78
  79                         System.out.println("Table: Exception: ");
  80                         ex.printStackTrace();
  81
  82                 }
  83         }
  84
  85         /**
  86          * A method to set the String WHERE for a more complex query
  87          *
  88          * @param     strWhr  String WHERE for a more complex query
  89          * @return            void
  90          */
  91         public void setWhereCondition(String strWhr) {
  92
  93                 strWhere = strWhr;
  94
  95         }
  96
  97         /**
  98          * A method to select entries by giving more complex WHERE in SQL query for IoTSet
  99          *
 100          * @param  strTableName String table name to create device table
 101          * @param  strWhere     String WHERE part of the query
 102          * @return              void
 103          */
 104         public void selectSetEntry() {
 105
 106                 // Creating SQL command
 107                 String strCommand = "SELECT * FROM " + strTableName;
 108                 if (strWhere == null) {
 109                         // No condition for query
 110                         strCommand = strCommand + ";";
 111                 } else {
 112                         // Condition for query
 113                         strCommand = strCommand + " WHERE " + strWhere + ";";
 114                 }
 115                 // Execute SQL command
 116                 RuntimeOutput.print("Executing: " + strCommand, bVerbose);
 117                 rs = sqlInterface.sqlCommandQuery(strCommand);
 118                 try {
 119                         rsmd = rs.getMetaData();
 120                 } catch (SQLException ex) {
 121                         System.out.println("Table: Exception: ");
 122                         ex.printStackTrace();
 123                 }
 124         }
 125
 126         /**
 127          * A method to get number of columns in the table
 128          *
 129          * @param   iIndex Row number in the ResultSet
 130          * @return  integer
 131          */
 132         public int getNumOfCols(int iIndex) {
 133
 134                 int iCnt = 0;
 135                 int iCols = 0;
 136                 try {
 137                         rs.beforeFirst();
 138                         while(rs.next()) {
 139                                 iCnt++;
 140                                 // Break when reaching the desired location
 141                                 if(iCnt > iIndex)
 142                                         break;
 143                         }
 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();
 155                         }
 156                         rs.beforeFirst();
 157                 } catch (SQLException ex) {
 158                         System.out.println("Table: Exception: ");
 159                         ex.printStackTrace();
 160                 }
 161
 162                 return iCols;
 163         }
 164
 165         /**
 166          * A method to get column data type
 167          *
 168          * @param   iCol   Column number
 169          * @param   iIndex Row number in the ResultSet
 170          * @return         String
 171          */
 172         public String getFieldType(int iCol, int iIndex) {
 173
 174                 String strColumnTypeName = "";
 175                 int iCnt = 0;
 176                 try {
 177                         rs.beforeFirst();
 178                         while(rs.next()) {
 179                                 iCnt++;
 180                                 // Break when reaching the desired location
 181                                 if(iCnt > iIndex)
 182                                         break;
 183                         }
 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);
 195                         rs.beforeFirst();
 196                 } catch (SQLException ex) {
 197                         System.out.println("Table: Exception: ");
 198                         ex.printStackTrace();
 199                 }
 200
 201                 return strColumnTypeName;
 202         }
 203
 204         /**
 205          * A method to return a array of String data structure that
 206          * contains the list of ID field values of objects
 207          *
 208          * @return  String[]
 209          */
 210         public String[] getFieldObjectIDs() {
 211
 212                 String[] arrFieldObjectIDs = new String[getNumOfRows()];
 213                 try {
 214                         int iCnt=0;
 215                         rs.beforeFirst();
 216                         while (rs.next()) {
 217                                 arrFieldObjectIDs[iCnt] = new String(rs.getString(1));
 218                                 iCnt++;
 219                         }
 220                         rs.beforeFirst();
 221                 } catch (SQLException ex) {
 222                         System.out.println("Table: Exception: ");
 223                         ex.printStackTrace();
 224                 }
 225                 return arrFieldObjectIDs;
 226         }
 227
 228         /**
 229          * A method to return a HashMap data structure that contains the list
 230          * of device names
 231          * <p>
 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
 236          *
 237          * @return  HashMap<String, String>
 238          */
 239         public HashMap<String, String> getEntryTypes() {
 240
 241                 HashMap<String, String> hmEntryTypes = new HashMap<String, String>();
 242                 try {
 243                         rs.beforeFirst();
 244                         while (rs.next()) {
 245                                 hmEntryTypes.put(rs.getString(1), rs.getString(2));
 246                         }
 247                         rs.beforeFirst();
 248                 } catch (SQLException ex) {
 249                         System.out.println("Table: Exception: ");
 250                         ex.printStackTrace();
 251                 }
 252                 return hmEntryTypes;
 253         }
 254
 255         /**
 256          * A method to return an array data structure representative for DB table
 257          * <p>
 258          * The outer array structure indexes the inner array structure that
 259          * represents a single database entry.
 260          *
 261          * @return  String[][]
 262          */
 263         //public HashMap<Integer, HashMap<Integer, String>> getDBTable() {
 264         public String[][] getDBTable() {
 265
 266                 int iCnt = 0;
 267                 int iCols = 0;
 268                 String[] arrTblElement;
 269                 String[][] arrTbl = new String[getNumOfRows()][];
 270                 try {
 271                         rs.beforeFirst();
 272                         while (rs.next()) {
 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
 276                                 // of a device type
 277                                 // e.g. ProximitySensorImplPS1 from the table below
 278                                 // +------+-----------------------+
 279                                 // | ID   | TYPE                  |
 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
 291                                 // +------+-------+
 292                                 // | ZONE | POWER |
 293                                 // +------+-------+
 294                                 // |    0 |   100 |
 295                                 // +------+-------+
 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));
 308                                         }
 309                                 }
 310                                 // Insert one row into the table
 311                                 arrTbl[iCnt++] = arrTblElement;
 312                         }
 313                         rs.beforeFirst();
 314                 } catch (SQLException ex) {
 315                         System.out.println("Table: Exception: ");
 316                         ex.printStackTrace();
 317                 }
 318
 319                 return arrTbl;
 320         }
 321
 322         /**
 323          * A method to close statement manually
 324          */
 325         public void closeStmt() {
 326
 327                 sqlInterface.closeStatement();
 328
 329         }
 330
 331         /**
 332          * A method to close connection manually
 333          */
 334         public void closeConn() {
 335
 336                 sqlInterface.closeConnection();
 337
 338         }
 339
 340         /**
 341          * A method to close ResultSet manually
 342          */
 343         public void closeRS() {
 344
 345                 try {
 346                         rs.close();
 347                 } catch (SQLException ex) {
 348                         System.out.println("Table: Exception: ");
 349                         ex.printStackTrace();
 350                 }
 351         }
 352 }