对qsql相关操作的模块封装,封装后的接口可以不用熟悉qsql相关类也能使用
// .h头文件
#include <QSqlQuery>
#include <QVector>
#include <QPixmap>
#include <QMap>
#include <QStringList>
class SqlInterface
{
public:
//SqlInterface();
//~SqlInterface();
/* connectionName用于区分不同的连接,既可以是同一个数据库的不同连接,也可以是不同数据库的不同连接,如果为空,则使用默认连接 */
static bool Open(const QString &dbFileName, const QString &connectionName = "");
static void RemoveDatabase(const QString &connectionName);
// 只返回一组值
static bool Get(const QString &tableName, const QString &fieldName, QVector<QVariant> &valVec,
const QString &conditionStr, const QString &connectionName = "");
//返回多组值,返回一组值时也可以调本函数
static bool Get(const QString &tableName, const QStringList &fieldNameList, QVector<QVector<QVariant> > &valVec,
const QString &conditionStr, const QString &connectionName = "");
static bool Get(const QString &tableName, const QString &fieldName, QVector<QVariant> &valVec,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName = "");
static bool Get(const QString &tableName, const QStringList &fieldNameList, QVector<QVector<QVariant> > &valVec,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName = "");
static bool Update(const QString &tableName, const QString &fieldName, const QVariant &val, const QString &conditionStr,
const QString &connectionName = "");
static bool Update(const QString &tableName, const QStringList &fieldNameList, const QVector<QVariant> &valVec,
const QString &conditionStr, const QString &connectionName = "");
static bool Update(const QString &tableName, const QString &fieldName, const QVariant &val,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName = "");
static bool Update(const QString &tableName, const QStringList &fieldNameList, const QVector<QVariant> &valVec,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName = "");
//一次只允许插入一条记录
static bool Insert(const QString &tableName, const QStringList &fieldNameList, const QVector<QVariant> &valVec
, const QString &connectionName = "");
//一次插入多条语句
static bool Insert(const QString &tableName, const QStringList &fieldNameList, const QVector<QVector<QVariant> > &valVec
, const QString &connectionName = "");
static bool Delete(const QString &tableName, const QString &conditionstr, const QString &connectionName = "");
static bool LoadPixMap(const QString &filepath, QPixmap &pix);
static bool SavePixMap(const QString &filepath, const QPixmap &pix);
private:
// static QSqlQuery Query(const QString &tableName, const QString &conditionStr, const QString &connectionName = "");
static QSqlQuery Query(const QString &tableName, const QStringList &fieldList, const QString &conditionStr,
const QString &connectionName = "");
static QString mapValToString(const QMap<QString,QVariant> &map);
};
// cpp文件
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QVariant>
#include <QSqlError>
#include "SqlInterface.h"
/*
SqlInterface::SqlInterface()
{
// m_isOpen = false;
}
SqlInterface::~SqlInterface()
{
}*/
/* 需要一个数据库路径,用于打开数据库,一个连接名,连接名用于区分其他的数据库连接 */
bool SqlInterface::Open(const QString &dbFileName, const QString &connectionName)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",connectionName);
db.setDatabaseName(dbFileName);
if(!db.open())
{
printf("open db fail\n");
return false;
}
printf("open db succ\n");
return true;
/*
QSqlQuery query;
bool suc = query.exec("select * from tabletest");
if(suc)
{
while(query.next())
{
QSqlRecord recd = query.record();
printf("count :%d",recd.count());
for(int i=0; i<recd.count(); i++)
{
printf("name:%s value:%s",recd.fieldName(i).toLocal8Bit().data(),
recd.value(i).toString().toLocal8Bit().data());
}
}
}
QString tablename = db.tables()[0];
printf("tablename:%s",tablename.toLocal8Bit().data());
QString str = QString("insert into %1 values(?,?,?)").arg(tablename);
query.prepare(str);
query.bindValue(0,"4");
query.bindValue(1,"guanyu");
query.bindValue(2,"24");
if(!query.exec())
{
printf("exec fail:%s",query.lastError().text().toLocal8Bit().data());
return false;
}
str = QString("update %1 set %2 = ?,%3 = ? where %4 = ?").arg(tablename).arg();
*/
}
void SqlInterface::RemoveDatabase(const QString &connectionName)
{
QSqlDatabase::removeDatabase(connectionName);
}
/* select * from tablename where a = b */
/*
QSqlQuery SqlInterface::Query(const QString &tableName, const QString &conditionStr, const QString &connectionName)
{
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
QString str = QString("select * from %1 where %2").arg(tableName).arg(conditionStr);
printf("%s",str.toLocal8Bit().data());
bool succ = query.exec(str);
if(!succ)
{
printf("query fail");
}
return query;
}*/
/* select a,b,c from tablename where d = e
fieldVec为空则默认为查询全部
conditionStr里面如果为字符串,则用单引号引住
*/
QSqlQuery SqlInterface::Query(const QString &tableName, const QStringList &fieldList,
const QString &conditionStr, const QString &connectionName)
{
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
if(fieldList.size() == 0)
{
printf("field name is empty\n");
return QSqlQuery();
}
// return Query(tableName, conditionStr,connectionName);
QString fstr = fieldList[0];
for(int i = 1 ; i< fieldList.size(); i++)
{
fstr += "," + fieldList[i];
}
QString str;
if (conditionStr.size() <= 0)
{
str = QString("select %1 from %2").arg(fstr).arg(tableName);
}
else
{
str = QString("select %1 from %2 where %3").arg(fstr).arg(tableName).arg(conditionStr);
}
printf("%s\n",str.toLocal8Bit().data());
bool succ = query.exec(str);
if(!succ)
{
printf("query fail,err[%s]\n", query.lastError().text().toLocal8Bit().data());
}
return query;
}
/* select c from tablename where a = b
结果可能有多个:
c 列名
1
2
3
*/
bool SqlInterface::Get(const QString &tableName, const QString &fieldName, QVector<QVariant> &valVec,
const QString &conditionStr, const QString &connectionName)
{
QStringList fnlist;
fnlist<<fieldName;
QSqlQuery query = Query(tableName,fnlist,conditionStr,connectionName);
while(query.next())
{
QVariant var = query.value(fieldName);
if(var.isValid())
{
valVec.push_back(var);
}
}
return true;
}
bool SqlInterface::Get(const QString &tableName, const QString &fieldName, QVector<QVariant> &valVec,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName)
{
QString conditionStr = mapValToString(conditionMap);
return Get(tableName,fieldName,valVec,conditionStr,connectionName);
}
/* select a,b,c from tablename where e = f
结果返回如:
a b c 结果可能有多行,每行用一个vector存储,外面再用一个vector存储所有的行
1 2 3
4 5 6
7 8 9
*/
bool SqlInterface::Get(const QString &tableName, const QStringList &fieldNameList,
QVector<QVector<QVariant> > &valVec, const QString &conditionStr,
const QString &connectionName)
{
QSqlQuery query = Query(tableName,fieldNameList,conditionStr,connectionName);
while(query.next())
{
QVector<QVariant> rowValVec;
for(int i=0; i<fieldNameList.size(); i++)
{
QVariant var = query.value(fieldNameList[i]);
rowValVec.push_back(var);
}
valVec.push_back(rowValVec);
}
return true;
}
bool SqlInterface::Get(const QString &tableName, const QStringList &fieldNameList,
QVector<QVector<QVariant> > &valVec,const QMap<QString,QVariant> &conditionMap,
const QString &connectionName)
{
QString conditionStr = mapValToString(conditionMap);
return Get(tableName,fieldNameList,valVec,conditionStr,connectionName);
}
bool SqlInterface::Update(const QString &tableName, const QString &fieldName, const QVariant &val,
const QString &conditionStr, const QString &connectionName)
{
QString str = QString("update %1 set %2 = ? where %4").arg(tableName).arg(fieldName).arg(conditionStr);
printf("%s\n",str.toLocal8Bit().data());
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
query.prepare(str);
query.bindValue(0,val);
bool succ = query.exec();
if(!succ)
{
printf("Update fail,err:%s\n",query.lastError().text().toLocal8Bit().data());
return false;
}
return true;
}
bool SqlInterface::Update(const QString &tableName, const QString &fieldName, const QVariant &val,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName)
{
QString conditionStr = mapValToString(conditionMap);
return Update(tableName, fieldName, val, conditionStr,connectionName);
}
/* fieldNameVec的大小必须跟 valVec的大小一样 */
bool SqlInterface::Update(const QString &tableName, const QStringList &fieldNameList,
const QVector<QVariant> &valVec, const QString &conditionStr, const QString &connectionName)
{
if(fieldNameList.size() == 0 || valVec.size() == 0)
{
printf("Update field or value is null\n");
return false;
}
if(fieldNameList.size() != valVec.size())
{
printf("Update fieldNameVec.size() != valVec.size()\n");
return false;
}
QString fieldstr = fieldNameList[0] + " = ?";
for(int i=1; i<fieldNameList.size(); i++)
{
fieldstr += "," + fieldNameList[i] + " = ?";
}
QString str = QString("update %1 set %2 where %3").arg(tableName).arg(fieldstr).arg(conditionStr);
printf("%s\n",str.toLocal8Bit().data());
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
query.prepare(str);
for(int i=0; i<valVec.size(); i++)
{
query.bindValue(i,valVec[i]);
}
bool succ = query.exec();
if(!succ)
{
printf("Update fail,err:%s\n",query.lastError().text().toLocal8Bit().data());
return false;
}
return true;
}
bool SqlInterface::Update(const QString &tableName, const QStringList &fieldNameList, const QVector<QVariant> &valVec,
const QMap<QString,QVariant> &conditionMap, const QString &connectionName)
{
QString conditionStr = mapValToString(conditionMap);
return Update(tableName, fieldNameList, valVec, conditionStr,connectionName);
}
bool SqlInterface::Insert(const QString &tableName, const QStringList &fieldNameList,
const QVector<QVariant> &valVec, const QString &connectionName)
{
if(fieldNameList.size() == 0 || valVec.size()==0)
{
printf("Insert field or value is null\n");
return false;
}
if(fieldNameList.size() != valVec.size())
{
printf("Insert fieldNameVec.size() != valVec.size()\n");
return false;
}
QString fieldstr = fieldNameList[0];
QString valstr = "?";
for(int i=1; i<fieldNameList.size(); i++)
{
fieldstr += "," + fieldNameList[i];
valstr += QString(",") + "?";
}
QString str = QString("insert into %1(%2) values(%3)").arg(tableName).arg(fieldstr).arg(valstr);
printf("%s\n",str.toLocal8Bit().data());
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
query.prepare(str);
for(int i=0; i<valVec.size(); i++)
{
query.bindValue(i,valVec[i]);
}
bool succ = query.exec();
if(!succ)
{
printf("Insert fail,err:%s\n", query.lastError().text().toLocal8Bit().data());
return false;
}
return true;
}
bool SqlInterface::Insert(const QString &tableName, const QStringList &fieldNameList, const QVector<QVector<QVariant> > &valVec,
const QString &connectionName)
{
if (fieldNameList.size() == 0 || valVec.size() == 0)
{
printf("Insert field or value is null\n");
return false;
}
if (fieldNameList.size() != valVec[0].size())
{
printf("Insert fieldNameVec.size() != valVec.size()\n");
return false;
}
QString fieldstr = fieldNameList[0];
QString valstr = "?";
for (int i = 1; i < fieldNameList.size(); i++)
{
fieldstr += "," + fieldNameList[i];
valstr += QString(",") + "?";
}
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
QString str = QString("insert into %1(%2) values(%3)").arg(tableName).arg(fieldstr).arg(valstr);
printf("%s\n", str.toLocal8Bit().data());
db.transaction();
for (int i = 0; i < valVec.size(); i++)
{
query.prepare(str);
for (int j = 0; j<valVec[i].size(); j++)
{
query.bindValue(j, valVec[i][j]);
}
bool succ = query.exec();
if (!succ)
{
printf("Insert fail,err:%s,str[%s]\n", query.lastError().text().toLocal8Bit().data(),
str.toLocal8Bit().data());
}
}
db.commit();
return true;
}
bool SqlInterface::Delete(const QString &tableName, const QString &conditionstr, const QString &connectionName)
{
QString str;
if (conditionstr.size() <= 0)
{
str = QString("delete from %1 where 1 = 1").arg(tableName);
}
else
{
str = QString("delete from %1 where %2").arg(tableName).arg(conditionstr);
}
printf("%s\n",str.toLocal8Bit().data());
QSqlDatabase db = QSqlDatabase::database(connectionName);
QSqlQuery query(db);
bool succ = query.exec(str);
if(!succ)
{
printf("Delete fail,err:%s\n", query.lastError().text().toLocal8Bit().data());
return false;
}
return true;
}
QString SqlInterface::mapValToString(const QMap<QString,QVariant> &conditionMap)
{
QString conditionStr;
QMap<QString,QVariant>::const_iterator it = conditionMap.begin();
for(; it!=conditionMap.end(); it++)
{
if(it != conditionMap.begin())
{
conditionStr += " and ";
}
conditionStr += it.key() + QString("= '") + it.value().toString() + QString("'");
}
return conditionStr;
}
bool SqlInterface::LoadPixMap(const QString &filepath, QPixmap &pix)
{
QPixmap p(filepath);
if(!p.isNull())
{
pix = p;
return true;
}
return false;
}
bool SqlInterface::SavePixMap(const QString &filepath, const QPixmap &pix)
{
return pix.save(filepath);
}
#if 0
//测试用例
const QString tablename("interface"), connName("test"),tablename1("interface1"),connName1("test1");
SqlInterface::Open("./QtTest/QtTest/test.s3db",connName);
SqlInterface::Open("./QtTest/QtTest/test1.s3db",connName1);
//查询
QVector<QVariant> vec;
bool succ = SqlInterface::Get(tablename,"name",vec,"age = 12",connName);
qDebug("vec.size = %d",vec.size());
for(int i = 0 ;i<vec.size(); i++)
{
qDebug("table get1:name:%s",vec[i].toString().toLocal8Bit().data());
}
vec.clear();
succ = SqlInterface::Get(tablename1,"name",vec,"age = 12",connName1);
qDebug("vec.size = %d",vec.size());
for(int i = 0 ;i<vec.size(); i++)
{
qDebug("table1 get1:name:%s",vec[i].toString().toLocal8Bit().data());
}
QMap<QString,QVariant> map;
map["age"] = 12;
vec.clear();
succ = SqlInterface::Get(tablename,"name",vec,map,connName);
qDebug("get:suc[%d] vec.size()[%d]",succ,vec.size());
for(int i = 0 ;i<vec.size(); i++)
{
qDebug("Get2: name:%s",vec[i].toString().toLocal8Bit().data());
}
QStringList fnameLs;
fnameLs<<QString("name")<<QString("age")<<QString("phone");
map.clear();
map["name"] = "liubei";
QVector<QVector<QVariant> > vvec;
//succ = sql.Get(tablename,fnameV,vvec,"name = 'liubei'");
succ = SqlInterface::Get(tablename,fnameLs,vvec,map,connName);
qDebug("succ[%d],vec.size[%d]",succ,vvec.size());
for(int i=0; i<vvec.size(); i++)
{
for(int j=0; j<vvec[i].size(); j++)
{
qDebug("get3: vvec[%d], %s",i,vvec[i][j].toString().toLocal8Bit().data());
}
}
//更新
succ = SqlInterface::Update(tablename,"age",39,"name = 'zhaoyun' ",connName);
qDebug("succ[%d]",succ);
QVector<QVariant> valvv;
valvv.push_back("zhaoyun");
valvv.push_back("24");
valvv.push_back("13631421");
succ = SqlInterface::Update(tablename,fnameLs,valvv,"name = 'zhaoyun'",connName);
qDebug("Update:succ[%d]",succ);
//插入
QVector<QVariant> invec;
invec.push_back("zhangfei");
invec.push_back(28);
invec.push_back("1542452");
succ = SqlInterface::Insert(tablename,fnameLs,invec,connName);
qDebug("insert:succ[%d]",succ);
const QString testtable("tabletest");
//同一数据库不同表查询查询
QVector<QVariant> tvec;
succ = SqlInterface::Get(testtable,"name",tvec,"age = 12",connName);
for(int i=0; i<tvec.size(); i++)
{
qDebug("tvec[%d],[%s]",i,tvec[i].toString().toLocal8Bit().data());
}
SqlInterface::Delete(tablename,"name = 'liubei' and age = 12" ,connName);
//tables()函数是获取数据库所有表名, connectionNames是获取该数据库所有连接名
/* QStringList ls = QSqlDatabase::database(connName).connectionNames();
for(int i=0; i<ls.size(); i++)
{
qDebug("tablename[%s]",ls[i].toLocal8Bit().data());
}*/
SqlInterface::RemoveDatabase(connName);
QStringList ls = QSqlDatabase::database(connName).connectionNames();
for(int i=0; i<ls.size(); i++)
{
qDebug("tablename[%s]",ls[i].toLocal8Bit().data());
}
#endif