看了网上的很多例子,都没有一个比较完整的用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;
}
完整代码请下载例子: