数据上传,下载功能(四)之插入数据QSqlQuery操作

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;
}