参考前一篇文章
参考网址: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