因为sqlite是c语言写的,它的跨系统特性比较好,一般直接把sqlite.h和sqlite.c文件放到项目中就可以了
#ifndef DBUTIL_H
#define DBUTIL_H
#include <QString>
#include "sqlite3/sqlite3.h"
/**
* database util
*/
class DBUtil
{
public:
DBUtil();
~DBUtil();
public:
void close();
bool insert_data(
const QString& sql_str);
void update_data(
const QString& sql_str);
QMap<QString, QString> select_line_data(
const QString& sql_str);
// select many row data
QVector<QMap<QString, QString> >
select_data(
const QString& sql_str);
bool select_exist(
const QString& sql_str);
void delete_data(
const QString& sql_str);
private:
bool connect();
private:
sqlite3* d_db;
};
#endif // DBUTIL_H
#include "DBUtil.h"
#include "util/CommonFileUtil.h"
#include <QApplication>
#include <QDebug>
#include <sstream>
DBUtil::DBUtil()
{
}
DBUtil::~DBUtil()
{}
void
DBUtil::close()
{
}
bool
DBUtil::connect()
{
QByteArray db_name = CommonFileUtil::get_database_name().toUtf8();
const char* file_name = db_name.data();
if( SQLITE_OK != sqlite3_open(file_name, &d_db) )
{
qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << file_name << sqlite3_errcode(d_db) << sqlite3_errmsg(d_db) << "\n";
return false;
}
return true;
}
bool
DBUtil::insert_data(
const QString& sql_str)
{
const bool& c_ok = connect();
if( !c_ok )
{
return false;
}
char* error_msg;
int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);
sqlite3_free(error_msg);
sqlite3_close(d_db);
return (result == SQLITE_OK);
}
void
DBUtil::update_data(
const QString& sql_str)
{
const bool& c_ok = connect();
if( !c_ok )
{
return;
}
char* error_msg;
int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);
sqlite3_free(error_msg);
sqlite3_close(d_db);
Q_UNUSED(result);
}
QMap<QString, QString>
DBUtil::select_line_data(
const QString& sql_str)
{
QMap<QString, QString> map_result_data;
const bool& c_ok = connect();
if( !c_ok )
{
return map_result_data;
}
sqlite3_stmt* stmt = nullptr;
int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
if( SQLITE_OK != result )
{
qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
return map_result_data;
}
if( SQLITE_ROW == sqlite3_step(stmt) )
{
const int& column_count = sqlite3_column_count(stmt);
for(int i = 0; i < column_count; i++)
{
//qDebug() << __FILE__ << __LINE__ << " i = " << i;
const char* key = sqlite3_column_name(stmt, i);
const unsigned char* text = sqlite3_column_text(stmt, i);
//qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
std::ostringstream oss;
if( text )
{
oss << text;
}
else
{
oss << "";
}
map_result_data[QString::fromLocal8Bit(key)] = QString::fromStdString(oss.str());
}
}
//qDebug() << __FILE__ << __LINE__ << " map_result_data = " << map_result_data;
sqlite3_finalize(stmt);
sqlite3_close(d_db);
return map_result_data;
}
QVector<QMap<QString, QString> >
DBUtil::select_data(
const QString& sql_str)
{
QVector<QMap<QString, QString> > result_data;
const bool& c_ok = connect();
if( !c_ok )
{
return result_data;
}
QMap<QString, QString> column_data;
sqlite3_stmt* stmt = nullptr;
int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
if( SQLITE_OK != result )
{
qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
return result_data;
}
while( SQLITE_ROW == sqlite3_step(stmt) )
{
const int& column_count = sqlite3_column_count(stmt);
for(int i = 0; i < column_count; i++)
{
//qDebug() << __FILE__ << __LINE__ << " i = " << i;
const char* key = sqlite3_column_name(stmt, i);
const unsigned char* text = sqlite3_column_text(stmt, i);
//qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
std::ostringstream oss;
if( text )
{
oss << text;
}
else
{
oss << "";
}
column_data[QString::fromLocal8Bit(key)] = QString::fromStdString(oss.str());
//qDebug() << __FILE__ << __LINE__ << " key = " << key << " text = " << text;
}
result_data.push_back(column_data);
}
//qDebug() << __FILE__ << __LINE__ << " result_data = " << result_data;
sqlite3_finalize(stmt);
sqlite3_close(d_db);
return result_data;
}
bool
DBUtil::select_exist(
const QString& sql_str)
{
const bool& c_ok = connect();
if( !c_ok )
{
return false;
}
sqlite3_stmt* stmt = nullptr;
int result = sqlite3_prepare(d_db, sql_str.toUtf8().data(), sql_str.length(), &stmt, nullptr);
if( SQLITE_OK != result )
{
qDebug() << __FILE__ << __LINE__ << "warning: database not connect. " << sqlite3_errmsg(d_db) << "\n";
return false;
}
if( SQLITE_ROW == sqlite3_step(stmt) )
{
sqlite3_finalize(stmt);
sqlite3_close(d_db);
return true;
}
sqlite3_finalize(stmt);
sqlite3_close(d_db);
return false;
}
void
DBUtil::delete_data(
const QString& sql_str)
{
const bool& c_ok = connect();
if( !c_ok )
{
return;
}
char* error_msg;
int result = sqlite3_exec(d_db, sql_str.toUtf8().data(), 0, 0, &error_msg);
sqlite3_free(error_msg);
sqlite3_close(d_db);
Q_UNUSED(result);
}
前面有一篇是使用了Qt中的数据库类,本篇直接使用sqlite3的api O(∩_∩)O哈哈~