SOCKET writes communication between client and server, connects Mysql database, and java realizes dynamic monitoring

See the detailed course design report and the source code of C and java
Code cloud:
Build database skip

C ා connect to database

Need to add MySql.Data.dll reference connection database
Code page adding using client

string str = "server=localhost; User Id=root; password=root; Database=server";
//String to connect to MySQL
MySqlConnection mycon = new MySqlConnection(str);//Instantiate link
mycon.Open();//Open connection
MySqlCommand mycmd = new MySqlCommand("insert into tcp(type,id,sn,power,state,time) values('" + type1 + "','" + id1 + "','" + sn1 + "','" + power1 + "','" + state1 + "','" + time1 + "')", mycon);
if (mycmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Data inserted successfully!");

Two way communication between server and client

The two-way communication between the server and the client needs to send feedback information to the client after receiving the client's information based on the server, so as to inform the client that the information has been successfully received. After the reverse communication mechanism is established, the client can also be operated by sending relevant control information to the client from the server.

Server sends simple feedback

After the server receives the client data and stores the data in the database successfully. Server begins to send feedback
1. Sent by server:
byte[] dataToC = System.Text.Encoding.ASCII.GetBytes("Received successfully");

2. Client reception:
byte[] bytes = new Byte[1024];
int bytesRec = clientSocket.Receive(bytes);
string dataBack = Encoding.ASCII.GetString(bytes, 0, bytesRec);
At this point, the user-defined statement of the server can be completed, received and displayed on the client.

3. Design idea:
The control switch mechanism is established in the server to judge the specific feedback information transmitted to the client, and the client judges the specific task according to the feedback information received.

Server control function:
It is preliminarily controlled by keyboard input in the command desk. When the port monitoring is established in each cycle, the keyboard will be monitored. When the keyboard enters the corresponding termination key, the switch statement will execute the corresponding termination statement, otherwise the judgment statement will jump out by default. The following is the corresponding code.

//Non blocking monitor keyboard input
if (Console.KeyAvailable)
    ConsoleKeyInfo key = Console.ReadKey(true);
    switch (key.Key)
        case ConsoleKey.F2:
            Console.WriteLine("You pressed F2!");
            byte[] data = System.Text.Encoding.ASCII.GetBytes("ShutDown");
Client judgment function:
if (dataBack == "ShutDown")

Further optimization:
In order to connect GUI control conveniently, the judgment content should be changed to a specific variable instead of listening to keyboard input. Both the server and the client should control the state of the client with the state value of "on" or "off" in the statement.

Using JAVA and MySQL to realize dynamic real-time monitoring of data

Design idea: after the server partition information is stored in the database, JAVA is used to connect the database, and JfreeChart is used to realize the visualization and dynamic update of the device voltage change.
The main steps are as follows:

Design DBUtil tool class module.

Used to connect to Mysql database.
The main codes are as follows:

Connection conn = null;
String url = "jdbc:mysql://localhost:3306/server?serverTimezone=UTC";
			String username = "root";
			String password = "******";
			conn = DriverManager.getConnection(url,username,password);

And package try/catch exception acquisition, print error prompt and error information.

Design Entity module,

It is used to create a class to store every data in Mysql, construct get function to get the determination information, and construct set function to set or modify the specific information.
The specific attribute values in the class are shown in the following code, which one by one correspond to the information format specified in the information transmission, that is, the data format in the database. All of them are defined as String string type. In order to avoid external interference, it is set to private property.

public class Entity {
	private String type;
	private String id;
	private String sn;
	private String power;
	public String state;
	private String time;
In order to obtain and set the attributes in the class conveniently, we need to construct get and set functions for each attribute. The following are get and set functions for the Type attribute. The rest are omitted and will not be discussed in detail.
	public String getType() {
		return type;
	public void setType(String type) {
		this.type = type;

Design DAO module

It is used to construct various functions related to the database, such as getAll() to get all the data in the database, getTen() to get the latest ten pieces of data in the database, and update() to modify a piece of data in the database.
The following is the main source code. Because the obtained result is multiple classes, the type of the obtained function is set to ArrayList array linked list in java. This function has the characteristics of both array and linked list, which is very convenient.

	public ArrayList<Entity> getAll(){
		ArrayList<Entity> ar = new ArrayList<Entity>();

Set conn to call the connect database function in DBUtil. ps is the execute statement function and rs is the result store function.

		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
Set the sql statement as follows, execute and store the execution result.
		String sql = "select type,id,sn,power,state,time from tcp";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

The set function in Entity is called in a while loop to save the acquired database information to the new ArrayList class.

			while( {
				Entity ent = new Entity();

At this point, all data is saved in ArrayList ar. Finally, return ar function can get the result. It should be noted that we must remember to release rs, ps and conn functions in turn.
Change SQL statement to

"select * from tcp Order By time Desc limit 10"

In order to prepare gui control interface, we also write a modify function, getTen(), to change the status of an ID device in the database from java for opening and closing operations, which is still to be implemented. This is the java database interface. The main source code is as follows:

String sql = "UPDATE tcp SET state= ? WHERE id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, ent.getState());

Design Charts module

It is used to visualize the data in the database through JfreeChart and draw a line chart to show the change trend of nearly ten data. And the timer is constructed to refresh dynamically every second and retrieve the latest ten data in the database.
JFreeChart is an open chart drawing class library on JAVA platform. It is completely written in JAVA language and designed for applications, applications, servlets and JSP. JFreeChart can generate pie charts, bar charts, scatter plots, time series, Gantt charts and other charts, as well as output in PNG and JPEG formats.
This time we use its line chart to draw. To use JfreeChart, you need to download two packages: Jcommon and JfreeChart. At present, the latest packages are jcommon-1.0.23.jar and jfreechart-1.0.19.jar. After downloading, import the project and construct the path. Then you can use JfreeChart.

With StandardChartTheme, you can set the appearance format of Chart, such as font size, etc.

StandardChartTheme mChartTheme = new StandardChartTheme("CN");
        mChartTheme.setLargeFont(new Font("Blackbody", Font.BOLD, 20));
        mChartTheme.setExtraLargeFont(new Font("Song style", Font.PLAIN, 15));
    mChartTheme.setRegularFont(new Font("Song style", Font.PLAIN, 15));

Set the data set of the line graph in turn through the for loop.

ArrayList<Entity> ar=new DAO().getTen();
        int index = ar.size()-1;
		for(Entity ne; index>=0 ;index--) {
			ne = ar.get(index);
			mDataset.addValue(Double.valueOf(ne.getPower()), "equipment"+ne.getId(), ne.getTime().split(" ")[1]);

Create a new line chart, set the table title, x-axis, y-axis and other related information.

JFreeChart mChart = ChartFactory.createLineChart(
                "Voltage fluctuation line chart",//Map name
                mDataset,//data set
                true, // Show Legend
                true, // Adopt standard generator
            false);// Generate hyperlink or not

Finally, the following screen 4.22 is realized, with the voltage as the ordinate and the time as the abscissa, to display the voltage fluctuation.

Put the data set setting function in the while infinite loop and set the thread to sleep for 1000ms to dynamically update the line graph every second.


Problems encountered

When the java line graph is dynamically implemented to display the latest ten pieces of data, the SQL statement is
select * from tcp Order By time Desc limit 10
That is, the data is sorted in reverse chronological order and the top ten are output, which leads to the newer data being saved in the front of the array, so that when the output draws a line chart, the latest data is displayed on the left side of the line chart, and when the whole line chart is dynamically updated, it will move from left to right as a whole, which is not in line with the traditional user use logic, so it is necessary to extract the data from the database When the data is assigned to the data set of the line graph, the reverse order operation is performed. The specific changes are as follows:

Original code:

ArrayList<Entity> ar=new DAO().getTen();
		for(Entity ne:ar) {
mDataset.addValue(Double.valueOf(ne.getPower()), "equipment"+ne.getId(), ne.getTime().split(" ")[1]);

Code after change:

ArrayList<Entity> ar=new DAO().getTen();
        int index = ar.size()-1;
		for(Entity ne; index>=0 ;index--) {
			ne = ar.get(index);
mDataset.addValue(Double.valueOf(ne.getPower()), "equipment"+ne.getId(), ne.getTime().split(" ")[1]);

First, get the size of the array with size(), and then decrease it from the end of the array with for loop to add data set. After the change, the direction of dynamic refresh is changed.

Published 30 original articles, won praise 1, visited 4833
Private letter follow

Keywords: Database Java MySQL SQL

Added by pratheshshah on Mon, 03 Feb 2020 16:01:18 +0200