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、头文件
头文件如下:
#ifndef POSTGREOPERATOR_H
#define POSTGREOPERATOR_H
#include <iostream>
#include <string>
#include <map>
#include <thread>
#include "pqxx/pqxx"
using namespace std;
using namespace pqxx;
struct TableField {
string name;
string type;
};
class PostgreOperator
{
private:
PostgreOperator() {}
PostgreOperator(const PostgreOperator&) = delete;
PostgreOperator& operator=(const PostgreOperator&) = delete;
~PostgreOperator();
public:
static PostgreOperator& getInstance();
bool connect();
void disConnect();
bool insertOneRow(const string& tableName,const vector<string>& rowData);
bool updateOneRow(const string& tableName, const string& conditionColumnName,
const string& conditionValue, const vector<string>& columnNames,
const vector<string>& newValues);
void selectRows(const string& tableName, vector<vector<string>>& resultRows,
const vector<string>& selectedColumns,
const string& conditionColumnName = "",const string& conditionValue = "");
bool deleteRows(const string& tableName, const string& conditionColumnName = "",
const string& conditionValue = "");
private:
static void initTable();
static bool createdb(const string& dbname,const string& user,const string& password);
static bool createInitTable();
static bool addTable(const string& tableName,const vector<TableField>& fields);
static bool deleteTable(const string& tableName);
static bool addFieldToTable(const string& tableName, const vector<TableField>& fields);
static bool removeFieldFromTable(const string& tableName, const vector<string>& fieldNames);
private:
static string m_user;
static string m_passwd;
static string m_dbName;
static bool m_initTable;
static PostgreOperator *m_instance;
static map<string, vector<TableField>> m_tables;
static connection* m_pConnection;
static thread_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;
}
}
bool PostgreOperator::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;
}
void PostgreOperator::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[]"},
}},
};
}
bool PostgreOperator::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 (const auto& 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.name == msgIdStr || fields.name == snStr) {
createTableQuery += "\"" + fields.name + "\" " + " " + fields.type;
createTableQuery += " NOT NULL";
} else {
createTableQuery += "\"" + fields.name + "\" " + " " + fields.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;
}
bool PostgreOperator::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;
return true;
} else {
cout << "Failed to connect to dbname=" << m_dbName << " with user=" << m_user << endl;
return false;
}
} catch (const std::exception& e) {
std::cerr << "Connection failed: " << e.what() << std::endl;
return false;
}
}
return true;
}
void PostgreOperator::disConnect()
{
if (thread_local_connection_) {
thread_local_connection_->disconnect();
thread_local_connection_.reset();
}
}
bool PostgreOperator::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(const auto& 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;
}
bool PostgreOperator::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(const auto& 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;
}
bool PostgreOperator::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.name + "\" " + " " + fields.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 = 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;
}
bool PostgreOperator::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;
}
bool PostgreOperator::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 (const auto& 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.name + "\" ";
if (i != fields.size() - 1) {
sql += ",";
}
}
}
}
sql += ") VALUES (";
for (size_t i = 0; i < rowData.size(); i++) {
sql += "'" + rowData + "'";
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;
}
bool PostgreOperator::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 + "\" ";
sql += columnName + " = '" + newValues + "'";
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;
}
void PostgreOperator::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 + "\"";
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 (const auto& row : result) {
vector<string> record;
for (const auto& 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;
}
}
bool PostgreOperator::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"
void insertfun(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);
}
}
void deletefun(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);
}
int main(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();
}
#if 0
std::thread t1(deletefun, 5, std::ref(operatorInstance));
t1.join();
#endif
return 1;
}
4、编译
命令如下:
g++ -pthread --std=c++11 -o demo.out main.cpp postgreoperator.cpp -lpqxx -lpq
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]