Java+SVNCloud+Mysql课程设计

1、主要内容

课程设计，主要通过Javas wing创建窗口，jdbc连接云端mysql数据库进行基本操作，支持随机生成数据并用动态展示数据结果。

先来看一下实现效果：

2、所需准备

1. 在SVNCloud上部署MySQL
2. 下载所需Java包并导入到IDEA

请访问: SVNCloud 官网、JfreeChart下载、Mysql jar下载部署
等内容，形成类似以下的目录结构，若是代码建构过程发现错误，请仔细检查环境问题。

3、与sql访问的中间类：SqlMessage

package Course_Design;
import java.sql.*;

class DataStruct{   //数据结构体根据数据库内容依次定义
    String tim;     //时间
    String tmp;     //温度
    String hum;     //湿度
    String lit;     //光强
}
public class SqlMessage {
    static DataStruct selected;     //选中的数据存储到这里
    static DataStruct prepared;     //即将改写的数据存到这里
    
    //查询语句等结构
    static Connection connection;
    static Statement statement;
    static PreparedStatement st;
    static ResultSet result;
    SqlMessage(){
        selected = new DataStruct();
        prepared = new DataStruct();
    }

    void SqlInit() throws SQLException, ClassNotFoundException{
        //  1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //  2.用户信息和url，请注意加上useSSL = false,否则连接失败
        String url = "jdbc:mysql://sql.wsfdb.cn:3306/ Database(请改为你的数据库名称) ?useSSL=false";
        //url： jdbc:mysql:// + 目的地址 + 端口号 + 数据库名称? + 其他,这里请确保你在SVNCloud建立了数据库且可以通过navicat等工具正常连接
        String username="username";	//用户名
        String password="******";	//密码
        
        //  3.连接成功，数据库对象 Connection
        connection = DriverManager.getConnection(url,username,password);
        //  4.绑定
        statement = connection.createStatement();
    }

    void SendMessage(String sql)throws SQLException {       //发送sql语句(有结果集版本)
        result = statement.executeQuery(sql);
    }
    void upDate(String sql) throws SQLException {           //发送sql语句(无结果集版本)
        st = connection.prepareStatement(sql);
        System.out.println("execute:"+sql);
        int res = st.executeUpdate();
        if(res > 0){
            System.out.println("数据更新成功:"+res);
        }
        st.close();
    }
    void CloseAll()throws SQLException {		//关闭数据库
        statement.close();
        connection.close();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

4、窗口界面

package Course_Design;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartFrame;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;


import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.*;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.Random;


public class MyWindow {
    static SqlMessage sys = new SqlMessage();
    static int connect_flag = 0;//是否连接数据库的标识，当连接数据库时，生成数据失效
    static JTextField Edit_tim; //时间文本区域
    static JTextField Edit_tmp; //温度文本区域
    static JTextField Edit_hum; //湿度文本区域
    static JTextField Edit_lit; //光强文本区域

    static JButton BTN_add = new JButton("添加数据"); //添加
    static JButton BTN_del = new JButton("删除数据"); //删除
    static JButton BTN_mod = new JButton("修改数据"); //修改
    static JButton BTN_sum = new JButton("生成数据"); //生成
    static JButton BTN_con = new JButton("连接/断开");//连接与断开SQL
    static JButton BTN_lad = new JButton("刷新");     //刷新数据
    static JFrame window = new JFrame("传感器信息操作系统");
    static JTable table;
    static MyTableModel model;
    private Box getPanel() { //绘制所有小控件
		//建议panel内部使用GridLayout布局，Panel之间使用Box布局更加美观

        Box Vbox1 = Box.createVerticalBox();
        Box Vbox2 = Box.createVerticalBox();
        Panel Vbox3 = new Panel(new GridLayout(3,2));
        Box Vbox4 = Box.createVerticalBox();
        Box Hbox  = Box.createHorizontalBox();

        JLabel label1 = new JLabel("时间：");
        Edit_tim = new JTextField(10);
        JLabel label2 = new JLabel("温度：");
        Edit_tmp = new JTextField(10);
        JLabel label3 = new JLabel("湿度：");
        Edit_hum = new JTextField(10);
        JLabel label4 = new JLabel(" 光强：");
        Edit_lit = new JTextField(10);

        //label 与 text edit 加入窗体
        Vbox1.add(label1);
        Vbox2.add(Edit_tim);
        Vbox1.add(label2);
        Vbox2.add(Edit_tmp);
        Vbox1.add(label3);
        Vbox2.add(Edit_hum);
        Vbox1.add(label4);
        Vbox2.add(Edit_lit);

        // 按键加入窗体,创建消息映射
        Vbox3.add(BTN_add); BTN_add.addActionListener(e -> On_BTN_add());
        Vbox3.add(BTN_del); BTN_del.addActionListener(e -> On_BTN_del());
        Vbox3.add(BTN_mod); BTN_mod.addActionListener(e -> On_BTN_mod());
        Vbox3.add(BTN_sum); BTN_sum.addActionListener(e -> On_BTN_sum());
        Vbox3.add(BTN_con); BTN_con.addActionListener(e -> On_BTN_con());
        Vbox3.add(BTN_lad); BTN_lad.addActionListener(e -> On_BTN_lad());

        Hbox.add(Vbox1);
        Hbox.add(Vbox2);

        Panel func = new Panel(new GridLayout(1,0));
        func.add(BTN_Func_Summon);BTN_Func_Summon.addActionListener(e -> On_BTN_Func_Summon());
        func.add(BTN_Func_Drawer);BTN_Func_Drawer.addActionListener(e -> On_BTN_Func_Drawer());
        Vbox4.add(func);
        Vbox4.add(Box.createVerticalStrut(70));
        Vbox4.add(Hbox);
        Vbox4.add(Box.createVerticalStrut(40));
        Vbox4.add(Vbox3);


        return Vbox4;
    }
    public void InitWindow(){   //初始化table主窗体
        window.setSize(600,400);
        window.setResizable(false);
        window.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        // 初始化表格形式，选择信号为行选
        Object[][] tableData = new Object[0][4];    //表格内容
        String[] names = {"时间","温度","湿度","光强"};  //设置表头
        model = new MyTableModel(tableData,names);
        table = new JTable(model);
        table.getSelectionModel().setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        //设置table点击事件，点击即选中单行
        table.addMouseListener(new MouseListener() {
            @Override public void mouseClicked(MouseEvent e) {
                On_Table_clicked();
            }
            @Override public void mousePressed(MouseEvent e) {}
            @Override public void mouseReleased(MouseEvent e) {}
            @Override public void mouseEntered(MouseEvent e) {}
            @Override public void mouseExited(MouseEvent e) {}
        });

        //将table放入JScrollPane以便于滚动表格
        JScrollPane scrollPane = new JScrollPane(table);
        //开始布局控件
        JPanel panel1 = new JPanel();
        panel1.add(scrollPane);
        JPanel panel2 = new JPanel();
        panel2.add(getPanel());
        JPanel total = new JPanel();
        total.add(panel1);
        total.add(panel2);
        //加入控件
        window.add(total);
        window.pack();
        window.setVisible(true);
    }
    void On_BTN_add(){
        if (connect_flag == 0){
            Update_Field();
            model.addRow(GetObject());
        }
        else{
            String sql = "Insert Into visual_sensor values('"
                    +SqlMessage.prepared.tim+"',"
                    +SqlMessage.prepared.tmp+","
                    +SqlMessage.prepared.hum+","
                    +SqlMessage.prepared.lit+");";
            try {
                sys.upDate(sql);
                Update_Field();
                model.addRow(GetObject());
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    void On_BTN_del(){
        if(connect_flag == 0){
            int flag = table.getSelectedRow();
            if(flag == -1) {
                JOptionPane.showMessageDialog(null, "未选择删除元素", "错误",JOptionPane.ERROR_MESSAGE);
            }else{
                model.removeRow(table.getSelectedRow());
            }
        }else{
            String sql = "Delete from visual_sensor Where time='"
                    + SqlMessage.selected.tim + "' and temperature = "
                    + SqlMessage.selected.tmp + " and humidity = "
                    + SqlMessage.selected.hum + " and light = "
                    + SqlMessage.selected.lit + ";";
            try {
                sys.upDate(sql);
                model.removeRow(table.getSelectedRow());
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    void On_BTN_mod(){
        if (connect_flag == 0){
            On_BTN_del();
            On_BTN_add();
        }else{
            String sql = "Update visual_sensor set time = '"
            +SqlMessage.prepared.tim+ "', temperature = "
            +SqlMessage.prepared.tmp+ ", humidity = "
            +SqlMessage.prepared.hum+ ",light = "
            +SqlMessage.prepared.lit+ " where time = '"
            +SqlMessage.selected.tim+ "' AND temperature = "
            +SqlMessage.selected.tmp+ " AND humidity = "
            +SqlMessage.selected.hum+ " AND light = "
            +SqlMessage.selected.lit+ ";";
            try {
                sys.upDate(sql);
                //避免重复执行sql节约资源
                connect_flag = 0;
                On_BTN_del();
                On_BTN_add();
                connect_flag = 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    void On_BTN_sum(){
        //生成数据并填入缓冲区
		//数据均采用两位小数
        //格式化时间 时：分：秒
        LocalTime time = LocalTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss");
        String tm = time.format(formatter);
        SqlMessage.prepared.tim = tm;
        //格式化湿度 0-1
        double rand1=Math.random();
        String hum="0";
        hum += new DecimalFormat(".00").format(rand1);
        SqlMessage.prepared.hum = hum;
        //格式化光强 0-600
        double rand21=Math.random();
        Random random = new Random();
        int rand22 = random.nextInt(600);
        String light = rand22 + new DecimalFormat(".00").format(rand21);
        SqlMessage.prepared.lit = light;
        //格式化温度 18-34
        double rand31=Math.random();
        int rand32 = random.nextInt(16)+18;
        String tmp = rand32 + new DecimalFormat(".00").format(rand31);
        SqlMessage.prepared.tmp = tmp;
        //打印提示信息
        System.out.println("Summon column as\t"+tm+"\t"+tmp+"\t"+hum+"\t"+light);
        ShowInfo();
    }
    void On_BTN_con(){
        //切换sql连接状态，默认关闭,点击开启后直接读取DB table信息并加载
        if(connect_flag == 0){
            try {
                sys.SqlInit();
                System.out.println("Connect sql successfully!");
                connect_flag =1;
            } catch (SQLException | ClassNotFoundException e) {
                System.out.println("Connect sql failed!");
                throw new RuntimeException(e);
            }
        }else{
            try {
                sys.CloseAll();
                System.out.println("Close sql successfully!");
                connect_flag = 0;
            } catch (SQLException e) {
                System.out.println("Close sql failed!");
                throw new RuntimeException(e);
            }
        }
    }
    void On_BTN_lad(){
        if(connect_flag == 1){
        String sql = "select * from visual_sensor;";
        try {
            sys.SendMessage(sql);
            model.setRowCount(0);
            while(SqlMessage.result.next()){
                //改为在窗口上显示
                Object[] ob = new Object[4];
                ob[0] = SqlMessage.result.getString("time");
                ob[1] = SqlMessage.result.getString("temperature");
                ob[2] = SqlMessage.result.getString("humidity");
                ob[3] = SqlMessage.result.getString("light");
                model.addRow(ob);
            }
            SqlMessage.result.close();
        }
        catch (SQLException e) {
            System.out.println("加载数据异常");
            throw new RuntimeException(e);
        }}else{
            JOptionPane.showMessageDialog(null, "请连接数据库后刷新", "错误",JOptionPane.ERROR_MESSAGE);
        }

    }
    void On_Table_clicked(){
        int flag = table.getSelectedRow();
        if (flag!= -1){
            SqlMessage.prepared.tim = table.getValueAt(flag,0).toString();
            SqlMessage.prepared.tmp = table.getValueAt(flag,1).toString();
            SqlMessage.prepared.hum = table.getValueAt(flag,2).toString();
            SqlMessage.prepared.lit = table.getValueAt(flag,3).toString();

            SqlMessage.selected.tim = SqlMessage.prepared.tim;
            SqlMessage.selected.tmp = SqlMessage.prepared.tmp;
            SqlMessage.selected.hum = SqlMessage.prepared.hum;
            SqlMessage.selected.lit = SqlMessage.prepared.lit;
            ShowInfo();
        }
    }
    void ShowInfo(){
        Edit_lit.setText(SqlMessage.prepared.lit);
        Edit_tmp.setText(SqlMessage.prepared.tmp);
        Edit_hum.setText(SqlMessage.prepared.hum);
        Edit_tim.setText(SqlMessage.prepared.tim);
    }
    Object[] GetObject(){
        Object[] ob = new Object[4];
        ob[0] = SqlMessage.prepared.tim;
        ob[1] = SqlMessage.prepared.tmp;
        ob[2] = SqlMessage.prepared.hum;
        ob[3] = SqlMessage.prepared.lit;
        return ob;
    }
    void Update_Field(){
        SqlMessage.prepared.tim = Edit_tim.getText();
        SqlMessage.prepared.tmp = Edit_tmp.getText();
        SqlMessage.prepared.hum = Edit_hum.getText();
        SqlMessage.prepared.lit = Edit_lit.getText();
    }

    //***********************************************生成与绘制功能模块主要内容**********************************************
    Timer timer = new Timer(1000, e -> {
        //1秒生成一个数据,通过模拟按键实现
        On_BTN_sum();
        Insert_DataSet();
        On_BTN_add();
    });
    static JButton BTN_Func_Summon = new JButton("生成器");
    static JButton BTN_Func_Drawer = new JButton("制表器");
    void On_BTN_Func_Summon(){
        if(!timer.isRunning()){
            timer.start();
            System.out.println("Timer running ... ...");
        }else{
            timer.stop();
            System.out.println("Timer stopped ... ...");
        }
    }

    DefaultCategoryDataset dataset = new DefaultCategoryDataset();
    void On_BTN_Func_Drawer(){
        JFreeChart chart = ChartFactory.createLineChart(
                "DataSet",
                "Collected Time",
                "Values(%)",
                dataset,
                PlotOrientation.VERTICAL,
                true,false,false
                );
        ChartFrame chartFrame = new ChartFrame("Course Design",chart);
        chartFrame.pack();
        chartFrame.setVisible(true);
    }
    void Insert_DataSet(){
        dataset.addValue((Double.parseDouble(SqlMessage.prepared.tmp)-18)/0.16,"temperature",SqlMessage.prepared.tim);
        dataset.addValue(Double.parseDouble(SqlMessage.prepared.hum)*100,"humidity",SqlMessage.prepared.tim);
        dataset.addValue(Double.parseDouble(SqlMessage.prepared.lit)/6,"light",SqlMessage.prepared.tim);
    }
}

class MyTableModel extends DefaultTableModel{
    //自己构建表格模型，删除表格内编辑属性
    @Override
    public boolean isCellEditable(int row, int column) {
        return false;
    }
    public MyTableModel(Object[][] data, Object[] columnNames) {
        setDataVector(data, columnNames);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355

5、main方法

package Course_Design;
import java.sql.*;

public class Conn {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        MyWindow window = new MyWindow();
        window.InitWindow();
    }
}
//请注意将上面三个类放在一个软件包里，并用packge 包名;
1
2
3
4
5
6
7
8
9
10

看到最后，如果觉得对你有帮助，不妨点个关注，到主页看看我的其他作品，欢迎下次光临。