Dao layer to access db data: implementation suggestions

[rspock]

Hi, there I'm tryng to develop a very basically dao layer to manage db data. I have some dao objects to manage operations and some model objects. I show you some snippets:

Models
@class Entity : public QObject
{
Q_OBJECT

public:

Q_PROPERTY(int id READ id WRITE setId)

Entity(int id=-1) {
    this->_id=id;
}

void setId(int id){_id = id;}
int id() const { return _id; }

protected:
int _id;
};@

@class Category : public Entity
{
Q_OBJECT
public:
Category(int id=-1);
Q_PROPERTY(QString name READ name WRITE setName)
Q_PROPERTY(bool discount READ discount WRITE setDiscount)
Q_PROPERTY(bool active READ active WRITE setActive)

void setName(QString name){_name = name;}
QString name() const { return _name; }

void setDiscount(bool discount){_discount = discount;}
int discount() const { return _discount; }

void setActive(bool active){_active = active;}
int active() const { return _active; }

private:
QString _name;
bool _discount;
bool _active;
};@

Dao:

@class Dao : public QObject
{
Q_OBJECT
public:
Dao(QObject* parent=0 );
DbError insert(Entity& entity);
DbError update(Entity& entity);
DbError remove(Entity& entity);
DbError find(int id, Entity& entity);
DbError findByParams(QList<Entity*>& result, const QVariantMap& params = QVariantMap());

int rowCount();
int columnCount();

protected:
virtual QString tableName()=0;
virtual DbError isValid(Entity &entity, bool extended=false)=0;
virtual DbError classToRecord(Entity & entity, QSqlRecord &sqlRecord);
virtual DbError recordToClass(const QSqlRecord sqlRecord, Entity & entity, bool extended);

void init();
DbError checkSqlError(QSqlQuery query);

virtual Entity* getNewInstance()=0;

QObject * parent;
DbService * dbService;

};@

@
class CategoryDao : public Dao
{
public:
CategoryDao(QObject * parent=0);
protected:
virtual DbError isValid(Entity &entity, bool extended=false);

QString tableName(){
    return "category";
}

Entity* getNewInstance(){
    return new Category();
}

};@

The problems came from the findByParams which need a list of QList<Entity*> and this is the realization:

@DbError Dao::findByParams(QList<Entity *> &result, const QVariantMap& params){

QString sql = "SELECT * FROM %1 WHERE 1=1 %2";
QString where = "";
QVariantMap queryParams;
for(QVariantMap::const_iterator iter = params.begin(); iter != params.end(); ++iter) {
    where.append(" AND "+iter.key()+"=:"+iter.key());
    queryParams.insert(":"+iter.key(),iter.value());
}
sql = sql.arg(this->tableName()).arg(where);
QSqlQuery query = this->dbService->runQuery(sql,queryParams);
while(query.next()){
    Entity * newEntity = this->getNewInstance();
    DbError error = this->recordToClass(query.record(),*newEntity,false);
    if(error== DbError()){
        result.append(newEntity);
    }else{
        return error;
    }
}
return DbError();

}@

in the main I'd like to do somethings similar:

@int main(int argc, char *argv[])
{
QApplication app(argc, argv);

CategoryDao * categoryDao = new CategoryDao();
QList<Category*> categoryList;

QVariantMap params = QVariantMap();
params.insert("name","prova");
categoryDao->findByParams(categoryList,params);

QList<Category*>::iterator i;
for (i = categoryList.begin(); i != categoryList.end(); ++i){
    qDebug()<<(*i)->name()<<" id "<<(*i)->id();
}
qDeleteAll(categoryList);
return app.exec();

}@

but obviously i get compilation error:
@error: no matching function for call to 'CategoryDao::findByParams(QList<Category*>&,QVariantMap&)'
categoryDao->findByParams(categoryList,params);@

Do you have any suggestions to modify the findByParams methods?

Moreover do you suggest to modify, and eventually how, the QList<Entity*>& result in:
@DbError findByParams(QList<Entity*>& result, const QVariantMap& params = QVariantMap());@
to use the smart pointer in order to reduce memory leeks?

[SGaist]

Hi,

Are you trying to do something like "qxorm":http://www.qxorm.com/qxorm_en/home.html ?

Anyway, first thing I can see is that your base class is already Entity oriented. It should rather be independent of it and you should have a DAO for each of your models.

As findByParams, do you mean you want a list of Entity from your Category or you would to have the same function but for Category ?

[rspock]

[quote author="SGaist" date="1421530304"]Hi,

Are you trying to do something like "qxorm":http://www.qxorm.com/qxorm_en/home.html ?
[/quote]
I knonw that library but i don't want boost dependency and I have licence problem.

[quote author="SGaist" date="1421530304"]
Anyway, first thing I can see is that your base class is already Entity oriented. It should rather be independent of it and you should have a DAO for each of your models.
[/quote]

I missed some stuff the base Dao class use the metaobject to inspect all the attibutes, I show some methods:

@
DbError Dao::insert(Entity& entity){
QString sql = "INSERT INTO %1 (%2) VALUES (%3)";
const QMetaObject *metaobject = entity.metaObject();
QString fieldsName ="";
QString parametersName = "";
QVariantMap parameter;

int count = metaobject->propertyCount();
for (int i=metaobject->propertyOffset(); i<count; ++i) {
    QMetaProperty metaproperty = metaobject->property(i);
    QString name = QString::fromUtf8(metaproperty.name());
    fieldsName.append(name);
    parametersName.append(":"+name);
    if(i<count-1){
        fieldsName.append(", ");
        parametersName.append(", ");
    }
    parameter.insert(":"+name,entity.property(metaproperty.name()));
}

sql = sql.arg(this->tableName(), fieldsName,parametersName);
QSqlQuery query = this->dbService->runQuery(sql,parameter);
return this->checkSqlError(query);

}@

@DbError Dao::classToRecord(Entity & entity, QSqlRecord &sqlRecord){
const QMetaObject *metaobject = entity.metaObject();
int count = metaobject->propertyCount();
for (int i=0; i<count; ++i) {
QMetaProperty metaproperty = metaobject->property(i);
const char *name = metaproperty.name();
QVariant value = entity.property(name);
sqlRecord.setValue(metaproperty.name(), value);
}

return this->isValid(entity);

}
@

@
DbError Dao::recordToClass(const QSqlRecord sqlRecord, Entity & entity, bool extended){
DbError recordError = this->isValid(entity, extended);
if( recordError != DbError()){
return recordError;
}
for(int i=0; i<sqlRecord.count(); i++){
entity.setProperty(sqlRecord.fieldName(i).toUtf8().constData(), sqlRecord.value(i));
}

return DbError();

}@

So in this way the base Dao class can handle all the Entity derived class.

[quote author="SGaist" date="1421530304"]
As findByParams, do you mean you want a list of Entity from your Category or you would to have the same function but for Category ?
[/quote]

Basically what I need is that the Dao method acceptes Entity object and derived class, in order to extend the Dao base class with CategoryDao and so on. The method find, insert, findByParams should have generic object, like T, as paramater.

Thanks you.

[SGaist]

Then why not use a template method ?