In the previous example, we wrote our very first SQL query, which involves the SELECT statement. This time, we will learn how to use some other SQL statements, such as INSERT, UPDATE, and DELETE.
Let's create a simple program that demonstrates basic SQL query commands by following these steps:
- We can use our previous project files, but there are couples of things we need to change. First, open up
mainwindow.uiand replace the labels for name and age with line edit widgets. Then, add three buttons to the canvas and call them Update, Insert, and Delete:
- After that, open up
mainwindow.hand add the following variables under private inheritance:private: Ui::MainWindow *ui; QSqlDatabase db; bool connected; int currentID;
- Next, open up
mainwindow.cppand go to the class constructor. It is still pretty much the same as the previous example, except we store the database connection status in a Boolean variable calledconnectedand we also obtain the ID of the data from the database and store it to an integer variable calledcurrentID:MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow) { ui->setupUi(this); db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName("127.0.0.1"); db.setUserName("yourusername"); db.setPassword("yourpassword"); db.setDatabaseName("databasename"); connected = db.open(); if (connected) { QSqlQuery query; if (query.exec("SELECT id, name, age, gender, married FROM employee")) { while (query.next()) { currentID = query.value(0).toInt(); ui->name->setText(query.value(1).toString()); ui->age->setText(query.value(2).toString()); ui->gender->setCurrentIndex(query.value(3).toInt()); ui->married->setChecked(query.value(4).toBool()); } } else { qDebug() << query.lastError().text(); } } else { qDebug() << "Failed to connect to database."; } } - Then, go to
mainwindow.uiand right-click on one of the buttons we added to the canvas in step 1. Select Go to slot… and click OK. Repeat these steps on the other button, and now you should see three slot functions being added to both yourmainwindow.handmainwindow.cpp:private slots: void on_updateButton_clicked(); void on_insertButton_clicked(); void on_deleteButton_clicked();
- After that, open up
mainwindow.cppand we will declare what the program will do when we click on the Update button:void MainWindow::on_updateButton_clicked() { if (connected) { if (currentID == 0) { qDebug() << "Nothing to update."; } else { QString id = QString::number(currentID); QString name = ui->name->text(); QString age = ui->age->text(); QString gender = QString::number(ui->gender->currentIndex()); QString married = QString::number(ui->married->isChecked()); qDebug() << "UPDATE employee SET name = '" + name + "', age = '" + age + "', gender = " + gender + ", married = " + married + " WHERE id = " + id; QSqlQuery query; if (query.exec("UPDATE employee SET name = '" + name + "', age = '" + age + "', gender = " + gender + ", married = " + married + " WHERE id = " + id)) { qDebug() << "Update success."; } else { qDebug() << query.lastError().text(); } } } else { qDebug() << "Failed to connect to database."; } } - Once you have done that, we will proceed to declare what will happen when the Insert button is clicked:
void MainWindow::on_insertButton_clicked() { if (connected) { QString name = ui->name->text(); QString age = ui->age->text(); QString gender = QString::number(ui->gender->currentIndex()); QString married = QString::number(ui->married->isChecked()); qDebug() << "INSERT INTO employee (name, age, gender, married) VALUES ('" + name + "','" + age + "'," + gender + "," + married + ")"; QSqlQuery query; if (query.exec("INSERT INTO employee (name, age, gender, married) VALUES ('" + name + "','" + age + "'," + gender + "," + married + ")")) { currentID = query.lastInsertId().toInt(); qDebug() << "Insert success."; } else { qDebug() << query.lastError().text(); } } else { qDebug() << "Failed to connect to database."; } } - After that, we also declare what will happen when the Delete button is clicked:
void MainWindow::on_deleteButton_clicked() { if (connected) { if (currentID == 0) { qDebug() << "Nothing to delete."; } else { QString id = QString::number(currentID); qDebug() << "DELETE FROM employee WHERE id = " + id; QSqlQuery query; if (query.exec("DELETE FROM employee WHERE id = " + id)) { currentID = 0; qDebug() << "Delete success."; } else { qDebug() << query.lastError().text(); } } } else { qDebug() << "Failed to connect to database."; } } - Lastly, call
QSqlDatabase::close()at the class destructor to properly terminate the SQL connection before exiting the program:MainWindow::~MainWindow() { db.close(); delete ui; } - Compile and run the program now and you should be able to select the default data from the database; then you can choose to update it or delete it from the database. You can also insert new data into the database by clicking the Insert button. You can use phpMyAdmin to check whether the data is being altered correctly or not:
It's very important to check whether or not the database is connected in the first place before we proceed to send a SQL query to the database. Therefore, we keep that status in a variable and use it to check before sending out any queries. This, however, is not recommended for complex programs that are kept open for long periods of time, as the database might get disconnected during these periods, and a fixed variable may not be accurate. In that case, it's better to check the actual status by calling QSqlDatabase::isOpen().
The currentID variable is used to save the ID of the current data you obtained from the database. When you want to update the data or delete it from the database, this variable is crucial for letting the database know which data you're trying to update or delete. If you set your database table correctly, MySQL will treat each item of data as a unique entry, so you can be sure that no repeated ID will be produced in the database when new data is being saved.
After inserting new data into the database, we call QSqlQuery::lastInsertId() to obtain the ID of the new data and save it as a currentID variable, so that it becomes the current data that we can update or delete from the database.
It is a good habit to test your SQL queries on phpMyAdmin first before using them in Qt. You can instantly find out whether your SQL statements are correct or incorrect, instead of waiting for your project to get built, then try it out, then rebuild again. As a programmer, we must work in the most efficient way. Work hard, and work smart.