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                 // Check if this table has any entries
 238                 String strCommand = "SELECT * FROM " + strTableName;
 239                 rs = sqlInterface.sqlCommandQuery(strCommand);
 240                 try {
 241                         if (!rs.first()) {
 242                                 return true;
 243                         }
 244                 } catch(SQLException ex) {
 245                         System.out.println("Table: Exception: ");
 246                         ex.printStackTrace();
 247                 }
 248                 return false;
 249         }
 250
 251         /**
 252          * A method to get number of rows in the table
 253          *
 254          * @return           integer
 255          */
 256         public int getNumOfRows() {
 257
 258                 int iRows = 0;
 259                 try {
 260                         rs.first();
 261                         if (rs.last()) {
 262                                 iRows = rs.getRow();
 263                                 rs.beforeFirst();
 264                         }
 265                 } catch (SQLException ex) {
 266                         System.out.println("Table: Exception: ");
 267                         ex.printStackTrace();
 268                 }
 269                 return iRows;
 270         }
 271
 272         /**
 273          * A method to get number of columns in general table
 274          * <p>
 275          * This doesn't do 2-round lookup as it does for device driver table
 276          *
 277          * @return  integer
 278          */
 279         public int getGeneralNumOfCols() {
 280
 281                 int iCols = 0;
 282                 try {
 283                         rsmd = rs.getMetaData();
 284                         iCols = rsmd.getColumnCount();
 285                 } catch (SQLException ex) {
 286                         System.out.println("Table: Exception: ");
 287                         ex.printStackTrace();
 288                 }
 289                 return iCols;
 290         }
 291
 292         /**
 293          * A method to return a narray data structure representative for DB table
 294          * <p>
 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
 297          *
 298          * @return  String[][]
 299          */
 300         public String[][] getGeneralDBTable() {
 301
 302                 int iCnt = 0;
 303                 int iCols = getGeneralNumOfCols();
 304                 String[] arrTblElement = new String[iCols];
 305                 String[][] arrTbl = new String[getNumOfRows()][];
 306
 307                 try {
 308                         rs.beforeFirst();
 309                         while (rs.next()) {
 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));
 315                                 }
 316                                 // Insert one row into the table
 317                                 arrTbl[iCnt++] = arrTblElement;
 318                         }
 319                         rs.beforeFirst();
 320                 } catch (SQLException ex) {
 321                         System.out.println("Table: Exception: ");
 322                         ex.printStackTrace();
 323                 }
 324
 325                 return arrTbl;
 326         }
 327
 328         /**
 329          * A method to close statement manually
 330          */
 331         public void closeStmt() {
 332
 333                 sqlInterface.closeStatement();
 334
 335         }
 336
 337         /**
 338          * A method to close connection manually
 339          */
 340         public void closeConn() {
 341
 342                 sqlInterface.closeConnection();
 343
 344         }
 345
 346         /**
 347          * A method to close ResultSet manually
 348          */
 349         public void closeRS() {
 350
 351                 try {
 352                         rs.close();
 353                 } catch (SQLException ex) {
 354                         System.out.println("Table: Exception: ");
 355                         ex.printStackTrace();
 356                 }
 357         }
 358 }