题记
之前项目中使用OTL连接操作Oracle数据库,对于Mysql有用,但没有总结。目前常用的两种连接方式:
方式一:mysql conncetor
(http://dev.mysql.com/downloads/connector/c/), mysql官网提供。
方式二:mysql++。
由于mysql connector我没有用过,不做评价。把mysql ++ 的优点列一下,对比mysql connector:
1)mysql++历史更悠久;
2)mysql++是第三方库;
3)mysql++编程风格:使用 使用原生的C++标准库和STL。而mysql conncetor更像JAVA风格。
4)mysql++更成熟。
原作者给出的比较:
http://www.zeuux.com/group/candcplus/bbs/content/55922/
本文主要详解:
Linux(确切是Centos)下msyql++的安装、增、删、改、查源码封装接口实现。
1、Mysql++作用
Mysql++是官方发布的、为MySQL设计的C++语言的API,这个API的作用是使工作更加简单且容易。
Mysql++为Mysql的C-Api的再次封装,它用STL开发并编写,并为C++开发程序员提供象操作STL容器一样方便的操作数据库的一套机制。
经常使用STL、OTL的朋友,使用起来会非常方便。
下载地址:https://tangentsoft.net/mysql++/
2、Mysql++安装步骤(CentOS release 6.8 (Final))
第1步:安装 libmysqlclient
yum install mysql-devel
libmysqlclient.so安装位置查看:
[root@laoyang testMysqlConn]# rpm -ql mysql-devel
********
/usr/lib64/mysql/libmysqlclient.so
如上,从结果中可以看到libmysqlclient.so在/usr/lib64/mysql/目录下
第2步: 安装mysql3.2.2
1)解压:
mysql3.2.2 .tar.gz ,默认目录为:mysql++-3.2.2
2)配置
./configure –prefix=/usr/local –enable-thread-check –with-mysql-lib=/usr/lib64/mysql
3)编译
make
4)安装
make install
5)修改/etc/ld.so.conf文件,
添加如下内容:
/usr/local/lib
/sbin/ldconfig
/bin/ln -s /usr/local/lib/libmysqlpp.so /usr/lib/libmysqlpp.so
至此,mysql++安装配置ok。
3、Mysql++操作
3.1 核心功能点:
1)通过Mysql++类库,连接Mysql。
2)实现对Mysql的增、删、改、查操作。
3.2 用户手册
API的详细介绍及使用Demo
http://tangentsoft.net/mysql++/doc/html/userman/
3.3 核心接口
MySql++支持三种查询: Query::execute(), Query::store(), Query::use()
1)execute( )接口
用于不返回数据的查询,该函数返回一个SimpleResult对象。
2)exec( )接口
它返回一个bool值,标示执行成功与否;如果只要成功与否的标识,可以使用该接口。
3)store() 接口
用于用服务器获取数据,该函数返回一个StoreQueryResult对象。对象包含了整个查询结果,使用stl::map方式从里面取数据即可。
4)use()接口
同样用于从服务器获取数据,不过该函数返回UseQueryResult对象。相比store()而言更节省内存,该对象类似StoreQueryResult,但是不提供随机访问的特性。use查询会让服务器一次返回结果集的一行。
Query对象的errnum()返回上次执行对应的错误代码,error()返回错误信息,affected_rows()返回受影响的行数。
3.4 实战源码实现:
1)test.cpp内容如下:
#include <mysql++.h>
#include <stdlib.h>
#include <stdio.h>
#include <string>
#include <iostream>
using namespace std;
using namespace mysqlpp;
//insert into cc(id, name, status) values(22, "laoyang", "ok");
const char* g_szInsertFormat = "insert into cc(id, name, status) values(%d, \"%s\", \"%s\");";
const char* g_szUpdateFormat = "update cc set name = \"%s\" where id = %d;";
const char* g_szDeleteFormat = "delete from cc where id = %d;";
const char* g_szSearchFormat = "select * from cc;";
#define DATEBASE_NAME "test"
#define DATEBASE_IP "192.168.1.1"
#define DATEBASE_USERNAME "admin"
#define DATEBASE_PWD "**********"
#define DATA_BUF_SIZE 2048
//增
void insertFun(Query* pQuery)
{
cout << "Inserting test" << endl;
char szInsert[DATA_BUF_SIZE] = {0};
memset(szInsert, 0, DATA_BUF_SIZE);
int iId = 66;
const char* pszName = "Miss Zhangx";
const char* pszStatus = "OK";
sprintf((char*)szInsert, g_szInsertFormat, iId, pszName, pszStatus);
cout << "szInsert = " << szInsert << endl;
*pQuery << szInsert;
SimpleResult res = pQuery->execute();
// Report successful insertion
cout << "Inserted into cc table, ID =" << res.insert_id() << endl;
cout << endl;
}
//删
void deleteFun(Query* pQuery)
{
cout << "deleting test" << endl;
char szDelete[DATA_BUF_SIZE] = {0};
int iId = 44;
memset(szDelete, 0, DATA_BUF_SIZE);
sprintf((char*)szDelete, g_szDeleteFormat, iId);
cout << "szDelete = " << szDelete << endl;
*pQuery << szDelete;
if (pQuery->exec())
{
cout << "deleted success!" << endl;
}
cout << endl;
}
//改
void updateFun(Query* pQuery)
{
cout << "updating test" << endl;
char szUpdate[DATA_BUF_SIZE] = {0};
memset(szUpdate, 0, DATA_BUF_SIZE);
int iId = 2;
const char* pszNewName = "new line 2 revise";
sprintf((char*)szUpdate, g_szUpdateFormat, pszNewName, iId);
cout << "szUpdate = " << szUpdate << endl;
*pQuery << szUpdate;
if (pQuery->exec())
{
cout << "updated success!" << endl;
}
cout << endl;
}
//查
void searchFun(Query* pQuery)
{
/* Now SELECT */
cout << "selecting test:" << endl;
*pQuery << g_szSearchFormat;
StoreQueryResult ares = pQuery->store();
cout << "ares.num_rows() = " << ares.num_rows() << endl;
for (size_t i = 0; i < ares.num_rows(); i++)
{
cout << "id: " << ares[i]["id"] << "\t - Name: " << ares[i]["name"] \
<< "\t - Status: " << ares[i]["status"] << "\t - Modified_at" << ares[i]["modified_at"] << endl;
}
/* Let's get a count of something */
*pQuery << "SELECT COUNT(*) AS row_count FROM cc";
StoreQueryResult bres = pQuery->store();
cout << "Total rows: " << bres[0]["row_count"] << endl;
cout << endl;
}
int main()
{
try
{
Connection conn(false);
conn.connect(DATEBASE_NAME, DATEBASE_IP, DATEBASE_USERNAME, DATEBASE_PWD);
Query query = conn.query();
/*insert , delete , update, search testing */
(void)insertFun(&query);
(void)deleteFun(&query);
(void)updateFun(&query);
(void)searchFun(&query);
}
catch (BadQuery er)
{ // handle any connection or
// query errors that may come up
cerr << "Error: " << er.what() << endl;
return -1;
}
catch (const BadConversion& er)
{
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return -1;
}
catch (const Exception& er)
{
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return -1;
}
return (EXIT_SUCCESS);
}
2)makefile文件:
[root@laoyang testMysqlConn]# cat Makefile
CXX := g++
CXXFLAGS := -I/usr/include/mysql -I/usr/local/include/mysql++
LDFLAGS := -L/usr/lib64/mysql -lmysqlpp -lmysqlclient -lnsl -lz -lm
EXECUTABLE := main
all: test
clean:
rm -f $(EXECUTABLE) *.o
3)执行结果如下:
[root@laoyang testMysqlConn]# ./test
Inserting test
szInsert = insert into cc(id, name, status) values(66, "Miss Zhangx", "OK");
Inserted into cc table, ID =66
deleting test
szDelete = delete from cc where id = 44;
deleted success!
updating test
szUpdate = update cc set name = "new line 2 revise" where id = 2;
updated success!
selecting test:
ares.num_rows() = 11
id: 1 - Name: laoyang360 - Status: ok - Modified_at0000-00-00 00:00:00
id: 2 - Name: new line 2 revise - Status: ok - Modified_at2016-06-23 06:16:42
id: 11 - Name: test11 - Status: ok - Modified_at2016-06-24 02:09:15
id: 5 - Name: jdbc_test_update08 - Status: ok - Modified_at0000-00-00 00:00:00
id: 7 - Name: test7 - Status: ok - Modified_at0000-00-00 00:00:00
id: 8 - Name: test008 - Status: ok - Modified_at0000-00-00 00:00:00
id: 9 - Name: test009 - Status: ok - Modified_at0000-00-00 00:00:00
id: 10 - Name: test10 - Status: ok - Modified_at2016-06-24 02:08:14
id: 22 - Name: laoyang - Status: ok - Modified_at2016-08-27 06:24:05
id: 55 - Name: Miss Zhang - Status: OK - Modified_at2016-08-27 07:40:38
id: 66 - Name: Miss Zhangx - Status: OK - Modified_at2016-08-27 08:41:51
4、易出错点
1) /usr/bin/ld: cannot find -lmysqlclient
修正方法:Makefile文件中包含mysqlclient.so的路径:/usr/lib64/mysql。
2)程序初始编译出“segment fault” 调试方法
第1步:让core显示出来
编辑/root/.bash_profile文件,在其中加入 ulimit -S -c unlimited , 如下:
[root@laoyang testMysqlConn]# tail -f /root/.bash_profile
ulimit -S -c unlimited
第2步:调试
gdb 可执行文件 core文件
gdb test core.10968
5、下一步工作
对mysql++如果可能的化,封装成自己常用的类。目前已经基本相对清晰。
作者:铭毅天下
转载请标明出处,原文地址:
http://blog.csdn.net/laoyang360/article/details/52335669