C++实现MySQL分页查询

看了网上的很多例子，都没有一个比较完整的用C++实现mysql分页查询。这是我自己做的一个例子。分享出来共同学习。

完整例子http://download.csdn.net/detail/u013235609/9484807

一、连接数据库

我用的是mysql API进行数据库的操作。对于添加mysql头文件请参照网上的教程。

<pre name="code" class="cpp">#include "StdAfx.h"
#include "MySQLClass.h"


CConnectMySQL::CConnectMySQL()
{}

CConnectMySQL::~CConnectMySQL()
{
}

//连接数据库
bool CConnectMySQL::ConnectMySQL(CString strIP_Adress,CString strPort,CString strUserName,CString strPass,CString strDataBase)
{
	mysql_init(&m_sqlCon);
	if(!mysql_real_connect(&m_sqlCon,CW2A(strIP_Adress),CW2A(strUserName),CW2A(strPass),CW2A(strDataBase),_tstoi(LPCTSTR(strPort)),NULL,0))
	{
		return false;
	}
	mysql_query(&m_sqlCon,"SET NAMES GBK");//设置编码格式，否则无法显示中文
	return true;
}

void CConnectMySQL::CloseMySQL()
{
	//关闭连接
	mysql_close(&m_sqlCon);
}
//当传入表名时调用此方法进行查询
CQueryInformation* CConnectMySQL::QueryByTableName( CString qu )
{
	CString str1("select * from ");
	CString str = str1 + qu;
	return Query(str);
}

//当输入查询语句时执行
CQueryInformation* CConnectMySQL::Query(CString strQu)
{
	if (strQu =="")
	{
		return 0;
	}	
	int nPageOfNum = 10;
	CQueryInformation *pQueryInf = new CQueryInformation(&m_sqlCon,strQu);
	return pQueryInf;
}

MYSQL_RES* CConnectMySQL::GetTableName()
{
	//查询数据库中所有的表名
	mysql_query(&m_sqlCon,"show tables");
	MYSQL_RES *Result;
	if(!(Result = mysql_store_result(&m_sqlCon)))
	{
		return 0;
	}
	return Result;
}

二、分页查询

#include "StdAfx.h"
#include "QueryInformation.h"


CQueryInformation::CQueryInformation(MYSQL *Sql,CString str, int pag )
	:m_pSql(Sql),m_strQuery(str),m_nPageOfNum(pag),m_nAllPage(0),m_nPage(0),m_nLimStart(0)
{
	CalculateAllRecord();
	SplitQuerySentence();
}

CQueryInformation::CQueryInformation(MYSQL *Sql, CString str)
	:m_pSql(Sql),m_strQuery(str),m_nPageOfNum(1000),m_nAllPage(0),m_nPage(0),m_nLimStart(0)
{
	CalculateAllRecord();
	SplitQuerySentence();
}

CQueryInformation::~CQueryInformation()
{}

//计算总记录数
void CQueryInformation::CalculateAllRecord()
{
	//求总记录数
	USES_CONVERSION;
	if (0 != mysql_real_query(m_pSql,W2A(m_strQuery),(UINT)strlen(W2A(m_strQuery))))
	{
		return;
	}
	//保存查询结果
	MYSQL_RES *Result;//数据集指针
	if (!(Result = mysql_store_result(m_pSql)))
	{
		return;
	}//end if
	m_nAllPage = mysql_num_rows(Result);
	mysql_free_result(Result);
}

//拆分查询语句
void CQueryInformation::SplitQuerySentence()
{
	//拆分查询语句
	CString str1 ("LIMIT");
	CString str2 = m_strQuery;
	str2.MakeUpper();
	int nIndex = str2.Find(str1);
	if ( -1 != nIndex )
	{
		//提取查询语句中的起始索引
		str2.Delete(0,nIndex + 5);	//删除limit及其以前的字符以及去除cstring两端的空格
		str2.Trim();
		int n = str2.Find(',');
		m_nLimStart = _wtoi(str2.Left(n).Trim());
		str2.Delete(0,n+1);
		int len = m_strQuery.GetLength();
		//将查询语句中的分页条件去掉
		m_strQuery.Delete(nIndex,len - nIndex);
	}	//end if
}

//获得总记录数
int CQueryInformation::GetAllPage()
{
	return m_nAllPage;
}

//返回当前页码
int CQueryInformation::GetPage()
{
	return m_nPage;
	
}

//设置页码
MYSQL_RES* CQueryInformation::SetPage(int pag)
{
	//判断设置的页码是否超界
	if ( pag < 0 || pag*m_nPageOfNum >= m_nAllPage )
	{
		return 0;
	}
	else
	{
		m_nPage = pag;
	}
	return PageQuery();
}

//下一页
MYSQL_RES* CQueryInformation::setPageUp()
{	
	//判断查询是否超出了记录总数
	m_nPage++;
	if ( m_nAllPage <= m_nPage*m_nPageOfNum )
	{
		m_nPage--;
	}
	return PageQuery();
}

//上一页
MYSQL_RES* CQueryInformation::setPageDown()
{
	//当页码到达第一页时不再改变页码
	if (0 == m_nPage )
	{
		m_nPage =0;
	}
	else
	{
		m_nPage--;
	}
	return PageQuery();
}

//跳转到第一页
MYSQL_RES* CQueryInformation::ToFirstPage()
{
	m_nPage = 0;
	return PageQuery();
}

//跳转到最后一页
MYSQL_RES* CQueryInformation::ToEndPage()
{
	m_nPage = m_nAllPage / m_nPageOfNum;
	if (0 ==  m_nAllPage % m_nPageOfNum)
	{
		m_nPage--;
	}
	return PageQuery();
}

//获得每页显示的记录条数
int CQueryInformation::GetPageOfNum()
{
	return m_nPageOfNum;
}

//分页查询
MYSQL_RES* CQueryInformation::PageQuery()
{
	USES_CONVERSION;
	//构造分页查询字符串
	CString strPage ;
	strPage.Format(_T("%d"),(m_nPage * m_nPageOfNum + m_nLimStart));
	CString str1= m_strQuery;
	CString str2 (" LIMIT ");
	CString str3 (",");
	CString strPageOfNum;
	strPageOfNum.Format(_T("%d"),m_nPageOfNum);

	//判断下一页的记录是否足够一页的数量
	int n2 = (m_nPage + 1) * m_nPageOfNum ;
	if ( m_nAllPage <= n2 )
	{
		int nNum = m_nPage*m_nPageOfNum;
		int m_nNewPageOfNum = m_nAllPage - nNum;
		strPageOfNum.Format(_T("%d"),m_nNewPageOfNum);
	}
		
	CString strQuery = str1 + str2 + strPage + str3 + strPageOfNum;
	char *ch_que = W2A(strQuery);

	if (0 != mysql_real_query(m_pSql,ch_que,(UINT)strlen(ch_que)))
	{
		return 0;
	}

	//保存查询结果
	MYSQL_RES *Result;//数据集指针
	if (!(Result = mysql_store_result(m_pSql)))
	{
		return 0;
	}//end if
	return Result;
}

完整代码请下载例子：