MySql expected output not coming

2019-09-09 22:38发布

问题:

So I have this program which reads serial Input from COM port and places it in MySql database named asset.

Code:

import java.io.*;
import java.util.*;
import gnu.io.*;
import java.sql.*;

public class SimpleRead implements Runnable, SerialPortEventListener {
static CommPortIdentifier portId;
static Enumeration portList;
static Connection con=null;
static String url = "jdbc:mysql://localhost:3306/track";
static String uid="root";
static String pwd="root";

InputStream inputStream;
SerialPort serialPort;
Thread readThread;

public static void main(String[] args) {
portList = CommPortIdentifier.getPortIdentifiers();

while (portList.hasMoreElements()) {
    portId = (CommPortIdentifier) portList.nextElement();
    if (portId.getPortType() == CommPortIdentifier.PORT_SERIAL) {
         if (portId.getName().equals("COM1")) {
    //                if (portId.getName().equals("/dev/term/a")) {
            SimpleRead reader = new SimpleRead();
                try
                {

                    Class.forName("com.mysql.jdbc.Driver");
                    con =     DriverManager.getConnection(url,uid,pwd);
                }
                catch(Exception s){
                    System.out.println(s);
                    }

    }
}
}
 }
 public SimpleRead() {
 try {
     serialPort = (SerialPort) portId.open("SimpleReadApp", 2000);
 } catch (PortInUseException e) {System.out.println(e);}
 try {
    inputStream = serialPort.getInputStream();
 } catch (IOException e) {System.out.println(e);}
 try {
    serialPort.addEventListener(this);
 } catch (TooManyListenersException e) {System.out.println(e);}
 serialPort.notifyOnDataAvailable(true);
 try {
    serialPort.setSerialPortParams(9600,
        SerialPort.DATABITS_8,
        SerialPort.STOPBITS_1,
        SerialPort.PARITY_NONE);
  } catch (UnsupportedCommOperationException e) {System.out.println(e);}
  readThread = new Thread(this);
  readThread.start();
  }

public void run() {
try {
    Thread.sleep(20000);
} catch (InterruptedException e) {System.out.println(e);}
}

public void serialEvent(SerialPortEvent event) {
switch(event.getEventType()) {
case SerialPortEvent.BI:
case SerialPortEvent.OE:
case SerialPortEvent.FE:
case SerialPortEvent.PE:
case SerialPortEvent.CD:
case SerialPortEvent.CTS:
case SerialPortEvent.DSR:
case SerialPortEvent.RI:
case SerialPortEvent.OUTPUT_BUFFER_EMPTY:
    break;
case SerialPortEvent.DATA_AVAILABLE:
    byte[] readBuffer = new byte[20];

    try {
        while (inputStream.available() > 0) {
            int numBytes = inputStream.read(readBuffer);
        }
        System.out.print(new String(readBuffer));
    } catch (IOException e) {System.out.println(e);}

    Statement st=null;
    try{
        st=con.createStatement();

    }
    catch(Exception ex)
    {
        System.out.println(ex);
    }

    try{
        String idd = new String(readBuffer);
        String query = "INSERT INTO asset (id) VALUES(?)";
        PreparedStatement pstm = con.prepareStatement(query);
        pstm.setString(1, idd);
        pstm.executeUpdate();
        pstm.close();
        con.close();

        //st.executeUpdate("insert into asset(id) VALUES('"+idd+"'");
        //con.close();
    }
    catch(Exception ex){
        System.out.println(ex);
                }
                break;
    }
}
}

So whenever the data is recieved from the COM port, an entry is created by the above code in mysql table.

The expected entry should be something like :

What actual output i get :

What actually happens is that One input from com port is creating two entries into the table one with the serial input and timestamp and one only with the timestamp.

What I want to achieve is that only 1 entry per COM input. Like the expected Image.

回答1:

Check ID before inserting it into the database -

    String idd = new String(readBuffer);
    if(!idd.equals("") || !idd.equals(null)){
       String query = "INSERT INTO asset (id) VALUES(?)";
       PreparedStatement pstm = con.prepareStatement(query);
       pstm.setString(1, idd);
       pstm.executeUpdate();
       pstm.close();
       con.close();
    }


标签: java mysql jdbc