iotjava/iotinstaller/Table.java

   1 package iotinstaller;
   2
   3 import iotinstaller.MySQLInterface;
   4 import iotinstaller.TableProperty;
   5 import iotruntime.master.RuntimeOutput;
   6
   7 // Java libraries
   8 import java.io.*;
   9 import java.sql.*;
  10 import java.util.Map;
  11 import java.util.HashMap;
  12 import java.util.Scanner;
  13 import java.util.Properties;
  14
  15 /** A class that does table related operations in a Table object
  16  *
  17  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
  18  * @version     1.0
  19  * @since       2015-12-07
  20  */
  21 public class Table {
  22
  23         /**
  24          * Table class properties
  25          */
  26         protected MySQLInterface sqlInterface;
  27         protected String strTableName;
  28         protected String strWhere;
  29         protected ResultSet rs;
  30         protected ResultSetMetaData rsmd;
  31         protected boolean bVerbose;
  32
  33         /**
  34          * Table class constants
  35          */
  36         protected final static String STR_COMM_TABLE_NAME = "IoTComm";
  37         protected final static String STR_MAIN_TABLE_NAME = "IoTMain";
  38
  39         /**
  40          * Class constructor #1
  41          */
  42         public Table(boolean _bVerbose) {
  43
  44                 sqlInterface = new MySQLInterface(_bVerbose);
  45                 strTableName = null;
  46                 strWhere = null;
  47                 rs = null;
  48                 rsmd = null;
  49                 bVerbose = _bVerbose;
  50         }
  51
  52         /**
  53          * Class constructor #2 - with table name specified
  54          *
  55          * @param     strTblName  String table name that this Table object operates on
  56          */
  57         public Table(String strTblName, boolean _bVerbose) {
  58
  59                 try {
  60                         sqlInterface = new MySQLInterface(_bVerbose);
  61                         strTableName = strTblName;
  62                         strWhere = null;
  63                         rs = sqlInterface.sqlCommandQuery("SELECT * FROM " + strTableName + ";");
  64                         rsmd = rs.getMetaData();
  65                         bVerbose = _bVerbose;
  66                 } catch(SQLException ex) {
  67                         System.out.println("Table: Exception: ");
  68                         ex.printStackTrace();
  69                 }
  70         }
  71
  72         /**
  73          * A method to set table name
  74          *
  75          * @param     strTableName  String table name that this Table object operates on
  76          * @return                  void
  77          */
  78         public void setTableName(String strTblName) {
  79
  80                 strTableName = strTblName;
  81
  82         }
  83
  84         /**
  85          * A method to get table name
  86          *
  87          * @return  String
  88          */
  89         public String getTableName() {
  90
  91                 return strTableName;
  92
  93         }
  94
  95
  96
  97         /**
  98          * A method to create a new table (Table object)
  99          *
 100          * @param  tp               array of TableProperty class to construct query
 101          * @param  strUniqueField   field that is unique in this table
 102          * @return                  void
 103          */
 104         public void createTable(TableProperty[] tp, String strUniqueField) {
 105
 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
 113                         if (i<tp.length-1) {
 114                                 strCommand = strCommand + ", ";
 115                         }
 116                 }
 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 +");");
 124                 }
 125                 RuntimeOutput.print("Table: Creating a new entity/device table", bVerbose);
 126         }
 127
 128         /**
 129          * A method to insert a record into a table for a specific device
 130          *
 131          * @param  strFieldVals  array of String that contains field values of a table
 132          * @return               void
 133          */
 134         public void insertEntry(String[] strFieldVals) {
 135
 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] + "'";
 141
 142                         // Add ', ' except for the last entry in the array
 143                         if (i<strFieldVals.length-1) {
 144                                 strCommand = strCommand + ", ";
 145                         }
 146                 }
 147                 strCommand = strCommand + ");";
 148                 // Execute SQL command
 149                 sqlInterface.sqlCommand(strCommand);
 150                 RuntimeOutput.print("Table: Inserting a new entry into " + strTableName + "..", bVerbose);
 151         }
 152
 153         /**
 154          * A method to delete a record into a table for a specific device
 155          *
 156          * @param  strWhere  String WHERE part of the query
 157          * @return           void
 158          */
 159         public void deleteEntry(String strWhere) {
 160
 161                 // Creating SQL command
 162                 String strCommand = "DELETE FROM " + strTableName;
 163                 if (strWhere == null) {
 164                         // No condition for query
 165                         strCommand = strCommand + ";";
 166                 } else {
 167                         // Condition for query
 168                         strCommand = strCommand + " WHERE " + strWhere + ";";
 169                 }
 170                 // Execute SQL command
 171                 sqlInterface.sqlCommand(strCommand);
 172                 RuntimeOutput.print("Table: Deleting entry from " + strTableName + "..", bVerbose);
 173         }
 174
 175         /**
 176          * A method to drop a table
 177          *
 178          * @return           void
 179          */
 180         public void dropTable() {
 181
 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);
 187         }
 188
 189         /**
 190          * A method to check table existence in the database
 191          *
 192          * @return           boolean
 193          */
 194         public boolean isTableExisting() {
 195
 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);
 202                 try {
 203                         if (rs != null) {
 204                                 rs.beforeFirst();
 205                                 if (rs.next()) {
 206                                         // Table does exist
 207                                         bExist = true;
 208                                 }
 209                                 rs.beforeFirst();
 210                         }
 211                 } catch (SQLException ex) {
 212                         System.out.println("Table: Exception: ");
 213                         ex.printStackTrace();
 214                 }
 215
 216                 return bExist;
 217         }
 218
 219         /**
 220          * A method to return ResultSet
 221          *
 222          * @return           ResultSet
 223          */
 224         public ResultSet getResultSet() {
 225
 226                 return rs;
 227
 228         }
 229
 230         /**
 231          * A method to check if table is empty
 232          *
 233          * @return           boolean
 234          */
 235         public boolean isTableEmpty() {
 236
 237                 if (rs == null) {
 238                         return true;
 239                 }
 240                 return false;
 241
 242         }
 243
 244         /**
 245          * A method to get number of rows in the table
 246          *
 247          * @return           integer
 248          */
 249         public int getNumOfRows() {
 250
 251                 int iRows = 0;
 252                 try {
 253                         rs.first();
 254                         if (rs.last()) {
 255                                 iRows = rs.getRow();
 256                                 rs.beforeFirst();
 257                         }
 258                 } catch (SQLException ex) {
 259                         System.out.println("Table: Exception: ");
 260                         ex.printStackTrace();
 261                 }
 262                 return iRows;
 263         }
 264
 265         /**
 266          * A method to get number of columns in general table
 267          * <p>
 268          * This doesn't do 2-round lookup as it does for device driver table
 269          *
 270          * @return  integer
 271          */
 272         public int getGeneralNumOfCols() {
 273
 274                 int iCols = 0;
 275                 try {
 276                         rsmd = rs.getMetaData();
 277                         iCols = rsmd.getColumnCount();
 278                 } catch (SQLException ex) {
 279                         System.out.println("Table: Exception: ");
 280                         ex.printStackTrace();
 281                 }
 282                 return iCols;
 283         }
 284
 285         /**
 286          * A method to return a narray data structure representative for DB table
 287          * <p>
 288          * This works just like getDBTable() but for other tables in general
 289          * It does not do 2-round process as it does for device driver table lookup
 290          *
 291          * @return  String[][]
 292          */
 293         public String[][] getGeneralDBTable() {
 294
 295                 int iCnt = 0;
 296                 int iCols = getGeneralNumOfCols();
 297                 String[] arrTblElement = new String[iCols];
 298                 String[][] arrTbl = new String[getNumOfRows()][];
 299
 300                 try {
 301                         rs.beforeFirst();
 302                         while (rs.next()) {
 303                                 arrTblElement = new String[iCols];
 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(rs.getString(i+1));
 308                                 }
 309                                 // Insert one row into the table
 310                                 arrTbl[iCnt++] = arrTblElement;
 311                         }
 312                         rs.beforeFirst();
 313                 } catch (SQLException ex) {
 314                         System.out.println("Table: Exception: ");
 315                         ex.printStackTrace();
 316                 }
 317
 318                 return arrTbl;
 319         }
 320
 321         /**
 322          * A method to close statement manually
 323          */
 324         public void closeStmt() {
 325
 326                 sqlInterface.closeStatement();
 327
 328         }
 329
 330         /**
 331          * A method to close connection manually
 332          */
 333         public void closeConn() {
 334
 335                 sqlInterface.closeConnection();
 336
 337         }
 338
 339         /**
 340          * A method to close ResultSet manually
 341          */
 342         public void closeRS() {
 343
 344                 try {
 345                         rs.close();
 346                 } catch (SQLException ex) {
 347                         System.out.println("Table: Exception: ");
 348                         ex.printStackTrace();
 349                 }
 350         }
 351 }