linux系统下PostgreSQL的使用

---

前言

最近工作中使用到了pgsql,重要是使用其c++驱动完成数据库创建及增编削查等利用…
一、安装pgsql数据库

使用命令如下:

1. sudo apt-get install postgresql

复制代码

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

1. psql --version

复制代码

二、安装c和c++驱动

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

1. sudo apt-get install libpq-dev

复制代码

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

1. sudo tar -zxvf libpqxx-6.4.8.tar.gz
2. cd libpqxx-6.4.8/
3. sudo ./configure --disable-documentation
4. sudo make
5. sudo make install

复制代码

三、使用

1、头文件

头文件如下:

1. #ifndef POSTGREOPERATOR_H
2. #define POSTGREOPERATOR_H
4. #include <iostream>
5. #include <string>
6. #include <map>
7. #include <thread>
9. #include "pqxx/pqxx"
11. using namespace std;
12. using namespace pqxx;
14. struct TableField {
15.     string name;
16.     string type;
17. };
19. class PostgreOperator
20. {
21. private:
22.     PostgreOperator() {}
23.     PostgreOperator(const PostgreOperator&) = delete;
24.     PostgreOperator& operator=(const PostgreOperator&) = delete;
25.     ~PostgreOperator();
27. public:
28.     static PostgreOperator& getInstance();
30.     bool connect();
31.     void disConnect();
33.     bool insertOneRow(const string& tableName,const vector<string>& rowData);
34.     bool updateOneRow(const string& tableName, const string& conditionColumnName,
35.                    const string& conditionValue, const vector<string>& columnNames,
36.                    const vector<string>& newValues);
37.     void selectRows(const string& tableName, vector<vector<string>>& resultRows,
38.                     const vector<string>& selectedColumns,
39.                     const string& conditionColumnName = "",const string& conditionValue = "");
40.     bool deleteRows(const string& tableName, const string& conditionColumnName = "",
41.                       const string& conditionValue = "");
43. private:
44.     static void initTable();
45.     static bool createdb(const string& dbname,const string& user,const string& password);
46.     static bool createInitTable();
48.     static bool addTable(const string& tableName,const vector<TableField>& fields);
49.     static bool deleteTable(const string& tableName);
51.     static bool addFieldToTable(const string& tableName, const vector<TableField>& fields);
52.     static bool removeFieldFromTable(const string& tableName, const vector<string>& fieldNames);
54. private:
55.     static string m_user;
56.     static string m_passwd;
57.     static string m_dbName;
58.     static bool m_initTable;
59.     static PostgreOperator *m_instance;
60.     static map<string, vector<TableField>> m_tables;
61.     static connection* m_pConnection;
62.     static thread_local unique_ptr<connection> thread_local_connection_;
63. };
65. #endif // POSTGREOPERATOR_H

复制代码

2、源文件

源文件如下:

1. #include "postgreoperator.h"
3. string      PostgreOperator::m_dbName;
4. string      PostgreOperator::m_user;
5. string      PostgreOperator::m_passwd;
6. bool        PostgreOperator::m_initTable = false;
7. connection* PostgreOperator::m_pConnection = nullptr;
9. map<string, vector<TableField>> PostgreOperator::m_tables;
10. PostgreOperator* PostgreOperator::m_instance = nullptr;
11. thread_local unique_ptr<connection> PostgreOperator::thread_local_connection_ = nullptr;
13. PostgreOperator& PostgreOperator::getInstance()
14. {
15.     if (!m_instance) {
16.         m_instance = new PostgreOperator;
17.         if(!m_initTable){
18.             initTable();
19.             createdb("disposaldb","tami","tami");
20.             createInitTable();
21.         }
22.     }
24.     return *m_instance;
25. }
27. PostgreOperator::~PostgreOperator()
28. {
29.     if(m_pConnection){
30.         m_pConnection->disconnect();
31.         delete m_pConnection;
32.         m_pConnection = nullptr;
33.     }
34. }
36. bool PostgreOperator::createdb(const string& dbname,const string& user,const string& password)
37. {
38.     m_dbName = dbname;
39.     m_user = user;
40.     m_passwd = password;
41.     string connectStr = "dbname=postgres user=postgres password=postgres "
42.                         "hostaddr=127.0.0.1 port=5432";
43.     bool ret = false;
45.     try {
46.         connection *connection = new pqxx::connection(connectStr);
48.         if(connection->is_open()){
49.             nontransaction txn(*connection);
50.             string quotedDb = "'" + dbname + "'";
52.             if(user.compare("postgres") == 0 && password.compare("postgres") == 0){
53.                 //cout<<"hello111 "<<"user ="<<user<<"password ="<<password<<endl;
54.                 string checkDb = "SELECT 1 FROM pg_database WHERE datname = " + quotedDb;
55.                 pqxx::result result_check = txn.exec(checkDb);
56.                 if(result_check.empty()){
57.                     string sql = "CREATE DATABASE " + dbname + " WITH OWNER= postgres"+" ENCODING='UTF-8' ;";
58.                     txn.exec(sql);
59.                     cout << "create database "+ dbname +" with user=postgres successed!" << endl;
60.                 }
61.                 else{
62.                     cout << "database "+ dbname + " already exists!" <<endl;
63.                 }
64.             }
65.             else{
66.                 //cout<<"hello222 "<<"user ="<<user<<" password ="<<password<<endl;
67.                 string quotedUser = "'" + user + "'";
68.                 string checkUser = "SELECT 1 FROM pg_user WHERE usename = " + quotedUser;
69.                 pqxx::result result_checkUser = txn.exec(checkUser);
70.                 if (result_checkUser.empty()) {
71.                     string sql = "CREATE USER " + user + " WITH PASSWORD '" + password + "'";
72.                     txn.exec(sql);
73.                 }
74.                 else{
75.                     cout << "user "+ user + " already exists!" <<endl;
76.                 }
77.                 std::string checkDb = "SELECT 1 FROM pg_database WHERE datname = " + quotedDb;
78.                 pqxx::result result_check = txn.exec(checkDb);
79.                 if(result_check.empty()){
80.                     string dbSql = "CREATE DATABASE " + dbname + " WITH OWNER="+user+" ENCODING='UTF-8';";
81.                     txn.exec(dbSql);
82.                     cout << "create database "+ dbname +" with user="+user+" successed!" << endl;
83.                 }
84.                 else{
85.                     cout << "database "+ dbname + " already exists!" <<endl;
86.                 }
87.             }
89.             ret = true;
90.         }else{
91.             cout<<"open database " + dbname +" with user=postgres failed!"<<endl;
92.             connection->disconnect();
94.             return ret;
95.         }
96.         delete connection;
97.         connection = nullptr;
98.     }catch (const std::exception &e) {
99.         cerr<<e.what()<<endl;
100.     }
102.     return ret;
103. }
105. void PostgreOperator::initTable()
106. {
107.     m_tables.clear();
108.     m_tables = {
109.         {"p_frequency", {
110.             {"msgId", "bigint"},
111.             {"sn", "bigint"},
112.             {"startFrequency", "bigint"},
113.             {"endFrequency", "bigint"},
114.             {"rbw", "double precision"},
115.             {"dataType", "smallint"},
116.             {"number", "integer"},
117.         }},
118.         {"d_frequency", {
119.             {"msgId", "bigint"},
120.             {"sn", "bigint"},
121.             {"times", "timestamp without time zone"},
122.             {"timems", "timestamp without time zone"},
123.             {"data", "smallint[]"},
124.         }},
125.         {"p_timedomain", {
126.              {"msgId", "bigint"},
127.              {"sn", "bigint"},
128.              {"centerFrequency", "bigint"},
129.              {"gain", "smallint"},
130.              {"timestamp", "timestamp without time zone"},
131.              {"frameTotal", "integer"},
132.              {"frameNumber", "integer"},
133.              {"number", "integer"},
134.         }},
135.         {"d_timedomain", {
136.              {"msgId", "bigint"},
137.              {"sn", "bigint"},
138.              {"times", "timestamp without time zone"},
139.              {"timems", "timestamp without time zone"},
140.              {"data", "integer[]"},
141.         }},
142.     };
143. }
145. bool PostgreOperator::createInitTable()
146. {
147.     string connectStr = "dbname="+m_dbName+" user="+m_user+" password="+m_passwd+
148.                         " hostaddr=127.0.0.1 port=5432";
149.     bool ret = false;
151.     try {
152.         m_pConnection = new pqxx::connection(connectStr);
153.         if(m_pConnection->is_open()){
154.             nontransaction txn(*m_pConnection);
156.             for (const auto& table : m_tables) {
157.                 const std::string tableName = table.first;
158.                 const std::vector<TableField>& fields = table.second;
160.                 string quotedtable = "'" + tableName + "'";
161.                 string checktable = "SELECT 1 FROM information_schema.tables WHERE table_name = " + quotedtable;
162.                 //cout<<"checktable ="<<checktable<<endl;
163.                 pqxx::result result_check = txn.exec(checktable);
165.                 if (result_check.empty()) {
166.                     string quotedTableName = """ + tableName + """;
167.                     string createTableQuery = "CREATE TABLE " + quotedTableName + " (";
168.                     string idStr = "id";
169.                     string msgIdStr = "msgId";
170.                     string snStr = "sn";
172.                     createTableQuery += """ + idStr + "" ";
173.                     createTableQuery += " BIGSERIAL, ";
175.                     for (size_t i = 0; i < fields.size(); i++) {
176.                         if (fields[i].name == msgIdStr || fields[i].name == snStr) {
177.                             createTableQuery += """ + fields[i].name + "" " + " " + fields[i].type;
178.                             createTableQuery += " NOT NULL";
179.                         } else {
180.                             createTableQuery += """ + fields[i].name + "" " + " " + fields[i].type;
181.                         }
182.                         if (i < fields.size() - 1) {
183.                             createTableQuery += ", ";
184.                         }
185.                     }
187.                     createTableQuery += ", PRIMARY KEY ("" + idStr + "")";
188.                     createTableQuery += ")";
189.                     //cout<<"createTableQuery ="<<createTableQuery<<endl;
190.                     txn.exec(createTableQuery);
191.                     cout << "create table " + tableName + " succeeded!" << endl;
192.                 } else {
193.                     cout << "table " + tableName + " already exists!" << endl;
194.                 }
195.             }
197.             ret = true;
198.         }
201.     } catch (const std::exception& e) {
202.         cerr << e.what() << endl;
203.     }
205.     return ret;
206. }
208. bool PostgreOperator::connect()
209. {
210.     if (!thread_local_connection_) {
211.         std::string connectStr = "dbname=" + m_dbName + " user=" + m_user + " password=" + m_passwd
212.                                 + " hostaddr=127.0.0.1 port=5432";
213.         try {
214.             thread_local_connection_.reset(new pqxx::connection(connectStr));
216.             if (thread_local_connection_->is_open()) {
217.                 cout << "Connected to dbname=" << m_dbName << " with user=" << m_user << " succeeded!" << endl;
218.                 return true;
219.             } else {
220.                 cout << "Failed to connect to dbname=" << m_dbName << " with user=" << m_user << endl;
221.                 return false;
222.             }
223.         } catch (const std::exception& e) {
224.             std::cerr << "Connection failed: " << e.what() << std::endl;
225.             return false;
226.         }
227.     }
229.     return true;
230. }
232. void PostgreOperator::disConnect()
233. {
234.     if (thread_local_connection_) {
235.         thread_local_connection_->disconnect();
236.         thread_local_connection_.reset();
237.     }
238. }
240. bool PostgreOperator::addFieldToTable(const string& tableName, const vector<TableField>& fields)
241. {
242.     bool ret = false;
244.     try {
245.         work txn(*m_pConnection);
246.         string quotedtable = "'" + tableName + "'";
247.         string checktable = "SELECT 1 FROM information_schema.tables WHERE table_name = " + quotedtable;
248.         pqxx::result result_check = txn.exec(checktable);
250.         if (!result_check.empty()) {
251.             for(const auto& field : fields){
252.                 string columnName = "'" + field.name + "'";
253.                 string sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" + tableName + "' AND column_name = " +columnName;
254.                 //cout<<"select sql ="<<sql<<endl;
255.                 pqxx::result result = txn.exec(sql);
256.                 if(result.empty()){
257.                     string sql = "ALTER TABLE "+ tableName+" ADD COLUMN ";
258.                     sql += """ + field.name + "" " + " " + field.type;
259.                     //cout<<"alter add sql ="<<sql<<endl;
260.                     txn.exec(sql);
261.                     cout << "addFieldToTable successfully." << endl;
262.                     ret = true;
263.                 }
264.                 else{
265.                     cout<<"column "+field.name+" of "+tableName+" already exists!"<<endl;
266.                 }
267.             }
268.         }
269.         else{
270.            cout << "table " + tableName + " does not exist!" << endl;
271.         }
272.         txn.commit();
273.     } catch (const std::exception& e) {
274.         cerr << e.what() << endl;
275.     }
277.     return ret;
278. }
280. bool PostgreOperator::removeFieldFromTable(const string& tableName, const vector<string>& fieldNames)
281. {
282.     bool ret = false;
284.     try {
285.         work txn(*m_pConnection);
286.         string quotedtable = "'" + tableName + "'";
287.         string checktable = "SELECT 1 FROM information_schema.tables WHERE table_name = " + quotedtable;
288.         pqxx::result result_check = txn.exec(checktable);
290.         if (!result_check.empty()) {
291.             for(const auto& field : fieldNames){
292.                 string columnName = "'" + field + "'";
293.                 string sql = "SELECT column_name FROM information_schema.columns WHERE table_name = '" + tableName + "' AND column_name = " +columnName;
294.                 //cout<<"select sql ="<<sql<<endl;
295.                 pqxx::result result = txn.exec(sql);
296.                 if(!result.empty()){
297.                     string sql = "ALTER TABLE "+ tableName+" DROP COLUMN ";
298.                     sql += """ + field + "" " + " ";
299.                     //cout<<"alter drop sql ="<<sql<<endl;
300.                     txn.exec(sql);
301.                     cout << "removeFieldFromTable successfully." << endl;
302.                     ret = true;
303.                 }
304.                 else{
305.                     cout<<"column "+field+" of "+tableName+" does not exists!"<<endl;
306.                 }
307.             }
308.         }
309.         else{
310.            cout << "table " + tableName + " does not exist!" << endl;
311.         }
312.         txn.commit();
313.     } catch (const std::exception& e) {
314.         cerr << e.what() << endl;
315.     }
317.     return ret;
318. }
320. bool PostgreOperator::addTable(const string& tableName, const vector<TableField>& fields)
321. {
322.     bool ret = false;
323.     work txn(*m_pConnection);
325.     string quotedtable = "'" + tableName + "'";
326.     string checktable = "SELECT 1 FROM information_schema.tables WHERE table_name = " + quotedtable;
327.     //cout<<"checktable ="<<checktable<<endl;
328.     pqxx::result result_check = txn.exec(checktable);
330.     if (result_check.empty()) {
331.         string quotedTableName = """ + tableName + """;
332.         string createTableQuery = "CREATE TABLE " + quotedTableName + " (";
333.         string idStr = "id";
334.         string msgIdStr = "msgId";
335.         string snStr = "sn";
337.         createTableQuery += """ + idStr + "" ";
338.         createTableQuery += " BIGSERIAL, ";
339.         createTableQuery += """ + msgIdStr + "" ";
340.         createTableQuery += " bigint NOT NULL, ";
341.         createTableQuery += """ + snStr + "" ";
342.         createTableQuery += " bigint NOT NULL, ";
344.         if(!fields.empty()){
345.             for (size_t i = 0; i < fields.size(); i++) {
346.                 createTableQuery += """ + fields[i].name + "" " + " " + fields[i].type;
347.                 if (i < fields.size() - 1) {
348.                     createTableQuery += ", ";
349.                 }
350.             }
351.             createTableQuery += ", PRIMARY KEY ("" + idStr + "")";
352.         }
353.         else{
354.             createTableQuery += " PRIMARY KEY ("" + idStr + "")";
355.         }
356.         createTableQuery += ")";
357.         txn.exec(createTableQuery);
358.         txn.commit();
359.         //cout<<"createTableQuery ="<<createTableQuery<<endl;
360.         cout << "create table " + tableName + " succeeded!" << endl;
362.         m_tables[tableName] = fields;
364. //        for (const auto& table : m_tables) {
365. //            const string tableName = table.first;
366. //            cout<<"tablename ="<<tableName<<endl;
368. //            const vector<TableField>& fields = table.second;
369. //            for(const auto& field : fields){
370. //                cout<<"name="<<field.name<<"type ="<<field.type;
371. //            }
372. //            cout<<endl;
373. //        }
375.         ret = true;
376.     } else {
377.         cout << "table " + tableName + " already exists!" << endl;
378.     }
380.     return ret;
381. }
383. bool PostgreOperator::deleteTable(const string& tableName)
384. {
385.     bool ret = false;
387.     try {
388.         string sql = "DROP TABLE IF EXISTS " + tableName;
389.         pqxx::work txn(*m_pConnection);
390.         txn.exec(sql);
392.         if(!m_tables.empty()){
393.             auto it = m_tables.find(tableName);
394.             if (it != m_tables.end()) {
395.                 m_tables.erase(it);
396.                 //cout << "Table '" << tableName << "' deleted from m_tables successfully." << endl;
397.                 ret = true;
398.             } else {
399.                 cout << "Table '" << tableName << "' not found from m_tables." << endl;
400.             }
401.         }
402.         txn.commit();
403.     } catch (const std::exception& e) {
404.         cerr << e.what() << endl;
405.     }
407.     return ret;
408. }
410. bool PostgreOperator::insertOneRow(const string& tableName,const vector<string>& rowData)
411. {
412.     bool ret = false;
413.     pqxx::work txn(*thread_local_connection_);
415.     try {
416.         std::string sql = "INSERT INTO " + tableName + " (";
418.         for (const auto& table : m_tables) {
419.             const std::string tableNameStr = table.first;
420.             const std::vector<TableField>& fields = table.second;
422.             if(tableNameStr == tableName){
423.                 for (size_t i = 0; i < fields.size(); i++) {
424.                     sql += """ + fields[i].name + "" ";
426.                     if (i != fields.size() - 1) {
427.                         sql += ",";
428.                     }
429.                 }
430.             }
431.         }
432.         sql += ") VALUES (";
433.         for (size_t i = 0; i < rowData.size(); i++) {
434.             sql += "'" + rowData[i] + "'";
436.             if (i != rowData.size() - 1) {
437.                 sql += ",";
438.             }
439.         }
441.         sql += ")";
442.         //cout<<"insert sql ="<<sql<<endl;
444.         txn.exec(sql);
445.         txn.commit();
447.         ret = true;
448.     } catch (const std::exception &e) {
449.         cerr<<e.what()<<endl;
450.         txn.abort();
451.     }
453.     return ret;
454. }
456. bool PostgreOperator::updateOneRow(const string& tableName, const string& conditionColumnName,
457.                const string& conditionValue, const vector<string>& columnNames,
458.                const vector<string>& newValues)
459. {
460.     bool ret = false;
462.     try {
463.         pqxx::work txn(*thread_local_connection_);
465.         std::string sql = "UPDATE " + tableName + " SET ";
467.         for (size_t i = 0; i < columnNames.size(); i++) {
468.             string columnName = """ + columnNames[i] + "" ";
469.             sql += columnName + " = '" + newValues[i] + "'";
471.             if (i != columnNames.size() - 1) {
472.                 sql += ",";
473.             }
474.         }
476.         string conditionName = """ + conditionColumnName + "" ";
477.         sql += " WHERE " + conditionName + " = '" + conditionValue + "'";
478.         txn.exec(sql);
479.         txn.commit();
481.         cout << "Data updated successfully." << endl;
482.         ret = true;
483.     } catch (const std::exception &e) {
484.         cerr << e.what() << endl;
485.     }
487.     return ret;
488. }
490. void PostgreOperator::selectRows(const string& tableName, vector<vector<string>>& resultRows, const vector<string>& selectedColumns,
491.                                  const string& conditionColumnName,const string& conditionValue)
492. {
493.     try {
494.         pqxx::work txn(*thread_local_connection_);
496.         string sql;// = "SELECT * FROM " + tableName;
497.         if (!selectedColumns.empty()) {
498.             sql = "SELECT ";
499.             for (size_t i = 0; i < selectedColumns.size(); ++i) {
500.                 sql += """ + selectedColumns[i] + """;
501.                 if (i < selectedColumns.size() - 1)
502.                     sql += ",";
503.             }
504.             sql += " FROM " + tableName;
505.         } else {
506.             sql = "SELECT * FROM " + tableName;
507.         }
509.         if(!conditionColumnName.empty() && !conditionValue.empty()) {
510.             string conditionName = """ + conditionColumnName + "" ";
511.             sql += " WHERE " + conditionName + " = '" + conditionValue + "'";
512.         }
514.         pqxx::result result = txn.exec(sql);
515.         for (const auto& row : result) {
516.             vector<string> record;
517.             for (const auto& field : row) {
518.                 //cout << field.name() << ": " << field.c_str() << "     ";
519.                 record.push_back(field.c_str());
520.             }
521.             resultRows.push_back(record);
522.             //cout << endl;
523.         }
525.         txn.commit();
527.         cout << "Data select successfully." << endl;
528.     } catch (const std::exception &e) {
529.         cerr << e.what() << endl;
530.     }
531. }
533. bool PostgreOperator::deleteRows(const string& tableName, const string& conditionColumnName,
534.                                    const string& conditionValue)
535. {
536.     bool ret = false;
537.     pqxx::work txn(*thread_local_connection_);
539.     try {
540.         string sql = "DELETE FROM " + tableName;
541.         if(!conditionColumnName.empty() && !conditionValue.empty()) {
542.             string conditionName = """ + conditionColumnName + "" ";
543.             sql += " WHERE " + conditionName + " = '" + conditionValue + "'";
544.         }
545.         cout<<"deleterows sql = "<<sql<<endl;
546.         txn.exec(sql);
547.         txn.commit();
549.         cout << "Data delete successfully." << endl;
551.         ret = true;
552.     } catch (const std::exception &e) {
553.         cerr << e.what() << endl;
555.         txn.abort();
556.     }
558.     return ret;
559. }

复制代码

3、main文件

1. #include <chrono>
2. #include <iostream>
3. #include <thread>
5. #include "postgreoperator.h"
7. void insertfun(int id,PostgreOperator &operatorInstance) {
8.     if (!operatorInstance.connect()) {
9.         std::cerr << "Thread " << id << " failed to connect to the database." << std::endl;
10.         return;
11.     }
12.     string tableName = "p_frequency";
14.     for(int i = 0;i<500;i++){
15.         vector<string> data = {"12345", "10086", "0", "2000000", "0.1", "1", "10"};
16.         operatorInstance.insertOneRow(tableName, data);
17.     }
19.     for(int i = 500;i<1000;i++){
20.         vector<string> data = {"67890", "10086", "0", "2000000", "0.1", "1", "10"};
21.         operatorInstance.insertOneRow(tableName, data);
22.     }
23. }
25. void deletefun(int id,PostgreOperator &operatorInstance)
26. {
27.     if (!operatorInstance.connect()) {
28.         std::cerr << "Thread " << id << " failed to connect to the database." << std::endl;
29.         return;
30.     }
31.     string tableName = "p_frequency";
32.     string conditionColumnName = "msgId";
33.     string conditionValue = "12345";
35.     operatorInstance.deleteRows(tableName, conditionColumnName, conditionValue);
36. }
38. int main(int argc, char *argv[])
39. {
40.     PostgreOperator& operatorInstance = PostgreOperator::getInstance();
41.     // 创建并启动多个线程
42.     std::vector<std::thread> threads;
43.     for (int i = 0; i < 5; ++i) {
44.         threads.emplace_back(insertfun, i,std::ref(operatorInstance));
45.     }
47.     // 等待所有线程完成
48.     for (auto& thread : threads) {
49.         thread.join();
50.     }
52. #if 0
53.     std::thread t1(deletefun, 5, std::ref(operatorInstance));
54.     t1.join();
56. #endif
58.     return 1;
59. }

复制代码

4、编译

命令如下:

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。