#include #include class param; QString param::dtype; QString param::imei; QString param::iccid; QString param::csq = "0"; QString param::mqtt_main_ip; QString param::mqtt_other_ip; int param::mqtt_main_port; int param::mqtt_other_port; int param::mqtt_main_keepalive; int param::mqtt_other_keepalive; int param::mqtt_main_qos; int param::mqtt_other_qos; QString param::mqtt_main_clientid; QString param::mqtt_other_clientid; QString param::mqtt_main_uid; QString param::mqtt_other_uid; QString param::mqtt_main_pwd; QString param::mqtt_other_pwd; QString param::mqtt_main_sub; QString param::mqtt_other_sub; QString param::mqtt_main_pub; QString param::mqtt_other_pub; QString param::mqtt_main_offline; QString param::mqtt_other_offline; QString param::ftp_main_ip; QString param::ftp_other_ip; int param::ftp_main_port; int param::ftp_other_port; QString param::ftp_main_dir; QString param::ftp_other_dir; QString param::ftp_main_uid; QString param::ftp_other_uid; QString param::ftp_main_pwd; QString param::ftp_other_pwd; int param::ftp_main_mode; int param::ftp_other_mode; int param::dat_f_main; int param::dat_f_other; QString param::gps_state; QString param::lbs_lat; QString param::lbs_lng; QString param::gps_lat; QString param::gps_lng; QString param::gps_elevation; QString param::camera_type; QString param::stitch_mode; int param::stitch_photonum; int param::photo_height; int param::photo_width; QString param::auto_focus; QString param::movZ_f_l; QString param::movZ_b_l; QString param::movX_f_l; QString param::movX_b_l; int param::camera_tc = 0; QStringList param::ptlist; QStringList param::ptlist_res; int param::jsonCacheEnable; int param::imageCacheEnable; int param::maxJsonEntries; int param::maxImageEntries; int param::x_init; int param::y_init; bool param::net_alive; QString param::net_name; QString param::net_description; QString param::net_mac; QString param::net_ipv4; int param::datupload_remain; SqliteOperator::SqliteOperator(QString &dbname) { LOG("实例化:SqliteOperator:{}",dbname.toStdString()); if(QSqlDatabase::contains("qt_sql_default_connection")) { db = QSqlDatabase::database("qt_sql_default_connection"); } else { #if ANDROID QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation); QString db_name = db_Path + "/" + dbname; #else QString db_Path = "./"; QString db_name = db_Path + dbname; #endif db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection"); db.setDatabaseName(db_name); // db.setUserName("admin"); // db.setPassword("yf123456"); } } bool InsertJson_mqtt() { #if ANDROID QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation); QString db_name = db_Path + "/" + "data.db"; #else QString db_Path = "./"; QString db_name = "./data.db"; #endif QSqlDatabase mqtt_db; if(QSqlDatabase::contains("qt_sql_default_connection")) { mqtt_db = QSqlDatabase::addDatabase("QSQLITE"); } QSqlQuery sql_mq; mqtt_db.setDatabaseName(db_name); if(!mqtt_db.open()) { qDebug() << "Error: Failed to connect database." << mqtt_db.lastError(); return false; } //判断表是否存在 if(!mqtt_db.tables().contains("data")) { sql_mq.prepare(QString("CREATE TABLE data (\ id INT PRIMARY KEY NOT NULL,\ datetime TEXT NOT NULL,\ Json_data TEXT NOT NULL,\ Json_warn TEXT NOT NULL)")); if(!sql_mq.exec()) { qDebug() << "Error: Fail to create table." << sql_mq.lastError(); return false; } else { //qDebug() <<"mqtt data Table created!"; LOG("mqtt data Table created!"); } } //获取游标对象 //查询数据数量是否超过设定值 sql_mq.prepare("SELECT COUNT(*) FROM data;"); if(sql_mq.exec()&&sql_mq.next()) { int row_count = sql_mq.value(0).toInt(); qDebug() << "Number of rows in table: " << row_count; } // config_db_type mqtt_class_history; // QJsonObject json_dat; // json_dat.insert("vbat","24.01"); // json_dat.insert("imei","123132123"); // for(int dbi = 0;dbi <10000; dbi++) // { // QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间 // mqtt_class_history.json_db.id = dbi; // mqtt_class_history.json_db.datetime = dateTime .toString("yyyy-MM-dd hh:mm:ss"); // mqtt_class_history.json_db.Json_data = QString(QJsonDocument(json_dat).toJson()); // mqtt_class_history.json_db.Json_warn = QString(QJsonDocument(json_dat).toJson()); // sql_mq.prepare(QString("INSERT INTO data VALUES(:id,:datetime,:Json_data,:Json_warn)")); // sql_mq.bindValue(":id", mqtt_class_history.json_db.id); // sql_mq.bindValue(":datetime", mqtt_class_history.json_db.datetime); // sql_mq.bindValue(":Json_data", mqtt_class_history.json_db.Json_data); // sql_mq.bindValue(":Json_warn", mqtt_class_history.json_db.Json_warn); // LOG("write json to data db:{}",dbi); // if(!sql_mq.exec()) // { // qDebug() <<"Error: Fail to insert data:"<< sql_mq.lastError(); // } // else // { // LOG("InsertJson Success!"); // } // } mqtt_db.close(); return true; } bool SqliteOperator::OpenDb() { QString db_Path = QStandardPaths::writableLocation(QStandardPaths::GenericDataLocation); QString db_name = db_Path + "/" + "config.db"; if(QSqlDatabase::contains("qt_sql_default_connection")) { db = QSqlDatabase::database("qt_sql_default_connection"); }else{ db = QSqlDatabase::addDatabase("QSQLITE","qt_sql_default_connection"); db.setDatabaseName(db_name); } if(!db.open()) { qDebug() << "Error: Failed to connect database." << db.lastError(); return false; } return true; } bool SqliteOperator::isTableExist(QString& tableName) { QSqlDatabase database = QSqlDatabase::database(); if(database.tables().contains(tableName)) { return true; } return false; } int SqliteOperator::CloseDb() { QSqlDatabase::removeDatabase("qt_sql_default_connection"); //db.close(); return 0; } int SqliteOperator::CreateTable(QString& tableName) { QSqlQuery sql_query; QString creat_sql; if(tableName == QString::fromLocal8Bit("mqtt")) { creat_sql = QString("CREATE TABLE mqtt (\ id INT PRIMARY KEY NOT NULL,\ dtype TEXT NOT NULL,\ ip TEXT NOT NULL,\ port INT NOT NULL,\ imei TEXT NOT NULL,\ uid TEXT NOT NULL,\ pwd TEXT NOT NULL,\ clientid TEXT,\ qos INT NOT NULL,\ keepalive INT NOT NULL,\ sub TEXT NOT NULL,\ pub TEXT NOT NULL,\ offline TEXT NOT NULL,\ dat_f INT NOT NULL)"); } if(tableName == QString::fromLocal8Bit("mqtt_other")) { creat_sql = QString("CREATE TABLE mqtt_other (\ id INT PRIMARY KEY NOT NULL,\ dtype TEXT NOT NULL,\ ip TEXT NOT NULL,\ port INT NOT NULL,\ imei TEXT NOT NULL,\ uid TEXT NOT NULL,\ pwd TEXT NOT NULL,\ clientid TEXT,\ qos INT NOT NULL,\ keepalive INT NOT NULL,\ sub TEXT NOT NULL,\ pub TEXT NOT NULL,\ offline TEXT NOT NULL,\ dat_f INT NOT NULL)"); } if(tableName == QString::fromLocal8Bit("ftp")) { creat_sql = QString("CREATE TABLE ftp (\ id INT PRIMARY KEY NOT NULL,\ ip TEXT NOT NULL,\ port INT NOT NULL,\ dir TEXT NOT NULL,\ uid TEXT NOT NULL,\ pwd TEXT NOT NULL,\ mode INT NOT NULL)"); } if(tableName == QString::fromLocal8Bit("ftp_other")) { creat_sql = QString("CREATE TABLE ftp_other (\ id INT PRIMARY KEY NOT NULL,\ ip TEXT NOT NULL,\ port INT NOT NULL,\ dir TEXT NOT NULL,\ uid TEXT NOT NULL,\ pwd TEXT NOT NULL,\ mode INT NOT NULL)"); } if(tableName==QString::fromLocal8Bit("location")) { creat_sql = QString("CREATE TABLE location (\ id INT PRIMARY KEY NOT NULL,\ lbs_lat TEXT NOT NULL,\ lbs_lng TEXT NOT NULL,\ gps_lat TEXT NOT NULL,\ gps_lng TEXT NOT NULL,\ gps_elevation TEXT NOT NULL)"); } if(tableName==QString::fromLocal8Bit("camera")) { creat_sql = QString("CREATE TABLE camera (\ camera_type TEXT NOT NULL,\ mode TEXT NOT NULL,\ stitch_photonum INT NOT NULL,\ photo_width INT NOT NULL,\ photo_height INT NOT NULL,\ auto_focus TEXT NOT NULL,\ movZ_f TEXT NOT NULL,\ movZ_b TEXT NOT NULL,\ movX_f TEXT NOT NULL,\ movX_b TEXT NOT NULL)"); } if(tableName==QString::fromLocal8Bit("config")) { creat_sql = QString("CREATE TABLE config (\ jsonCacheEnable INT NOT NULL,\ imageCacheEnable INT NOT NULL,\ maxJsonEntries INT NOT NULL,\ maxImageEntries INT NOT NULL,\ x_init INT NOT NULL,\ y_init TEXT NOT NULL)"); } sql_query.prepare(creat_sql); if(!sql_query.exec()) { qDebug() << "Error: Fail to create table." << sql_query.lastError(); } else { qDebug() << "Table created!" << creat_sql; } return 0; } int SqliteOperator::InsertData(config_db_type &singledb,QString tablename) { QSqlQuery sql_query; if(tablename == QString::fromLocal8Bit("mqtt")||tablename == QString::fromLocal8Bit("mqtt_other")) { sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:dtype,:ip,:port,:imei,:uid,:pwd,:clientid,:qos,:keepalive,:sub,:pub,:offline,:dat_f)").arg(tablename)); sql_query.bindValue(":id", singledb.mqtt_db.id); sql_query.bindValue(":dtype", singledb.mqtt_db.dtype); sql_query.bindValue(":ip", singledb.mqtt_db.ip); sql_query.bindValue(":port", singledb.mqtt_db.port); sql_query.bindValue(":imei",singledb.mqtt_db.imei); sql_query.bindValue(":uid",singledb.mqtt_db.uid); sql_query.bindValue(":pwd",singledb.mqtt_db.pwd); sql_query.bindValue(":clientid",singledb.mqtt_db.clientid); sql_query.bindValue(":qos",singledb.mqtt_db.qos); sql_query.bindValue(":keepalive",singledb.mqtt_db.keepalive); sql_query.bindValue(":sub",singledb.mqtt_db.sub); sql_query.bindValue(":pub",singledb.mqtt_db.pub); sql_query.bindValue(":offline",singledb.mqtt_db.offline); sql_query.bindValue(":dat_f",singledb.mqtt_db.dat_f); } else if(tablename == QString::fromLocal8Bit("ftp")||tablename == QString::fromLocal8Bit("ftp_other")) { sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:ip,:port,:dir,:uid,:pwd,:mode)").arg(tablename)); sql_query.bindValue(":id", singledb.ftp_db.id); sql_query.bindValue(":ip", singledb.ftp_db.ip); sql_query.bindValue(":port", singledb.ftp_db.port); sql_query.bindValue(":dir",singledb.ftp_db.dir); sql_query.bindValue(":uid",singledb.ftp_db.uid); sql_query.bindValue(":pwd",singledb.ftp_db.pwd); sql_query.bindValue(":mode", singledb.ftp_db.mode); } else if(tablename == QString::fromLocal8Bit("location")) { sql_query.prepare(QString("INSERT INTO %1 VALUES(:id,:lbs_lat,:lbs_lng,:gps_lat,:gps_lng,:gps_elevation)").arg(tablename)); sql_query.bindValue(":id", singledb.location_db.id); sql_query.bindValue(":lbs_lat", singledb.location_db.lbs_lat); sql_query.bindValue(":lbs_lng", singledb.location_db.lbs_lng); sql_query.bindValue(":gps_lat", singledb.location_db.gps_lat); sql_query.bindValue(":gps_lng", singledb.location_db.gps_lng); sql_query.bindValue(":gps_elevation", singledb.location_db.gps_elevation); }else if(tablename == QString::fromLocal8Bit("camera")){ sql_query.prepare(QString("INSERT INTO %1 VALUES(:camera_type,:mode,:stitch_photonum,:photo_width,:photo_height,:auto_focus,:movZ_f,:movZ_b,:movX_f,:movX_b)").arg(tablename)); sql_query.bindValue(":camera_type", singledb.camera_db.camera_type); sql_query.bindValue(":mode", singledb.camera_db.mode); sql_query.bindValue(":stitch_photonum", singledb.camera_db.stitch_photonum); sql_query.bindValue(":photo_width", singledb.camera_db.photo_width); sql_query.bindValue(":photo_height", singledb.camera_db.photo_height); sql_query.bindValue(":auto_focus",singledb.camera_db.auto_focus); sql_query.bindValue(":movZ_f",singledb.camera_db.movz_f); sql_query.bindValue(":movZ_b",singledb.camera_db.movz_b); sql_query.bindValue(":movX_f",singledb.camera_db.movx_f); sql_query.bindValue(":movX_b",singledb.camera_db.movx_b); }else if(tablename == QString::fromLocal8Bit("config")){ sql_query.prepare(QString("INSERT INTO %1 VALUES(:jsonCacheEnable,:imageCacheEnable,:maxJsonEntries,:maxImageEntries,:x_init,:y_init)").arg(tablename)); sql_query.bindValue(":jsonCacheEnable", singledb.config_db.jsonCacheEnable); sql_query.bindValue(":imageCacheEnable", singledb.config_db.imageCacheEnable); sql_query.bindValue(":maxJsonEntries", singledb.config_db.maxJsonEntries); sql_query.bindValue(":maxImageEntries", singledb.config_db.maxImageEntries); sql_query.bindValue(":x_init", singledb.config_db.x_init); sql_query.bindValue(":y_init",singledb.config_db.y_init); } if(!sql_query.exec()) { qDebug() <<"Error: Fail to insert data:"<< tablename << sql_query.lastError(); } else { LOG("InsertData Success!"); } return 0; } void InertListConfig(QList &moreData,QString tablename) { // 进行多个数据的插入时,可以利用绑定进行批处理 QSqlQuery sqlQuery; sqlQuery.prepare("INSERT INFO mqtt VALUES(?,?,?)"); QVariantList idList; } int SqliteOperator::UpdateData(QString tablename, QString name, QString value) { LOG("UpdateData:{}-{}-{}",tablename.toStdString(),name.toStdString(),value.toStdString()); //QString update_sql = "UPDATE mqtt SET ip = :ip WHERE id = :id"; QSqlQuery sql_query; sql_query.prepare(QString("UPDATE %1 SET %2=?").arg(tablename,name)); sql_query.addBindValue(value); if(!sql_query.exec()) { ERROR("update Failed!"); }else{ LOG("updated Success!"); } return 0; } int SqliteOperator::QueryAllData(QString& table_name) { QSqlQuery sql_query; int data_num = 0; sql_query.exec(QString("SELECT * FROM %1").arg(table_name)); if(!sql_query.exec()) { qDebug() << "Error: Fail to query table. " << sql_query.lastError(); } else { while(sql_query.next()) { data_num ++; if(table_name == QString::fromLocal8Bit("mqtt")) { //param_mqtt_main["id"] = sql_query.value(0).toInt(); param::dtype = sql_query.value(1).toString(); param::mqtt_main_ip = sql_query.value(2).toString(); param::mqtt_main_port = sql_query.value(3).toInt(); if(param::imei.size()<10) param::imei = sql_query.value(4).toString(); param::mqtt_main_uid = sql_query.value(5).toString(); param::mqtt_main_pwd = sql_query.value(6).toString(); param::mqtt_main_clientid = sql_query.value(7).toString(); param::mqtt_main_qos = sql_query.value(8).toInt(); param::mqtt_main_keepalive = sql_query.value(9).toInt(); param::mqtt_main_sub = sql_query.value(10).toString(); param::mqtt_main_pub = sql_query.value(11).toString(); param::mqtt_main_offline = sql_query.value(12).toString(); param::dat_f_main = sql_query.value(13).toInt(); LOG("ip:{}",param::mqtt_main_ip.toStdString()); LOG("dat_f:{}",param::dat_f_main); } else if(table_name == QString::fromLocal8Bit("mqtt_other")) { //param_mqtt_other["id"] = sql_query.value(0).toInt(); //param_mqtt_other["dtype"] = sql_query.value(1).toString(); param::mqtt_other_ip = sql_query.value(2).toString(); param::mqtt_other_port = sql_query.value(3).toInt(); //param_mqtt_other["imei"] = sql_query.value(4).toString(); param::mqtt_other_uid = sql_query.value(5).toString(); param::mqtt_other_pwd = sql_query.value(6).toString(); param::mqtt_other_clientid = sql_query.value(7).toString(); param::mqtt_other_qos = sql_query.value(8).toInt(); param::mqtt_other_keepalive = sql_query.value(9).toInt(); param::mqtt_other_sub = sql_query.value(10).toString(); param::mqtt_other_pub = sql_query.value(11).toString(); param::mqtt_other_offline = sql_query.value(12).toString(); param::dat_f_other = sql_query.value(13).toInt(); } else if(table_name == QString::fromLocal8Bit("ftp")) { param::ftp_main_ip = sql_query.value(1).toString(); param::ftp_main_port = sql_query.value(2).toInt(); param::ftp_main_dir = sql_query.value(3).toString(); param::ftp_main_uid = sql_query.value(4).toString(); param::ftp_main_pwd = sql_query.value(5).toString(); param::ftp_main_mode = sql_query.value(6).toInt(); } else if(table_name == QString::fromLocal8Bit("ftp_opther")) { param::ftp_other_ip = sql_query.value(1).toString(); param::ftp_other_port = sql_query.value(2).toInt(); param::ftp_other_dir = sql_query.value(3).toString(); param::ftp_other_uid = sql_query.value(4).toString(); param::ftp_other_pwd = sql_query.value(5).toString(); param::ftp_other_mode = sql_query.value(6).toInt(); } else if(table_name == QString::fromLocal8Bit("location")) { param::lbs_lat = sql_query.value(1).toString(); param::lbs_lng = sql_query.value(2).toString(); param::gps_lat = sql_query.value(3).toString(); param::gps_lng = sql_query.value(4).toString(); param::gps_elevation = sql_query.value(5).toString(); }else if(table_name == QString::fromLocal8Bit("camera")) { param::camera_type = sql_query.value(0).toString(); param::stitch_mode = sql_query.value(1).toString(); param::stitch_photonum = sql_query.value(2).toInt(); param::photo_width = sql_query.value(3).toInt(); param::photo_height = sql_query.value(4).toInt(); param::auto_focus = sql_query.value(5).toString(); param::movZ_f_l = sql_query.value(6).toString(); param::movZ_b_l = sql_query.value(7).toString(); param::movX_f_l = sql_query.value(8).toString(); param::movX_b_l = sql_query.value(9).toString(); LOG("camera_type:{}",param::camera_type.toStdString()); LOG("stitch_mode:{}",param::stitch_mode.toStdString()); LOG("stitch_photonum:{}",param::stitch_photonum); LOG("photo_width:{}",param::photo_width); LOG("photo_height:{}",param::photo_height); if(param::movZ_f_l.isEmpty()||param::movZ_f_l==""){ param::movZ_f_l = movZ_f; } if(param::movZ_b_l.isEmpty()||param::movZ_b_l==""){ param::movZ_b_l = movZ_b; } if(param::movX_f_l.isEmpty()||param::movX_f_l==""){ param::movX_f_l = movX_f; } if(param::movX_b_l.isEmpty()||param::movX_b_l==""){ param::movX_b_l = movX_b; } LOG("movZ_f_l:{}",param::movZ_f_l.toStdString()); LOG("movZ_b_l:{}",param::movZ_b_l.toStdString()); LOG("movX_f_l:{}",param::movX_f_l.toStdString()); LOG("movX_b_l:{}",param::movX_b_l.toStdString()); }else if(table_name == QString::fromLocal8Bit("config")) { param::jsonCacheEnable = sql_query.value(0).toInt(); param::imageCacheEnable = sql_query.value(1).toInt(); param::maxJsonEntries = sql_query.value(2).toInt(); param::maxImageEntries = sql_query.value(3).toInt(); param::x_init = sql_query.value(4).toInt(); param::y_init = sql_query.value(5).toInt(); LOG("jsonCacheEnable:{}",param::jsonCacheEnable); LOG("imageCacheEnable:{}",param::imageCacheEnable); LOG("maxJsonEntries:{}",param::maxJsonEntries); LOG("maxImageEntries:{}",param::maxImageEntries); LOG("x_init:{}",param::x_init); LOG("y_init:{}",param::y_init); } LOG("data_num:{}",data_num); } } return 0; } int SqliteOperator::QueryData() { QString select_sql = QString("select * from student where name = '%1' and (age = '%2' or age = '%3')") .arg("Wang") .arg(30) .arg(25); QSqlQuery sql_query; if(!sql_query.exec(select_sql)) { qDebug()<