server/db_api/mysqltest01.cpp

#include <iostream>
#include <mysql.h>
#include <stdio.h>
#include <mysql.h>
#include <string>
#include <time.h>
#include <random>
#include <thread>
#include <chrono>
#include <string.h>
#include "CDBConnPool.h"
#include "CDBHelper.h"

void mysql_base_test()
{
	MYSQL conn;
	int res = 0;
	mysql_init(&conn);
	if ( mysql_real_connect( &conn, "localhost", "root", "ok123", "yaxt", 0, NULL, CLIENT_FOUND_ROWS ) )
	{
		printf("connect success!\n");
		res = mysql_query( &conn, "insert into test01(id,name,type,BirthDate) values(3,'user',2,'2018-1-2 12:13:14')" );
		if ( res )
		{
			printf("error\n");
        }
	    else
	    {
		    printf("OK\n");
	    }

	    mysql_close(&conn);
    }
}

void db_test_thread()
{
	std::cout << "db_test_thread begin:[" << std::this_thread::get_id() << "]" << std::endl;

	//随机等待
	std::default_random_engine e;
	std::uniform_int_distribution<unsigned> u( 1, 9 );
	int r = u( e );
	std::this_thread::sleep_for( std::chrono::milliseconds( r * 100 ) );

	std::string Error;
	//从连接池中获得一个数据库连接
	YADB::CDBConnect *pConn = sDBConnPool.GetDBConnect( Error );
	if ( pConn )
	{
		//------------------------------------------------------
		//操作test01表,基础功能测试
		//------------------------------------------------------
        //插入
		char szInsert[1024] = { 0 };
		//线程ID的hash值实在是没法看，所以用很蠢的办法把线程ID赋给记录ID
		char szID[30] = { 0 };
		sprintf( szID, "%d", std::this_thread::get_id() );
		long long ID = atoll( szID );
		std::string Name = "Tom.J";
		int Type = 1;
		std::string BirthDate = "2017-12-1 09:13:45";

		my_ulonglong ullRes = 0;

		//插入记录
		sprintf( szInsert, "insert into test01(ID,Name,Type,BirthDate) values (%lld, \'%s\', %d,\'%s\')",	ID, Name.c_str(), Type, BirthDate.c_str() );
		ullRes = pConn->ExecuteRealSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "插入记录失败:%s!\n", Error.c_str() );
			Error = pConn->GetLastError();
		}
		else
		{
			printf( "插入记录成功!\n" );
		}

		//查询
		YADB::CDBResultSet *pRes = pConn->ExecuteQuery( "select * from test01", Error );
		my_ulonglong ResCount = pRes->GetRecordCount( Error );
		printf( "Record count = %lld\n", ResCount );

		while ( pRes->GetNextRecod( Error ) )
		{
			long long ID = 0;
			pRes->GetField( "ID", ID, Error );

			std::string strName;
			pRes->GetField( "Name", strName, Error );

			char szName[100] = { 0 };
			pRes->GetField( "Name", szName, sizeof( szName ), Error );

			int Type = 0;
			pRes->GetField( "Type", Type, Error );

			std::string Birth;
			pRes->GetField( "BirthDate", Birth, Error );

			MYSQL_TIME dtBirth;
			pRes->GetField( "BirthDate", dtBirth, Error );

			printf( "ID=%lld Name=%s Type=%d BirthDate=%s\n", ID, strName.c_str(), Type, Birth.c_str() );
		}

		//修改
		memset( szInsert , 0, sizeof( szInsert ) );
		Name = "李明";
		BirthDate = "2018-1-1 01:01:01";

		sprintf( szInsert, "update test01 set Name='%s\',BirthDate ='%s\' where ID=%lld", Name.c_str(), BirthDate.c_str(), ID );
		ullRes = pConn->ExecuteRealSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "更新记录失败:%s!\n", Error.c_str() );
		}
		else
		{
			printf( "更新记录成功!\n" );
		}

		//删除
		memset( szInsert, 0, sizeof( szInsert ) );
		sprintf( szInsert, "delete from test01 where ID=%lld", ID );
		ullRes = pConn->ExecuteSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "删除记录失败:%s!\n", Error.c_str() );
		}
		else
		{
			printf( "删除记录成功!\n" );
		}

		//用预处理方式出入记录
		long long LID = 100000000 + ID;
		char name[20] = "Jarry";
		long nameLen = strlen( name );
		int type = 2;
		MYSQL_TIME ts;
		ts.year = 2018;
		ts.month = 1;
		ts.day = 13;
		ts.hour = 13;
		ts.minute = 15;
		ts.second = 16;
		ts.second_part = 0;
		ts.time_type = MYSQL_TIMESTAMP_DATE;

		MYSQL_BIND bindData[4];
		memset( bindData, 0, sizeof( bindData ) );

		bindData[0].buffer_type = MYSQL_TYPE_LONGLONG;
		bindData[0].buffer = ( long long * ) &LID;
		bindData[0].is_null = 0;

		bindData[1].buffer_type = MYSQL_TYPE_STRING;
		bindData[1].buffer = ( char * ) name;
		bindData[1].buffer_length = nameLen;
		bindData[1].is_null = 0;
		bindData[1].length = ( unsigned long* ) &nameLen;

		bindData[2].buffer_type = MYSQL_TYPE_LONG;
		bindData[2].buffer = &type;
		bindData[2].is_null = 0;

		bindData[3].buffer_type = MYSQL_TYPE_DATETIME;
		bindData[3].buffer = ( char * ) &ts;
		bindData[3].is_null = 0;
		bindData[3].length = 0;

		if ( !pConn->stmtExcute( bindData, 0, Error ) )
		{
			printf( "执行预处理失败:%s!\n", Error.c_str() );
		}
		else
		{
			printf( "执行预处理成功!\n" );

			//删除
			memset( szInsert, 0, sizeof( szInsert ) );
			sprintf( szInsert, "delete from test01 where ID=%lld", LID );
			ullRes = pConn->ExecuteSql( szInsert, Error );
			if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
			{
				printf( "删除记录失败:%s!\n", Error.c_str() );
			}
			else
			{
				printf( "删除记录成功!\n" );
			}
		}

		//------------------------------------------------------
		//操作test02表,test02表的ID字段是自增的
		//------------------------------------------------------
		//插入记录
		sprintf( szInsert, "insert into test02(Name,Type,BirthDate) values (\'%s\', %d,\'%s\')",	Name.c_str(), Type, BirthDate.c_str() );
		ullRes = pConn->ExecuteRealSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "插入记录失败:%s!\n", Error.c_str() );
			Error = pConn->GetLastError();
		}
		else
		{
			printf( "插入记录成功!\n" );
		}

		my_ulonglong InsertedID = pConn->GetLastInsertID( Error );
		printf( "新插入ID:%lld!\n", InsertedID );

		//删除
		memset( szInsert, 0, sizeof( szInsert ) );
		sprintf( szInsert, "delete from test02 where ID=%lld", InsertedID );
		ullRes = pConn->ExecuteSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "删除记录失败:%s!\n", Error.c_str() );
		}
		else
		{
			printf( "删除记录成功!\n" );
		}

		//------------------------------------------------------
		//操作test01表,事务测试
		//------------------------------------------------------
		pConn->AutoCommit( false, Error );//把自动提交设置成false
		Name = "GuaGua";
		LID = 900000000 + ID;
		sprintf( szInsert, "insert into test01(ID,Name,Type,BirthDate) values (%lld, \'%s\', %d,\'%s\')",	LID, Name.c_str(), Type, BirthDate.c_str() );
		ullRes = pConn->ExecuteRealSql( szInsert, Error );
		if ( YADB::DB_ERR_EXCUTE_QUERY == ullRes )
		{
			printf( "插入记录失败:%s!\n", Error.c_str() );
			Error = pConn->GetLastError();
		}
		else
		{
			printf( "插入记录成功!\n" );
		}
		//pConn->Commit( Error );//提交
		pConn->RollBack( Error );//回滚
		pConn->AutoCommit( true, Error );//把自动提交设置成true

		//归还数据库连接
		sDBConnPool.GiveBack( pConn );

		//异步执行测试
		printf( "异步执行测试开始!\n" );
		char szSQL[1024] = { 0 };
		//memset( szSQL, 0, sizeof( szSQL ) );
		sprintf( szSQL, "insert into test01(ID,Name,Type,BirthDate) values (%lld, \'%s\', %d,\'%s\')",	LID, "Mike", 3, "2000-1-1 12:13:14" );
		sDBConnPool.PushAsync( szSQL );

		memset( szSQL, 0, sizeof( szSQL ) );
		sprintf( szSQL, "delete from test01 where ID=%lld",	LID );
		sDBConnPool.PushAsync( szSQL );
		printf( "异步执行测试结束!\n" );
	}

	//显示线程信息
	std::cout << "[" << std::this_thread::get_id() << "]" << " db_test_thread end!\n" ;
}

//在多线程环境下测试
void thread_db_class_test()
{
	printf( "----------------------------------------------------------------\n" );
	printf( "                           多线程测试                             \n" );
	printf( "----------------------------------------------------------------\n" );

	YADB::_DB_POOL_SETTING_ DBSetting;
	DBSetting.Host     = "localhost";
	DBSetting.User     = "root";
	DBSetting.PWD      = "ok123";
	DBSetting.DBName   = "yaxt";
	//DBSetting.CharSet  = "GB2312";
	DBSetting.CharSet  = "utf8";
	DBSetting.TimeOut  = 5;
	DBSetting.PoolSize = 10;
	DBSetting.stmtSQL  = "insert into test01(ID,Name,Type,BirthDate) values (?,?,?,?)";

	std::string Error;

	//创建连接池
	if ( !sDBConnPool.Create( DBSetting, Error ) )
	{
		printf( "线程池创建失败,Err=%s\n", Error.c_str() );
		return;
	}

	//多线程测试
	int ThreadCount = 1;//线程数
	for ( int i = 0; i < ThreadCount; i++ )
	{
		std::thread t( db_test_thread );
		t.detach();
	}

	sDBConnPool.Close();
}

void simple_db_class_test()
{
	printf("----------------------------------------------------------------\n");
	printf("                           简单测试                              \n");
	printf("----------------------------------------------------------------\n");

	YADB::_DB_POOL_SETTING_ DBSetting;
	DBSetting.Host = "localhost";
	DBSetting.User = "root";
	DBSetting.PWD = "ok123";
	DBSetting.DBName = "yaxt";
	DBSetting.CharSet = "utf8";
	DBSetting.TimeOut = 5;
	DBSetting.PoolSize = 10;
	DBSetting.stmtSQL =	"insert into test01(ID,Name,Type,BirthDate) values (?,?,?,?)";

	std::string Error;

	//创建连接池
	if ( !sDBConnPool.Create( DBSetting, Error ) )
	{
		printf("线程池创建失败,Err=%s\n", Error.c_str());
		return;
	}

	YADB::CDBHelper DBHelper;
	YADB::CDBResultSet DBRes;
	sDBConnPool.Query( "select * from test01", DBRes, Error );
	int nCount = DBRes.GetRecordCount( Error );
	if (nCount > 0)
	{
		printf( "The record count=%d\n", nCount );

		while ( DBRes.GetNextRecod(Error) )
		{
			long long ID = 0;
			DBRes.GetField( "ID", ID, Error );

			char szName[100] = { 0 };
			DBRes.GetField( "Name", szName, sizeof( szName ), Error );

			std::string strName;
			DBRes.GetField( "Name", strName, Error );

			int Type = 0;
			DBRes.GetField( "Type", Type, Error );

			unsigned int uType = 0;
			DBRes.GetField( "Type", uType, Error );

			std::string Birth;
			DBRes.GetField( "BirthDate", Birth, Error );

			float fValue = 0.0;
			DBRes.GetField( "FloatField", fValue, Error );

			double dValue = 0.0;
			DBRes.GetField( "DoubleField", dValue, Error );

			bool bValue = false;
			DBRes.GetField( "BoolField", bValue, Error );

			MYSQL_TIME dtBirth;
			DBRes.GetField( "DateTimeField", dtBirth, Error );
			char szBirth[40] = { 0 };
			DBHelper.DateTime2String( dtBirth, szBirth, sizeof( szBirth ) );

			std::cout << "ID=" << ID << " ";
			std::cout << "Name=" << szName << " ";
			std::cout << "Name=" << strName.c_str() << " ";
			std::cout << "Type=" << Type << " ";
			std::cout << "Type=" << uType << " ";
			std::cout << "BirthDate=" << Birth << " ";
			std::cout << "FloatField=" << fValue << " ";
			std::cout << "DoubleField=" << dValue << " ";
			std::cout << "BoolField=" << bValue << " ";
			std::cout << "DateTimeField=" << szBirth << " ";
			std::cout << std::endl;
		}
	}

	std::string strUptSQL;
	strUptSQL = "update test01 set DoubleField=1.888888 where ID=1";
	if ( sDBConnPool.ExecuteSql( strUptSQL.c_str(), Error ) < 0 )
	{
		printf( "Err,Failed to ExecuteSql:%s,Err=%s\n", strUptSQL.c_str(), Error.c_str() );
	}
}

void stmt_test()
{
	printf("----------------------------------------------------------------\n");
	printf("                          预处理测试                              \n");
	printf("----------------------------------------------------------------\n");

	YADB::_DB_POOL_SETTING_ DBSetting;
	DBSetting.Host     = "localhost";
	DBSetting.User     = "root";
	DBSetting.PWD      = "ok123";
	DBSetting.DBName   = "yaxt";
	DBSetting.CharSet  = "utf8";
	DBSetting.TimeOut  = 5;
	DBSetting.PoolSize = 10;

	std::string Error;

	//创建连接池
	if ( !sDBConnPool.Create( DBSetting, Error ) )
	{
		printf( "线程池创建失败,Err=%s\n", Error.c_str() );
		return;
	}

	YADB::CDBConnect *pConn = sDBConnPool.GetDBConnect( Error );

	//delete old record
	pConn->ExecuteSql( "delete from test01 where ID >=10000", Error );

	//insert new record bystmt
	if ( !pConn->Preparestmt( "insert into test01(ID,Name,Type,BirthDate) values (?,?,?,?)", Error ) )
	{
		printf("Invoke Preparestmt失败,Err=%s\n", Error.c_str());
		return;
	}

	for ( int i = 10000; i <= 10010000; i++ )
	{
		long long LID = i;
		char name[20] = "Jarry";
		long nameLen = strlen(name);
		int type = 2;
		MYSQL_TIME ts;
		ts.year = 2018;
		ts.month = 1;
		ts.day = 13;
		ts.hour = 13;
		ts.minute = 15;
		ts.second = 16;
		ts.second_part = 0;
		ts.time_type = MYSQL_TIMESTAMP_DATE;

		MYSQL_BIND bindData[4];
		memset(bindData, 0, sizeof(bindData));

		bindData[0].buffer_type = MYSQL_TYPE_LONGLONG;
		bindData[0].buffer = (long long *) &LID;
		bindData[0].is_null = 0;

		bindData[1].buffer_type = MYSQL_TYPE_STRING;
		bindData[1].buffer = (char *) name;
		bindData[1].buffer_length = nameLen;
		bindData[1].is_null = 0;
		bindData[1].length = (unsigned long*) &nameLen;

		bindData[2].buffer_type = MYSQL_TYPE_LONG;
		bindData[2].buffer = &type;
		bindData[2].is_null = 0;

		bindData[3].buffer_type = MYSQL_TYPE_DATETIME;
		bindData[3].buffer = (char *) &ts;
		bindData[3].is_null = 0;
		bindData[3].length = 0;

		if ( !pConn->stmtExcute( bindData, 0, Error ) )
		{
			printf("执行预处理失败:%s!\n", Error.c_str());
		}
	}
	printf("预处理完成!\n");
}

int main()
{
	//mysql_base_test();
	simple_db_class_test();
	//thread_db_class_test();
	//stmt_test();

	printf( "Press Enter key to continue...\n" );
	fgetc( stdin );

	printf( "All Done!\n" );
	return 0;
}