基于Qt的sqlite编程

通过Qt操作sqlite数据库。建库，建表，添加字段、增删改查。

先介绍下常用的sql语句：

判断数据库中是否存在某个表： SELECT 1 FROM sqlite_master where type = 'table' and  name = 'book';

判断表中是否存在某字段：SELECT 1 FROM sqlite_master where type = 'table' and name= 'book' and sql like '%idtest%';

建表语句：CREATE TABLE book(id VARCHAR(20) PRIMARY KEY  , name VARCHAR(100), info VARCHAR(100));

添加字段：ALTER TABLE book ADD descinfo VARCHAR(100);

创建索引：CREATE INDEX book_index1 on book(id);

添加：INSERT INTO book(id, name, info) VALUES('1111', 'Qt编程', '这是一本好书');

删除：DELETE FROM book  WHERE id = '1111';

更新：UPDATE book SET name = 'test' WHERE id = '1111';

精确搜索：SELECT * FROM book WHERE id = '111';

模糊搜索：SELECT * FROM book WHERE name like '%:strBookName%' ESCAPE '!';

Qt项目的pro文件中需要添加sql支持。若采用VS开发Qt程序，需要在项目配置中添加QtSql支持。

QT       += core gui sql

代码实现主要函数(建库，建表，添加字段、增删改查)：

//创建数据库文件
bool DbManager::createDataFile(const QString &strFileName)
{
    if(!QFile::exists(strFileName))//文件不存在，则创建
    {
        QDir fileDir = QFileInfo(strFileName).absoluteDir();
        QString strFileDir = QFileInfo(strFileName).absolutePath();
        if(!fileDir.exists()) //路径不存在，创建路径
        {
            fileDir.mkpath(strFileDir);
        }
        QFile dbFile(strFileName);
        if(!dbFile.open(QIODevice::WriteOnly))//未成功打开
        {
            dbFile.close();
            return false;
        }
        dbFile.close();
    }
    return true;
}

//打开数据库文件(Sqlite,*.db文件)
bool DbManager::openDataBase(const QString& strFileName)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(strFileName);
    if(m_db.open())
    {
        return true;
    }
    return false;
}

//关闭数据库
void DbManager::closeDataBase()
{
    m_db.close();
}

//判断数据库中是否存在该表
bool DbManager::isExistTable(const QString& strTableName)
{
    QSqlQuery query;
    QString strSql = QString("SELECT 1 FROM sqlite_master where type = 'table' and  name = '%1'").arg(strTableName);
    query.exec(strSql);
    if(query.next())
    {
        int nResult = query.value(0).toInt();//有表时返回1，无表时返回null
        if(nResult)
        {
            return true;
        }
    }
    return false;
}

//判断表中是否含有某字段(列)
bool DbManager::isExistField(const QString& strTableName, const QString& strFieldName)
{
    QSqlQuery query(m_db);
    QString strSql = QString("SELECT 1 FROM sqlite_master where type = 'table'"
                             " and name= '%1' and sql like '%%2%'").arg(strTableName).arg(strFieldName);
    query.exec(strSql);
    if(query.next())
    {
        int nResult = query.value(0).toInt();//有此字段时返回1，无字段时返回null
        if(nResult)
        {
            return true;
        }
    }
    return false;
}

//创建表
void DbBook::createTable()
{
    QString strSql = "create table book(id VARCHAR(20) PRIMARY KEY  , name VARCHAR(100), info TEXT);";
    QSqlQuery query(DbManager::m_db);
    query.exec(strSql);//建表

    QString strIndexSql = "CREATE INDEX book_index1 on book(id);";
    bool bResult = query.exec(strIndexSql); //创建索引
}

//添加一图书
void DbBook::addBook(const QString& strBookId,const QString& strBookName,const QString& strBookInfo)
{
    QString strSql;
    QSqlQuery query(DbManager::m_db);
    strSql.append("INSERT INTO book(id, name, info) VALUES (:id, :name, :info)"); //需要绑定的字段
    query.prepare(strSql);
    query.bindValue(":id",strBookId);
    query.bindValue(":name",strBookName);
    query.bindValue(":info",strBookInfo);

    bool bResult = query.exec();
    qDebug() << "addBook,result=" << bResult;
}

//根据id删除图书
void DbBook::removeBook(const QString& strBookId)
{
    QString strSql = QString("DELETE FROM book WHERE id = '%1'").arg(strBookId);
    QSqlQuery query(DbManager::m_db);
    bool bResult = query.exec(strSql);

    qDebug() << "removeBook,result=" << bResult;
}

//根据id更新图书名
void DbBook::updateBookName(const QString& strBookId, const QString& strBookName)
{
    QString strSql = "UPDATE book SET name = :name WHERE id = :id";
    QSqlQuery query(DbManager::m_db);
    query.prepare(strSql);
    query.bindValue(":id",strBookId);
    query.bindValue(":name",strBookName);
    bool bReuslt = query.exec();
}

//根据图书名找图书(模糊搜索)
QStringList DbBook::searchByBookName(const QString& strBookName)
{
    QStringList bookIdList;
    QSqlQuery query(DbManager::m_db);
    QString strName = strBookName;
    strName.replace("%","!%");
    const QString strSqlTemplate = "SELECT * FROM book WHERE name like '%:strBookName%' ESCAPE '!'";//sql语句模板
    QString strSql = strSqlTemplate;
    strSql.replace(":strBookName",strName);
    query.exec(strSql);

    while(query.next())
    {
        QString strBookId = query.value(0).toString();//图书id
        bookIdList.append(strBookId);
    }
    return bookIdList;
}

演示程序下载地址

http://download.csdn.net/detail/lingyun0/8242033

By Lankin

2014/12/9