QT5 連接配接Mysql(mariadb)資料庫執行個體

花了幾天搞定QT5的Mysql(mariadb)連接配接，今天意識到直連資料庫風險太高，還是要改用REST。

在我删掉它之前還是貼出來水一篇吧。

先上CMakeList

include_directories(./mysql/include/mysql） #添加.h目錄                    

link_directories(./mysql/lib) #添加.obj目錄

add_executable(xh_scan
    main.cpp
    mainwindow.cpp
    mainwindow.h
    mainwindow.ui
    MysqlHelper.cpp
    ./mysql/include/mysql/mysql.h
)

target_link_libraries(xh_scan PRIVATE
                              Qt${QT_VERSION_MAJOR}::Widgets
                              libmariadb   #mysql mariadb     #添加dll名稱                         
                              )
           

#ifndef MYSQLHELPER_H
#define MYSQLHELPER_H

#include <iostream>
#include "winsock.h"
#include "mysql.h"
#include <QString>
#include <QList>

//#pragma comment(lib,"libmariadb.lib")

using namespace std;

struct ShipPlan
{
	int id;
	QString date;
	QString remark;
	int plan;
	int shipped;
};

class MysqlHelper
{
public:
	MysqlHelper();
	~MysqlHelper();

private:
	MYSQL mysql, *sock = nullptr;	//聲明MySQL的句柄
	const char *host = "127.0.0.1"; //因為是作為本機測試，是以填寫的是本地IP
	const char *user = "root";		//這裡改為你的使用者名，即連接配接MySQL的使用者名
	const char *passwd = "";        //這裡改為你的使用者密碼
	const char *db = "erp";		    //這裡改為你要連接配接的資料庫的名字,一個資料可能有幾張表
	unsigned int port = 3306;		//這是MySQL的伺服器的端口，如果你沒有修改過的話就是3306。
	const char *unix_socket = NULL; //unix_socket這是unix下的，我在Windows下，是以就把它設定為NULL
	unsigned long client_flag = 0;	//這個參數一般為0

	MYSQL_RES *result = nullptr; //儲存結果集的

public:
	QList<ShipPlan> getShipPlan();

private:
	MYSQL_RES *query(const char *query);
	QString connectSock();

	//轉換utf8用，，，暫時用不到
	void UTF_8ToGB2312(string &pOut, char *pText, int pLen);
	void UnicodeToGB2312(char *pOut, WCHAR uData);
	void UTF_8ToUnicode(WCHAR *pOut, char *pText);
};

#endif
           

QT 5 自帶了UTF8轉換，後面半截暫時用不上

#include "MysqlHelper.h"
#include <QList>
#include <ctime>
#include <string>
#include <stdlib.h>

using namespace std;

MysqlHelper::MysqlHelper()
{
	mysql_init(&mysql); //連接配接之前必須使用這個函數來初始化
}

MysqlHelper::~MysqlHelper()
{
	if (nullptr != sock)
	{
		mysql_close(sock); //關閉連接配接
	}
	if (nullptr != result)
	{
		mysql_free_result(result);
	}
}

///

QString MysqlHelper::connectSock()
{
	if ((sock = mysql_real_connect(&mysql, host, user, passwd, db, port, unix_socket, client_flag)) == NULL) //連接配接MySQL
	{
		printf("fail to connect mysql \n");
		fprintf(stderr, " %s\n", mysql_error(&mysql));
		return "未能連接配接資料庫";
	}
	else
	{
		mysql_set_character_set(&mysql, "utf8"); //用utf8通信，win顯示的時候要轉GB
		fprintf(stderr, "connect ok!!\n");
		return "OK";
	}
}

QList<ShipPlan> MysqlHelper::getShipPlan()
{
	QList<ShipPlan> list;

	if (nullptr == sock)
	{
		QString s = connectSock();
		if (s.compare("OK")!=0)
		{
			return list;
		}
	}
	//計算兩周後日期
	time_t now = time(0);
	time_t before = now + 24 * 3600 * 14; //14天後日期
	tm *ltm = localtime(&before);
	string dateBefore = to_string(1900 + ltm->tm_year) + "-" + to_string(ltm->tm_mon + 1) + "-" + to_string(ltm->tm_mday);

	time_t after = now - 24 * 3600 * 90; //90天前日期
	ltm = localtime(&after);
	string dateAfter = to_string(1900 + ltm->tm_year) + "-" + to_string(ltm->tm_mon + 1) + "-" + to_string(ltm->tm_mday);
	//cout << d << endl;

	string q = "select s.id,s.`date`,s.comment,s.plan,s.shipped from sales_style_shipping_arrange s where s.`date`>='" +
			   dateAfter + "' and s.`date`<='" + dateBefore + "' order by s.`date` desc,s.id desc limit 100";

	//cout<<q<<endl;

	MYSQL_RES *result = query(q.data());
	if (NULL == result)
	{
		printf("error result");
	}
	else
	{

		MYSQL_ROW row;									//代表的是結果集中的一行
		while ((row = mysql_fetch_row(result)) != NULL) //讀取結果集中的資料，傳回的是下一行。因為儲存結果集時，目前的遊标在第一行【之前】
		{
			ShipPlan sp;

			sp.id = atoi(row[0]);
			sp.date = QString::fromUtf8(row[1]);
			sp.remark = QString::fromUtf8(row[2]);
			sp.plan = atoi(row[3]);
			sp.shipped = atoi(row[4]);

			list.append(sp);
		}
	}

	return list;
}

MYSQL_RES *MysqlHelper::query(const char *query)
{

	if (mysql_query(&mysql, query) != 0) //如果連接配接成功，則開始查詢 .成功傳回0
	{
		fprintf(stderr, "fail to query!\n");
		return NULL;
	}
	else
	{
		if ((result = mysql_store_result(&mysql)) == NULL) //儲存查詢的結果
		{
			fprintf(stderr, "fail to store result!\n");
			return NULL;
		}
		else
		{
			return result;
		}
	}
	return NULL;
}

//轉換utf8用，，，暫時用不到
//UTF_8 轉gb2312
void MysqlHelper::UTF_8ToGB2312(string &pOut, char *pText, int pLen)
{
	char buf[4];
	char *rst = new char[pLen + (pLen >> 2) + 2];
	memset(buf, 0, 4);
	memset(rst, 0, pLen + (pLen >> 2) + 2);

	int i = 0;
	int j = 0;

	while (i < pLen)
	{
		if (*(pText + i) >= 0)
		{

			rst[j++] = pText[i++];
		}
		else
		{
			WCHAR Wtemp;

			UTF_8ToUnicode(&Wtemp, pText + i);

			UnicodeToGB2312(buf, Wtemp);

			unsigned short int tmp = 0;
			tmp = rst[j] = buf[0];
			tmp = rst[j + 1] = buf[1];
			tmp = rst[j + 2] = buf[2];

			//newBuf[j] = Ctemp[0];
			//newBuf[j + 1] = Ctemp[1];

			i += 3;
			j += 2;
		}
	}
	rst[j] = '\0';
	pOut = rst;
	delete[] rst;
}

void MysqlHelper::UnicodeToGB2312(char *pOut, WCHAR uData)
{
	WideCharToMultiByte(CP_ACP, NULL, &uData, 1, pOut, sizeof(WCHAR), NULL, NULL);
	return;
}

void MysqlHelper::UTF_8ToUnicode(WCHAR *pOut, char *pText)
{
	char *uchar = (char *)pOut;
	uchar[1] = ((pText[0] & 0x0F) << 4) + ((pText[1] >> 2) & 0x0F);
	uchar[0] = ((pText[1] & 0x03) << 6) + (pText[2] & 0x3F);
	return;
}