AbstractDbo Database Access Object

#include <boost/shared_ptr.hpp>

#include <QString>

class SqliteDataAccess;

class QSqlQuery;

class AbstractDbo

{

public:

    AbstractDbo(QString tableName);

    ~AbstractDbo();

    bool From(QSqlQuery & q, bool isFirst = true);

    bool Insert(SqliteDataAccess & dao);

    int Updata(SqliteDataAccess & dao, QString set, QString where = "1=1");

    int Delete(SqliteDataAccess & dao, QString where = "1=1");

    int Create(SqliteDataAccess & dao);

    int Drop(SqliteDataAccess &dao);

    boost::shared_ptr<QSqlQuery> SelectAll(SqliteDataAccess &dao);

    void Print();

protected:

    virtual QString GetInsertNames() = 0;

    virtual QString GetInsertValues() = 0;

    virtual QString GetCreateColumns() = 0;

    virtual bool SetFrom(QSqlQuery & q) = 0;

    virtual QString GetPrintString() = 0 ;

    QString m_tableName;

};

#include"AbstractDbo.h"

#include <QSqlQuery>

#include <QVariant>

#include "SqliteDataAccess.h"

#include "Log.h"

AbstractDbo::AbstractDbo(QString tableName)

    : m_tableName(tableName)

{

}

AbstractDbo::~AbstractDbo()

{

}

//,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,public function  ******

bool AbstractDbo::From(QSqlQuery & q, bool isFirst)

{

    if(q.isSelect() == false)

    {

        SU_ERROR("q is not select object!");

        return false;

    }

    if(isFirst)

        q.first();

    if(q.isValid())

    {

        return this->SetFrom(q);

    }

    SU_ERROR("query is invalid!");

    return false;

}

bool AbstractDbo::Insert(SqliteDataAccess & dao)

{

    QString fmt("insert into %1 ( %2) values (%3)");

    QString name = this->GetInsertNames();

    QString val = this->GetInsertValues();

    return dao.ExecuteNoSelect(fmt.arg(m_tableName).arg(name).arg(val)) > 0;

}

int AbstractDbo::Updata(SqliteDataAccess & dao, QString set, QString where)

{

    QString fmt("update %1 set %2 where %3");

    return dao.ExecuteNoSelect(fmt.arg(m_tableName).arg(set).arg(where));

}

int AbstractDbo::Delete(SqliteDataAccess & dao, QString where)

{

    QString fmt("delete from %1 where %2");

    return dao.ExecuteNoSelect(fmt.arg(m_tableName).arg(where));

}

int AbstractDbo::Create(SqliteDataAccess &dao)

{

    QString fmt( "create table %1 (%2)");

    QString strColumns = this->GetCreateColumns();

    return dao.ExecuteNoSelect(fmt.arg(m_tableName).arg(strColumns));

}

int AbstractDbo::Drop(SqliteDataAccess &dao)

{

    QString fmt( "drop table %1");

    return dao.ExecuteNoSelect(fmt.arg(m_tableName));

}

void AbstractDbo::Print()

{

    QString str = this->GetPrintString();

    SU_INFO(str.toAscii().constData());

}

boost::shared_ptr<QSqlQuery> AbstractDbo::SelectAll(SqliteDataAccess &dao)

{

    boost::shared_ptr<QSqlQuery> pQuery(new QSqlQuery);

    QString selSql = "select * from %1";

    if(dao.ExecuteSelect(*pQuery, selSql.arg(m_tableName)) == false)

    {

        SU_ERROR("select all record error!");

        return boost::shared_ptr<QSqlQuery>();

    }

    return pQuery;

}

---> Implementation

#include "AbstractDbo.h"

class Person : public AbstractDbo

{

public:

    Person();

    ~Person();

    int ID;

    QString Name;

    QString Addr;

    int Typeid;

protected:

    QString GetInsertNames();

    QString GetInsertValues();

    QString GetCreateColumns();

    bool SetFrom(QSqlQuery & q);

    QString GetPrintString();

};

#include"Person.h"

#include <QSqlQuery>

#include <QVariant>

#include "SqliteDataAccess.h"

#include "Log.h"

Person::Person()

    : AbstractDbo("person"), ID(0), Typeid(0)

{

}

Person::~Person()

{

}

bool Person::SetFrom(QSqlQuery & q)

{

    ID = q.value(0).toInt();

    Name = q.value(1).toString();

    Addr = q.value(2).toString();

    Typeid = q.value(3).toInt();

    return true;

}

QString Person::GetInsertNames()

{

    return " id, name, address, typeid ";

}

QString Person::GetInsertValues()

{

    return QString("%1, '%2', '%3', %4").arg(ID).arg(Name).arg(Addr).arg(Typeid);

}

QString Person::GetCreateColumns()

{

    return "id int primary key, name varchar(20), address varchar(200), typeid int";

}

QString Person::GetPrintString()

{

    QString fmt("id:%1, name:%2, address:%3, typeid%4 TableName:%5");

    return fmt.arg(this->ID).arg(this->Name).arg(this->Addr).arg(this->Typeid).arg(m_tableName);

}

void Person_Test()

{

    {

        SqliteDataAccess dao("C:\\a.sqlite");

        Person p;

        //drop

        p.Drop(dao);

        // create

        if(p.Create(dao) < 0)

        {

            SU_ERROR("create talbe error!");

            return ;

        }

        SU_INFO("create a table success!");

        p.ID = 1;

        p.Name = "Alice";

        p.Addr = "123 Main Street";

        p.Typeid = 101;

        p.Insert(dao);

        SU_INFO("after insert ");

        boost::shared_ptr<QSqlQuery> pQuery = p.SelectAll(dao);

        p.From(*pQuery);

        p.Print();

        p.Updata(dao, "id=3, name = 'string'");

        SU_INFO("after update!");

        pQuery = p.SelectAll(dao);

        p.From(*pQuery);

        p.Print();

        p.Delete(dao, "id = 3");

        SU_INFO("after delete!");

        pQuery = p.SelectAll(dao);

        p.From(*pQuery);

        p.Print();

    }

    SU_INFO("create other sql database object");

    {

        SqliteDataAccess dao1("C:\\b.sqlite");

        Person p;

        p.ID = 2;

        p.Name = "Alice";

        p.Addr = "123 Main Street";

        p.Typeid = 101;

        p.Drop(dao1);

        p.Create(dao1);

        p.Insert(dao1);

    }

    {

    SqliteDataAccess dao2("C:\\a.sqlite");

    }

    {

    SqliteDataAccess dao6("C:\\a.sqlite");

    }

    {

    SqliteDataAccess dao3("C:\\a.sqlite");

    }

    {

    SqliteDataAccess dao4("C:\\a.sqlite");

    }

    {

    SqliteDataAccess dao5("C:\\a.sqlite");

    }

    SU_INFO("create finished  sql database object");

}