MySQL：QT与数据库的连接

首先注意导入INCLUDE 和 LIBS

INCLUDEPATH += "C:/Program Files (x86)/MySQL/MySQL Server 5.7/include/"

LIBS += "C:/Program Files (x86)/MySQL/MySQL Server 5.7/lib/libmysql.lib"

然后要注意各个文件该放在那个文件夹中(.dll放在QT的build下的debug)

.pro:

QT -= gui

CONFIG += c++11 console
CONFIG -= app_bundle

# The following define makes your compiler emit warnings if you use
# any Qt feature that has been marked deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS

# You can also make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

SOURCES += \
        CMySql.cpp \
        main.cpp
INCLUDEPATH += "C:/Program Files (x86)/MySQL/MySQL Server 5.7/include/"
LIBS += "C:/Program Files (x86)/MySQL/MySQL Server 5.7/lib/libmysql.lib"
# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

HEADERS += \
    CMySql.h

 

CMysql.h:

#pragma once
#include <./mysql.h>
//#include <WinSock2.h>
//#pragma comment(lib,"../mysql/libmysql.lib")
//
#include <list>
#include <string>
using namespace std;

class CMySql
{
public:
    CMySql(void);
    ~CMySql(void);
public:
    bool  ConnectMySql(const char *host,const char *user,const char *pass,const char *db);
    void  DisConnect();
    bool  SelectMySql(const char* szSql,int nColumn,list<string>& lstStr);
   
    //更新：删除、插入、修改
    bool  UpdateMySql(const char* szSql);
 
private:
    MYSQL *sock;   
	MYSQL_RES *results;   
	MYSQL_ROW record; 
   
};

CMysql.cpp:

#include "CMySql.h"


CMySql::CMySql(void)
{
    /*这个函数用来分配或者初始化一个MYSQL对象，用于连接mysql服务端。
    如果你传入的参数是NULL指针，它将自动为你分配一个MYSQL对象，
    如果这个MYSQL对象是它自动分配的，那么在调用mysql_close的时候，会释放这个对象*/
    sock = new MYSQL;
    mysql_init(sock );  
    mysql_set_character_set(sock,"gb2312"); //gb2312 中华人民共和国简体字标准
}


CMySql::~CMySql(void)
{
    if(sock)
    {
        delete sock;
        sock = NULL;
    }
    
}

void CMySql::DisConnect()
{
    mysql_close(sock);
}

bool CMySql::ConnectMySql(const char *host,const char *user,const char *pass,const char *db)
{
   //localhost   127.0.0.1         主机   用户名 密码  数据库 默认0（代表3306）
	if (!mysql_real_connect(sock, host, user, pass, db, 0, NULL, 0))
	{
        //连接错误
		return false;
	}
	
    return true;
}

bool CMySql::SelectMySql(const char* szSql,int nColumn,list<string>& lstStr)
{
    //mysql_query() 函数用于向 MySQL 发送并执行 SQL 语句
     if(mysql_query(sock,szSql))return false;

     /*·mysql_store_result 对于成功检索了数据的每个查询(SELECT、SHOW、DESCRIBE、EXPLAIN、CHECK TABLE等)
     返回值:
     . CR_COMMANDS_OUT_OF_SYNC 　　以不恰当的顺序执行了命令。
 　　· CR_OUT_OF_MEMORY 　　内存溢出。
 　　· CR_SERVER_GONE_ERROR 　　MySQL服务器不可用。
 　　· CR_SERVER_LOST 　　在查询过程中，与服务器的连接丢失。
 　　· CR_UNKNOWN_ERROR 　　出现未知错误。*/
	results=mysql_store_result(sock);
    if(NULL == results)return false;
	while (record = mysql_fetch_row(results))
	{
        
		 for(int i = 0;i < nColumn;i++)
         { 
			 if(NULL == record[i])
			 {
				 record[i] = "null";
			 }
             lstStr.push_back(record[i]);
         }
    

	}

    return true;
}

 bool  CMySql::UpdateMySql(const char* szSql)
 {
    if(!szSql)return false;

    if(mysql_query(sock,szSql))return false;

    return true;
 }

main:

#include <QCoreApplication>
#include <CMySql.h>
#include <iostream>
int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    //与数据库建立连接
    CMySql mysql;
    if(mysql.ConnectMySql("localhost","root","021224","0902test"))
    {
        cout << "connect mysql success" << endl;
        //char szbuf[1024] = "insert into student values(12,'test',now(),'woman');";
        //char szbuf[1024] = "update student set sname = '特使' where snum = 11";
        //char szbuf[1024] = "delete from student where snum = 12";
        //if(mysql.UpdateMySql(szbuf))
        //{
            //cout << "数据库插入成功" << endl;
            //cout << "数据库修改成功" << endl;
            //cout << "数据库删除成功" << endl;
        //}

        list<string> lststr;
        char szbuf[1024] = "select sname,snum,sage,score from myview";
        mysql.SelectMySql(szbuf,4,lststr);
        while(lststr.size() > 0)
        {
            string strsname = lststr.front();
            lststr.pop_front();
            string strsnum = lststr.front();
            lststr.pop_front();
            string strsage = lststr.front();
            lststr.pop_front();
            string strsscore = lststr.front();
            lststr.pop_front();
            cout << "sname:" << strsname << " snum:" << strsnum << " sage:" << strsage <<  " score:" << strsscore << endl;
         }
    }
    return a.exec();
}