Serial Port로 온도를 수집하여 저장하기

● /dev/ttyUSB0 에서 전송되는 장치의 온도 데이터를 수집한 후 이를 MySQL 데이터베이스에 저장하는 Java Application을 작성합니다.

• MySQL 8.0 DBMS 설치
  

  $ sudo vi /etc/my.cnf
  default-time-zone='+9:00'​

  Fedora Server의 MySQL 설정파일에 default-time-zone 설정을 추가하여 MySQL 서버의 시간대를 Asia/Seoul로 설정합니다.
• phpMyAdmin 설치
• jSerialComm 2.7.0 다운로드
  https://fazecast.github.io/jSerialComm/
• json-simple 1.1.1 다운로드
  https://code.google.com/archive/p/json-simple/

package com.genie;
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
import com.fazecast.jSerialComm.*;
class caribou {
	public static void main(String args[]) {
		final String devName = "/dev/ttyUSB0";
		StringBuilder sb = new StringBuilder();
		TimeZone.setDefault(TimeZone.getTimeZone("Asia/Seoul"));
		SerialPort comPort = SerialPort.getCommPort(devName);
		if (comPort.openPort())
		{
			System.out.println(devName + " is opend!");
			try {
				while (true)
				{
					while (comPort.bytesAvailable() == 0)
						Thread.sleep(20);
					byte[] readBuffer = new byte[comPort.bytesAvailable()];
					int numRead = comPort.readBytes(readBuffer, readBuffer.length);
					if (numRead > 0)
					{
						StringBuilder cb = new StringBuilder();
						for (int j = 0; j < numRead; j++)
						{
							cb.append((char)readBuffer[j]);
						}
						sb.append(cb.toString());
						if (sb.toString().contains("}"))
						{
							String buffer = sb.toString();
							sb.setLength(0);
							JSONObject jsonObject = objConvert(buffer);
							if (jsonObject != null)
							{
								if (jsonObject.get("command") == null)
								{
									double value = ((Number)jsonObject.get("data")).doubleValue();
									Timestamp time = Timestamp.valueOf(jsonObject.get("time").toString());
									trapDbms(time, jsonObject.get("sensor").toString(), value);
								} else
								{
								}
							}
						}
					}
				}
			} catch (Exception e) { e.printStackTrace(); }
		} else System.out.println("Serial COM Port is not opened!");
		comPort.closePort();
	}
	private static JSONObject objConvert(String jsonString) {
		JSONObject jsonObject = null;
		try {
			JSONParser parser = new JSONParser();
			jsonObject = (JSONObject) parser.parse(jsonString);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return jsonObject;
	}
	private static void trapDbms(Timestamp insTime, String strChannel, double insData)
	{
		String strConn = String.format("jdbc:mysql://%s:%d/%s?user=%s&password=%s&serverTimezone=Asia/Seoul",
			"localhost",
			3306,
			"scom",
			"${USERNAME}",
			"${PASSWORD}");
		String strSql = String.format("INSERT INTO `temperature` (`AgentId`, `InspectionTime`, `ClientTag`, `Channel`, `InspectionData`) " +
			"VALUES(?, ?, ?, ?, ?);");
		PreparedStatement stmt = null;
		try {
			Connection conn = DriverManager.getConnection(strConn);
			stmt = conn.prepareStatement(strSql);
			stmt.setString(1, "Fedora_34");
			stmt.setTimestamp(2, insTime);
			stmt.setString(3, "ttyUSB0");
			stmt.setString(4, strChannel);
			stmt.setDouble(5, insData);
			stmt.execute();
		} catch (Exception ex) {
			System.out.println("SQLException : " + ex.getMessage());
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException sqlEx) {}
				stmt = null;
		}
	}
}

caribou.java

caribou.java 코드의 ${USERNAME}는 로그인 ID를 ${PASSWORD}는 암호를 대입합니다.

 

온도 수집장치에서 전송되어 저장된 온도 데이터