封装connect/c++连接mysql

论坛 › 期权论坛 › 脚本     
匿名网站用户   2020-12-21 09:08   87   0

参考前一篇文章

参考网址：http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html#trx


头文件：

#include <iostream>
#include <stdio.h>
#include "mysql_connection.h"
#include "mysql_driver.h"
#include "mysql_error.h"
#include "cppconn/connection.h"
#include "cppconn/statement.h"
#include "cppconn/resultset.h"
#include "cppconn/metadata.h"
#include "cppconn/prepared_statement.h"
#include "cppconn/parameter_metadata.h"
#include "cppconn/sqlstring.h"
#include <string>

#define DBHOST "tcp://127.0.0.1:3306"
#define USER "root"
#define PASSWORD "root"
#define DATABASE "test"

using namespace std;
using namespace sql;
using namespace sql::mysql;

class MysqlOperation
{
public: 
    MysqlOperation();
    virtual ~MysqlOperation();

 bool Connect();
 bool IsAliveConn();
 bool IsCloseConn();
 void Execute(const SQLString& sql);
 bool ExecuteQuery(const SQLString& sql);
 void ExecuteUpdate(const SQLString& sql);
 bool ExecuteProcedure(const SQLString& sql);
 void RollBack(Savepoint * savepoint=NULL);
 void Commit();

 int getColumnCount();
 int getRowCount();
 string getColumnName(int index);
 void PrintDataBaseInfo();

 bool isValueNull(int columnindex);
 bool isValueNull(const string& columnname);
 bool ReConnect();
 void SetTable(const string& tablename);
 int getValueInt(const string& columnname);
 string getValueString(const string& columnname);
 bool getNext();
 
private:
 mysql::MySQL_Driver *driver;
 Connection *conn;
 Statement *state;
 ResultSet *res;
 DatabaseMetaData  *dbcon_meta;
 ResultSetMetaData *rs_meta;
 sql::PreparedStatement *prep_stmt;
 Savepoint *savept;
};

源文件： 

#include "mysql_c++.h"

MysqlOperation::MysqlOperation()
{
 if(!Connect())
 {
  cout << "create the mysqloperation obj err\n";
  return;
 }
 //turn off the autocommit
 conn->setAutoCommit(0);
 conn->setSchema(DATABASE);
 cout << "connet to database OK\n"; 
}

MysqlOperation::~MysqlOperation()
{
 if(res)
 {
  delete res;
  res = NULL;
 }
 if(state)
 {
  delete state;
  state = NULL;
 }
 if(prep_stmt)
 {
  delete prep_stmt;
  prep_stmt = NULL;
 }
 if(conn)
 {
  conn->close();
  delete conn;
  conn = NULL;
 }
}

void MysqlOperation::SetTable(const string& tablename)
{
 conn->setSchema(tablename);
}

bool MysqlOperation::Connect()
{
 
 driver = mysql::get_driver_instance();
 if(driver == NULL)
 {
  cout << "get driver err\n";
  return false;
 }
 conn = driver->connect(DBHOST, USER, PASSWORD);
 state = conn->createStatement();
 if(!conn || !state)
  return false;
 return true;
}

bool MysqlOperation::IsAliveConn()
{
 return conn->isValid()?true:false;
}

bool MysqlOperation::IsCloseConn()
{
 return conn->isClosed()?true:false;
}

int MysqlOperation::getColumnCount()
{
 if(res == NULL)
 {
     return -1;
 }
 rs_meta = res->getMetaData();
 int cols = rs_meta->getColumnCount();
 return cols;
}

int MysqlOperation::getRowCount()
{
 int rows = 0;
 rows = res->rowsCount();
 return rows;
}

void MysqlOperation::Execute(const SQLString& sql)
{
 if(!state)
 {
  state = conn->createStatement();
 }
 state->execute(sql);
}

bool MysqlOperation::ExecuteProcedure(const SQLString& sql)
{
 
}

void MysqlOperation::PrintDataBaseInfo()
{
 if(dbcon_meta == NULL)
 {
  dbcon_meta = conn->getMetaData(); 
 } 
 cout << "Database Product Name: " << dbcon_meta->getDatabaseProductName() << endl;  
    cout << "Database Product Version: " << dbcon_meta->getDatabaseProductVersion() << endl;  
    cout << "Database User Name: " << dbcon_meta->getUserName() << endl << endl;  
  
    cout << "Driver name: " << dbcon_meta->getDriverName() << endl;  
    cout << "Driver version: " << dbcon_meta->getDriverVersion() << endl << endl;  
  
    cout << "Database in Read-Only Mode?: " << dbcon_meta->isReadOnly() << endl;  
    cout << "Supports Transactions?: " << dbcon_meta->supportsTransactions() << endl;  
    cout << "Supports DML Transactions only?: " << dbcon_meta->supportsDataManipulationTransactionsOnly() << endl;  
    cout << "Supports Batch Updates?: " << dbcon_meta->supportsBatchUpdates() << endl;  
    cout << "Supports Outer Joins?: " << dbcon_meta->supportsOuterJoins() << endl;  
    cout << "Supports Multiple Transactions?: " << dbcon_meta->supportsMultipleTransactions() << endl;  
    cout << "Supports Named Parameters?: " << dbcon_meta->supportsNamedParameters() << endl;  
    cout << "Supports Statement Pooling?: " << dbcon_meta->supportsStatementPooling() << endl;  
    cout << "Supports Stored Procedures?: " << dbcon_meta->supportsStoredProcedures() << endl;  
    cout << "Supports Union?: " << dbcon_meta->supportsUnion() << endl << endl;  
  
    cout << "Maximum Connections: " << dbcon_meta->getMaxConnections() << endl;  
    cout << "Maximum Columns per Table: " << dbcon_meta->getMaxColumnsInTable() << endl;  
    cout << "Maximum Columns per Index: " << dbcon_meta->getMaxColumnsInIndex() << endl;  
    cout << "Maximum Row Size per Table: " << dbcon_meta->getMaxRowSize() << " bytes" << endl;  
  
    cout << "/nDatabase schemas: " << endl;  
  
    auto_ptr < ResultSet > rs ( dbcon_meta->getSchemas());  
  
    cout << "/nTotal number of schemas = " << rs->rowsCount() << endl;  
    cout << endl;  
  
}
//execute select sql and return resultset
bool MysqlOperation::ExecuteQuery(const SQLString& sql)
{
 if (!state)
 {
  state = conn->createStatement();
 }
 res = state->executeQuery(sql);
 if(res)
  return true;
 else
  return false;
}

void MysqlOperation::Commit()
{
 conn->commit();
}

void MysqlOperation::RollBack(Savepoint * savepoint)
{
 conn->rollback(savepoint);
 conn->releaseSavepoint(savepoint); 
}

//execute insert,update,delete sql
void MysqlOperation::ExecuteUpdate(const SQLString& sql)
{
 savept = conn->setSavepoint("savept");
 try{
  conn->setTransactionIsolation(TRANSACTION_SERIALIZABLE); //when change the data,we can't read the data
  prep_stmt = conn->prepareStatement(sql); 
  int updatecount = prep_stmt->executeUpdate();
  if(updatecount == 0)
  {
   printf("no rows update\n");
   return;
  }
  Commit();
 }catch(SQLException &e)
 {
  cout << "ERROR: SQLException in " << __FILE__;
  cout << " (" << __func__<< ") on line " << __LINE__ << endl;
  cout << "ERROR: " << e.what();
  cout << " (MySQL error code: " << e.getErrorCode();
  cout << ", SQLState: " << e.getSQLState() << ")" << endl;
  RollBack(savept);
 } 
}

string MysqlOperation::getColumnName(int index)
{
 if(rs_meta == NULL)
 {
  rs_meta = res->getMetaData();
 }
 string columnname = rs_meta->getColumnLabel(index+1);
 return columnname;
}

bool MysqlOperation::isValueNull(int columnindex)
{
 return res->isNull(columnindex)?true:false;
}

bool MysqlOperation::isValueNull(const string& columnname)
{
 return res->isNull(columnname)?true:false;
}

bool MysqlOperation::ReConnect()
{
 return conn->reconnect();
}

int MysqlOperation::getValueInt(const string& columnname)
{
 int value = res->getInt(columnname);
 return value;
}

string MysqlOperation::getValueString(const string& columnname)
{
 string value = res->getString(columnname);
 return value;
}

bool MysqlOperation::getNext()
{
 return res->next();
}

int main(int argc, char *argv[])
{
    MysqlOperation *opera = new MysqlOperation();
 if(!opera->IsAliveConn())
 {
  opera->ReConnect();
 }
 cout << opera->IsAliveConn() << endl;
 opera->SetTable("test");

 opera->PrintDataBaseInfo();

 SQLString sql = "select * from user";
 opera->ExecuteQuery(sql);
 cout << "row num:"<< opera->getRowCount() << " " << "cols num:" << opera->getColumnCount() << endl;

 while(opera->getNext())
 {
  cout << opera->getValueInt("id") << ":" << opera->getValueString("name") << ":" << opera->getValueInt("sex") << ":" << opera->getValueString("password") << endl; 
 }

 SQLString sql2 = "delete from user where name = '@name'";
 sql2.SetParam("@name", "hehe");  //注意：需要修改库文件sqlstring.h
 cout << sql2 << endl;
 opera->ExecuteUpdate(sql2);

 SQLString sql3 = "create table if not exists tt (id int not null auto_increment, name varchar(20) not null, age int, primary key(id))";
 opera->Execute(sql3);
 opera->Execute("drop table tt");
    return 0;
}

修改sqlstring.h，添加 

 void SetParam(const char *name, long val)
  {
   char buf[32];
   sprintf(buf, "%ld", val);
   SetParam(name, buf);
  }
  void SetParam(const char *name, long long val)
  {
   char buf[64];
   sprintf(buf, "%lld", val);
   SetParam(name, buf);
  }
  void SetParam(const char *name, double val)
  {
   char buf[32];
   sprintf(buf, "%.8f", val);
   SetParam(name, buf);
  }
  void SetParam(const char *name, const char *val)
  {
   unsigned int pos = 0;
   int len = strlen(name);
   int newlen = strlen(val);
   pos = realStr.find(name);
   while(pos != -1)
   {
    realStr.replace(pos, len, val);
    pos = realStr.find(name, pos+newlen);
   }
  }

编译测试：

g++ -g -o test mysql_c++.h mysql_c++.cpp  -I/home/laijia/database/include -I/home/laijia/database/include/cppconn -I/usr/include -L/home/laijia/database/lib -L/usr/local/lib -lmysqlcppconn -lmysqlcppconn-static


回复
分享到 :
0 人收藏
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

16级独孤
匿名网站用户

积分:1136255
帖子:227251
精华:0
+ 关注 私信
期权论坛 期权论坛
期权网络科技版权所有
关于我们
联系我们
加入我们
反馈问题
免责声明
积分充值
统一社会信用代码
积分规则
网站地图
爱文库
下属网站
官方
新浪微博
微信公众号
◆
下载
表情包
App下载
期权论坛 期权论坛

QQ咨询|关于我们|Archiver|手机版|小黑屋|( 辽ICP备15012455号-4 ) Powered by 期权论坛 X3.2 © 2001-2016 期权工具网&期权论坛 Inc.

发布
内容

下载期权论坛手机APP