db_mana.h
#ifndef DATABASEMANGER_H
#define DATABASEMANGER_H
#include <QString>
class QSqlDatabase;
class QSqlQuery;
class db_mana
{
public:
static bool ExecuteSql(const QString &sql, QSqlQuery &rslt);
static void SetDbNameUserPasswd(const QString &hostname, const QString &dbname, const QString &user, const QString &passwd);
static bool Open(QString &err);
static bool IsOpen();
static void Close();
private:
db_mana(){}
~db_mana(){}
static QString m_sHostName;
static QString m_sDbName;
static QString m_sUser;
static QString m_sPasswd;
static QSqlDatabase *m_DataBase;
static QSqlQuery *m_Query;
};
#endif // DATABASEMANGER_H
db_mana.cpp
#include "db_mana.h"
#include <QString>
#include <QtSql>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
QString db_mana::m_sHostName = "";
QString db_mana::m_sDbName = "";
QString db_mana::m_sUser = "";
QString db_mana::m_sPasswd = "";
QSqlDatabase *db_mana::m_DataBase = NULL;
QSqlQuery *db_mana::m_Query = NULL;
void db_mana::SetDbNameUserPasswd(const QString &hostname,
const QString &dbname, const QString &user, const QString &passwd){
m_sHostName = hostname;
m_sDbName = dbname;
m_sUser = user;
m_sPasswd = passwd;
}
bool db_mana::Open( QString &err )
{
if(IsOpen())
return true;
if(m_sHostName.length() == 0){
return false;
}
if (m_sDbName.length() == 0){
return false;
}
if (m_sUser.length() == 0){
return false;
}
if (m_sPasswd.length() == 0){
return false;
}
m_DataBase = new QSqlDatabase;
//mysql数据库用QMYSQL
//sqlserver数据库用QODBC
//qt 连接sqlserver 出现 “未发现数据源名称并且未指定默认驱动程序”的错误解决方法
//控制面板->管理工具-> (ODBC)32位。用户DSN表格中添加用户数据源。名称为数据库名,服务器选sql2008。
//下一步,设置sa及密码。下一步,默认数据库为要访问的数据库名。
*m_DataBase = QSqlDatabase::addDatabase("QODBC", m_sHostName + m_sDbName);
m_DataBase->setHostName( m_sHostName );
m_DataBase->setDatabaseName( m_sDbName );
m_DataBase->setUserName( m_sUser );
m_DataBase->setPassword( m_sPasswd );
bool open = m_DataBase->open();
if ( !open )
{
err = m_DataBase->lastError().text();
return false;
}
m_Query = new QSqlQuery(*m_DataBase);
return true;
}
bool db_mana::IsOpen()
{
if(!m_DataBase)
return false;
return m_DataBase->isOpen();
}
void db_mana::Close()
{
if(m_DataBase){//qt db相关的代码需要调查,目前的用法存在不稳定(对象)或者内存未释放(指针)
if(m_DataBase->isOpen())
m_DataBase->close();
delete m_DataBase;
m_DataBase = NULL;
}
if(m_Query){
delete m_Query;
m_Query = NULL;
}
}
bool db_mana::ExecuteSql(const QString &sql, QSqlQuery &rslt){
if ( !m_DataBase->isOpen() )
return false;
m_Query->clear();
bool reslut = m_Query->exec( sql );
rslt = *m_Query;
//rslt.first();
return reslut;
}
使用方式,示例代码:
设置数据库连接参数:
void db_view::InitDb()
{
db_mana::SetDbNameUserPasswd(xml_data_.db_infor_.host_name_,
xml_data_.db_infor_.db_name_,
xml_data_.db_infor_.username_,
xml_data_.db_infor_.password_);
}
读取db数据存放到内存
bool db_view::ReadDataFromDb(data_node *p_data_node, const data_map &data_map_t)
{
if (p_data_node == NULL)
{
return false;
}
if (p_data_node->get_mem_data()->size() > 0)
{
return true;
}
QString err;
bool b = db_mana::Open(err);
if (!b)
{
LogX(err);
return b;
}
b = db_mana::IsOpen();
QSqlQuery sqlquery;
//QString sql = QString("select ID,C from moi_server.dbo.base where ID >=0 and ID<=1000 order by ID");
QString sql = QString("select ID,C from base where ID >=0 and ID<=1000 order by ID");
if (!db_mana::ExecuteSql(sql, sqlquery)){
LogX("DBmana::ExecuteSql error");
return false;
}
mem_data *p_data = p_data_node->get_mem_data();
p_data->clear();
while (sqlquery.next())
{
int id_t = sqlquery.value(sqlquery.record().indexOf("ID")).toInt();
double d_t = sqlquery.value(sqlquery.record().indexOf("C")).toDouble();
p_data->push_back(id_t, d_t);
}
if (p_data->size() == 0){
LogX(sql + ". no data.");
}
sqlquery.clear();
db_mana::Close();
return true;
}
有个问题不知道原因:
访问mysql数据库时,判断查询结果是否有数据用QSqlQuery的size()>0可以判断。
访问ms的sql server 2008时,QSqlQuery的size()返回值不大于0,但实际可以取到数据,所以直接用while (sqlquery.next()){…}获得数据。
本来想用poco这种库,但是代码太大,不太适合简单程序。