PostgreSQL简单封装

工作需要，虽然有libpqxx，但是目前为了刚刚够用，尽量不要引入第三方库。在网上找到简单的封装http://www.touspassagers.com/2010/12/a-postgresql-database-interface-wrapper-in-c/

 

直接贴上代码:

#include <iostream>#include <stdio.h>#include <string>#include <stdlib.h>#include <libpq-fe.h>#include <map>#include <math.h>#include <string.h>using namespace std;typedef map<int,map<string,string> > map_result;static voidfinish_connection(PGconn *conn){ PQfinish(conn);}class Conn{public: // Methods Conn(char *connstring); ~Conn(); map_result fetch(char *SQL); int insert(char *SQL); void reset();private: // Members PGconn *conn; const char *conninfo; PGresult *res;};Conn::Conn(char *connstring){ // Connect to the DB conninfo = connstring; conn = PQconnectdb(conninfo); // See if the connection took: if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Could not connect to db/n%s", PQerrorMessage(conn)); finish_connection(conn); }}Conn::~Conn(){ finish_connection(conn);}void Conn::reset(){ PQfinish(conn); conn = PQconnectdb(conninfo);}map_result Conn::fetch(char *SQL){ int row, col; map_result results; map<string,string> pairs; // Check the connection: if (PQstatus(conn) != CONNECTION_OK) this->reset(); // Start a transaction: res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "Failed to BEGIN transaction /n%s", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } // Set up fetch query with a CURSOR: string FinalSQL = string("DECLARE myportal CURSOR FOR ") + string(SQL); // Declare CURSOR and execute query: res = PQexec(conn,FinalSQL.c_str()); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "QUERY FAILED/n%s/n", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } PQclear(res); // Fetch results: res = PQexec(conn, "FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL failed/n%s/n", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } else { row = col = 0; for (row=0; row<PQntuples(res); row++) { for(col=0; col<PQnfields(res); col++) { pairs[PQfname(res,col)] = PQgetvalue(res, row, col); } results[row] = pairs; } } return results;}int Conn::insert(char *sql){ // Check the connection: if (PQstatus(conn) != CONNECTION_OK) this->reset(); // Start a transaction: res = PQexec(conn, "START TRANSACTION;"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "Failed to BEGIN transaction /n%s/n", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } // Execute Insert: res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "Failed to execute INSERT /n%s/n", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } // COMMIT transaction: res = PQexec(conn, "COMMIT"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "Failed to COMMIT transaction/n%s/n", PQerrorMessage(conn)); PQclear(res); finish_connection(conn); } return 0;} 

使用代码：

#include <iostream>#include <boost/lexical_cast.hpp>#include "PGConn.hpp"using namespace std;int main(){ map_result res; Conn *postgres = new Conn("dbname=mydb user=postgres"); for( int i=0; i<10; ++i ) { std::string strTemp( "(" +boost::lexical_cast<std::string>(i) + "," ); strTemp += boost::lexical_cast<std::string>(i+1) + ","; strTemp += boost::lexical_cast<std::string>(i+2) + ");"; std::string strSql("INSERT INTO exam_en(language,math,computer) VALUES " + strTemp ); postgres->insert( (char*)strSql.c_str() ); } char sql[] = "SELECT * FROM exam_en;"; res = postgres->fetch(sql); map_result::iterator it = res.begin(); for( ; it!=res.end(); ++it ) { std::cout << "ROW:" << it->first << std::endl; map<string,string>::iterator itField = it->second.begin(); for( ; itField!=it->second.end(); ++itField ) { std::cout << "Field:" << itField->first << " Value:" << itField->second << std::endl; } } return 0;}