Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package bizlogic; import java.io.BufferedReader; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.logging.Level; import java.util.logging.Logger; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.json.simple.parser.ParseException; /** * * @author ett11281 */ public class Records { public static void add(Connection DBcon, String sensor_name, String smpl_interval, String running, String name) throws SQLException { String isRunning; Statement st; ResultSet rs = null; try { st = DBcon.createStatement(); rs = st.executeQuery("SELECT * FROM USERCONF.SENSORLIST WHERE NAME = '" + sensor_name + "' "); } catch (SQLException ex) { Logger lgr = Logger.getLogger(Records.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } rs.next(); int id = rs.getInt("sensor_id"); String sql_statement; if (running.equals("true")) { isRunning = "B'1'"; } else { isRunning = "B'0'"; } st = DBcon.createStatement(); sql_statement = "INSERT INTO USERCONF.LOG_LIST(SENSOR_ID, SMPL_INTERVAL, RUNNING, NAME) " + "VALUES (" + id + ", " + smpl_interval + ", " + isRunning + ", " + "'" + name + "'" + " );"; System.out.println(sql_statement); st.clearBatch(); st = DBcon.createStatement(); DBcon.createStatement(); st.executeUpdate(sql_statement); } public static void list(Connection DBcon) throws IOException, ParseException, SQLException { Statement st; ResultSet rs = null; try { st = DBcon.createStatement(); rs = st.executeQuery("SELECT userconf.log_list.sensor_id, " + "userconf.log_list.smpl_interval, " + "userconf.log_list.running, " + "userconf.log_list.name AS log_name, " + "userconf.log_list.log_id, " + "userconf.sensorlist.name AS sensor_name " + "FROM USERCONF.LOG_LIST " + "JOIN userconf.sensorlist " + "ON userconf.log_list.sensor_id=userconf.sensorlist.sensor_id"); } catch (SQLException ex) { Logger lgr = Logger.getLogger(Records.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } try { FileWriter recordsFile = new FileWriter("/var/lib/tomcat8/webapps/ROOT/Records/records.json"); //BufferedWriter recordsFile = new BufferedWriter(_file); //recordsFile.write(""); //recordsFile.flush(); FileReader fr = new FileReader("/var/lib/tomcat8/webapps/ROOT/Records/records.json"); BufferedReader br = new BufferedReader(fr); JSONObject Records = new JSONObject(); int _total = 0; JSONArray recordList = new JSONArray(); while (rs.next()) { String isRunningStr; JSONObject sensor_Obj = new JSONObject(); int sensor_id = rs.getInt("sensor_id"); sensor_Obj.put("sensor_id", sensor_id); String smpl_interval = rs.getString("smpl_interval"); sensor_Obj.put("smpl_interval", smpl_interval); Boolean running = rs.getBoolean("running"); if (running) { //System.out.print("1"); isRunningStr = "ON"; } else { //System.out.print("0"); isRunningStr = "OFF"; } sensor_Obj.put("running", isRunningStr); String log_name = rs.getString("log_name"); sensor_Obj.put("log_name", log_name); String sensor_name = rs.getString("sensor_name"); sensor_Obj.put("sensor_name", sensor_name); int log_id = rs.getInt("log_id"); sensor_Obj.put("recid", log_id); recordList.add(sensor_Obj); _total++; } rs.close(); Records.put("total", _total); Records.put("records", recordList); recordsFile.write(Records.toJSONString()); recordsFile.flush(); recordsFile.close(); System.out.print(Records.toJSONString()); System.out.print(br.readLine()); } catch (IOException ex) { Logger.getLogger(Records.class.getName()).log(Level.SEVERE, null, ex); } } public static void del(Connection DBcon, String deleteRecords) throws SQLException { Statement st; String sql_statement; String _deleteRecords = deleteRecords.replace(";", ","); st = DBcon.createStatement(); //for(int i = 0; i<30; i++) { sql_statement = "DELETE FROM USERCONF.LOG_LIST WHERE LOG_ID IN(" + _deleteRecords + ")"; System.out.println(sql_statement); st.clearBatch(); st = DBcon.createStatement(); DBcon.createStatement(); st.executeUpdate(sql_statement); } public static void setColumn(Connection DBcon, String records, String DB, String column, String value) throws SQLException { Statement st; String _value; String sql_statement; String _records = records.replace(";", ","); switch (value) { case "true": _value = "B'1'"; break; case "false": _value = "B'0'"; break; default: _value = value; break; } //st = DBcon.createStatement(); //for(int i = 0; i<30; i++) { sql_statement = "UPDATE " + DB + " SET " + column + " = " + _value + " WHERE LOG_ID IN (" + _records + ")"; System.out.println(sql_statement); //st.clearBatch(); st = DBcon.createStatement(); DBcon.createStatement(); st.executeUpdate(sql_statement); } public static void writeCSV(Connection DBcon, String record_id) throws SQLException { Statement st; ResultSet rs = null; System.out.println("WriteCSV started"); try { st = DBcon.createStatement(); rs = st.executeQuery("SELECT * FROM PUBLIC.t" + record_id); System.out.println("Result set read finished"); } catch (SQLException ex) { Logger lgr = Logger.getLogger(Records.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } try { String DELIMITER = ","; String NEW_LINE = "\n"; String FILE_HEADER = "Time,Series"; System.out.println("Delete old file"); FileWriter csvFile = new FileWriter("/var/lib/tomcat8/webapps/ROOT/Records/Data/" + record_id + ".csv"); //BufferedWriter csvFile = new BufferedWriter( // new OutputStreamWriter(new FileOutputStream(new File( // "/var/lib/tomcat8/webapps/ROOT/Records/Data/" + // record_id + ".csv")))); csvFile.write(""); csvFile.append(FILE_HEADER); csvFile.append(NEW_LINE); Calendar calendar = new GregorianCalendar(); System.out.println("Writing file..."); while (rs.next()) { long time_stamp = rs.getLong("time"); double value = rs.getDouble("value"); String _year; String _month; String _day; String _hour; String _min; String _sec; calendar.setTimeInMillis(time_stamp); _year = Integer.toString(calendar.get(Calendar.YEAR)); _month = Integer.toString(calendar.get(Calendar.MONTH) + 1); _day = Integer.toString(calendar.get(Calendar.DAY_OF_MONTH)); _hour = Integer.toString(calendar.get(Calendar.HOUR_OF_DAY)); _min = Integer.toString(calendar.get(Calendar.MINUTE)); _sec = Integer.toString(calendar.get(Calendar.SECOND)); csvFile.append(_year + "/" + _month + "/" + _day + " " + _hour + ":" + _min + ":" + _sec + DELIMITER + Double.toString(value) + NEW_LINE); //new Date("2009/07/19 12:34:56") } System.out.print("File written"); rs.close(); //csvFile.flush(); csvFile.close(); } catch (IOException ex) { Logger.getLogger(Records.class.getName()).log(Level.WARNING, null, ex); } } }