一、记录安装PG的命令(防丢失)
详细见引用: https://www.howtoing.com/ubuntu-postgresql-installation/
1)安装命令
sudo apt-get -y install postgresql postgresql-contrib phppgadmin
2)登录PG并进入命令行
sudo su
su - postgrespsql
命令行内容
\password postgres
ENTER YOUR PASSWORD然后输入\ q离开psql命令行。
exit
修改配置
cd /etc/apache2/conf-available/
nano phppgadmin.conf注释行#Require本地通过在行前添加一个#,并添加所有的行允许,以便您可以从浏览器访问。
cd /etc/phppgadmin/
nano config.inc.php
找到行$ conf ['extra_login_security'] = true; 并将值更改为false ,以便您可以使用用户postgres登录到phpPgAdmin
二、QT调用PG代码
2.1 程序例程
2.1.1 程序运行示例
1)程序例子:
C币设置为0了,直接下载就可以了。
2)数据库读取结果,需要Open,指示灯为绿色之后,才能点击“GetTable”。其中test和test2为数据库,Name和id为列,属性值显示在右框中(已读取进内存,显示待做)。
其中连接配置是从xml文件中读取的。
3) 附带一个socket通讯测试,有需要的同学可以直接调用里面的类。发送和接收都会显示在Comand上面。
4)xml和socket封装成类了,可以直接使用,由于是学习用的,可能存在不少bug。
5)setting文件配置
文件mainwindow.cpp中变量m_qszSettingsPath保存了Setting文件的路径,创建一个名字为Settings.xml的文件,然后把下面的配置复制进去即可。路径根据需要修改。
<?xml version='1.0' encoding='UTF-8'?>
<Settings>
<PGDBHostName>127.0.0.1</PGDBHostName>
<PGDBName>testDB</PGDBName>
<PGDBPassword>sa</PGDBPassword>
<PGDBPort>5432</PGDBPort>
<PGDBUserName>postgres</PGDBUserName>
<ClientIP>192.188.10.146</ClientIP>
<ClientPort>5000</ClientPort>
</Settings>
2.2 配置数据库工程
1)首先在Pro文件里加上QT += sql
2)头文件增加:
#include <QSqlDatabase>
#include "qsqlquery.h"
2.3 连接数据库代码实现
1)首先创建实例对象
QSqlDatabase *db=new QSqlDatabase(QSqlDatabase::addDatabase("QPSQL"));
2)连接数据库
db->setHostName("127.0.0.1"); //服务器IP地址
db->setPort(5432); // 服务使用的端口
db->setUserName("postgres"); //用户名
db->setPassword("sa"); //密码
db->setDatabaseName("testDB"); //数据库名称
注:附件例子中根据配置文件读入连接信息
2.4 数据库相关操作
2.4.1 获取所有的表
QList<QString> DBManager::GetAllTables(){
QStringList tables = db->tables(); //获取数据库中的表
return m_pCommon->ConvertStringlistToList(tables);}在QT5中,QStringList不能直接转换为QList,所以另外做了个函数转换
QList<QString> MainWindow::ConvertStringlistToList(QStringList &strList)
{
QStringListIterator itTmp(strList);
QList<QString> szList;
while (itTmp.hasNext())
{
QString tableName = itTmp.next().toLocal8Bit();
szList.append(tableName);
}
return szList;
}
2.4.2 获取列属性
使用select语句读取出列属性,然后把需要的属性赋值给结构体ColumnsPro,结构体格式见Common文件。自定义的,不需要纠结。
QMap<QString, DBColumnProperty> DBManager::GetDBColumnPro(QString qstrTableName)
{
QSqlQuery query(*db); QString strSQl = QString("select * from information_schema.columns where table_name= '%1' and table_schema='public'").arg(qstrTableName);
query.prepare(strSQl);
if (query.exec())
{
query.size(); QMap<QString, DBColumnProperty> m_tmpProperty;
while (query.next())
{
DBColumnProperty ColumnsPro;
ColumnsPro.qszName = query.value("column_name").toString();
ColumnsPro.qszIsNullAble = query.value("is_nullable").toString();
ColumnsPro.qszDataType = query.value("data_type").toString();
m_tmpProperty.insert(ColumnsPro.qszName, ColumnsPro);
} return m_tmpProperty;
}
}2.4.3 结构体ColumnsPro
struct DBColumnProperty
{
QString qszName;
QString qszIsNullAble;
QString qszDataType;
};
2.5 附带几个关键类
1)Tcp客户端类
#include "tcpclient.h"
TCPClient::TCPClient()
{
}
void TCPClient::TCPConnect(QString qszIP, int nPort)
{
QIODevice::OpenMode tmpOpenMode;
tmpOpenMode |= QIODevice::ReadWrite;
m_socket.connectToHost(qszIP, nPort, tmpOpenMode);
}
bool TCPClient::IsConnect()
{
return m_socket.isOpen() && m_socket.state() == QAbstractSocket::ConnectedState;
}
void TCPClient::DisConnect()
{
m_socket.close();
}
void TCPClient::Write(QString qszSend)
{
m_socket.write(qszSend.toLatin1());
m_socket.flush();
}
QString TCPClient::Read()
{
QString str = "";
QByteArray buffer;
buffer = m_socket.readAll();
if(!buffer.isEmpty())
{
str = QString(buffer);
}
return str;
}
2)xml读写类
#include "xmlhelper.h"
#include <QDir>
#include <QTextStream>
XmlHelper::XmlHelper()
{
}
//反序列号成map,无子节点结构,用来做简单的配置文件
QString XmlHelper::DeSerializationToMap(QString qszFilePath, QMap<QString, QString> * pQMapResult)
{
QFile file(qszFilePath);
QIODevice::OpenMode tmpOpenMode;
tmpOpenMode |= QIODevice::ReadOnly;
if(!file.open(tmpOpenMode))
{
file.close();
return "file open failed";
}
QDomDocument doc;
if(!doc.setContent(&file))
{
file.close();
return "xml setContent failed";
}
file.close();
QDomElement root=doc.documentElement(); //返回根节点
QDomNode node=root.firstChild(); //获得第一个子节点
while(!node.isNull()) //如果节点不空
{
if(node.isElement()) //如果节点是元素
{
QDomElement tmpElement = node.toElement(); //转换为元素
QString szName = tmpElement.tagName();
QString szValue = tmpElement.text();
pQMapResult->insert(szName, szValue);
}
node=node.nextSibling(); //下一个兄弟节点
}
return "ok";
}
QString XmlHelper::SerializationToXml(QString qszFilePath, QMap<QString, QString> qmapResult)
{
CreateEmptyXml(qszFilePath);
QFile file(qszFilePath);
QIODevice::OpenMode tmpOpenMode;
tmpOpenMode |= QIODevice::ReadOnly;
if(!file.open(tmpOpenMode))
{
file.close();
return "file open failed";
}
QDomDocument doc;
if(!doc.setContent(&file))
{
file.close();
return "xml setContent failed";
}
file.close();
QMap<QString, QDomNode> qmapTmpExist; //存储已经存在的节点
QDomElement root=doc.documentElement(); //返回根节点
QDomNode node=root.firstChild(); //获得第一个子节点
while(!node.isNull()) //如果节点不空
{
if(node.isElement()) //如果节点是元素
{
QDomElement tmpElement = node.toElement(); //转换为元素
QString szName = tmpElement.tagName();
QString szValue = tmpElement.text();
qmapTmpExist.insert(szName, node);
//删除重复节点
/* if(qmapResult.contains(szName))
{
QDomText text = doc.createTextNode(qmapResult[szName]);
tmpElement.setNodeValue("");
tmpElement.appendChild(text);
QString STRT = tmpElement.text();
}*/
}
node = node.nextSibling(); //下一个兄弟节点
}
//插入不存在的节点
QMap<QString, QString>::Iterator it = qmapResult.begin();
while(it != qmapResult.end())
{
QString szName = QString(it.key());
QDomElement newnode = doc.createElement(szName);
QDomText text = doc.createTextNode(qmapResult[szName]);
newnode.appendChild(text);
if(!qmapTmpExist.contains(szName))
{
root.appendChild(newnode);
}
else
{
root.replaceChild(newnode, qmapTmpExist[szName]);
}
it++;
}
QIODevice::OpenMode tmpOpenMode1;
tmpOpenMode1 |= QIODevice::ReadWrite;
tmpOpenMode1 |= QIODevice::Truncate;
file.open(tmpOpenMode1);
QTextStream out(&file);
doc.save(out, 0);
file.close();
return "ok";
}
//创建空白xml文件, 自动创建最后一个文件夹,创建失败或路径不存在则false
bool XmlHelper::CreateEmptyXml(QString qszPath)
{
QString tmpDir = qszPath.left(qszPath.lastIndexOf("/"));
QDir *pDirFolder = new QDir();
if(!pDirFolder->exists(tmpDir))
{
if(!pDirFolder->mkdir(tmpDir))
return false;
}
QFile file(qszPath);
if(!file.exists())
{
QIODevice::OpenMode tmpOpenMode;
tmpOpenMode |= QIODevice::ReadWrite;
tmpOpenMode |= QIODevice::Text;
file.open(tmpOpenMode);
QDomDocument docXml;
QString strHeader( "version=\"1.0\" encoding=\"UTF-8\"" );
docXml.appendChild(docXml.createProcessingInstruction("xml", strHeader));
QDomElement root_elem = docXml.createElement( "Settings" );
docXml.appendChild( root_elem );
QTextStream out(&file);
docXml.save(out, 0);
file.close();
}
return true;
}
3)数据库处理类
#include "dbmanager.h"
#include <QSqlDatabase>
#include "qsqlquery.h"
#include "common.h"
QSqlDatabase *db=new QSqlDatabase(QSqlDatabase::addDatabase("QPSQL"));
Common * m_pCommon = new Common();
DBManager::DBManager()
{
db->setHostName("127.0.0.1"); //服务器IP地址
db->setPort(5432); // 服务使用的端口
db->setUserName("postgres"); //用户名
db->setPassword("sa"); //密码
db->setDatabaseName("testDB"); //数据库名称
}
void DBManager::DBSet(QString qszHostName, int nPort, QString qszUserName, QString qszPasswd, QString qszDBName)
{
db->setHostName(qszHostName); //服务器IP地址
db->setPort(nPort); // 服务使用的端口
db->setUserName(qszUserName); //用户名
db->setPassword(qszPasswd); //密码
db->setDatabaseName(qszDBName); //数据库名称
}
void DBManager::DBOpen()
{
db->open();
}
void DBManager::DBClose()
{
db->close();
}
bool DBManager::IsOpen()
{
return db->isOpen();
}
QList<QString> DBManager::GetAllTables()
{
QStringList tables = db->tables(); //获取数据库中的表
return m_pCommon->ConvertStringlistToList(tables);
}
QMap<QString, DBColumnProperty> DBManager::GetDBColumnPro(QString qstrTableName)
{
QSqlQuery query(*db);
QString strSQl = QString("select * from information_schema.columns where table_name= '%1' and table_schema='public'").arg(qstrTableName);
query.prepare(strSQl);
if (query.exec())
{
query.size();
QMap<QString, DBColumnProperty> m_tmpProperty;
while (query.next())
{
DBColumnProperty ColumnsPro;
ColumnsPro.qszName = query.value("column_name").toString();
ColumnsPro.qszIsNullAble = query.value("is_nullable").toString();
ColumnsPro.qszDataType = query.value("data_type").toString();
m_tmpProperty.insert(ColumnsPro.qszName, ColumnsPro);
}
return m_tmpProperty;
}
}
4)界面类
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "QDateTime"
MainWindow::MainWindow(QWidget *parent)
: QMainWindow(parent)
, ui(new Ui::MainWindow)
{
ui->setupUi(this);
m_pDbManager = new DBManager();
m_pTimerDBState = new QTimer(this);
m_pTimerDataRev = new QTimer(this);
m_pCommon = new Common();
m_XmlHelper = new XmlHelper();
m_pTcpClient = new TCPClient();
connect(m_pTimerDBState, SIGNAL(timeout()), this, SLOT(on_timerDBState_timeout()));
connect(m_pTimerDataRev, SIGNAL(timeout()), this, SLOT(on_timerDataRev_timeout()));
m_qszSettingsPath = "/home/hzz/proText/Settings.xml";
QMap<QString, QString> qmapXmlResult;
m_XmlHelper->DeSerializationToMap(m_qszSettingsPath, &qmapXmlResult);
ui->txtHostName->insertPlainText(qmapXmlResult["PGDBHostName"]);
ui->txtPort->insertPlainText(qmapXmlResult["PGDBPort"]);
ui->txtUserName->insertPlainText(qmapXmlResult["PGDBUserName"]);
ui->txtDBName->insertPlainText(qmapXmlResult["PGDBName"]);
ui->txtPassword->insertPlainText(qmapXmlResult["PGDBPassword"]);
ui->txtClientIP->insertPlainText(qmapXmlResult["ClientIP"]);
ui->txtClientPort->insertPlainText(qmapXmlResult["ClientPort"]);
m_pTimerDBState->start();
m_pTimerDataRev->start();
//控件外观设置
ui->lampDB->setStyleSheet(QStringLiteral("border-image: url(/home/hzz/proImage/lamp1.png)"));
m_pCommon->SetWidgetWindowColor(ui->centralwidget, Qt::white);
}
MainWindow::~MainWindow()
{
delete ui;
}
void MainWindow::on_btnDBOpen_clicked()
{
m_pDbManager->DBSet(ui->txtHostName->toPlainText(), ui->txtPort->toPlainText().toInt(), ui->txtUserName->toPlainText(),
ui->txtPassword->toPlainText(), ui->txtDBName->toPlainText());
m_pDbManager->DBOpen();
}
void MainWindow::on_btnClose_clicked()
{
m_pDbManager->DBClose();
}
void MainWindow::on_timerDBState_timeout()
{
if(m_pDbManager->IsOpen())
{
m_pCommon->SetButtonColor(ui->lampDB, Qt::green);
}
else
{
m_pCommon->SetButtonColor(ui->lampDB, Qt::black);
}
if(m_pTcpClient->IsConnect())
{
m_pCommon->SetButtonColor(ui->lampTCP, Qt::green);
}
else
{
m_pCommon->SetButtonColor(ui->lampTCP, Qt::black);
}
}
void MainWindow::on_timerDataRev_timeout()
{
if(m_pTcpClient->IsConnect())
{
QString qszRead = m_pTcpClient->Read();
if(qszRead.length() > 0)
AddCMDString(qszRead);
}
}
void MainWindow::on_btnTest_clicked()
{
QString filePath = "/home/hzz/proText/Settings.xml";
QMap<QString, QString> qmapXmlResult;
qmapXmlResult.insert("PGDBHostName", ui->txtHostName->toPlainText());
qmapXmlResult.insert("PGDBPort", ui->txtPort->toPlainText());
qmapXmlResult.insert("PGDBUserName", ui->txtUserName->toPlainText());
qmapXmlResult.insert("PGDBName", ui->txtDBName->toPlainText());
qmapXmlResult.insert("PGDBPassword", ui->txtPassword->toPlainText());
qmapXmlResult.insert("ClientIP", ui->txtClientIP->toPlainText());
qmapXmlResult.insert("ClientPort", ui->txtClientPort->toPlainText());
m_XmlHelper->SerializationToXml(filePath, qmapXmlResult);
return;
// QMap<QString, QString> qmapXmlResult;
// m_XmlHelper->DeSerializationToMap(filePath, &qmapXmlResult);
// QString filePath=QString("%1/%2").arg( qApp->applicationDirPath()).arg("Settings.xml");
/* QFile file(filePath);
QIODevice::OpenMode tmpOpenMode;
tmpOpenMode |= QIODevice::ReadOnly;
if(!file.open(tmpOpenMode))
{
file.close();
}
QDomDocument doc;
if(!doc.setContent(&file))
{
file.close();
}
file.close();
QDomElement root=doc.documentElement(); //返回根节点
QDomNode node=root.firstChild(); //获得第一个子节点
while(!node.isNull()) //如果节点不空
{
if(node.isElement()) //如果节点是元素
{
QDomElement tmpElement = node.toElement(); //转换为元素,注意元素和节点是两个数据结构,其实差不多
QString szName = tmpElement.tagName();
QString szValue = tmpElement.text();
}
node=node.nextSibling(); //下一个兄弟节点,nextSiblingElement()是下一个兄弟元素,都差不多
}*/
}
void MainWindow::closeEvent(QCloseEvent *event)
{
QMap<QString, QString> qmapXmlResult;
qmapXmlResult.insert("PGDBHostName", ui->txtHostName->toPlainText());
qmapXmlResult.insert("PGDBPort", ui->txtPort->toPlainText());
qmapXmlResult.insert("PGDBUserName", ui->txtUserName->toPlainText());
qmapXmlResult.insert("PGDBName", ui->txtDBName->toPlainText());
qmapXmlResult.insert("PGDBPassword", ui->txtPassword->toPlainText());
qmapXmlResult.insert("ClientIP", ui->txtClientIP->toPlainText());
qmapXmlResult.insert("ClientPort", ui->txtClientPort->toPlainText());
m_XmlHelper->SerializationToXml(m_qszSettingsPath, qmapXmlResult);
}
void MainWindow::GetAllTable()
{
if(m_pDbManager->IsOpen())
{
QList<QString> listTables = m_pDbManager->GetAllTables();
QMap<QString, QMap<QString, DBColumnProperty>> m_mapColumnsPro;
for (int i = 0; i < listTables.count(); i++)
{
QMap<QString, DBColumnProperty> m_tmpProperty = m_pDbManager->GetDBColumnPro(listTables[i]);
m_mapColumnsPro.insert(listTables[i], m_tmpProperty);
}
ui->treeWDB->setColumnCount(1);
ui->treeWDB->setHeaderLabel("DB Tree");
QMap<QString, QMap<QString, DBColumnProperty>>::Iterator it = m_mapColumnsPro.begin();
while (it != m_mapColumnsPro.end())
{
QTreeWidgetItem* pItem = new QTreeWidgetItem(ui->treeWDB);
QString strItem = QString(it.key());
pItem->setText(0, strItem);
QMap<QString, DBColumnProperty>::Iterator itChild = it.value().begin();
while (itChild != it.value().end())
{
QTreeWidgetItem* pItemChild = new QTreeWidgetItem(pItem);
QString strItemChild = QString(itChild.key());
pItemChild->setText(0, strItemChild);
itChild++;
}
it++;
}
}
}
void MainWindow::on_btnGetTables_clicked()
{
GetAllTable();
}
void MainWindow::on_treeWDB_itemClicked(QTreeWidgetItem *item, int column)
{
}
void MainWindow::on_btnOpenClient_clicked()
{
m_pTcpClient->TCPConnect(ui->txtClientIP->toPlainText(), ui->txtClientPort->toPlainText().toInt());
}
void MainWindow::on_btnCloseClient_clicked()
{
m_pTcpClient->DisConnect();
}
void MainWindow::on_btnClientSend_clicked()
{
m_pTcpClient->Write(ui->txtClientSend->toPlainText());
AddCMDString(ui->txtClientSend->toPlainText());
}
void MainWindow::AddCMDString(QString qszCmdAdd)
{
QString qszDateTime = QDateTime::currentDateTime().toString("yyyy.MM.dd hh:mm:ss");//.toString("yyyy.MM.dd hh:mm:ss.zzz ddd");
m_qszCmdCache += (qszDateTime + " " + qszCmdAdd);
m_qszCmdCache += "\r\n";
ui->txtCmd->setPlainText(m_qszCmdCache);
}