文章目录
- 1、主要内容
- 2、所需准备
- 3、与sql访问的中间类:SqlMessage
- 4、窗口界面
- 5、main方法
1、主要内容
课程设计,主要通过Javas wing创建窗口,jdbc连接云端mysql数据库进行基本操作,支持随机生成数据并用动态展示数据结果。
先来看一下实现效果:
2、所需准备
- 在SVNCloud上部署MySQL
- 下载所需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();
}
}
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);
}
}
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 包名;
看到最后,如果觉得对你有帮助,不妨点个关注,到主页看看我的其他作品,欢迎下次光临。