我已经子类化了QSqlQueryModel
,命名为UeOrdersModel,该模型可以根据以下SQL
语句从数据库提取数据:
SELECT ORDERS.USER_ID,
PEOPLE.NAME,
PLACE_ID,
PLACES.NAME,
PRODUCT_ID,
PRODUCTS.IMAGE,
PRODUCTS.NAME,
PRODUCTS.PRICESELL,
TAXES.RATE,
ORDERS.PRODUCT_QUANTITY
FROM ORDERS
JOIN PEOPLE
ON ORDERS.USER_ID=PEOPLE.ID
JOIN PRODUCTS
ON ORDERS.PRODUCT_ID=PRODUCTS.ID
JOIN PLACES
ON ORDERS.PLACE_ID=PLACES.ID
JOIN TAXCATEGORIES
ON PRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES
ON TAXCATEGORIES.ID=TAXES.ID
WHERE ORDERS.USER_ID="15a2a62b-2a95-4d88-b0ad-d98001d730b4"
AND ORDERS.PLACE_ID="1";
以下是来自我的数据库的phpmyadmin
输出:
╔══════════════════════════════════════╦═══════════╦══════════╦════════╦══════════════════════════════════════╦═══════╦═══════════════════╦═══════════════╦═══════╦══════════════════╗
║ USER_ID ║ NAME ║ PLACE_ID ║ NAME ║ PRODUCT_ID ║ IMAGE ║ NAME ║ PRICESELL ║ RATE ║ PRODUCT_QUANTITY ║
╠══════════════════════════════════════╬═══════════╬══════════╬════════╬══════════════════════════════════════╬═══════╬═══════════════════╬═══════════════╬═══════╬══════════════════╣
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ eda6eaa3-fb7d-4470-8890-9b05aaf97fb6 ║ NULL ║ Test product 1991 ║ 25 ║ 0 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 8d307531-afe5-4746-a1f1-be5a743d453f ║ NULL ║ Test product 1761 ║ 1,8264840183 ║ 0,095 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 158e427c-11df-49b8-8d39-bfeff97c8f91 ║ NULL ║ Test product 1333 ║ 1,6393442623 ║ 0,22 ║ 6 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 2890fc4f-2e59-4c34-b838-b43b0dceb145 ║ NULL ║ Test product 33 ║ 1,6393442623 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 3b12cb57-0ecd-48e6-9c78-8b1715a365d5 ║ NULL ║ Test product 34 ║ 1,6393442623 ║ 0,22 ║ 2 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 3e72f6dc-baf5-4872-a42c-03b5a2748339 ║ NULL ║ Test product 75 ║ 12,2950819672 ║ 0,22 ║ 3 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 5e02ff9c-dcb3-42b1-a04e-f3728d06af3b ║ NULL ║ Test product 20 ║ 1,6393442623 ║ 0,22 ║ 12 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 67d44180-02c5-40b4-8c60-deebc99c1909 ║ NULL ║ Test product 19 ║ 2,868852459 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 6951109f-ae14-4691-b3b9-c64f11059780 ║ NULL ║ Test product 18 ║ 1,6393442623 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 7c007b66-2429-4e28-8bc0-8a15e595c606 ║ NULL ║ Test product 52 ║ 1,6393442623 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 7e43ff6a-80d9-4669-8600-a57f6ff030f7 ║ NULL ║ Test product 12 ║ 1,6393442623 ║ 0,22 ║ 6 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ 9ab29108-b058-48a3-b012-4c6684470217 ║ NULL ║ Test product 13 ║ 2,7049180328 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ a549b305-6a34-4d3a-b53c-63a93a2dea4a ║ NULL ║ Test product 96 ║ 0,0819672131 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ ba9d3fd6-2ed6-4eba-8f4e-4b550074b469 ║ NULL ║ Test product 120 ║ 1,0655737705 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ c23d03eb-dcf9-4757-bdad-161226753492 ║ NULL ║ Test product 5 ║ 1,8852459016 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ c30ceddf-2d89-492b-a5c3-defbe4999dd2 ║ NULL ║ Test product 4 ║ 12,2950819672 ║ 0,22 ║ 1 ║
║ 15a2a62b-2a95-4d88-b0ad-d98001d730b4 ║ Test User ║ 1 ║ Miza 1 ║ e60ed145-5bbd-40e5-84f0-0ad8e63515d3 ║ NULL ║ Test product 12 ║ 1,0655737705 ║ 0,22 ║ 1 ║
╚══════════════════════════════════════╩═══════════╩══════════╩════════╩══════════════════════════════════════╩═══════╩═══════════════════╩═══════════════╩═══════╩══════════════════╝
从我们可以看到的内容来看,SQL
语句本身并不是一个问题,因为它执行时没有出现错误。然而,当程序流进入子类化的 QSqlQueryModel::data()
方法时,我得到的所有角色都是空字符串,令人沮丧。我已经三重检查了数据库连接,连接正常,查询也能够正常执行,但是我现在真的不知道该怎么办了。有谁能给我一些提示吗?这里是UeOrdersModel头文件:
#ifndef UEORDERSMODEL_H
#define UEORDERSMODEL_H
#include <QObject>
#include <QSqlQueryModel>
#include <QQuickImageProvider>
#include <QObject>
#include <QSqlRecord>
#include <QSqlQuery>
#include <QList>
#include <QDebug>
#include <QSqlError>
#include <QSqlField>
#include "../settings/uedefaults.h"
#include "../core/uedatabaseconnectionstatus.h"
#include "../core/uetypes.h"
#include "../database/ueuserrecord.h"
class UeOrdersModel : public QSqlQueryModel,
public QQuickImageProvider
{
Q_OBJECT
friend class UeApplicationStatus;
private:
QString m_ueUserId;
QString m_uePlaceId;
QString m_ueUserName;
QString m_uePlaceName;
QSqlDatabase m_ueDb;
QSqlDatabase ueDatabase() const
{ return this->m_ueDb; }
void ueSetDatabase(const QSqlDatabase& database)
{ this->m_ueDb=database; }
void queryChange();
public:
explicit UeOrdersModel(QObject *parent = 0,
const QString& userId="",
const QString& placeId="",
const QString &userName="",
const QString &placeName="");
~UeOrdersModel();
inline QString ueUserId() const
{ return this->m_ueUserId; }
inline QString uePlaceId() const
{ return this->m_uePlaceId; }
inline QString ueUserName() const
{ return this->m_ueUserName; }
inline QString uePlaceName() const
{ return this->m_uePlaceName; }
inline void ueSetUserId(const QString& userId)
{ this->m_ueUserId=userId; }
inline void ueSetPlaceId(const QString& placeId)
{ this->m_uePlaceId=placeId; }
inline void ueSetUserName(const QString& userName)
{ this->m_ueUserName=userName; }
inline void ueSetPlaceName(const QString& placeName)
{ this->m_uePlaceName=placeName; }
QVariant data(const QModelIndex &index,
int role=Qt::DisplayRole) const Q_DECL_OVERRIDE;
QImage requestImage(const QString &id,
QSize *size,
const QSize &requestedSize);
UeTypeRoles roleNames() const;
int rowCount(const QModelIndex &parent) const;
int columnCount(const QModelIndex &parent) const;
Q_INVOKABLE QVariantMap get(int row);
void ueConnectToDatabase(const QString& userId,
const QString& placeId);
Q_INVOKABLE bool ueAddOrder(const QString& userId,
const QString& placeId,
const QString& productId,
const QString& quantity);
public:
static const int ueRoleUserId=Qt::UserRole+1;
static const int ueRoleUserName=Qt::UserRole+2;
static const int ueRolePlaceId=Qt::UserRole+3;
static const int ueRolePlaceName=Qt::UserRole+4;
static const int ueRoleProductId=Qt::UserRole+5;
static const int ueRoleProductImage=Qt::UserRole+6;
static const int ueRoleProductName=Qt::UserRole+7;
static const int ueRoleProductPriceSell=Qt::UserRole+8;
static const int ueRoleProductVatRate=Qt::UserRole+9;
static const int ueRoleProductQuantity=Qt::UserRole+10;
static const int ueRoleOrderAmountWithoutVAT=Qt::UserRole+11;
static const int ueRoleOrderAmountWithVAT=Qt::UserRole+12;
signals:
void ueSignalDatabaseConnectionChanged(const UeDatabaseConnectionStatus::UeTypeDatabaseConnectionStatus& newStatus);
public slots:
Q_INVOKABLE void ueSlotRefreshOrdersData(const QString& userId,
const QString& placeId);
};
#endif // UEORDERSMODEL_H
以及它的实现:
#include "ueordersmodel.h"
UeOrdersModel::UeOrdersModel(QObject *parent,
const QString& userId,
const QString& placeId,
const QString& userName,
const QString& placeName)
: QSqlQueryModel(parent),
QQuickImageProvider(QQmlImageProviderBase::Image,
QQmlImageProviderBase::ForceAsynchronousImageLoading)
{
this->ueSetUserId(userId);
this->ueSetPlaceId(placeId);
this->ueSetUserName(userName);
this->ueSetPlaceName(placeName);
} // constructor
UeOrdersModel::~UeOrdersModel()
{
} // destructor
QVariant UeOrdersModel::data(const QModelIndex &index,
int role) const
{
qDebug() << "(UeOrdersModel::data) this->query().lastQuery(): " << this->query().lastQuery()
<< "\n"
<< "(UeOrdersModel::data) this->query().executedQuery(): " << this->query().executedQuery()
<< "\n"
<< "(UeOrdersModel::data) this->query().isValid(): " << this->query().isValid();
bool result=false;
if(!this->query().isValid())
{
if(this->query().isActive())
if(this->query().isSelect())
result=this->query().first();
}
if(index.isValid()&&index.row()>=0&&index.column()>=0&&this->record().isEmpty()==false&&this->query().isValid())
{
switch(role)
{
case ueRoleUserId:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_USERID).toString();
} break;
case ueRoleUserName:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_USERNAME).toString();
} break;
case ueRolePlaceId:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PLACEID).toString();
} break;
case ueRolePlaceName:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PLACENAME).toString();
} break;
case ueRoleProductId:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCTID).toString();
} break;
case ueRoleProductImage:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCTIMAGE).toString();
} break;
case ueRoleProductName:
{
return this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCTNAME).toString();
} break;
case ueRoleProductPriceSell:
{
return QString::number(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_PRICESELL).toDouble());
} break;
case ueRoleProductVatRate:
{
return QString::number(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_VATRATE).toDouble());
} break;
case ueRoleProductQuantity:
{
return QString::number(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_QUANTITY).toUInt());
} break;
case ueRoleOrderAmountWithoutVAT:
{
return QString::number(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_QUANTITY).toUInt()*
this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_PRICESELL).toDouble());
} break;
case ueRoleOrderAmountWithVAT:
{
return QString::number(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_QUANTITY).toUInt()*
(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_PRICESELL).toDouble()+
(this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_PRICESELL).toDouble()*
this->record(index.row()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCT_VATRATE).toDouble())));
} break;
default:
{
return QVariant();
} break; // default
} // switch
} // if
return QVariant();
} // data
QImage UeOrdersModel::requestImage(const QString &id,
QSize *size,
const QSize &requestedSize)
{
Q_UNUSED(size)
Q_UNUSED(requestedSize);
QImage image=QImage::fromData(this->record(id.toInt()).value(UePosDatabase::UeQueryIndexes::QUERY_GET_ORDERS_FILTERED_BY_USERNAME_PLACENAME::INDEX_PRODUCTIMAGE).toByteArray(),
"PNG");
if(image.isNull())
{
image=QImage(":/ueIcons/icons/ueGenericProduct.png");
}
return image.scaled(UeDefaults::UeGraphics::UeProductImageSize::IMAGE_WIDTH,
UeDefaults::UeGraphics::UeProductImageSize::IMAGE_HEIGHT,
Qt::IgnoreAspectRatio,
Qt::SmoothTransformation);;
} // requestImage
UeTypeRoles UeOrdersModel::roleNames() const
{
UeTypeRoles roles;
const int iRoleUserId=UeOrdersModel::ueRoleUserId;
const int iRoleUserName=UeOrdersModel::ueRoleUserName;
const int iRolePlaceId=UeOrdersModel::ueRolePlaceId;
const int iRolePlaceName=UeOrdersModel::ueRolePlaceName;
const int iRoleProductId=UeOrdersModel::ueRoleProductId;
const int iRoleProductImage=UeOrdersModel::ueRoleProductImage;
const int iRoleProductName=UeOrdersModel::ueRoleProductName;
const int iRoleProductPriceSell=UeOrdersModel::ueRoleProductPriceSell;
const int iRoleProductVatRate=UeOrdersModel::ueRoleProductVatRate;
const int iRoleProductQuantity=UeOrdersModel::ueRoleProductQuantity;
const int iRoleOrderAmountWithoutVAT=UeOrdersModel::ueRoleOrderAmountWithoutVAT;
const int iRoleOrderAmountWithVAT=UeOrdersModel::ueRoleOrderAmountWithVAT;
roles.insert(iRoleUserId,
"ueRoleUserId");
roles.insert(iRoleUserName,
"ueRoleUserName");
roles.insert(iRolePlaceId,
"ueRolePlaceId");
roles.insert(iRolePlaceName,
"ueRolePlaceName");
roles.insert(iRoleProductId,
"ueRoleProductId");
roles.insert(iRoleProductImage,
"ueRoleProductImage");
roles.insert(iRoleProductName,
"ueRoleProductName");
roles.insert(iRoleProductPriceSell,
"ueRoleProductPriceSell");
roles.insert(iRoleProductVatRate,
"ueRoleProductVatRate");
roles.insert(iRoleProductQuantity,
"ueRoleProductQuantity");
roles.insert(iRoleOrderAmountWithoutVAT,
"ueRoleOrderAmountWithoutVAT");
roles.insert(iRoleOrderAmountWithVAT,
"ueRoleOrderAmountWithVAT");
return roles;
} // roleNames
int UeOrdersModel::columnCount(const QModelIndex &parent) const
{
Q_UNUSED(parent);
// qDebug() << "UeOrdersModel::columnCount: " << this->record().count();
return this->record().count();
}
int UeOrdersModel::rowCount(const QModelIndex &parent) const
{
Q_UNUSED(parent);
// qDebug() << "UeOrdersModel::rowCount: " << this->query().size();
return this->query().size();
} // rowCount
QVariantMap UeOrdersModel::get(int row)
{
QHash<int,QByteArray> names=this->roleNames();
QHashIterator<int, QByteArray> i(names);
QVariantMap res;
while (i.hasNext())
{
i.next();
QModelIndex idx=this->index(row,
0);
QVariant data=idx.data(i.key());
res[i.value()]=data;
} // while
return res;
} // get
void UeOrdersModel::ueConnectToDatabase(const QString& userId,
const QString& placeId)
{
if(!QSqlDatabase::connectionNames().contains(UePosDatabase::UeDatabaseConnectionNames::DATABASE_CONNECTION_NAME_ORDERS,
Qt::CaseInsensitive))
{
this->ueSetDatabase(QSqlDatabase::addDatabase(UePosDatabase::DATABASE_DRIVER,
UePosDatabase::UeDatabaseConnectionNames::DATABASE_CONNECTION_NAME_ORDERS));
} // if
this->ueDatabase().setHostName(UePosDatabase::UeDatabaseConnectionParameters::DATABASE_HOSTNAME);
this->ueDatabase().setDatabaseName(UePosDatabase::UeDatabaseConnectionParameters::DATABASE_NAME);
this->ueDatabase().setUserName(UePosDatabase::UeDatabaseConnectionParameters::DATABASE_USERNAME);
this->ueDatabase().setPassword(UePosDatabase::UeDatabaseConnectionParameters::DATABASE_PASSWORD);
if(this->ueDatabase().open())
{
this->setQuery(UePosDatabase::UeSqlQueries::UeTableOrders::SQL_QUERY_GET_ORDERS_FILTERED_BY_USERID_PLACEID.arg(userId)
.arg(placeId),
this->ueDatabase());
if(this->lastError().isValid())
{
emit this->ueSignalDatabaseConnectionChanged(UeDatabaseConnectionStatus::NOT_CONNECTED);
qDebug() << this->lastError().text();
}
else
{
emit this->ueSignalDatabaseConnectionChanged(UeDatabaseConnectionStatus::CONNECTED);
} // if
}
else
{
emit this->ueSignalDatabaseConnectionChanged(UeDatabaseConnectionStatus::NOT_CONNECTED);
} // if
} // ueConnectToDatabase
bool UeOrdersModel::ueAddOrder(const QString& userId,
const QString& placeId,
const QString& productId,
const QString& quantity)
{
QSqlQuery queryInsertOrder(this->ueDatabase());
bool result=false;
queryInsertOrder.prepare(UePosDatabase::UeSqlQueries::UeTableOrders::SQL_QUERY_INSERT_NEW_RECORD);
queryInsertOrder.addBindValue(userId);
queryInsertOrder.addBindValue(placeId);
queryInsertOrder.addBindValue(productId);
queryInsertOrder.addBindValue(quantity.toUInt());
if(queryInsertOrder.exec())
{
result=true;
}
else
{
if(queryInsertOrder.lastError().nativeErrorCode().compare(UePosDatabase::UeMySQLErrors::SQL_ERROR_RECORD_ALREADY_EXISTS)==0)
{
QSqlQuery queryIncreaseProductQuantity(this->ueDatabase());
queryIncreaseProductQuantity.prepare(UePosDatabase::UeSqlQueries::UeTableOrders::SQL_QUERY_INCREASE_PRODUCT_QUANTITY.arg(userId)
.arg(placeId)
.arg(productId));
if(queryIncreaseProductQuantity.exec())
{
result=true;
}
else
{
result=false;
} // if
} // if
} // if
if(result==true)
{
this->setQuery(UePosDatabase::UeSqlQueries::UeTableOrders::SQL_QUERY_GET_ORDERS_FILTERED_BY_USERID_PLACEID.arg(userId)
.arg(placeId),
this->ueDatabase());
if(this->lastError().isValid())
{
qDebug() << this->lastError().text();
} // if
} // if
return result;
} // ueAddOrder
void UeOrdersModel::ueSlotRefreshOrdersData(const QString& userId,
const QString& placeId)
{
this->ueSetUserId(userId);
this->ueSetPlaceId(placeId);
if(this->ueDatabase().isOpen())
{
this->setQuery(UePosDatabase::UeSqlQueries::UeTableOrders::SQL_QUERY_GET_ORDERS_FILTERED_BY_USERID_PLACEID.arg(userId)
.arg(placeId),
this->ueDatabase());
}
else
{
this->ueConnectToDatabase(userId,
placeId);
} // if
bool result=this->query().first();
int i=0;
} // ueSlotRefreshOrdersData
void UeOrdersModel::queryChange()
{
qDebug() << "UeOrdersModel::queryChange(): " << this->query().lastQuery();
} // queryChange
UeOrdersModel 在 main.cpp
中实例化,连接到数据库(像我说的那样,检查了 3 次):
UeOrdersModel* ueOrdersModel=new UeOrdersModel(qApp);
我在这个应用程序中有几个非常相似的模型,全部都能完美地工作,但是其中一个却不能。
switch
语句的default:
部分下的data()
方法中添加了QSqlQueryModel::data(item, role);
,现在它可以工作了!你能把你的提示升级为答案吗? - KernelPanic