[ Class ] DBManager | Docwiki Nintersoft

Singleton class inherited from QSqlDatabase. It manages a unique connection between the application and the database.

Inherited methods

DBManager inherits some methods from QSqlDatabase class. Do not forget of having a look at Qt documentation as well!

Those are some useful methods inherited from QSqlDatabase:

Return	Method name
bool	open()
bool	isOpen()
bool	isValid()
void	close()
QSqlError	lastError()

Static methods

Return	Method name
DBManager *	[ static ] getInstance(const DBData &data = DBData())
QVariant	[ static ] pixmapToVariant(const QPixmap &pixmap)
QPixmap	[ static ] variantToPixmap(const QVariant &variant)
QString	[ static ] getUniqueConnectionName(const QString &partname = "")

Class methods

Return	Method name
bool	removeInstance()
bool	createTable(const QString &tableName, const QStringList &columns)
bool	insertRow(const QString &tableName, const QStringList &columnName, const QVariantList &data)
bool	updateRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QStringList &columnName, const QVariantList &data, const QString &operation = "=")
bool	updateRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QStringList &columnName, const QVariantList &data, const QString &operation = "=")
bool	removeRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = "=")
bool	removeRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QString &operation = "=")
bool	rowExists(const QString &tableName, const QString &columnNameCond, const QVariant &data, const QString &operation = "=")
bool	rowExists(const QString &tableName, const QStringList &columnNameCond, const QVariantList &data, const QString &operation = "=")
QVariantList	retrieveRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = "=")
QVariantList	retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QString &operation = "=")
QVariantList	retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, QStringList columnName, const QString &operation = "=")
QList< QVariantList >	retrieveAll(const QString &tableName, const QStringList &columns = QStringList(), const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())
QList< QVariantList >	retrieveAllCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=", const QStringList &orderby = QStringList())
QList< QVariantList >	retrieveAllCond(const QString &tableName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=", const QStringList &orderby = QStringList())
QList< QVariantList >	retrieveAllCond(const QString &tableName, const QStringList &columnName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=", const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())
QList< QVariantList >	retrieveAllCond(const QString &tableName, const QStringList &columnName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=", const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())
int	rowsCount(const QString &tableName)
int	rowsCountCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=")
int	rowsCountCond(const QString &tableName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=")
bool	clearTable(const QString &tableName)
bool	dropTable(const QString &tableName)
QSqlQuery	createCustomQuery(const QString &query = "")
bool	setConnectionType(DBConnectionType cType)
DBConnectionType	connectionType()
bool	setConnectionName(const QString &cName)
QString	currentConnectionName()
bool	setDBPrefix(const QString &prefix)
QString	dbPrefix()
bool	setDatabaseData(const DBManager::DBData &dbData)
DBManager::DBData	databaseData()

open()

Starts a new connection with the database (according to the connection name). Returns true on success, or false otherwise.

See method documentation at: http://doc.qt.io/qt-5/qsqldatabase.html#open.

if (myDB->open()){
    // ... Do something ...
}

isOpen()

Returns true if the current connection is open.

See method documentation at: http://doc.qt.io/qt-5/qsqldatabase.html#isOpen.

bool isOpen = myDB->isOpen();

isValid()

Returns true if the database has a valid driver.

See method documentation at: http://doc.qt.io/qt-5/qsqldatabase.html#isValid.

close()

Finishes the current connection to the database.

See method documentation at: http://doc.qt.io/qt-5/qsqldatabase.html#close.

myDB->closeDB();

lastError()

This method returns the cause of the latest error related to the database manipulation, for example: resquest syntax, invalid type or position, etc.

See method documentation at: https://doc.qt.io/qt-5/qsqldatabase.html#lastError.
See class documentation at [QSqlError]: http://doc.qt.io/qt-5/qsqlerror.html.

myDB->lastError();

[ static ] getInstance(const DBData &data = DBData())

Singleton method which builds and retrieves a DBManager object with the properties given by a DBManager::DBData object (which must contain database name, database host, username, password, etc).

It is worth to mention that the connection is not open right after the constructor execution. Therefore, a call to "openDB()" must be performed in order to stablish a new connection.

In case of inconsistent data, the private constructor is going to throw an exeception, which will be redirected to the "getInstance" method and the object will not be created.

DBManager::DBData myData;
myData.setHostName("127.0.0.1");
myData.setPort(3306);
myData.setDatabaseName("test");
myData.setUserName("root");
myData.setPassword("");
myData.setDatabaseConnectionType(DBManager::MYSQL);
myData.setTablePrefix("mtt_");
myData.setConnectionName(DBManager::getUniqueConnectionName("sqlite_c_"));

DBManager *myDB;
try {
    //If the database name is not empty or the database type is undefined
    //The new instance is not built and an exception is thrown
    myDB = DBManager::getInstance(myData);
} catch (std::invalid_argument e){
    qDebug() << e.what();
}

[ static ] pixmapToVariant(const QPixmap &pixmap)

This method returns a QVariant object generated from a QPixmap given as parameter. It is useful to store images in the database.

QPixmap randomImage;
randomImage.load(QDir::homePath() + "/Pictures/Nintersoft/ns-logo.png");
QVariant imageVariant = DBManager::pixmapToVariant(randomImage);
myDB->insertRow("testImageTable", QStringList() << "IMAGE", QList<QVariant>() << imageVariant);

[ static ] variantToPixmap(const QVariant &variant)

This method returns an image generated from the QVariant which is given as parameter. It is useful to retrieve images from the database.

QPixmap retrievedImage = DBManager::variantToPixmap(myDB->retrieveRow("testImageTable", QStringList() << "ID", QList<QVariant>() << 1, QStringList() << "IMAGE").at(0));

In case of invalid input, a null QPixmap is returned.

[ static ] getUniqueConnectionName(const QString &partname = "")

This static method returns a unique QString, which should be used as the database connection name (so as to avoid conflicts between connections with the same name). The partName parameter is used as prefix.

QString connection = DBManager::getUniqueConnectionName("test");

removeInstance()

This method tries to remove the current DBManager instance (which must be restarted, if necessary). Returns true on success, or false otherwise.

createTable(const QString &tableName, const QStringList &columns)

This method creates a new relation into the database, where the first parameter is the name of the table (relation) and the second one is the list containing the name of the columns and the data type specification.

myDB->createTable("testTable", QStringList() << "ID INTEGER PRIMARY KEY" << "NAME TEXT NOT NULL"
<< "PARENT TEXT NOT NULL" << "AGE INTEGER NOT NULL"); // SQLite Implementation

myDB->createTable("testTable" , QStringList() << "ID INTEGER(64) UNSIGNED AUTO_INCREMENT PRIMARY KEY" << "NAME TEXT NOT NULL"
<< "PARENT TEXT NOT NULL" << "AGE INTEGER NOT NULL"); // MySQL Implementation

Returns true on success, or false otherwise.

insertRow(const QString &tableName, const QStringList &columnName, const QVariantList &data)

This method inserts data into an existent table (received through the first parameter), where the second parameter is the name of the columns and the third one are their corresponding values.

myDB->insertRow("testTable", QStringList() << "NAME" << "PARENT" << "AGE",
               QList<QVariant>() << "Mauro Mascarenhas" << "Paulo Francisco" << 20);

Returns true on success, or false otherwise.

updateRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QStringList &columnName, const QVariantList &data, const QString &operation = "=")

This method updates the table (given in the first parameter) values. The new values must be given in fith parameter and its respective column on the fourth.

Hence, the second and the third parameter gives the values and column conditions of the columns in which the rows will be updated. The comparison operation must be given through the sixth parameter (the standard operation is the equality one).

myDB->updateRow("testTable", "NAME", "Mauro Mascarenhas",
                      QStringList() << "NAME" << "PARENT",
                      QList<QVariant>() << "Mauro Mascarenhas de Araújo" << "Ana Cláudia Mascarenhas");

Returns true on success, or false otherwise.

updateRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QStringList &columnName, const QVariantList &data, const QString &operation = "=")

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test.

myDB->updateRow("testTable", QStringList() << "NAME" << "PARENT",
                      QList<QVariant>() << "Fábio Francisco" << "Paulo Francisco",
                      QStringList() << "NAME" << "AGE",
                      QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns true on success, or false otherwise.

removeRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = "=")

This method removes every occurrence of the tuples which fits the conditional criteria from the specified table (first parameter).

myDB->removeRow("testTable", "NAME", "Mauro Mascarenhas");

Returns true on success, or false otherwise.

removeRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QString &operation = "=")

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test.

myDB->removeRow("testTable", QStringList() << "NAME" << "AGE",
                      QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns true on success, or false otherwise.

rowExists(const QString &tableName, const QString &columnNameCond, const QVariant &data, const QString &operation = "=")

This method checks if there is at least one occurrence of the condition criteria in the given relation (table).

myDB->rowExists("testTable", QStringList() << "NAME" << "AGE",
                     QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns true if the row exists, or false otherwise (and in case of incoherent data or incorrect syntax).

rowExists(const QString &tableName, const QStringList &columnNameCond, const QVariantList &data, const QString &operation = "=")

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test.

myDB->rowExists("testTable", "NAME", "Mauro Mascarenhas de Araújo");

Returns true if the row exists, or false otherwise (and in case of incoherent data or incorrect syntax).

retrieveRow(const QString &tableName, const QString &columnNameCond, const QVariant &condition, const QString &operation = "=")

This method is quite similar to "rowExists". However, not only does it inform you if there is a row which matches the search criteria, but also returns the first complete occurrence. In other words, it returns the data available in the resulting table row.

myDB->retrieveRow("testTable", "NAME", "Mauro Mascarenhas de Araújo");

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, const QString &operation = "=")

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test.

myDB->retrieveRow("testTable", QStringList() << "NAME" << "AGE",
                        QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveRow(const QString &tableName, const QStringList &columnNameCond, const QVariantList &condition, QStringList columnName, const QString &operation = "=")

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test. This method also allows you to specify which columns must be returned in the list.

myDB->retrieveRow("testTable", QStringList() << "NAME" << "AGE",
                              QList<QVariant>() << "Fábio Francisco de Araújo" << 19,
                              QStringList() << "AGE");

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveAll(const QString &tableName, const QStringList &columns = QStringList(), const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())

This method is quite similar to "retrieveRow", but instead of returning only the first occurrence, it returns everyone (thats why it is used a list of QVariant’s list).

myDB->retrieveAll("testTable");

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveAllCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=", const QStringList &orderby = QStringList())

This method is quite similar to "retrieveAll", but it returns only the rows which matches the conditional supplied in the parameters.

myDB->retrieveAllCond("testTable", "NAME", "%Fábio%", "LIKE");

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveAllCond(const QString &tableName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=", const QStringList &orderby = QStringList())

This is an overloaded method. It allows you to give more than one column and more than one value to the conditional test.

myDB->retrieveAllCond("testTable", QStringList() << "NAME" << "AGE",
                            QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveAllCond(const QString &tableName, const QStringList &columnName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=", const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())

This is an overloaded method. It allows you to specify which columns must be returned in the list.

myDB->retrieveAllCond("testTable", QStringList() << "NAME" << "AGE",
                                                  QStringList() << "NAME" << "AGE",
QList<QVariant>() << "Fábio Francisco de Araújo" << 19)

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

retrieveAllCond(const QString &tableName, const QStringList &columnName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=", const QStringList &groupby = QStringList(), const QStringList &orderby = QStringList())

myDB->retrieveAllCond("testTable", QStringList() << "SUM(AGE)",
                                                  QStringList() << "NAME" << "AGE",
                                                  QList<QVariant>() << "Fábio Francisco de Araújo" << 19,
                                                  "=",
QStringList(), QStringList() << "ID ASC")

Returns an empty list either if no result is gotten or in case of any (syntax or size) error in the given parameters.

rowsCount(const QString &tableName)

This method returns the row count of the given table.

int rows = myDB->rowsCount("testTable");

Returns -1 if anything goes wrong.

rowsCountCond(const QString &tableName, const QString &columnCondition, const QVariant &condition, const QString &operation = "=")

This method returns the row count of the specified table returned by the select query, where the values and columns must be provided as second and third parameters respectively.

int rows = myDB->rowsCountCond("testTable", "NAME", "Mauro Mascarenhas de Araújo");

Returns -1 if anything goes wrong.

rowsCountCond(const QString &tableName, const QStringList &columnCondition, const QVariantList &condition, const QString &operation = "=")

This is an overloaded method. You are able to give more than one column and values to the conditional operation check.

myDB->rowsCountCond("testTable", QStringList() << "NAME" << "AGE",
                         QList<QVariant>() << "Fábio Francisco de Araújo" << 19);

Returns -1 if anything goes wrong.

clearTable(const QString &tableName)

This method cleans the specified table without removing it.

myDB->clearTable("testTable");

Returns true on success, or false otherwise.

dropTable(const QString &tableName)

This method removes the specified table.

myDB->dropTable("testTableDrop");

Returns true on success, or false otherwise.

createCustomQuery(const QString &query = "")

Returns a QSqlQuery object which was built based on the current database connection. With this object in hands, you will be able to execute any supported SQL query.

QSqlQuery testC = myDB->createCustomQuery();
testC.prepare("DROP TABLE mtt_testTableDrop1");
testC.exec();

setConnectionType(DBConnectionType cType)

Changes the current database type in which DBManager is going to connect to.

if (myDB->setConnectionType(DBManager::MYSQL)) /* Do something */;

Returns true if it was possible to assign it, or false otherwise (take notice that it is not possible to change the database type while the database connection is open).

connectionType()

Returns the current database type.

switch (myDB->connectionType()){
    case DBManager::MYSQL :
        //....
    break;
    default:
        //....
    break;
}

setConnectionName(const QString &cName)

Sets a new database connection name.

if (myDB->setConnectionName(DBManager::getUniqueConnectionName("test_"))) /* Do something */;

Returns true if it was possible to assign it, or false otherwise (take notice that it is not possible to change the connection name while the database connection is open).

currentConnectionName()

Returns the current connection name.

QString connection = myDB->currentConnectionName();

setDBPrefix(const QString &prefix)

Sets a new standart prefix to the database relations.

myDB->setDBPrefix("lol_");

Returns true if it was possible to assign it, or false otherwise (take notice that it is not possible to change the standard table prefix while the database connection is open).

dbPrefix()

Returns the standard relation prefix.

QString prefix = myDB->dbPrefix();

setDatabaseData(const DBManager::DBData &dbData)

Defines a new configuration to the database connection.

if (myDB->setDatabaseData(myData)) /* Do something */;

Returns true if it was possible to assign it, or false otherwise (take notice that it is not possible to change the settings while the database connection is open).

databaseData()

Returns the current database connection settings.

const DBManager::DBData temp = myDB->databaseData();