基于Qt的sqlite编程
发布时间:2020-12-12 19:52:11 所属栏目:百科 来源:网络整理
导读:通过Qt操作sqlite数据库。建库,建表,添加字段、增删改查。 先介绍下常用的sql语句: 判断数据库中是否存在某个表: SELECT 1 FROM sqlite_master where type = 'table' and name = 'book'; 判断表中是否存在某字段:SELECT 1 FROM sqlite_master where typ
通过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",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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |