iotjava/iotinstaller/MySQLInterface.java

   1 package iotinstaller;
   2
   3 import java.sql.*;
   4 import java.io.File;
   5 import java.io.FileInputStream;
   6 import java.io.IOException;
   7 import java.util.Properties;
   8
   9 import iotruntime.master.RuntimeOutput;
  10
  11 /** A class that wraps connection to MySQL database
  12  *
  13  * @author      Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
  14  * @version     1.0
  15  * @since       2015-12-01
  16  */
  17 public final class MySQLInterface {
  18
  19         /**
  20          * MySQLInterface class properties
  21          */
  22         private static Properties prop;
  23         private static Connection conn;
  24         private static Statement stmt;
  25         private boolean bVerbose;
  26
  27         /**
  28          * MySQLInterface class constants
  29          */
  30         private static final String STR_DB_CLASS_NAME = "com.mysql.jdbc.Driver";
  31         private static final String STR_CONFIG_FILE = "MySQLInterface.config";
  32         private static String STR_CONNECTION;
  33         private static String STR_USERNAME;
  34         private static String STR_PASSWORD;
  35
  36
  37         /**
  38          * Class constructor
  39          */
  40         public MySQLInterface(boolean _bVerbose) {
  41
  42                 bVerbose = _bVerbose;
  43                 // Parse config file
  44                 // e.g. STR_CONNECTION = "jdbc:mysql://<ip_address/hostname>/IoTMain"
  45                 RuntimeOutput.print("Reading MySQLInterface.config:", bVerbose);
  46                 STR_CONNECTION = "jdbc:mysql://" + parseConfigFile(STR_CONFIG_FILE, "HOST") + "/" +
  47                         parseConfigFile(STR_CONFIG_FILE, "DATABASE");
  48                 RuntimeOutput.print("STR_CONNECTION=" + STR_CONNECTION, bVerbose);
  49                 STR_USERNAME = parseConfigFile(STR_CONFIG_FILE, "USERNAME");
  50                 RuntimeOutput.print("STR_USERNAME=" + STR_USERNAME, bVerbose);
  51                 STR_PASSWORD = parseConfigFile(STR_CONFIG_FILE, "PASSWORD");
  52                 RuntimeOutput.print("STR_PASSWORD=" + STR_PASSWORD, bVerbose);
  53
  54                 try {
  55                         RuntimeOutput.print("MySQLInterface: MySQL interface object creation", bVerbose);
  56                         // Loading JDBC classes and creating a drivermanager class factory
  57                         Class.forName(STR_DB_CLASS_NAME);
  58                         // Properties for user and password
  59                         prop = new Properties();
  60                         prop.put("user", STR_USERNAME);
  61                         prop.put("password", STR_PASSWORD);
  62                         // Now try to connect
  63                         conn = DriverManager.getConnection(STR_CONNECTION, prop);
  64                         RuntimeOutput.print("MySQLInterface: Object successfully created.. connection established!", bVerbose);
  65                 } catch (SQLException | ClassNotFoundException ex) {
  66                         System.out.println("MySQLInterface: Exception: ");
  67                         ex.printStackTrace();
  68                 }
  69         }
  70
  71
  72         /**
  73          * A method to parse information from a config file
  74          *
  75          * @param       strCfgFileName  Config file name
  76          * @param       strCfgField             Config file field name
  77          * @return      String
  78          */
  79         private String parseConfigFile(String strCfgFileName, String strCfgField) {
  80                 // Parse configuration file
  81                 Properties prop = new Properties();
  82                 File file = new File(strCfgFileName);
  83                 FileInputStream fis = null;
  84                 try {
  85                         fis = new FileInputStream(file);
  86                         prop.load(fis);
  87                         fis.close();
  88                 } catch (IOException ex) {
  89                         System.out.println("IoTMaster: Error reading config file: " + strCfgFileName);
  90                         ex.printStackTrace();
  91                 }
  92                 // NULL is returned if the property isn't found
  93                 return prop.getProperty(strCfgField, null);
  94         }
  95
  96
  97         /**
  98          * A method to wrap MySQL command execution
  99          *
 100          * @param  strCommand  string that contains SQL query
 101          * @return             void
 102          */
 103         public void sqlCommand(String strCommand) {
 104
 105                 try {
 106                         stmt = conn.createStatement();
 107                         stmt.execute(strCommand);
 108                         stmt.close();
 109                 } catch (SQLException ex) {
 110                         System.out.println("MySQLInterface: Exception: ");
 111                         ex.printStackTrace();
 112                 }
 113         }
 114
 115         /**
 116          * A method to wrap MySQL command query execution
 117          *
 118          * @param  strCommand  string that contains SQL query
 119          * @return             ResultSet that contains the result of query
 120          */
 121         public ResultSet sqlCommandQuery(String strCommand) {
 122
 123                 ResultSet rs = null;
 124                 try {
 125                         stmt = conn.createStatement();
 126                         rs = stmt.executeQuery(strCommand);
 127                 } catch (SQLException ex) {
 128                         System.out.println("MySQLInterface: Exception: ");
 129                         ex.printStackTrace();
 130                 }
 131                 return rs;
 132         }
 133
 134         /**
 135          * A method to close statement manually
 136          *
 137          */
 138         public void closeStatement() {
 139
 140                 try {
 141
 142                         stmt.close();
 143
 144                 } catch (SQLException ex) {
 145
 146                         System.out.println("MySQLInterface: Exception: ");
 147                         ex.printStackTrace();
 148
 149                 }
 150         }
 151
 152         /**
 153          * A method to close connection manually
 154          *
 155          */
 156         public void closeConnection() {
 157
 158                 try {
 159                         conn.close();
 160                 } catch (SQLException ex) {
 161                         System.out.println("MySQLInterface: Exception: ");
 162                         ex.printStackTrace();
 163                 }
 164         }
 165
 166         /**
 167          * Getting Connection
 168          *
 169          * @return SQL connection object
 170          */
 171         protected Connection getConnection() {
 172
 173                 return conn;
 174
 175         }
 176
 177         /**
 178          * Getting JDBC connector string
 179          *
 180          * @return String database class name
 181          */
 182         private String getDBClassName() {
 183
 184                 return STR_DB_CLASS_NAME;
 185
 186         }
 187
 188         /**
 189          * Getting Connection string
 190          *
 191          * @return SQL connection string
 192          */
 193         private String getConnectionString() {
 194
 195                 return STR_CONNECTION;
 196
 197         }
 198
 199         /**
 200          * Getting username
 201          *
 202          * @return String username
 203          */
 204         private String getUsername() {
 205
 206                 return STR_USERNAME;
 207
 208         }
 209
 210         /**
 211          * Getting password
 212          *
 213          * @return String password
 214          */
 215         private String getPassword() {
 216
 217                 return STR_PASSWORD;
 218
 219         }
 220 }