1. 上传数据类,通过QSqlQuery的函数 prepare(const QString &string)函数,通过占位符绑定值
bool MySQLManager::insertData(const QString &datauser, const QString &datahost, const QString &dataname, const QString &datetime, const QByteArray &data, const QString &datadescription)
{
QMutexLocker locker(&m_db_lock);
if (!m_db_mysql.isOpen())
{
m_db_mysql.open();
}
QSqlQuery query(m_db_mysql);//以下执行相关QSL语句
query.prepare("insert into data(DATA_ID,DATA_LOADER,LOADER_HOST,DATA_NAME,CREATE_TIME,DATA,DATA_DESCRIPTION) VALUES (null,:DATA_LOADER,:LOADER_HOST,:DATA_NAME,:CREATE_TIME,:DATA,:DATA_DESCRIPTION)");
query.bindValue(":DATA_LOADER", datauser);
query.bindValue(":LOADER_HOST", datahost);
query.bindValue(":DATA_NAME", dataname);
query.bindValue(":CREATE_TIME", datetime);
query.bindValue(":DATA", data);
query.bindValue(":DATA_DESCRIPTION", datadescription);
bool bsuccess = query.exec();
if (!bsuccess)
{
QString querys = query.lastError().text();
qDebug()<<querys;
m_db_mysql.close();
return false;
}
return bsuccess;
}
2.查询数据,是整体查询,之后显示在treewidget上,所以查询所有的文件,使用setForwardOnly(true);只向前查询,提高效率,返回一个保存QVector<QStringList>给界面,显示在treeWidget的Item上
QVector<QStringList> MySQLManager::selectData( const QString& sql )
{
QMutexLocker locker(&m_db_lock);
if (!m_db_mysql.isOpen())
{
m_db_mysql.open();
}
//数据存储查询类
QSqlQuery query(m_db_mysql);
query.setForwardOnly(true);
bool bsuccess = query.exec(sql);
QVector<QStringList> vList;
if (bsuccess)
{
int index = 1;
while (query.next())
{
//建表时约定
QString id = query.value(0).toString();
QString datauser = query.value(1).toString();
QString datahost = query.value(2).toString();
QString dataname = query.value(3).toString();
QDateTime datatime = query.value(4).toDateTime();
QString strDatatime = datatime.toString("yyyy-MM-dd hh:mm:ss");
QString data_description = query.value(6).toString();
QStringList strlist;
QString strIndex = QString::number(index);
strlist<<id<<strIndex<<dataname<<strDatatime<<datauser<<datahost<<data_description;
vList.push_back(strlist);
++index;
qDebug()<<"strList"<<strlist;
}
}
else
{
QString querys = query.lastError().text();
m_db_mysql.close();
qDebug()<<querys;
}
return vList;
}
3,删除选中的Item
4.下载,返回一个二进制值QByteArray
//下载数据
QByteArray MySQLManager::downData( const QString& sql)
{
QMutexLocker locker(&m_db_lock);
if (!m_db_mysql.isOpen())
{
m_db_mysql.open();
}
QSqlQuery query(m_db_mysql);
QByteArray data;
bool bsuccess = query.exec(sql);
if (bsuccess)
{
if (query.next())
{
data = query.value(0).toByteArray();
}
}
else
{
QString querys = query.lastError().text();
m_db_mysql.close();
qDebug()<<querys;
}
return data;
}