linux系统下PostgreSQL的使用

文章目录

前言

最近工作中使用到了pgsql,主要是使用其c++驱动完成数据库创建及增删改查等操作…

一、安装pgsql数据库

使用命令如下:

sudo apt-get install postgresql

安装完成,使用如下命令,确认数据库版本:

psql --version

二、安装c和c++驱动

使用如下命令安装c驱动:

sudo apt-get install libpq-dev

使用如下命令安装c++驱动:

sudo tar -zxvf libpqxx-6.4.8.tar.gz
cd libpqxx-6.4.8/
sudo ./configure --disable-documentation
sudo make
sudo make install

三、使用

1、头文件

头文件如下:

#ifndefPOSTGREOPERATOR_H#definePOSTGREOPERATOR_H#include<iostream>#include<string>#include<map>#include<thread>#include"pqxx/pqxx"usingnamespace std;usingnamespace pqxx;structTableField{
    string name;
    string type;};classPostgreOperator{private:PostgreOperator(){}PostgreOperator(const PostgreOperator&)=delete;
    PostgreOperator&operator=(const PostgreOperator&)=delete;~PostgreOperator();public:static PostgreOperator&getInstance();boolconnect();voiddisConnect();boolinsertOneRow(const string& tableName,const vector<string>& rowData);boolupdateOneRow(const string& tableName,const string& conditionColumnName,const string& conditionValue,const vector<string>& columnNames,const vector<string>& newValues);voidselectRows(const string& tableName, vector<vector<string>>& resultRows,const vector<string>& selectedColumns,const string& conditionColumnName ="",const string& conditionValue ="");booldeleteRows(const string& tableName,const string& conditionColumnName ="",const string& conditionValue ="");private:staticvoidinitTable();staticboolcreatedb(const string& dbname,const string& user,const string& password);staticboolcreateInitTable();staticbooladdTable(const string& tableName,const vector<TableField>& fields);staticbooldeleteTable(const string& tableName);staticbooladdFieldToTable(const string& tableName,const vector<TableField>& fields);staticboolremoveFieldFromTable(const string& tableName,const vector<string>& fieldNames);private:static string m_user;static string m_passwd;static string m_dbName;staticbool m_initTable;static PostgreOperator *m_instance;static map<string, vector<TableField>> m_tables;static connection* m_pConnection;staticthread_local unique_ptr<connection> thread_local_connection_;};#endif// POSTGREOPERATOR_H

2、源文件

源文件如下:

#include"postgreoperator.h"

string      PostgreOperator::m_dbName;
string      PostgreOperator::m_user;
string      PostgreOperator::m_passwd;bool        PostgreOperator::m_initTable =false;
connection* PostgreOperator::m_pConnection =nullptr;

map<string, vector<TableField>> PostgreOperator::m_tables;
PostgreOperator* PostgreOperator::m_instance =nullptr;thread_local unique_ptr<connection> PostgreOperator::thread_local_connection_ =nullptr;

PostgreOperator&PostgreOperator::getInstance(){if(!m_instance){
        m_instance =new PostgreOperator;if(!m_initTable){initTable();createdb("disposaldb","tami","tami");createInitTable();}}return*m_instance;}PostgreOperator::~PostgreOperator(){if(m_pConnection){
        m_pConnection->disconnect();delete m_pConnection;
        m_pConnection =nullptr;}}boolPostgreOperator::createdb(const string& dbname,const string& user,const string& password){
    m_dbName = dbname;
    m_user = user;
    m_passwd = password;
    string connectStr ="dbname=postgres user=postgres password=postgres ""hostaddr=127.0.0.1 port=5432";bool ret =false;try{
        connection *connection =new pqxx::connection(connectStr);if(connection->is_open()){
            nontransaction txn(*connection);
            string quotedDb ="'"+ dbname +"'";if(user.compare("postgres")==0&& password.compare("postgres")==0){//cout<<"hello111 "<<"user ="<<user<<"password ="<<password<<endl;
                string checkDb ="SELECT 1 FROM pg_database WHERE datname = "+ quotedDb;
                pqxx::result result_check = txn.exec(checkDb);if(result_check.empty()){
                    string sql ="CREATE DATABASE "+ dbname +" WITH OWNER= postgres"+" ENCODING='UTF-8' ;";
                    txn.exec(sql);
                    cout <<"create database "+ dbname +" with user=postgres successed!"<< endl;}else{
                    cout <<"database "+ dbname +" already exists!"<<endl;}}else{//cout<<"hello222 "<<"user ="<<user<<" password ="<<password<<endl;
                string quotedUser ="'"+ user +"'";
                string checkUser ="SELECT 1 FROM pg_user WHERE usename = "+ quotedUser;
                pqxx::result result_checkUser = txn.exec(checkUser);if(result_checkUser.empty()){
                    string sql ="CREATE USER "+ user +" WITH PASSWORD '"+ password +"'";
                    txn.exec(sql);}else{
                    cout <<"user "+ user +" already exists!"<<endl;}
                std::string checkDb ="SELECT 1 FROM pg_database WHERE datname = "+ quotedDb;
                pqxx::result result_check = txn.exec(checkDb);if(result_check.empty()){
                    string dbSql ="CREATE DATABASE "+ dbname +" WITH OWNER="+user+" ENCODING='UTF-8';";
                    txn.exec(dbSql);
                    cout <<"create database "+ dbname +" with user="+user+" successed!"<< endl;}else{
                    cout <<"database "+ dbname +" already exists!"<<endl;}}

            ret =true;}else{
            cout<<"open database "+ dbname +" with user=postgres failed!"<<endl;
            connection->disconnect();return ret;}delete connection;
        connection =nullptr;}catch(const std::exception &e){
        cerr<<e.what()<<endl;}return ret;}voidPostgreOperator::initTable(){
    m_tables.clear();
    m_tables ={{"p_frequency",{{"msgId","bigint"},{"sn","bigint"},{"startFrequency","bigint"},{"endFrequency","bigint"},{"rbw","double precision"},{"dataType","smallint"},{"number","integer"},}},{"d_frequency",{{"msgId","bigint"},{"sn","bigint"},{"times","timestamp without time zone"},{"timems","timestamp without time zone"},{"data","smallint[]"},}},{"p_timedomain",{{"msgId","bigint"},{"sn","bigint"},{"centerFrequency","bigint"},{"gain","smallint"},{"timestamp","timestamp without time zone"},{"frameTotal","integer"},{"frameNumber","integer"},{"number","integer"},}},{"d_timedomain",{{"msgId","bigint"},{"sn","bigint"},{"times","timestamp without time zone"},{"timems","timestamp without time zone"},{"data","integer[]"},}},};}boolPostgreOperator::createInitTable(){
    string connectStr ="dbname="+m_dbName+" user="+m_user+" password="+m_passwd+" hostaddr=127.0.0.1 port=5432";bool ret =false;try{
        m_pConnection =new pqxx::connection(connectStr);if(m_pConnection->is_open()){
            nontransaction txn(*m_pConnection);for(constauto& table : m_tables){const std::string tableName = table.first;const std::vector<TableField>& fields = table.second;

                string quotedtable ="'"+ tableName +"'";
                string checktable ="SELECT 1 FROM information_schema.tables WHERE table_name = "+ quotedtable;//cout<<"checktable ="<<checktable<<endl;
                pqxx::result result_check = txn.exec(checktable);if(result_check.empty()){
                    string quotedTableName ="\""+ tableName +"\"";
                    string createTableQuery ="CREATE TABLE "+ quotedTableName +" (";
                    string idStr ="id";
                    string msgIdStr ="msgId";
                    string snStr ="sn";

                    createTableQuery +="\""+ idStr +"\" ";
                    createTableQuery +=" BIGSERIAL, ";for(size_t i =0; i < fields.size(); i++){if(fields[i].name == msgIdStr || fields[i].name == snStr){
                            createTableQuery +="\""+ fields[i].name +"\" "+" "+ fields[i].type;
                            createTableQuery +=" NOT NULL";}else{
                            createTableQuery +="\""+ fields[i].name +"\" "+" "+ fields[i].type;}if(i < fields.size()-1){
                            createTableQuery +=", ";}}

                    createTableQuery +=", PRIMARY KEY (\""+ idStr +"\")";
                    createTableQuery +=")";//cout<<"createTableQuery ="<<createTableQuery<<endl;
                    txn.exec(createTableQuery);
                    cout <<"create table "+ tableName +" succeeded!"<< endl;}else{
                    cout <<"table "+ tableName +" already exists!"<< endl;}}

            ret =true;}}catch(const std::exception& e){
        cerr << e.what()<< endl;}return ret;}boolPostgreOperator::connect(){if(!thread_local_connection_){
        std::string connectStr ="dbname="+ m_dbName +" user="+ m_user +" password="+ m_passwd
                                +" hostaddr=127.0.0.1 port=5432";try{
            thread_local_connection_.reset(new pqxx::connection(connectStr));if(thread_local_connection_->is_open()){
                cout <<"Connected to dbname="<< m_dbName <<" with user="<< m_user <<" succeeded!"<< endl;returntrue;}else{
                cout <<"Failed to connect to dbname="<< m_dbName <<" with user="<< m_user << endl;returnfalse;}}catch(const std::exception& e){
            std::cerr <<"Connection failed: "<< e.what()<< std::endl;returnfalse;}}returntrue;}voidPostgreOperator::disConnect(){if(thread_local_connection_){
        thread_local_connection_->disconnect();
        thread_local_connection_.reset();}}boolPostgreOperator::addFieldToTable(const string& tableName,const vector<TableField>& fields){bool ret =false;try{
        work txn(*m_pConnection);
        string quotedtable ="'"+ tableName +"'";
        string checktable ="SELECT 1 FROM information_schema.tables WHERE table_name = "+ quotedtable;
        pqxx::result result_check = txn.exec(checktable);if(!result_check.empty()){for(constauto& field : fields){
                string columnName ="'"+ field.name +"'";
                string sql ="SELECT column_name FROM information_schema.columns WHERE table_name = '"+ tableName +"' AND column_name = "+columnName;//cout<<"select sql ="<<sql<<endl;
                pqxx::result result = txn.exec(sql);if(result.empty()){
                    string sql ="ALTER TABLE "+ tableName+" ADD COLUMN ";
                    sql +="\""+ field.name +"\" "+" "+ field.type;//cout<<"alter add sql ="<<sql<<endl;
                    txn.exec(sql);
                    cout <<"addFieldToTable successfully."<< endl;
                    ret =true;}else{
                    cout<<"column "+field.name+" of "+tableName+" already exists!"<<endl;}}}else{
           cout <<"table "+ tableName +" does not exist!"<< endl;}
        txn.commit();}catch(const std::exception& e){
        cerr << e.what()<< endl;}return ret;}boolPostgreOperator::removeFieldFromTable(const string& tableName,const vector<string>& fieldNames){bool ret =false;try{
        work txn(*m_pConnection);
        string quotedtable ="'"+ tableName +"'";
        string checktable ="SELECT 1 FROM information_schema.tables WHERE table_name = "+ quotedtable;
        pqxx::result result_check = txn.exec(checktable);if(!result_check.empty()){for(constauto& field : fieldNames){
                string columnName ="'"+ field +"'";
                string sql ="SELECT column_name FROM information_schema.columns WHERE table_name = '"+ tableName +"' AND column_name = "+columnName;//cout<<"select sql ="<<sql<<endl;
                pqxx::result result = txn.exec(sql);if(!result.empty()){
                    string sql ="ALTER TABLE "+ tableName+" DROP COLUMN ";
                    sql +="\""+ field +"\" "+" ";//cout<<"alter drop sql ="<<sql<<endl;
                    txn.exec(sql);
                    cout <<"removeFieldFromTable successfully."<< endl;
                    ret =true;}else{
                    cout<<"column "+field+" of "+tableName+" does not exists!"<<endl;}}}else{
           cout <<"table "+ tableName +" does not exist!"<< endl;}
        txn.commit();}catch(const std::exception& e){
        cerr << e.what()<< endl;}return ret;}boolPostgreOperator::addTable(const string& tableName,const vector<TableField>& fields){bool ret =false;
    work txn(*m_pConnection);

    string quotedtable ="'"+ tableName +"'";
    string checktable ="SELECT 1 FROM information_schema.tables WHERE table_name = "+ quotedtable;//cout<<"checktable ="<<checktable<<endl;
    pqxx::result result_check = txn.exec(checktable);if(result_check.empty()){
        string quotedTableName ="\""+ tableName +"\"";
        string createTableQuery ="CREATE TABLE "+ quotedTableName +" (";
        string idStr ="id";
        string msgIdStr ="msgId";
        string snStr ="sn";

        createTableQuery +="\""+ idStr +"\" ";
        createTableQuery +=" BIGSERIAL, ";
        createTableQuery +="\""+ msgIdStr +"\" ";
        createTableQuery +=" bigint NOT NULL, ";
        createTableQuery +="\""+ snStr +"\" ";
        createTableQuery +=" bigint NOT NULL, ";if(!fields.empty()){for(size_t i =0; i < fields.size(); i++){
                createTableQuery +="\""+ fields[i].name +"\" "+" "+ fields[i].type;if(i < fields.size()-1){
                    createTableQuery +=", ";}}
            createTableQuery +=", PRIMARY KEY (\""+ idStr +"\")";}else{
            createTableQuery +=" PRIMARY KEY (\""+ idStr +"\")";}
        createTableQuery +=")";
        txn.exec(createTableQuery);
        txn.commit();//cout<<"createTableQuery ="<<createTableQuery<<endl;
        cout <<"create table "+ tableName +" succeeded!"<< endl;

        m_tables[tableName]= fields;//        for (const auto& table : m_tables) {//            const string tableName = table.first;//            cout<<"tablename ="<<tableName<<endl;//            const vector<TableField>& fields = table.second;//            for(const auto& field : fields){//                cout<<"name="<<field.name<<"type ="<<field.type;//            }//            cout<<endl;//        }

        ret =true;}else{
        cout <<"table "+ tableName +" already exists!"<< endl;}return ret;}boolPostgreOperator::deleteTable(const string& tableName){bool ret =false;try{
        string sql ="DROP TABLE IF EXISTS "+ tableName;
        pqxx::work txn(*m_pConnection);
        txn.exec(sql);if(!m_tables.empty()){auto it = m_tables.find(tableName);if(it != m_tables.end()){
                m_tables.erase(it);//cout << "Table '" << tableName << "' deleted from m_tables successfully." << endl;
                ret =true;}else{
                cout <<"Table '"<< tableName <<"' not found from m_tables."<< endl;}}
        txn.commit();}catch(const std::exception& e){
        cerr << e.what()<< endl;}return ret;}boolPostgreOperator::insertOneRow(const string& tableName,const vector<string>& rowData){bool ret =false;
    pqxx::work txn(*thread_local_connection_);try{
        std::string sql ="INSERT INTO "+ tableName +" (";for(constauto& table : m_tables){const std::string tableNameStr = table.first;const std::vector<TableField>& fields = table.second;if(tableNameStr == tableName){for(size_t i =0; i < fields.size(); i++){
                    sql +="\""+ fields[i].name +"\" ";if(i != fields.size()-1){
                        sql +=",";}}}}
        sql +=") VALUES (";for(size_t i =0; i < rowData.size(); i++){
            sql +="'"+ rowData[i]+"'";if(i != rowData.size()-1){
                sql +=",";}}

        sql +=")";//cout<<"insert sql ="<<sql<<endl;

        txn.exec(sql);
        txn.commit();

        ret =true;}catch(const std::exception &e){
        cerr<<e.what()<<endl;
        txn.abort();}return ret;}boolPostgreOperator::updateOneRow(const string& tableName,const string& conditionColumnName,const string& conditionValue,const vector<string>& columnNames,const vector<string>& newValues){bool ret =false;try{
        pqxx::work txn(*thread_local_connection_);

        std::string sql ="UPDATE "+ tableName +" SET ";for(size_t i =0; i < columnNames.size(); i++){
            string columnName ="\""+ columnNames[i]+"\" ";
            sql += columnName +" = '"+ newValues[i]+"'";if(i != columnNames.size()-1){
                sql +=",";}}

        string conditionName ="\""+ conditionColumnName +"\" ";
        sql +=" WHERE "+ conditionName +" = '"+ conditionValue +"'";
        txn.exec(sql);
        txn.commit();

        cout <<"Data updated successfully."<< endl;
        ret =true;}catch(const std::exception &e){
        cerr << e.what()<< endl;}return ret;}voidPostgreOperator::selectRows(const string& tableName, vector<vector<string>>& resultRows,const vector<string>& selectedColumns,const string& conditionColumnName,const string& conditionValue){try{
        pqxx::work txn(*thread_local_connection_);

        string sql;// = "SELECT * FROM " + tableName;if(!selectedColumns.empty()){
            sql ="SELECT ";for(size_t i =0; i < selectedColumns.size();++i){
                sql +="\""+ selectedColumns[i]+"\"";if(i < selectedColumns.size()-1)
                    sql +=",";}
            sql +=" FROM "+ tableName;}else{
            sql ="SELECT * FROM "+ tableName;}if(!conditionColumnName.empty()&&!conditionValue.empty()){
            string conditionName ="\""+ conditionColumnName +"\" ";
            sql +=" WHERE "+ conditionName +" = '"+ conditionValue +"'";}

        pqxx::result result = txn.exec(sql);for(constauto& row : result){
            vector<string> record;for(constauto& field : row){//cout << field.name() << ": " << field.c_str() << "     ";
                record.push_back(field.c_str());}
            resultRows.push_back(record);//cout << endl;}

        txn.commit();

        cout <<"Data select successfully."<< endl;}catch(const std::exception &e){
        cerr << e.what()<< endl;}}boolPostgreOperator::deleteRows(const string& tableName,const string& conditionColumnName,const string& conditionValue){bool ret =false;
    pqxx::work txn(*thread_local_connection_);try{
        string sql ="DELETE FROM "+ tableName;if(!conditionColumnName.empty()&&!conditionValue.empty()){
            string conditionName ="\""+ conditionColumnName +"\" ";
            sql +=" WHERE "+ conditionName +" = '"+ conditionValue +"'";}
        cout<<"deleterows sql = "<<sql<<endl;
        txn.exec(sql);
        txn.commit();

        cout <<"Data delete successfully."<< endl;

        ret =true;}catch(const std::exception &e){
        cerr << e.what()<< endl;

        txn.abort();}return ret;}

3、main文件

#include<chrono>#include<iostream>#include<thread>#include"postgreoperator.h"voidinsertfun(int id,PostgreOperator &operatorInstance){if(!operatorInstance.connect()){
        std::cerr <<"Thread "<< id <<" failed to connect to the database."<< std::endl;return;}
    string tableName ="p_frequency";for(int i =0;i<500;i++){
        vector<string> data ={"12345","10086","0","2000000","0.1","1","10"};
        operatorInstance.insertOneRow(tableName, data);}for(int i =500;i<1000;i++){
        vector<string> data ={"67890","10086","0","2000000","0.1","1","10"};
        operatorInstance.insertOneRow(tableName, data);}}voiddeletefun(int id,PostgreOperator &operatorInstance){if(!operatorInstance.connect()){
        std::cerr <<"Thread "<< id <<" failed to connect to the database."<< std::endl;return;}
    string tableName ="p_frequency";
    string conditionColumnName ="msgId";
    string conditionValue ="12345";

    operatorInstance.deleteRows(tableName, conditionColumnName, conditionValue);}intmain(int argc,char*argv[]){
    PostgreOperator& operatorInstance =PostgreOperator::getInstance();// 创建并启动多个线程
    std::vector<std::thread> threads;for(int i =0; i <5;++i){
        threads.emplace_back(insertfun, i,std::ref(operatorInstance));}// 等待所有线程完成for(auto& thread : threads){
        thread.join();}#if0
    std::thread t1(deletefun,5, std::ref(operatorInstance));
    t1.join();#endifreturn1;}

4、编译

命令如下:

g++-pthread --std=c++11-o demo.out main.cpp postgreoperator.cpp -lpqxx -lpq