编程知识 cdmana.com

How to operate MySQL database with C + + under Linux?

Want to use C++ Write project , Databases are necessary , So I learned a little bit these two days C++ operation MySQL Database method . There are no tutorials , It's the knowledge searched on the Internet , Here's a summary . 

Connect MySQL There are two ways for databases : The first is to use ADO Connect , But this one is only suitable for Windows platform ; The second is to use MySQL Their own C API Function to connect to the database . I was in Linux Development under the platform , So the second way , There's a lot of Api function , But a few of them are commonly used , I use just a few of them .


 

API function

1.mysql_real_connect()

Connect a mysql The server

MYSQL *mysql_real_connect (MYSQL *mysql,

const char*host,

const char*user,

const char*passwd,

const char*db,

unsigned intport,

const char*unix_socket,

unsigned long client_flag)

If the connection is successful , return MYSQL* Connection handle . If the connection fails , return NULL. For a successful connection , Return value and 1 The values of the parameters are the same

2.mysql_query()

Execute assignment ” With NULL The ending string ” Of SQL Inquire about

Return a result table , Suppose the query is successful , You can call mysql_num_rows() To see if it corresponds to SELECT How many lines did the statement return , Or call mysql_affected_rows() To see if it corresponds to DELETE,INSERT,REPLACE or UPDATE How many lines are affected by the statement .

3.mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Retrieve the complete result set to the client . The most common way for the client to process the result set is by calling mysql_store_result(), Retrieve the entire result set at once . This function can get all the rows returned by the query from the server , And save them on the client side . For each query that successfully retrieved the data (SELECT、SHOW、DESCRIBE、EXPLAIN、CHECK TABLE etc. ), Must call mysql_store_result() or mysql_use_result() . For other queries , No call required mysql_store_result() or mysql_use_result(), But if in any case mysql_store_result(), It doesn't cause any damage or performance degradation .

4.mysql_num_rows()

Returns the number of rows in the result set .

5.mysql_num_fields()

Returns the number of fields in the result set , If you fail , Then return to false.

6.mysql_fetch_field()

MYSQL_FIELD* mysql_fetch_field(MYSQL_RES *result); 

Gets the type of the next table field , End return NULL.

7.mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result); 

Get the next line from the result set , Successfully returned an array , Greater than 0.

8.mysql_fetch_field_direct()

MYSQL_FIELD* mysql_fetch_field_direct(MYSQL_RES *result, int i); 

Given the field number , Returns the type of the table field , End return NULL.


 

Simple student information management code

I don't remember , Using these functions to write a student information management interface , Here is the code :

#include <iostream>

#include <string>

#include <stack>

#include <algorithm>

#include <sstream>

#include <mysql/mysql.h>

#include <unistd.h>

 

usingnamespacestd;

 

 

MYSQL mysql;  

MYSQL_ROW row; 

MYSQL_FIELD* field = NULL;      

MYSQL_RES* result;                                                 

 

string IntToStr(intnum)

{        

  stringstream ss;

  ss.clear();

  ss << num;

  returnss.str();

}

 

voidAdd()

{

  string fname,fsex,ftel,faddr;

  intfage;

  charchoice;

  do

  {

  ┊  cout << " Please enter the following information in turn :"<< endl;

  ┊  cout << "\nName: ";cin >> fname;

  ┊  cout << "\nSex: ";cin >> fsex;

  ┊  cout << "\nAge: "; cin >> fage;

  ┊  cout << "\nTel: "; cin >> ftel;

  ┊  cout << "\nAddr: "; cin >> faddr;

 

  ┊  string sql = "INSERT INTO Infor (name,sex,tel,addr,age) values('"+fname+"','"+fsex+"','"+ftel+"','"+faddr+"',   "+IntToStr(fage)+");";

  ┊  //string sql = "INSERT INTO Infor (name,sex,age,tel,addr) values(' Xiaohong ',' Woman ',18,'13333333333',          ' Yanta District, Xi'an City, Shaanxi Province ');";

 

  ┊  mysql_query(&mysql,sql.c_str());

  ┊  ┊              

  ┊  cout << " Whether to continue to add (y/n)?: ";

  ┊  cin >> choice;                                               

  }while(choice == 'y');      

 

}                  

 

voidSelect()            

{                  

  intid;             

  cout << " Please input the student ID of the student you want to query : ";

  cin >> id;            

 

  string sql = "SELECT * FROM Infor WHERE id = "+IntToStr(id)+";";

  mysql_query(&mysql,sql.c_str());

 

  result = mysql_store_result(&mysql);

  if(result == NULL)

  ┊  cout << "fail\n";

 

  for(inti=0; i<mysql_num_fields(result); i++)

  {        

  ┊  field = mysql_fetch_field_direct(result,i);

  ┊  cout << field->name << "\t\t";

  }               

  cout << endl;         

 

  row = mysql_fetch_row(result); 

  while(row != NULL)       

  {               

  ┊  for(inti=0; i<mysql_num_fields(result); i++)

  ┊  {             

  ┊  ┊  cout << row[i] << "\t\t";                                        

  ┊  }             

  ┊  cout << endl;       

  ┊  row = mysql_fetch_row(result);

  }               

}                 

 

 

voidUpdate()           

{                 

  intid;            

  charchoice;          

  string newaddr;        

  ┊  cout << " Please input the student number of the student you want to modify : ";

  ┊  cin >> id;         

  ┊  cout << endl << " Please enter the modified address : ";

  ┊  cin >> newaddr;

  ┊  string sql = "UPDATE Infor SET addr = '"+newaddr+"'WHERE id= "+IntToStr(id)+"; ";

  ┊  mysql_query(&mysql,sql.c_str());                                      

  ┊    

}      

 

intmain() 

{      

  charchoice[5];

  mysql_init(&mysql);

  /* Connect to database */

  if(!mysql_real_connect(&mysql,"localhost","root","dxm242012","Student",0,NULL,0))

  {    

  ┊  cout << "connect fial\n";

  ┊  return-1;

  }    

 

  while(atoi(choice) != 'q')

  {    

  ┊  sleep(4);

  ┊  system("clear");

  ┊  cout << "1. Add student information "<< endl;

  ┊  cout << "2. Search for student information "<< endl;

  ┊  cout << "3. Modify student information "<< endl;

 

  ┊  cin >> choice;

 

  ┊  cout << choice << endl;

  ┊  switch(atoi(choice))

  ┊  { 

  ┊  ┊  case1:

  ┊  ┊  ┊  Add();

  ┊  ┊  ┊  break;

  ┊  ┊  case2:

  ┊  ┊  ┊  Select();

  ┊  ┊  ┊  break;

  ┊  ┊  case3:

  ┊  ┊  ┊  Update();

  ┊  ┊  ┊  break;

  ┊  ┊  default:

  ┊  ┊  ┊  break;

  ┊  } 

  }

 

  mysql_close(&mysql);

  return0;

}

 


 

C++ encapsulation MyDB class

Later, these functions were simply encapsulated , It's convenient to use directly in the future .

#ifndef _MYDB_H

#define _MYDB_H

 

#include <string>

#include <iostream>

#include <mysql/mysql.h>

usingnamespacestd;

 

classMyDB

{

 

public:

  MyDB();

  ~MyDB();

  boolInitDB(string host,string user,string pwd,string dbname);                         

  boolExeSQL(string sql);

private:

  MYSQL* mysql;

  MYSQL* mysql;

  MYSQL_ROW row;

  MYSQL_RES* result;

  MYSQL_FIELD* field;                                               

};

#endif     

 

#include <iostream>

#include <string>

#include <stack>

#include <algorithm>  

#include <mysql/mysql.h>

#include "myDB.h"

 

usingnamespacestd;

 

MyDB::MyDB()  

{

  mysql = mysql_init(NULL);

  if(mysql == NULL)

  {

  ┊  cout << "Error: "<< mysql_error(mysql);

  ┊  exit(-1);

  }     

}

 

MyDB::~MyDB()

{                                                          

  if(!mysql)

  {

  ┊  mysql_close(mysql);

  }

}

 

boolMyDB::InitDB(string host,string user,string pwd,string dbname)

{

  /* Connect to database */

  if(!mysql_real_connect(mysql,host.c_str(),user.c_str(),pwd.c_str(),dbname.c_str(),0,NULL,0))

  {

  ┊  cout << "connect fial: "<< mysql_error(mysql);

  ┊  exit(-1);

  }

  returntrue;

}

 

boolMyDB::ExeSQL(string sql)

{

  /* Execution failure */

  if(mysql_query(mysql,sql.c_str()))

  {

  ┊  cout << "query fail: "<< mysql_error(mysql);

  ┊  exit(1);                                                  

  }

 

  else

  {

  ┊  /* Get the result set */

  ┊  result = mysql_store_result(mysql);

 

  ┊  intfieldnum = mysql_num_fields(result);

  ┊  for(inti=0; i<fieldnum; i++)

  ┊  {

  ┊  ┊  row = mysql_fetch_row(result);

  ┊  ┊  if(row <= 0)

  ┊  ┊  ┊  break;

  ┊  ┊  for(intj=0; j<fieldnum; j++)

  ┊  ┊  {

  ┊  ┊  ┊  cout << row[j] << "\t\t";

  ┊  ┊  }

  ┊  ┊  cout << endl;

  ┊  }

  ┊  mysql_free_result(result);

  }

  returntrue;

}

 

#include <iostream>

#include <string>

#include <stack>

#include <algorithm>

#include <mysql/mysql.h>

#include "myDB.h"

 

usingnamespacestd;

 

 

intmain()

{  

  MyDB db;

  db.InitDB("localhost","root","xxxxxx","Student");

  db.ExeSQL("SELECT * FROM Infor;");

  return0;

}  

Here are the results of the run :


 

Here's the problem : 

1. Error compiling  

There is no file or directory  

#include<mysql/mysql.h>

^

Compile interrupt . 

solve : except mysql-client and mysql-server, Installed again mysql-devel, And then it solved . 

2. Custom variables are passed in sql When the sentence is , Problems arise  

I found such a format on the Internet , 

string sql = "INSERT INTO Infor (name,sex,tel,addr,age) values('"+fname+"','"+fsex+"','"+ftel+"','"+faddr+"', "+IntToStr(fage)+");"; 

then string Type can succeed , Integer variables still don't work , I wrote a function again int To string.

string IntToStr(intnum)

{        

  stringstream ss;

  ss.clear();

  ss << num;

  returnss.str();

}

That's about it , The guard uncle is very fierce , I'm desperate, too , Just write about it , If you have any questions, please add .

Source of the article : author ——Tanswer_ 

This is the end of the article today , If you have any questions, please leave a message in the comments section .


 

Last , If you want to be a programmer too , Want to master programming quickly , Join us now Learn from Penguin circle

There are senior professional software development engineers , Answer all your doubts online ~ Introduction to programming language “so easy”

Programming learning books :


 

Programming learning video :


 

版权声明
本文为[Mu Ge AI programming]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224152542974r.html

Scroll to Top