qt 数据库增加字段且版本兼容_qt 数据库插入字段_东方忘忧

网络投稿 02-07 6577

数据库使用过程中经常会出现需要增加新的字段，这个时候又要兼容旧的版本。我们增加字段往往用到“ALTER TABLE tablename ADD COLUMN 字段名类型”的sql语句，而在qt中增加字段除了这个语句还需要几个类的配合，QSqlDatabase、QSqlError、QSqlQuery、QSqlRecord、QSqlField。 QSqlDatabase用来连接数据库，获取表数和表名； QSqlError用来sql的执行错误； QSqlQuery用来执行语句和获取执行后的结果； QSqlRecord获取表的字段名； QSqlField获取表的字段类型。

实例如下：

#ifndef WIDGET_H #define WIDGET_H #include <QtWidgets> #include <QSqlDatabase> #include <QSqlError> #include <QSqlQuery> #include <QSqlRecord> #include <QSqlField> QT_BEGIN_NAMESPACE namespace Ui { class Widget; } QT_END_NAMESPACE class Widget : public QWidget { Q_OBJECT public: Widget(QWidget *parent = nullptr); ~Widget(); bool initDb(); private: Ui::Widget *ui; QSqlDatabase db; }; #endif // WIDGET_H #include "widget.h" #include "ui_widget.h" #if 1 const QString CRESQL1 = "create table test1(id integer, age integer, name varchar)"; const QString CRESQL2 = "create table test2(id integer, size integer, data varchar)"; #else const QString CRESQL1 = "create table test1(id integer, age integer, name varchar, title integer)"; const QString CRESQL2 = "create table test2(id integer, size integer, data varchar, type integer, value varchar)"; #endif Widget::Widget(QWidget *parent) : QWidget(parent) , ui(new Ui::Widget) { ui->setupUi(this); initDb(); } Widget::~Widget() { delete ui; } bool Widget::initDb() { db = QSqlDatabase::addDatabase("QSQLITE","test_sqlite_connect"); QString projectDir = QCoreApplication::applicationDirPath(); QStringList tmpRootDirlist = QString(projectDir).split("/"); if(tmpRootDirlist.size() >= 2){ tmpRootDirlist.removeLast(); tmpRootDirlist.removeLast(); } QString userDataPath = QApplication::applicationDirPath()+"/library"; QString dbpath = userDataPath + "/test_sqlite.db"; QDir userDataDir(userDataPath); if(!userDataDir.exists()){ userDataDir.mkpath(userDataPath); } qDebug() << __FUNCTION__ << __LINE__ << " ============>>>> " << dbpath; db.setDatabaseName(dbpath); db.setPassword("123456"); db.setHostName("127.0.0.1"); db.setUserName("CSDN"); if (!db.open()){ perror(db.lastError().text().toUtf8().data()); return false; } QStringList tables = db.tables(); qDebug() << __FUNCTION__ << __LINE__ << tables; QSqlQuery q(db); QStringList sqlist( QStringList() << CRESQL1 << CRESQL2); QStringList tablelist( QStringList() << "test1" << "test2"); for (int i = 0; i < tablelist.size(); ++i) { QString tableName = tablelist.at(i); if (tables.contains(tableName)) { QString selectSql = QString("select * from %1;").arg(tableName); if (!q.exec(selectSql)) { perror(q.lastError().text().toUtf8().data()); continue; } QStringList oldtitles; for (int j = 0; j < q.record().count(); ++j) { auto type = q.record().field(j).type(); auto title = q.record().fieldName(j); oldtitles.append(title); } QString newtitle = sqlist.at(i); newtitle = newtitle.mid(newtitle.indexOf("(")+1,newtitle.indexOf(")")-newtitle.indexOf("(")-1); newtitle.remove("\n"); QStringList newtitles = newtitle.split(","); if (newtitles.size() != oldtitles.size()) { for (int k = oldtitles.size(); k < newtitles.size(); ++k) { QString title = newtitles.at(k); if (title.size()) { if (title.mid(0,1) == " ") { title = title.mid(1,title.size()); } title = title.split(" ").first(); if (!oldtitles.contains(title)) { QString sql = QString("ALTER TABLE %1 ADD COLUMN %2").arg(tableName).arg(newtitles.at(k)); qDebug()<<__FUNCTION__<<__LINE__<<"数据库添加新字段"<<sql; if(!q.exec(sql)) { perror(q.lastError().text().toUtf8().data()); continue; } } } } } } else { if(!q.exec(sqlist.at(i))) { perror(q.lastError().text().toUtf8().data()); continue; } } } return true; }

旧表：将 const QString CRESQL1 = “create table test1(id integer, age integer, name varchar)”; const QString CRESQL2 = “create table test2(id integer, size integer, data varchar)”; 改为 const QString CRESQL1 = “create table test1(id integer, age integer, name varchar, title integer)”; const QString CRESQL2 = “create table test2(id integer, size integer, data varchar, type integer, value varchar)”;