API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 19th, 2016
130
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.71 KB | None | 0 0
raw download clone embed print report
  1. package database;
  2. import java.sql.DriverManager;
  3. import java.sql.Connection;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. import java.util.ArrayList;
  7. import java.util.Date;
  8.  
  9. import models.DanfossRadiatorModel;
  10. import models.FourInOneSensorModel;
  11. import models.HumiditySensorModel;
  12. import models.LightSensorModel;
  13. import models.TemperatureSensorModel;
  14. import constants.DBTablesColumnConstants;
  15. import constants.SQLQueriesConstants;
  16. import csv.ExportToCSV;
  17.  
  18. import main.Factory;
  19.  
  20. public class DBConnection{
  21.  
  22. private static String DRIVER_CLASS = "com.mysql.jdbc.Driver";
  23. private static String url = "jdbc:mysql://helios.csesalford.com:3306/agile15_group4";
  24.  
  25. private static Factory factory = new Factory();
  26. /**
  27. * Main method to test the back end for exporting data into excel file.
  28. * After the functionality will be linked with front end this method will be removed.
  29. * @param args representing the arguments of the method.
  30. * @throws Exception in case any exception occurs.
  31. */
  32. public static void main(String args[]) throws Exception{
  33. //
  34. // ExportToCSV exportToCSV = new ExportToCSV();
  35. // exportToCSV.exportDataToCSVFile("4 in 1 Sensor");
  36. // exportToCSV.exportDataToCSVFile("Danfoss Radiator");
  37. // exportToCSV.exportDataToCSVFile("Humidity Sensor");
  38. // exportToCSV.exportDataToCSVFile("Light Sensor");
  39. // exportToCSV.exportDataToCSVFile("Temperature Sensor");
  40. //
  41.  
  42. }
  43.  
  44. /**This method establishes a connection to the database.
  45. * @return connection
  46. */
  47. public static Connection connectToDatabase(){
  48. Connection connection = null;
  49. try{
  50. Class.forName(getDRIVER_CLASS());
  51. connection = DriverManager.getConnection(url, "agile15", "vZjcDYl2oHFdlFrd");
  52. System.out.println("Connection ok.");
  53. }
  54. catch(Exception e){
  55. e.printStackTrace();
  56. }
  57. return connection;
  58. }
  59.  
  60. /**This method extracts the data from table Danfoss_Radiator.
  61. * @return an array list containing all the data from this table to be exported in CSV.
  62. */
  63. public static ArrayList<DanfossRadiatorModel> danfossRadiatorDB(boolean selectLastOnly, long unixStartDateTime, long unixEndDateTime){
  64.  
  65. ArrayList<DanfossRadiatorModel> sensorList = new ArrayList<DanfossRadiatorModel>();
  66. Connection con = connectToDatabase();
  67. try{
  68.  
  69. Statement stmt = (Statement) con.createStatement();
  70. ResultSet rs = null;
  71.  
  72. if(selectLastOnly) {
  73. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_LAST_ENTRY_FOR_DANFOSS_RADIATOR);
  74. }
  75. else if(unixStartDateTime != 0 || unixEndDateTime != 0) {
  76. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ENTRIES_FOR_DANFOSS_RADIATOR_WITHIN_RANGE + " AND " + DBTablesColumnConstants.TIME_CREATED + " BETWEEN " + unixStartDateTime + " AND " + unixEndDateTime + SQLQueriesConstants.SELECT_WITHIN_RANGE_LIMIT);
  77. }
  78. else {
  79. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ALL_ENTRIES_FOR_DANFOSS_RADIATOR);
  80. }
  81.  
  82. while(rs.next()){
  83.  
  84. DanfossRadiatorModel danfossRadiatorModel = factory.createDanfossRadiatorModel("Danfoss Radiator");
  85. danfossRadiatorModel.setDeviceID(rs.getInt(DBTablesColumnConstants.DEVICE_ID));
  86. danfossRadiatorModel.setReadingTime((rs.getInt(DBTablesColumnConstants.TIME_CREATED)));
  87. danfossRadiatorModel.setBatteryLevel(rs.getInt(DBTablesColumnConstants.BATTERY_LEVEL));
  88. danfossRadiatorModel.setTempLevel(rs.getInt(DBTablesColumnConstants.TEMP_LEVEL));
  89. danfossRadiatorModel.setActuatorStatus(rs.getString(DBTablesColumnConstants.ACTUATOR_STATUS));
  90.  
  91. sensorList.add(danfossRadiatorModel);
  92.  
  93. }
  94. con.close();
  95. rs.close();
  96. }catch(Exception e){
  97.  
  98. e.printStackTrace();
  99. }
  100.  
  101. return sensorList;
  102.  
  103. }
  104.  
  105. /**This method extracts the data from table 4_In_One_Sensor.
  106. * @return an array list containing all the data from this table to be exported in CSV.
  107. */
  108. public static ArrayList<FourInOneSensorModel> fourInOneSensorDB(boolean selectLastOnly, long unixStartDateTime, long unixEndDateTime){
  109. Connection con = connectToDatabase();
  110. ArrayList<FourInOneSensorModel> sensorList = new ArrayList<FourInOneSensorModel>();
  111.  
  112. try{
  113. Statement stmt = (Statement) con.createStatement();
  114. ResultSet rs = null;
  115.  
  116. if(selectLastOnly) {
  117. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_LAST_ENTRY_FOR_FOUR_IN_ONE_SENSOR);
  118. }
  119. else if(unixStartDateTime != 0 || unixEndDateTime != 0) {
  120. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ENTRIES_FOR_FOUR_IN_ONE_SENSOR_WITHIN_RANGE + " AND " + DBTablesColumnConstants.TIME_CREATED + " BETWEEN " + unixStartDateTime + " AND " + unixEndDateTime + SQLQueriesConstants.SELECT_WITHIN_RANGE_LIMIT);
  121. }
  122. else {
  123. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ALL_ENTRIES_FOR_FOUR_IN_ONE_SENSOR);
  124. }
  125.  
  126. while(rs.next()){
  127.  
  128. FourInOneSensorModel fourInOneSensorModel = factory.createFourInOneSensorModel("Four in One Sensor");
  129. fourInOneSensorModel.setDeviceID(rs.getInt(DBTablesColumnConstants.DEVICE_ID));
  130. fourInOneSensorModel.setReadingTime((rs.getInt(DBTablesColumnConstants.TIME_CREATED)));
  131. fourInOneSensorModel.setBatteryLevel(rs.getInt(DBTablesColumnConstants.BATTERY_LEVEL));
  132. fourInOneSensorModel.setArmedStatus(rs.getInt(DBTablesColumnConstants.ARMED_STATUS));
  133.  
  134. sensorList.add(fourInOneSensorModel);
  135. }
  136. con.close();
  137. rs.close();
  138. }catch (Exception e) {
  139. e.printStackTrace();
  140. }
  141. return sensorList;
  142.  
  143. }
  144.  
  145. /**This method extracts the data from table Light_Sensor.
  146. * @return an array list containing all the data from this table to be exported in CSV.
  147. */
  148. public static ArrayList<LightSensorModel> lightSensorDB(boolean selectLastOnly, long unixStartDateTime, long unixEndDateTime){
  149.  
  150. Connection con = connectToDatabase();
  151. ArrayList<LightSensorModel> sensorList = new ArrayList<LightSensorModel>();
  152.  
  153. try{
  154. Statement stmt = (Statement) con.createStatement();
  155. ResultSet rs = null;
  156.  
  157. if(selectLastOnly) {
  158. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_LAST_ENTRY_FOR_LIGHT_SENSOR);
  159. }
  160. else if(unixStartDateTime != 0 || unixEndDateTime != 0) {
  161. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ENTRIES_FOR_LIGHT_SENSOR_WITHIN_RANGE + " AND " + DBTablesColumnConstants.TIME_CREATED + " BETWEEN " + unixStartDateTime + " AND " + unixEndDateTime + SQLQueriesConstants.SELECT_WITHIN_RANGE_LIMIT);
  162. }
  163. else {
  164. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ALL_ENTRIES_FOR_LIGHT_SENSOR);
  165. }
  166.  
  167. while(rs.next()){
  168.  
  169. LightSensorModel lightSensorModel = factory.createLightSensorModel("Light Sensor");
  170. lightSensorModel.setDeviceID(rs.getInt(DBTablesColumnConstants.DEVICE_ID));
  171. lightSensorModel.setReadingTime((rs.getInt(DBTablesColumnConstants.TIME_CREATED)));
  172. lightSensorModel.setCurrentLevel(rs.getInt(DBTablesColumnConstants.CURRENT_LEVEL));
  173. sensorList.add(lightSensorModel);
  174. }
  175. con.close();
  176. rs.close();
  177. }catch (Exception e) {
  178. e.printStackTrace();
  179. }
  180. return sensorList;
  181.  
  182. }
  183.  
  184. /**This method extracts the data from table Humidity_Sensor.
  185. * @return an array list containing all the data from this table to be exported in CSV.
  186. */
  187. public static ArrayList<HumiditySensorModel> humiditySensorDB(boolean selectLastOnly, long unixStartDateTime, long unixEndDateTime){
  188.  
  189. Connection con = connectToDatabase();
  190. ArrayList<HumiditySensorModel> sensorList = new ArrayList<HumiditySensorModel>();
  191.  
  192. try{
  193. Statement stmt = (Statement) con.createStatement();
  194. ResultSet rs = null;
  195.  
  196. if(selectLastOnly) {
  197. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_LAST_ENTRY_FOR_HUMIDITY_SENSOR);
  198. }
  199. else if(unixStartDateTime != 0 || unixEndDateTime != 0) {
  200. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ENTRIES_FOR_HUMIDITY_SENSOR_WITHIN_RANGE + " AND " + DBTablesColumnConstants.TIME_CREATED + " BETWEEN " + unixStartDateTime + " AND " + unixEndDateTime + SQLQueriesConstants.SELECT_WITHIN_RANGE_LIMIT);
  201. }
  202. else {
  203. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ALL_ENTRIES_FOR_HUMIDITY_SENSOR);
  204. }
  205.  
  206. while(rs.next()){
  207. HumiditySensorModel humiditySensorModel = factory.createHumiditySensorModel("Humidity Sensor");
  208. humiditySensorModel.setDeviceID(rs.getInt(DBTablesColumnConstants.DEVICE_ID));
  209. humiditySensorModel.setReadingTime((rs.getInt(DBTablesColumnConstants.TIME_CREATED)));
  210. humiditySensorModel.setCurrentLevel(rs.getInt(DBTablesColumnConstants.CURRENT_LEVEL));
  211. sensorList.add(humiditySensorModel);
  212. }
  213. con.close();
  214. rs.close();
  215.  
  216. }catch (Exception e) {
  217. e.printStackTrace();
  218. }
  219. return sensorList;
  220.  
  221. }
  222.  
  223. /**This method extracts the data from table Temperature_Sensor.
  224. * @return an array list containing all the data from this table to be exported in CSV.
  225. */
  226. public static ArrayList<TemperatureSensorModel> temperatureSensorDB(boolean selectLastOnly, long unixStartDateTime, long unixEndDateTime){
  227.  
  228. Connection con = connectToDatabase();
  229. ArrayList<TemperatureSensorModel> sensorList = new ArrayList<TemperatureSensorModel>();
  230.  
  231. try{
  232. Statement stmt = (Statement) con.createStatement();
  233. ResultSet rs = null;
  234.  
  235. if(selectLastOnly) {
  236. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_LAST_ENTRY_FOR_TEMPERATURE_SENSOR);
  237. }
  238. else if(unixStartDateTime != 0 || unixEndDateTime != 0) {
  239. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ENTRIES_FOR_TEMPERATURE_SENSOR_WITHIN_RANGE + " AND " + DBTablesColumnConstants.TIME_CREATED + " BETWEEN " + unixStartDateTime + " AND " + unixEndDateTime + SQLQueriesConstants.SELECT_WITHIN_RANGE_LIMIT);
  240. }
  241. else {
  242. rs = (ResultSet) stmt.executeQuery(SQLQueriesConstants.SELECT_ALL_ENTRIES_FOR_TEMPERATURE_SENSOR);
  243. }
  244.  
  245. while(rs.next()){
  246. TemperatureSensorModel temperatureSensorModel = factory.createTemperatureSensorModel("Temperature Sensor");
  247. temperatureSensorModel.setDeviceID(rs.getInt(DBTablesColumnConstants.DEVICE_ID));
  248. temperatureSensorModel.setReadingTime((rs.getInt(DBTablesColumnConstants.TIME_CREATED)));
  249. temperatureSensorModel.setTemperatureLevel(rs.getInt(DBTablesColumnConstants.TEMP_LEVEL));
  250. sensorList.add(temperatureSensorModel);
  251. }
  252. con.close();
  253. rs.close();
  254.  
  255. }catch (Exception e) {
  256. e.printStackTrace();
  257. }
  258.  
  259. return sensorList;
  260.  
  261. }
  262.  
  263. /**
  264. * Getter method for DRIVER_CLASS
  265. * @return DRIVER_CLASS
  266. */
  267. public static String getDRIVER_CLASS() {
  268. return DRIVER_CLASS;
  269. }
  270.  
  271. /**
  272. * @todo Method that converts from Linux time to Java Date
  273. * @param timeStamp
  274. * @return time
  275. */
  276. public Date convertLinuxTimeToDate(int timeStamp){
  277. Date time = new java.util.Date((long) timeStamp * 1000);
  278. return time;
  279. }
  280.  
  281. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!