直接上源码, 需根据自己的数据库表做修改!
源文件:
#include "spliteoperate.h"
// 构造函数中初始化数据库对象,并建立数据库
SqliteOperator::SqliteOperator()
{
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
database = QSqlDatabase::database("qt_sql_default_connection");
}
else
{
// 建立和SQlite数据库的连接
database = QSqlDatabase::addDatabase("QSQLITE");
// 设置数据库文件的名字
database.setDatabaseName("MyDataBase.db");
}
}
// 打开数据库
bool SqliteOperator::openDb()
{
if (!database.open())
{
qDebug() << "Error: Failed to connect database." << database.lastError();
}
else
{
// do something
}
return true;
}
// 创建数据表
void SqliteOperator::createTable(QString tableName)
{
// 用于执行sql语句的对象
QSqlQuery sqlQuery;
// 构建创建数据库的sql语句字符串
QString createSql = QString("CREATE TABLE ").append(tableName).append(" (\
id INT PRIMARY KEY NOT NULL,\
name TEXT NOT NULL,\
age INT NOT NULL,\
password TEXT NOT NULL)");
//qDebug()<<createSql;
sqlQuery.prepare(createSql);
// 执行sql语句
if(!sqlQuery.exec())
{
qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
}
else
{
qDebug() << "Table created!";
}
}
// 判断数据库中某个数据表是否存在
bool SqliteOperator::isTableExist(QString& tableName)
{
QSqlDatabase database = QSqlDatabase::database();
if(database.tables().contains(tableName))
{
return true;
}
return false;
}
// 查询全部数据
void SqliteOperator::queryTable(QString tableName)
{
QSqlQuery sqlQuery;
sqlQuery.exec(QString("SELECT * FROM ").append(tableName));
if(!sqlQuery.exec())
{
qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
}
else
{
while(sqlQuery.next())
{
int id = sqlQuery.value(0).toInt();
QString name = sqlQuery.value(1).toString();
int age = sqlQuery.value(2).toInt();
QString password = sqlQuery.value(3).toString();
qDebug()<<QString("id:%1 name:%2 age:%3 password:%4").arg(id).arg(name).arg(age).arg(password);
}
}
}
// 插入单条数据
void SqliteOperator::singleInsertData(QString tableName, w2dba &singledb)
{
QSqlQuery sqlQuery;
sqlQuery.prepare(QString("INSERT INTO ").append(tableName).append(" VALUES(:id,:name,:age,:password)"));
sqlQuery.bindValue(":id", singledb.id);
sqlQuery.bindValue(":name", singledb.name);
sqlQuery.bindValue(":age", singledb.age);
sqlQuery.bindValue(":password", singledb.password);
if(!sqlQuery.exec())
{
qDebug() << "Error: Fail to insert data. " << sqlQuery.lastError();
}
else
{
// do something
}
}
// 插入多条数据
void SqliteOperator::moreInsertData(QString tableName, QList<w2dba>& moredb)
{
// 进行多个数据的插入时,可以利用绑定进行批处理
QSqlQuery sqlQuery;
sqlQuery.prepare(QString("INSERT INTO ").append(tableName).append(" VALUES(?,?,?,?)"));
QVariantList idList,nameList,ageList,passwordList;
for(int i=0; i< moredb.size(); i++)
{
idList << moredb.at(i).id;
nameList << moredb.at(i).name;
ageList << moredb.at(i).age;
passwordList << moredb.at(i).password;
}
sqlQuery.addBindValue(idList);
sqlQuery.addBindValue(nameList);
sqlQuery.addBindValue(ageList);
sqlQuery.addBindValue(passwordList);
if (!sqlQuery.execBatch()) // 进行批处理,如果出错就输出错误
{
qDebug() << sqlQuery.lastError();
}
}
// 修改数据
void SqliteOperator::modifyData(QString tableName, int id, QString name, int age, QString password)
{
QSqlQuery sqlQuery;
sqlQuery.prepare(QString("UPDATE ").append(tableName).append(" SET name=?,age=?,password=? WHERE id=?"));
sqlQuery.addBindValue(name);
sqlQuery.addBindValue(age);
sqlQuery.addBindValue(password);
sqlQuery.addBindValue(id);
if(!sqlQuery.exec())
{
qDebug() << sqlQuery.lastError();
}
else
{
qDebug() << "updated data success!";
}
}
// 删除数据
void SqliteOperator::deleteData(QString tableName, int id)
{
QSqlQuery sqlQuery;
sqlQuery.exec(QString("DELETE FROM ").append(tableName).append(" WHERE id = %1").arg(id));
if(!sqlQuery.exec())
{
qDebug()<<sqlQuery.lastError();
}
else
{
qDebug()<<"deleted data success!";
}
}
//删除数据表
void SqliteOperator::deleteTable(QString& tableName)
{
QSqlQuery sqlQuery;
sqlQuery.exec(QString("DROP TABLE %1").arg(tableName));
if(sqlQuery.exec())
{
qDebug() << sqlQuery.lastError();
}
else
{
qDebug() << "deleted table success";
}
}
void SqliteOperator::closeDb(void)
{
database.close();
}
头文件:
#ifndef SPLITEOPERATE_H
#define SPLITEOPERATE_H
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
typedef struct
{
int id;
QString name;
int age;
QString password;
}w2dba;
class SqliteOperator
{
public:
SqliteOperator();
// 打开数据库
bool openDb(void);
// 创建数据表
void createTable(QString tableName);
// 判断数据表是否存在
bool isTableExist(QString& tableName);
// 查询全部数据
void queryTable(QString tableName);
// 插入数据
void singleInsertData(QString tableName, w2dba &singleData); // 插入单条数据
void moreInsertData(QString tableName,QList<w2dba> &moreData); // 插入多条数据
// 修改数据
void modifyData(QString tableName,int id, QString name, int age, QString password);
// 删除数据
void deleteData(QString tableName, int id);
//删除数据表
void deleteTable(QString& tableName);
// 关闭数据库
void closeDb(void);
private:
QSqlDatabase database;// 用于建立和数据库的连接
};
#endif // SPLITEOPERATE_H
测试用例:
SqliteOperator sqlTest;
sqlTest.openDb();
// 创建数据表
sqlTest.createTable("userAccound");
// 判断数据表是否存在
QString str1 = QString("userAccound");
qDebug() << "isTabelExist:" <<sqlTest.isTableExist(str1);
// 插入单条数据
w2dba w2dbaTest1 = {1, "zhangSan", 24, "123456"};
w2dba w2dbaTest2 = {2, "lisi", 28, "987654"};
sqlTest.singleInsertData("userAccound", w2dbaTest1);
sqlTest.singleInsertData("userAccound",w2dbaTest2);
// // 插入多条数据
QList<w2dba> list;
w2dba w2dbaTest3 = {3, "liwu", 26, "fasfsa"};
w2dba w2dbaTest4 = {4, "niuer", 27, "fsdfsdafsdfsadfg"};
list.append(w2dbaTest3);
list.append(w2dbaTest4);
sqlTest.moreInsertData("userAccound",list);
// // 查询全部数据
sqlTest.queryTable("userAccound");
// qDebug() << "\R\N";
// // 修改数据
sqlTest.modifyData("userAccound",2, "modify", 10, "hahhahahahahahh");
// // 查询全部数据
sqlTest.queryTable("userAccound");
// //qDebug() << "\R\N";
// 删除数据
sqlTest.deleteData("userAccound",2);
// 查询全部数据
sqlTest.queryTable("userAccound");
//qDebug() << endl;
// 删除数据表
QString str2 = QString("userAccound");
sqlTest.deleteTable(str2);
// //关闭数据库
// sqlTest.closeDb();