O ile dobrze pójdzie to przyda się nam poniższy kod:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#include <QFile> | |
#include <QPixmap> | |
#include <QSqlDatabase> | |
#include <QSqlRecord> | |
#include <QSqlError> | |
#include <QSqlQuery> | |
#include <QDebug> | |
#include "studentdbhandler.h" | |
void StudentDBHandler::createTableIfNotExists(QSqlDatabase &db) | |
{ | |
if (!db.isOpen()) { | |
qDebug() << "Error: Failed to connect to database"; | |
return; | |
} | |
QSqlQuery query(db); | |
QString createTableQuery; | |
if ("QSQLITE" == m_dbType) { | |
createTableQuery = R"( | |
CREATE TABLE IF NOT EXISTS Students ( | |
id INTEGER PRIMARY KEY, | |
name VARCHAR(255) NOT NULL, | |
description VARCHAR(255) NOT NULL, | |
avatar BLOB | |
) | |
)"; | |
} else { // For other database types like MySQL | |
createTableQuery = R"( | |
CREATE TABLE IF NOT EXISTS Students ( | |
id INT AUTO_INCREMENT PRIMARY KEY, | |
name VARCHAR(255) NOT NULL, | |
description VARCHAR(255) NOT NULL, | |
avatar LONGBLOB DEFAULT NULL | |
) | |
)"; | |
} | |
if (!query.exec(createTableQuery)) { | |
qDebug() << "Error: Failed to create table" << query.lastError().text(); | |
} | |
} | |
void StudentDBHandler::addStudent(const std::string_view &name, | |
const std::string_view &description, | |
const std::string_view &imagePath) | |
{ | |
QSqlDatabase db = getDatabaseConnection(); | |
if (!db.isOpen()) { | |
qDebug() << "Error: Failed to connect to database"; | |
return; | |
} | |
createTableIfNotExists(db); | |
QSqlQuery query(db); | |
query.prepare("INSERT INTO Students (name, description, avatar) VALUES (:name, :description, :avatar)"); | |
query.bindValue(":name", name.data()); | |
query.bindValue(":description", description.data()); | |
if (!imagePath.empty()) { | |
QFile file(imagePath.data()); | |
if (!file.open(QIODevice::ReadOnly)) { | |
qDebug() << "Error: Failed to open image file" << imagePath; | |
return; | |
} | |
QByteArray imageData = file.readAll(); | |
query.bindValue(":avatar", imageData); | |
} else { | |
query.bindValue(":avatar", QVariant(QVariant::ByteArray)); | |
} | |
if (!query.exec()) { | |
qDebug() << "Error: Failed to add student" << query.lastError().text(); | |
} else { | |
qDebug() << "Student added successfully"; | |
} | |
db.close(); | |
} | |
void StudentDBHandler::listStudents() | |
{ | |
auto students = getStudents(); | |
for (const auto student : students) { | |
qDebug() << student.studentId_ << "Name:" << student.name_ | |
<< "Description:" << student.description_; | |
} | |
} | |
std::vector<Student> StudentDBHandler::getStudents() | |
{ | |
QSqlDatabase db = getDatabaseConnection(); | |
if (!db.isOpen()) { | |
throw std::runtime_error("Error: Failed to connect to database"); | |
} | |
QSqlQuery query(db); | |
if (!query.exec("SELECT * FROM Students")) { | |
throw std::runtime_error("Error: Failed to retrieve students" | |
+ query.lastError().text().toStdString()); | |
} | |
QSqlRecord record = query.record(); | |
const auto idIndex = record.indexOf("id"); | |
const auto nameIndex = record.indexOf("name"); | |
const auto descriptionIndex = record.indexOf("description"); | |
const auto avatarIndex = record.indexOf("avatar"); | |
std::vector<Student> students; | |
while (query.next()) { | |
Student student; | |
student.studentId_ = query.value(idIndex).toInt(); | |
student.name_ = query.value(nameIndex).toString().toStdString(); | |
student.description_ = query.value(descriptionIndex).toString().toStdString(); | |
QPixmap pixmap; | |
pixmap.loadFromData(query.value(avatarIndex).toByteArray()); | |
student.avatar_ = std::move(pixmap); | |
students.emplace_back(std::move(student)); | |
} | |
db.close(); | |
return students; | |
} | |
QSqlDatabase StudentDBHandler::getDatabaseConnection() | |
{ | |
static int nextDbConnectionNumber{}; /// to disable DB warning: https://stackoverflow.com/questions/55199965/qsqldatabase-how-to-avoid-qt-sql-default-connection-still-in-use-duplicate | |
QSqlDatabase db = QSqlDatabase::addDatabase(m_dbType.c_str(), | |
"connection_" + QString::number(nextDbConnectionNumber++)); | |
db.setDatabaseName(m_dbName.c_str()); | |
if (m_dbType == "QMYSQL") { | |
db.setHostName(m_dbHost.c_str()); | |
db.setUserName(m_dbUser.c_str()); | |
db.setPassword(m_dbPassword.c_str()); | |
} | |
if (!db.open()) { | |
qDebug() << "Error: Failed to connect to database"; | |
} | |
return db; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#ifndef STUDENTDBHANDLER_H | |
#define STUDENTDBHANDLER_H | |
#include <string> | |
#include <string_view> | |
#include <vector> | |
#include <optional> | |
class QSqlDatabase; | |
class QPixmap; | |
struct Student { | |
int studentId_; | |
std::string name_, description_; | |
std::optional<QPixmap> avatar_; | |
}; | |
class StudentDBHandler { | |
public: | |
StudentDBHandler(const std::string& dbType, const std::string& dbName, const std::string& dbHost = "", | |
const std::string& dbUser = "", const std::string& dbPassword = "") | |
: m_dbType(dbType), m_dbName(dbName), m_dbHost(dbHost), m_dbUser(dbUser), m_dbPassword(dbPassword) {} | |
void addStudent(const std::string_view& name, const std::string_view& description, const std::string_view& imagePath = ""); | |
std::vector<Student> getStudents(); | |
void listStudents(); | |
private: | |
void createTableIfNotExists(QSqlDatabase& db); | |
QSqlDatabase getDatabaseConnection(); | |
std::string m_dbType; | |
std::string m_dbName; | |
std::string m_dbHost; | |
std::string m_dbUser; | |
std::string m_dbPassword; | |
}; | |
#endif // STUDENTDBHANDLER_H |
- Wygodnym programem do przeglądania bazy danych jest: Dbeaver
- Polecam sobie aktywować bazę danych MySQL (dostępna przez https://panel.agh.edu.pl/)
- Aby się połączyć z bazą danych potrzebny VPN AGH.
- Alternatywnie można skorzystać z bazy danych SQLite, która działa lokalnie.
- Samo dodanie plików do projektu może spowodować błędy kompilacji! Dlatego trzeba jeszcze w pliku CMakeLists.txt dodać komponenty QT korzystające z bazy danych. Czyli człon
Sql
:find_package(QT NAMES Qt6 Qt5 REQUIRED COMPONENTS Widgets Sql)
find_package(Qt${QT_VERSION_MAJOR} REQUIRED COMPONENTS Widgets Sql)
find_package(Qt6 REQUIRED COMPONENTS Sql)
Oraz też kod przy linkowaniu:target_link_libraries(AplikacjaNaWarsztatyPrototyp PRIVATE Qt${QT_VERSION_MAJOR}::Widgets Qt${QT_VERSION_MAJOR}::Sql)