5. 程序示例

• 连接数据库

• 获取结果集

• 批量DML

5.1. 连接数据库

#include <QCoreApplication>
#include <QtDebug>
#include <QSqlDatabase>

const QString host = "127.0.0.1";
const int port = 54321;
const QString dbName = "TEST";
const QString user = "SYSTEM";
const QString password = "123456";

int main()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QKINGBASE");
    db.setHostName(host);
    db.setDatabaseName(dbName);
    db.setUserName(user);
    db.setPassword(password);
    db.setPort(port);
    db.setConnectOptions("connect_timeout=2");

    db.open();
    if (!db.isOpen()) {
        qDebug() << db.lastError();
    }
    db.close();
}

5.2. 获取结果集

#include <QCoreApplication>
#include <QtDebug>
#include <QSqlDatabase>
#include <QSqlQuery>

const QString host = "127.0.0.1";
const int port = 54321;
const QString dbName = "TEST";
const QString user = "SYSTEM";
const QString password = "123456";

int main()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QKINGBASE");
    db.setHostName(host);
    db.setDatabaseName(dbName);
    db.setUserName(user);
    db.setPassword(password);
    db.setPort(port);
    db.setConnectOptions("connect_timeout=2");

    db.open();
    if (!db.isOpen()) {
        qDebug() << db.lastError();
    }

    QSqlQuery q(db);
    q.exec("create table TEST(id integer, name TEXT)");
    q.exec("INSERT INTO TEST(id, name) VALUES(1, 'John')")

    q.exec("select id from TEST");
    while (q.next()) {
      qDebug() << q.value(0);
    }
    q.close();

    db.close();
}

5.3. 批量DML

bool testBatchDML(QSqlDatabase& db)
{
    QSqlQuery sql_query(db);

    sql_query.exec("drop table if exists Persons");
    sql_query.exec(QObject::tr("create table Persons(person_id int primary key , sex varchar(255) , age int , hobbies varchar(255)  , birthday  date)"));
    sql_query.prepare(QObject::tr("insert into Persons values(?,?,?,?, ?)"));

    QVariantList var_id_list , var_sex_list , var_age_list , var_hobbies_list , var_birthday_list;

    var_id_list << 1 << 2 << 0 << 3 << 4 << 5;
    var_sex_list << QObject::tr("man")<< QObject::tr("woman")<< QObject::tr("woman")
                 << QObject::tr("man")<< QObject::tr("man")<< QObject::tr("woman");
    var_age_list << 21 << 23 << 20 << 53 << 14 << 45;
    var_hobbies_list << QObject::tr("swim")<< QObject::tr("read")<< QObject::tr("swim")
                     << QObject::tr("read")<< QObject::tr("play")<< QObject::tr("football");
    var_birthday_list << QObject::tr("1995-04-04")<< QObject::tr("1925-12-14")<< QObject::tr("2005-02-04")
                      << QObject::tr("2014-02-11")<< QObject::tr("1995-04-04")<<QObject::tr("2007-04-04");

    sql_query.addBindValue(var_id_list);
    sql_query.addBindValue(var_sex_list);
    sql_query.addBindValue(var_age_list);
    sql_query.addBindValue(var_hobbies_list);
    sql_query.addBindValue(var_birthday_list);

    if(!sql_query.execBatch())
        qDebug() << sql_query.lastError();

    return true;
}