5 import java.io.FileInputStream;
6 import java.io.IOException;
7 import java.util.Properties;
9 import iotruntime.master.RuntimeOutput;
11 /** A class that wraps connection to MySQL database
13 * @author Rahmadi Trimananda <rahmadi.trimananda @ uci.edu>
17 public final class MySQLInterface {
20 * MySQLInterface class properties
22 private static Properties prop;
23 private static Connection conn;
24 private static Statement stmt;
25 private boolean bVerbose;
28 * MySQLInterface class constants
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;
40 public MySQLInterface(boolean _bVerbose) {
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);
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);
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: ");
73 * A method to parse information from a config file
75 * @param strCfgFileName Config file name
76 * @param strCfgField Config file field name
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;
85 fis = new FileInputStream(file);
88 } catch (IOException ex) {
89 System.out.println("IoTMaster: Error reading config file: " + strCfgFileName);
92 // NULL is returned if the property isn't found
93 return prop.getProperty(strCfgField, null);
98 * A method to wrap MySQL command execution
100 * @param strCommand string that contains SQL query
103 public void sqlCommand(String strCommand) {
106 stmt = conn.createStatement();
107 stmt.execute(strCommand);
109 } catch (SQLException ex) {
110 System.out.println("MySQLInterface: Exception: ");
111 ex.printStackTrace();
116 * A method to wrap MySQL command query execution
118 * @param strCommand string that contains SQL query
119 * @return ResultSet that contains the result of query
121 public ResultSet sqlCommandQuery(String strCommand) {
125 stmt = conn.createStatement();
126 rs = stmt.executeQuery(strCommand);
127 } catch (SQLException ex) {
128 System.out.println("MySQLInterface: Exception: ");
129 ex.printStackTrace();
135 * A method to close statement manually
138 public void closeStatement() {
144 } catch (SQLException ex) {
146 System.out.println("MySQLInterface: Exception: ");
147 ex.printStackTrace();
153 * A method to close connection manually
156 public void closeConnection() {
160 } catch (SQLException ex) {
161 System.out.println("MySQLInterface: Exception: ");
162 ex.printStackTrace();
169 * @return SQL connection object
171 protected Connection getConnection() {
178 * Getting JDBC connector string
180 * @return String database class name
182 private String getDBClassName() {
184 return STR_DB_CLASS_NAME;
189 * Getting Connection string
191 * @return SQL connection string
193 private String getConnectionString() {
195 return STR_CONNECTION;
202 * @return String username
204 private String getUsername() {
213 * @return String password
215 private String getPassword() {