Using MySQL database in C++ and its application skills

MySQL is a popular open source database management system that can be used to store and manage various types of data. This article will introduce how to use MySQL database in C and some application tips.

Install MySQL C Connector

First you need to install MySQL C Connector. You can download the MySQL C Connector corresponding to the operating system version from the MySQL official website (http://dev.mysql.com/downloads/connector/cpp/). After installation on Windows, add the include and lib folders under the installation path to the additional include directory and additional library directory of the Visual Studio project.

Connecting to MySQL database

You need to know the following parameters to connect to MySQL database:

• Host name: The host name where the MySQL server is located.
• Username and password: Username and password used when connecting to the database.
• Database name: The name of the database to be connected.

Use the following code to connect to the MySQL database:

#include <iostream>
#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>

using namespace std;

int main() {
    sql::Driver* driver;
    sql::Connection* con;
    sql::Statement* stmt;
    sql::ResultSet* res;

    driver = get_driver_instance();
    con = driver->connect("tcp://localhost:3306", "username", "password");
    stmt = con->createStatement();
    stmt->execute("USE database_name");

    // 这里可以执行需要的操作

    delete res;
    delete stmt;
    delete con;
    return 0;
}

Among them, "tcp://localhost:3306" represents the default port to connect to the local MySQL server, "username" and "password" " is the username and password used when connecting to the database, and "database_name" is the name of the database to be connected.

Execute basic SQL queries

After connecting to the MySQL database, we can use SQL queries to read and write data. Here are some basic SQL query examples:

Query

res = stmt->executeQuery("SELECT * FROM table_name");
while (res->next()) {
    cout << res->getString("column_name") << endl;
}

Insert

stmt->execute("INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')");

Update

stmt->execute("UPDATE table_name SET column1='new_value' WHERE column2='value_to_update'");

Delete

stmt->execute("DELETE FROM table_name WHERE column='value_to_delete'");

Note that in Before deleting or updating data, you should first use the SELECT statement to query to ensure that the data to be deleted or updated exists.

Processing query results

After using executeQuery to execute a SELECT query, you can use the ResultSet object to obtain the result set. The ResultSet class provides various methods to get different types of data, depending on the data type of the column you want to get. Here are some examples:

res = stmt->executeQuery("SELECT * FROM table_name");

//获取int类型数据
int c1 = res->getInt("column1");

//获取string类型数据
string c2 = res->getString("column2");

//获取double类型的数据
double c3 = res->getDouble("column3");

You can use a while loop to read all the data in the result set:

while (res->next()) {
    int c1 = res->getInt("column1");
    string c2 = res->getString("column2");
    double c3 = res->getDouble("column3");
    //做一些任务
}

Handling MySQL errors

When using MySQL in C, you can usually Handle errors by:

try {
    //需要执行的语句
} catch (sql::SQLException& e) {
    //发生错误时的处理
    cout << "MySQL Error: " << e.what() << endl;
}

Execute the statement that can throw SQLException in the try block, and then handle the error in the catch block. The error message can be obtained using e.what() in the catch block.

Using Transactions

MySQL database supports transactions, which can perform a set of operations as a single logical unit. If any of these operations fail, all operations are rolled back to the state before the transaction started. How to use transactions when:

sql::Savepoint* savepoint = con->setSavepoint();
try {
    stmt->execute("UPDATE table_name SET column1='new_value' WHERE column2='value_to_update'");
    stmt->execute("INSERT INTO table_name (column1, column2) VALUES ('value', 'value')");
    con->commit();
} catch (sql::SQLException& e) {
    con->rollback(savepoint);
}

Before executing statements that need to be operated as transactions, first set a savepoint (savepoint) on the connection object (con). After all statements are executed successfully, use the commit() method of the connection object to commit the transaction. If any statement fails to execute, use the rollback(savepoint) method of the connection object to roll back and undo all changes.

Conclusion

Using a MySQL database in C not only allows you to store and retrieve data, but you can also use transactions and error handling to ensure data integrity and accuracy. This article explains how to connect to a MySQL database, execute basic SQL queries, process query results, handle MySQL errors, and use transactions. Hopefully these tips will be helpful to C developers working with MySQL databases.

The above is the detailed content of Using MySQL database in C++ and its application skills. For more information, please follow other related articles on the PHP Chinese website!