MQTT_mqtt连接数据库_Mqtt数据库_mqtt_mqttmysql_mqtt与SQL2008

package mqtt; import java.util.Formatter; import java.nio.ByteBuffer; import java.nio.CharBuffer; import java.sql.*; import java.text.*; import java.time.LocalDate; import java.util.Calendar; public class SQL{ static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/Lanpang?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; // 数据库的用户名与密码，需要根据自己的设置 static final String USER = "root"; static final String PASS = "123456"; public static void Operation(char [] data) { Connection conn = null; Statement stmt = null; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql; String str1=null,str2 = null,str3 = null,str4 = null,str5=null; StringBuilder str_id = new StringBuilder(); StringBuilder str_temp = new StringBuilder(); StringBuilder str_humi = new StringBuilder(); str_id.append(data[0]).append(data[1]).append(data[2]).append(data[3]); str_temp.append(data[4]).append(data[5]).append(data[6]).append(data[7]).append(data[8]); str_humi.append(data[9]).append(data[10]).append(data[11]).append(data[12]).append(data[13]); //System.out.println(str_id.toString()); //str2=""+str_id; System.out.println(str_id.toString()); Date currentTime = new Date(System.currentTimeMillis()); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String dateString = formatter.format(currentTime); ResultSet rs = conn.getMetaData().getTables(null, null, "th_"+str_id.toString(), null); if (!(rs.next()))//表不存在则创建 { sql="CREATE TABLE th_"+str_id.toString()+"(`device_id` varchar(11) NOT NULL,`pid` int(64) NOT NULL AUTO_INCREMENT,`temperature` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`humidity` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `creat_time` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (`pid`)) ENGINE=InnoDB AUTO_INCREMENT=4882 DEFAULT CHARSET=utf8"; stmt.executeUpdate(sql); } str1="INSERT INTO th_"+str_id.toString()+"( device_id, temperature,humidity ) VALUES("; str5=dateString; sql=str1+str_id+','+str_temp+','+str_humi+')'; System.out.println(sql); // sql='"'+str1+str2+','+str3+','+str4+','+str5+')'+'"'; //sql = "INSERT INTO temperature_humidity( device_id, temperature,humidity,time ) VALUES(device_id, temperature,humidity,today.getTime() )"; stmt.executeUpdate(sql); stmt.close(); conn.close(); } catch(SQLException se) { // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e) { // 处理 Class.forName 错误 e.printStackTrace(); } finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); } catch(SQLException se2) { }// 什么都不做 try { if(conn!=null) conn.close(); }catch(SQLException se) { se.printStackTrace(); } } System.out.println("Goodbye!"); } }