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