Mysql database Learning (4): describes and uses Mysql APIs, and encapsulates a class of MysqlDB for accessing Mysql databases.

First, the environment is windows + vs2008, Mysql database has been installed, before use, you need to configure the project properties, add include directory

D: \ Program Files \ MySQL Server 5.6 \ include (Mysql installation directory), add D: \ Program Files \ MySQL Server 5.6 \ lib to the additional library directory, add mysqlib as an additional dependency. lib, of course, mysqllib. lib only contains symbols. mysqllib is required for running executable files. dll (under the lib directory), copy it to the same directory of exe.

I. Introduction and use of common Mysql C APIs

1. mysql_init

The MYSQL structure represents a connection handle MYSQL * mysql_init (MYSQL * mysql). If mysql is a NULL pointer, this function will allocate, initialize, and return a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init () is assigned a new object, when mysql_close () is called to close the connection. This object will be released.
2. mysql_real_connect
// Connect to the database MYSQL * mysql_real_connect (MYSQL * mysql, const char * host, const char * user, const char * passwd, const char * db, unsigned int port, const char * unix_socket, unsigned long client_flag)

// Set the database
My_bool reconnect = true;
Mysql_options (mysql, MYSQL_OPT_RECONNECT, & reconnect );
Mysql_options (mysql, MYSQL_SET_CHARSET_NAME, "gbk ");

3. mysql_query
Int mysql_query (MYSQL * mysql, const char * query)
Mysql_affected_rows
Mysql_store_result
Mysql_num_fields
Mysql_num_rows
Mysql_fetch_field
Mysql_fetch_row
Mysql_free_result

The sample code is as follows:
C ++ Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

# Include <Windows. h> # Include <mysql. h> # Include <stdio. h> Int main (void) { // Initialize a connection handle MYSQL * mysql = mysql_init (NULL ); If (mysql = NULL) { Printf ("error: % s", mysql_error (mysql )); Return 1; } My_bool reconnect = true; Mysql_options (mysql, MYSQL_OPT_RECONNECT, & reconnect ); Mysql_options (mysql, MYSQL_SET_CHARSET_NAME, "gbk "); If (! Mysql_real_connect (mysql, "localhost", "root", "123456 ", "Scott", 0, NULL, 0 )) { Printf ("error: % s", mysql_error (mysql )); Return 1; } // No operation returns the result set Int result; Result = mysql_query (mysql, "insert into emp values (8888, 'yyyy', 'cler', 7782, '2017-04-10 ', 1990, NULL, 50 );"); If (result! = 0) { Printf ("error: % s", mysql_error (mysql )); Return 1; } Printf ("% llu row affected \ n", mysql_affected_rows (mysql )); // Operations with returned result sets Result = mysql_query (mysql, "select * from emp where deptno = 30 ;"); If (result! = 0) { Printf ("error: % s", mysql_error (mysql )); Return 1; } MYSQL_RES * mysql_res; MYSQL_FIELD * mysql_field; MYSQL_ROW mysql_row; Unsigned int cols; Mysql_res = mysql_store_result (mysql ); Cols = mysql_num_fields (mysql_res ); If (mysql_res! = NULL) { Printf ("Return % llu row \ n", mysql_num_rows (mysql_res )); While (mysql_field = mysql_fetch_field (mysql_res ))) { Printf ("% s \ t", mysql_field-> name ); } Printf ("\ n "); While (mysql_row = mysql_fetch_row (mysql_res ))) { For (unsigned int I = 0; I <cols; I ++) { Printf ("% s \ t", mysql_row [I]? Mysql_row [I]: "NULL "); } Printf ("\ n "); } Mysql_free_result (mysql_res ); } Mysql_close (mysql ); Return 0; }

The output result is as follows, because the values of each field are different. Although the tab is added, the output is a bit awkward:



II. The MysqlDB class is encapsulated below
The basic usage is the function demonstrated above, so I will not explain it much. Let's look at the Code directly.
MysqlDB. h: C ++ Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

# Ifndef _ MYSQL_DB_H _ # Define _ MYSQL_DB_H _ // # Define WIN32_LEAN_AND_MEAN # Include <winsock2.h> # Include <mysql. h> # Include <vector> # Include <string> Using namespace std; Namespace DAL { Class MysqlDB; Class MysqlRecordset { Friend class MysqlDB; Public: Const string & GetItem (unsigned int nRow, unsigned int nCol) const { Return rows _ [nRow] [nCol]; } Const string & GetItem (unsigned int nRow, const string & name) const { Unsigned int index = GetFieldIndex (name ); Return rows _ [nRow] [index]; } Unsigned int GetRows () const { Return rows _. size (); } Unsigned int GetCols () const { Return fields _. size (); } Unsigned int GetFieldIndex (const std: string & name) const { Unsigned int index =-1; For (unsigned int I = 0; I <fields _. size (); ++ I) { If (fields _ [I]. name = name) Index = fields _ [I]. index; } Return index; } Void Clear () { Rows _. clear (); Fields _. clear (); } Typedef struct Field { String name; // field name of the column Unsigned int index; // subscript corresponding to the field name } FIELD; Typedef vector <FIELD> FIELDS; // a collection of FIELD structures in all columns Typedef vector <string> ROW; // The storage value of each ROW Private: Vector <ROW> rows _; // stores multiple rows in total FIELDS fields _; }; Class MysqlDB { Public: MysqlDB (); ~ MysqlDB (); Void Open (const char * host, Const char * user, Const char * passwd, Const char * db, Unsigned int port ); Void Close (); Unsigned long ExecSQL (const char * SQL ); MysqlRecordset QuerySQL (const char * SQL ); Unsigned long GetInsertId () const; Void StartTransaction (); Void Commit (); Void Rollback (); Private: MYSQL * mysql _; }; } # Endif // _ MYSQL_DB_H _

MysqlDB. cpp: C ++ Code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149

# Include <exception> # Include "MysqlDB. h" Using namespace std; MysqlDB: MysqlDB (): mysql _ (NULL) { } MysqlDB ::~ MysqlDB () { If (mysql _) { Close (); } } Void MysqlDB: Open (const char * host, Const char * user, Const char * passwd, Const char * db, Unsigned int port) { Mysql _ = mysql_init (NULL ); If (mysql _ = NULL) { String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } My_bool reconnect = true; Mysql_options (mysql _, MYSQL_OPT_RECONNECT, & reconnect ); Mysql_options (mysql _, MYSQL_SET_CHARSET_NAME, "gbk "); If (! Mysql_real_connect (mysql _, host, user, Passwd, db, 0, NULL, 0 )) { String errmsg = mysql_error (mysql _); Close (); Throw Exception ("db error:" + errmsg ); } } Void MysqlDB: Close () { If (NULL! = Mysql _) { Mysql_close (mysql _); Mysql _ = NULL; } } MysqlRecordset MysqlDB: QuerySQL (const char * SQL) { If (mysql_query (mysql _, SQL )! = 0) { // Int errno = mysql_errno (mysql _); String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } MYSQL_RES * mysql_res; Mysql_res = mysql_store_result (mysql _); // Obtain the number of rows returned by the query // Unsigned long n = mysql_affected_rows (mysql _); // Point to the mysql query field set MYSQL_FIELD * mysql_field = NULL; MysqlRecordset rs; Unsigned int I = 0; Unsigned int nCols = mysql_num_fields (mysql_res ); While (mysql_field = mysql_fetch_field (mysql_res ))! = NULL) { MysqlRecordset: FIELD field; Field. name = mysql_field-> name; Field. index = I; ++ I; Rs. fields _. push_back (field); // press the struct of a column field } MYSQL_ROW mysql_row; While (mysql_row = mysql_fetch_row (mysql_res ))) { MysqlRecordset: ROW row (nCols ); For (unsigned int I = 0; I <nCols; ++ I) { Row [I] = mysql_row [I]? Mysql_row [I]: ""; } Rs. rows _. push_back (row); // press the storage value of a row } Mysql_free_result (mysql_res ); Return rs; } Unsigned long MysqlDB: ExecSQL (const char * SQL) { If (mysql_query (mysql _, SQL )! = 0) { // Int errno = mysql_errno (mysql _); String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } Return mysql_affected_rows (mysql _); } Void MysqlDB: StartTransaction () { If (mysql_query (mysql _, "start transaction ")! = 0) { // Int errno = mysql_errno (mysql _); String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } } Void MysqlDB: Commit () { If (mysql_query (mysql _, "COMMIT ")! = 0) { // Int errno = mysql_errno (mysql _); String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } } Void MysqlDB: Rollback () { If (mysql_query (mysql _, "ROLLBACK") = 0) { // Int errno = mysql_errno (mysql _); String errmsg = mysql_error (mysql _); Throw Exception ("db error:" + errmsg ); } } Unsigned long MysqlDB: GetInsertId () const { Return mysql_insert_id (mysql _); // auto_increment Field }

The specific usage is simple, including MysqlDB. h, such as MysqlDB mysqldb; mysqldb. open (); mysqldb. QuerySQL ();...

Refer:

Introduction to Database Systems

Mysql 5.1 Reference Manual