简单的数据库可视化系统
文章目录
项目功能:用户可以通过界面互动的方式对数据库(mySql)进行查询,更新,删除,插入的操作。
项目简介:利用qt框架库+c++语言实现,用户在使用前需要先登录系统(用户登录后,信息会自动记录在配置文件中,之后会自动填写),然后选择对应的操作,之后在弹出的对话框中输入对应的信息即可。系统会根据用户输入的内容自动生成sql语言并自动去执行,最后将执行结果返回。
技术点:配置文件,qt数据操作,自定义对话框
项目难点:自动调节大小对话框,由于不同的表的属性个数是不同的所以,对话需要根据对话框中的内容,自动调节大小。
*这里展示的是项目主要代码,全部项目请移步下载:
https://github.com/llpig/windowsCode/tree/dev1/qt_DataBases/DataBases
项目截图
项目代码
custommessagebox.h
#ifndef CUSTOMMESSAGEBOX_H
#define CUSTOMMESSAGEBOX_H
#include <QMessageBox>
#include <QResizeEvent>
#include <QLabel>
#include <QPushButton>
#include <algorithm>
#include <QVector>
#include <QLineEdit>
using namespace std;
class CustomMessageBox : public QMessageBox
{
Q_OBJECT
public:
explicit CustomMessageBox(QWidget *parent = 0,QString="MessageBox");
~CustomMessageBox();
//对话框可以根据其内容自动计算大小
void adjustWindowSize(QSize windowSize);
QSize addWidgetToMessageBox(QStringList stringList);
QSize addWidgetToMessageBoxUpdate(QStringList stringList);
int addPushButtonToMessageBox();
QPushButton *m_CommitPushButton;
QPushButton *m_CancelPushButtom;
static QVector<QLineEdit*> m_LineEditVector;//用于保存创建的条形框
protected:
int m_MessageBoxWidth;
int m_MessageBoxHeight;
static int m_LeftInterval;//窗口左间隔
static int m_RightInterval;//窗口右间隔
static int m_IntervalLR;//组件左右间隔
static int m_IntervalUL;//组件上下间隔
static int m_LineEditWidth;//条形框的宽
void resizeEvent(QResizeEvent* event);
};
#endif // CUSTOMMESSAGEBOX_H
custommessagebox.cpp
#include "custommessagebox.h"
int CustomMessageBox::m_LeftInterval=15;
int CustomMessageBox::m_RightInterval=15;
int CustomMessageBox::m_IntervalLR=10;
int CustomMessageBox::m_IntervalUL=5;
int CustomMessageBox::m_LineEditWidth=110;
QVector<QLineEdit*> CustomMessageBox::m_LineEditVector;
CustomMessageBox::CustomMessageBox(QWidget *parent, QString titleText) : QMessageBox(parent)
{
setWindowTitle(titleText);
m_CommitPushButton=NULL;
m_CancelPushButtom=NULL;
}
CustomMessageBox::~CustomMessageBox()
{
}
void CustomMessageBox::adjustWindowSize(QSize windowSize)
{
m_MessageBoxWidth=windowSize.width();
m_MessageBoxHeight=windowSize.height();
}
QSize CustomMessageBox::addWidgetToMessageBox(QStringList stringList)
{
m_LineEditVector.clear();
if(stringList.isEmpty())
{
return QSize(0,0);
}
int maxLabelWidth=0,labelHeight=0;
for(int i=0;i<stringList.size();++i)
{
QLabel *label=new QLabel(stringList[i],this);
label->setGeometry(m_LeftInterval,i*(label->height()+m_IntervalUL),label->width(),label->height());
QLineEdit *lineEdit=new QLineEdit(this);
lineEdit->setObjectName(stringList[i]);
m_LineEditVector.push_back(lineEdit);
maxLabelWidth=max(maxLabelWidth,label->width());
labelHeight=label->height();
}
for(int i=0;i<stringList.size();++i)
{
m_LineEditVector[i]->setGeometry(maxLabelWidth+m_IntervalLR,i*(labelHeight+m_IntervalUL),m_LineEditWidth,labelHeight);
}
int messageBoxWidth=m_LeftInterval+m_RightInterval+maxLabelWidth+m_IntervalLR+m_LineEditWidth;
int messageBoxHeight=stringList.size()*(labelHeight+m_IntervalUL);
return QSize(messageBoxWidth,messageBoxHeight);
}
QSize CustomMessageBox::addWidgetToMessageBoxUpdate(QStringList stringList)
{
m_LineEditVector.clear();
if(stringList.isEmpty())
{
return QSize(0,0);
}
int maxLabelWidth=0,labelHeight=0;
for(int i=0;i<stringList.size();++i)
{
QLabel *label=new QLabel(stringList[i],this);
label->setGeometry(m_LeftInterval,(i+1)*(label->height()+m_IntervalUL),label->width(),label->height());
QLineEdit *oldLineEdit=new QLineEdit(this);
oldLineEdit->setObjectName(stringList[i]);
m_LineEditVector.push_back(oldLineEdit);
QLineEdit *newLineEdit=new QLineEdit(this);
m_LineEditVector.push_back(newLineEdit);
maxLabelWidth=max(maxLabelWidth,label->width());
labelHeight=label->height();
}
int k=1;
for(int i=0;i<m_LineEditVector.size();i+=2)
{
m_LineEditVector[i]->setGeometry(maxLabelWidth+m_IntervalLR,k*(labelHeight+m_IntervalUL),m_LineEditWidth,labelHeight);
m_LineEditVector[i+1]->setGeometry(maxLabelWidth+m_LineEditWidth+2*m_IntervalLR,k*(labelHeight+m_IntervalUL),m_LineEditWidth,labelHeight);
++k;
}
QLabel *newLabel=new QLabel("更新后数据",this);
newLabel->setGeometry(maxLabelWidth+m_LineEditWidth+2*m_IntervalLR,0,newLabel->width(),newLabel->height());
QLabel *oldLabel=new QLabel("更新前数据",this);
oldLabel->setGeometry(maxLabelWidth+m_IntervalLR,0,oldLabel->width(),oldLabel->height());
int messageBoxWidth=m_LeftInterval+m_RightInterval+maxLabelWidth+2*m_IntervalLR+2*m_LineEditWidth;
int messageBoxHeight=(stringList.size()+1)*(labelHeight+m_IntervalUL);
return QSize(messageBoxWidth,messageBoxHeight);
}
int CustomMessageBox::addPushButtonToMessageBox()
{
m_CommitPushButton=this->addButton("commit",QMessageBox::ActionRole);
m_CancelPushButtom=this->addButton(QMessageBox::Cancel);
return m_CancelPushButtom->height();
}
void CustomMessageBox::resizeEvent(QResizeEvent *event)
{
setFixedSize(m_MessageBoxWidth,m_MessageBoxHeight);
}
databases.h
#ifndef DATABASES_H
#define DATABASES_H
#include <QFile>
#include <QVector>
#include <QtDebug>
#include <QSqlQuery>
#include <QSqlError>
#include <QSettings>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QMainWindow>
#include <QMap>
#include <QFont>
#include <algorithm>
#include <QGroupBox>
#include "dblogin.h"
#include "custommessagebox.h"
using namespace std;
namespace Ui {
class DataBases;
}
enum OpType{Select,Update,Add,Delete};
class DataBases : public QMainWindow
{
Q_OBJECT
public:
static QString DataBasesName;
explicit DataBases(QWidget *parent = 0);
~DataBases();
void initWindow();
QSqlDatabase* getDataBasePointer();
private slots:
void on_DB_select_clicked();
void on_DB_update_clicked();
void on_DB_add_clicked();
void on_DB_detele_clicked();
private:
Ui::DataBases *ui;
QSqlDatabase *DB_Mysql;//数据库指针
QSqlQuery *query;//数据库执行指针
QStringList m_MessageBoxInfo;//用于保存的对话框信息
static QString configFileName;//配置文件名称
//创建配置文件
void createConfigFile();
//查询当前数据中的表,并将表名写入下拉框
void selectTable();
//创建数据库操作的对话框(查询,删除,插入)
bool createDialogBox(QString opName, QString tableName, OpType opType);
//获取表的描述
QStringList getTabelDescribe(QString tableName);
//判断是否点击的为"commit"按钮
bool isCommitClicked(CustomMessageBox* messageBox);
bool isCommitClickedUpdate(CustomMessageBox* messageBox);
//将text的内容写入展示框(文本框)中
void addTextToShowBox(QString text);
};
#endif // DATABASES_H
databases.cpp
#include "databases.h"
#include "ui_databases.h"
QString DataBases::configFileName="DataBases.ini";
QString DataBases::DataBasesName="";
DataBases::DataBases(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::DataBases)
{
ui->setupUi(this);
query=NULL;
DB_Mysql=new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL"));
createConfigFile();
}
DataBases::~DataBases()
{
delete ui;
}
QSqlDatabase *DataBases::getDataBasePointer()
{
return DB_Mysql;
}
void DataBases::initWindow()
{
setWindowTitle("数据库可视化管理工具");
ui->DB_lineEdit_DBname->setText(DataBasesName+".db");
query=new QSqlQuery(*DB_Mysql);
selectTable();
}
//查询按钮
void DataBases::on_DB_select_clicked()
{
QString tableName=ui->DB_comboBox_tableName->currentText();
if(!createDialogBox("信息查询",tableName,Select))
{
return;
}
QString queries="",queryCond="",queryStr="";
for(int i=0;i<m_MessageBoxInfo.size();i+=2)
{
if(m_MessageBoxInfo[i+1].isEmpty())
{
queries=queries+m_MessageBoxInfo[i]+",";
}
else
{
queryCond=queryCond+m_MessageBoxInfo[i]+" like '%"+m_MessageBoxInfo[i+1]+"%' and ";
}
}
if(queries.isEmpty())
{
qDebug()<<"未检测到需要查询的内容"<<endl;
return;
}
else if(queryCond.isEmpty())
{
queries.remove(queries.length()-1,1);
queryStr=QString("Select %1 from %2;").arg(queries).arg(tableName);
}
else
{
queries.remove(queries.length()-1,1);
queryCond.remove(queryCond.length()-5,5);
queryStr=QString("Select %1 from %2 where %3;").arg(queries).arg(tableName).arg(queryCond);
}
addTextToShowBox("查询内容:("+queries+")\n查询结果:");
int nums=queries.split(',').length();
if(!queryStr.isEmpty())
{
if(query->exec(queryStr))
{
while(query->next())
{
for(int i=0;i<nums;++i)
{
addTextToShowBox(query->value(i).toString()+",");
}
addTextToShowBox("\n");
}
}
else
{
addTextToShowBox(query->lastError().text()+"\n");
}
}
}
//更新按钮
void DataBases::on_DB_update_clicked()
{
//更新(更新存在旧值和新值的替换关系,所以需要一个新的自定义控件)
QString tableName=ui->DB_comboBox_tableName->currentText();
if(!createDialogBox("信息更新",tableName,Update))
{
return;
}
QString queries="",queryCond="",queryStr="";
for(int i=0;i<m_MessageBoxInfo.size();i+=3)
{
//需要修改的内容
if(!m_MessageBoxInfo[i+1].isEmpty())
{
queryCond=queryCond+m_MessageBoxInfo[i]+" like '%"+m_MessageBoxInfo[i+1]+"%' and ";
}
//条件
if(!m_MessageBoxInfo[i+2].isEmpty())
{
queries=queries+m_MessageBoxInfo[i]+" = '"+m_MessageBoxInfo[i+2]+"',";
}
}
if(queries.isEmpty()||queryCond.isEmpty())
{
qDebug()<<"请输入修改后的内容和修改的条件!"<<endl;
}
queries.remove(queries.length()-1,1);
queryCond.remove(queryCond .length()-5,5);
queryStr=QString("UpDate %1 Set %2 Where %3;").arg(tableName).arg(queries).arg(queryCond);
if(query->exec(queryStr))
{
addTextToShowBox("符合条件的记录已更改完成!\n");
}
else
{
addTextToShowBox(query->lastError().text()+"\n");
}
}
//添加按钮
void DataBases::on_DB_add_clicked()
{
QString tableName=ui->DB_comboBox_tableName->currentText();
if(!createDialogBox("信息添加",tableName,Add))
{
return;
}
QString queries="",queryCond="",queryStr="";
for(int i=0;i<m_MessageBoxInfo.size();i+=2)
{
if(!m_MessageBoxInfo[i+1].isEmpty())
{
queries=queries+m_MessageBoxInfo[i]+",";
queryCond=queryCond+"'"+m_MessageBoxInfo[i+1]+"',";
}
}
if(queries.isEmpty())
{
qDebug()<<"请输入需要插入的内容"<<endl;
return;
}
queries.remove(queries.length()-1,1);
queryCond.remove(queryCond.length()-1,1);
queryStr=QString("Insert into %1 (%2) values (%3);").arg(tableName).arg(queries).arg(queryCond);
if(query->exec(queryStr))
{
addTextToShowBox("记录已成功添加!");
}
else
{
addTextToShowBox(query->lastError().text()+"\n");
}
}
//删除按钮
void DataBases::on_DB_detele_clicked()
{
QString tableName=ui->DB_comboBox_tableName->currentText();
if(!createDialogBox("信息删除",tableName,Delete))
{
return;
}
QString queryCond="",queryStr="";
for(int i=0;i<m_MessageBoxInfo.size();i+=2)
{
if(!m_MessageBoxInfo[i+1].isEmpty())
{
queryCond=queryCond+m_MessageBoxInfo[i]+" like '%"+m_MessageBoxInfo[i+1]+"%' and ";
}
}
if(queryCond.isEmpty())
{
qDebug()<<"请填写需要删除内容的条件"<<endl;
return;
}
queryCond.remove(queryCond.length()-5,5);
queryStr=QString("Delete from %1 where %2;").arg(tableName).arg(queryCond);
qDebug()<<queryStr<<endl;
if(query->exec(queryStr))
{
addTextToShowBox("符合条件的内容已经被删除!\n");
}
else
{
addTextToShowBox(query->lastError().text());
}
}
void DataBases::createConfigFile()
{
//创建配置文件
QFile *filePointer=new QFile();
//如果配置文件不存在
if(!filePointer->exists(configFileName))
{
filePointer->setFileName(configFileName);
if(filePointer->open(QFile::WriteOnly))
{
filePointer->write("[USERNAME]\nusername=\n");
filePointer->write("[PASSWORD]\npassword=\n");
filePointer->write("[HOSTNAME]\nhostname=\n");
filePointer->write("[PORT]\nport=\n");
filePointer->write("[DATABASESNAME]\ndatabasesname=\n");
}
QSettings *iniPointer=new QSettings(configFileName,QSettings::IniFormat);
iniPointer->setValue("USERNAME/username","root");
iniPointer->setValue("HOSTNAME/hostname","localhost");
iniPointer->setValue("PORT/port","3306");
filePointer->close();
delete iniPointer;
delete filePointer;
}
}
void DataBases::selectTable()
{
QString sqlStr="show tables";
query->exec(sqlStr);
//遍历语句的执行结果将结果依此加入下拉框中
while(query->next())
{
ui->DB_comboBox_tableName->addItem(query->value(0).toString());
}
}
bool DataBases::createDialogBox(QString opName, QString tableName, OpType opType)
{
CustomMessageBox *dialogBox=new CustomMessageBox(this,opName);
QSize boxSize;
if(opType==Update)
{
boxSize=dialogBox->addWidgetToMessageBoxUpdate(getTabelDescribe(tableName));
}
else
{
boxSize=dialogBox->addWidgetToMessageBox(getTabelDescribe(tableName));
}
int boxHeight=dialogBox->addPushButtonToMessageBox();
boxSize.setHeight(boxSize.height()+1.5*boxHeight);
dialogBox->adjustWindowSize(boxSize);
dialogBox->exec();
if(opType==Update)
{
return isCommitClickedUpdate(dialogBox);
}
else
{
return isCommitClicked(dialogBox);
}
}
QStringList DataBases::getTabelDescribe(QString tableName)
{
QString sqlStr="Describe "+tableName;
QStringList sqlResult;
bool res=query->exec(sqlStr);
while(res&&query->next())
{
sqlResult.push_back(query->value(0).toString());
}
return sqlResult;
}
bool DataBases::isCommitClicked(CustomMessageBox *messageBox)
{
bool bRet=false;
m_MessageBoxInfo.clear();
if(messageBox->clickedButton()==messageBox->m_CommitPushButton)
{
bRet=true;
for(int i=0;i<messageBox->m_LineEditVector.size();++i)
{
m_MessageBoxInfo.push_back(messageBox->m_LineEditVector[i]->objectName());
m_MessageBoxInfo.push_back(messageBox->m_LineEditVector[i]->text());
}
}
return bRet;
}
bool DataBases::isCommitClickedUpdate(CustomMessageBox *messageBox)
{
bool bRet=false;
m_MessageBoxInfo.clear();
if(messageBox->clickedButton()==messageBox->m_CommitPushButton)
{
bRet=true;
for(int i=0;i<messageBox->m_LineEditVector.size();i+=2)
{
m_MessageBoxInfo.push_back(messageBox->m_LineEditVector[i]->objectName());
m_MessageBoxInfo.push_back(messageBox->m_LineEditVector[i]->text());
m_MessageBoxInfo.push_back(messageBox->m_LineEditVector[i+1]->text());
}
}
return bRet;
}
void DataBases::addTextToShowBox(QString text)
{
//将文本框中的内容清空
ui->DB_show->clear();
ui->DB_show->textCursor().insertText(text);
}
dblogin.h
#ifndef DBLOGIN_H
#define DBLOGIN_H
#include <QDialog>
#include <QString>
#include <QSettings>
#include <QFile>
#include "databases.h"
#define LOGINSUCCESS 0
#define LOGINFAILURE 1
namespace Ui {
class DBLogin;
}
class DBLogin : public QDialog
{
Q_OBJECT
public:
explicit DBLogin(QWidget *parent = 0);
~DBLogin();
QString getUserName();
QString getPassWord();
QString getHostName();
QString getPort();
QString getDataBaseName();
private slots:
void on_DB_pushButton_commit_clicked();
private:
Ui::DBLogin *ui;
QSqlDatabase *DBpointer;
QSettings *iniPointer;
//初始化登录窗口
void initLoginWindow();
//登录函数
bool login();
//读取配置文件
void readINIFile();
void updateINIFile();
};
#endif // DBLOGIN_H
dblogin.cpp
#include "dblogin.h"
#include "ui_dblogin.h"
DBLogin::DBLogin(QWidget *parent) :
QDialog(parent),
ui(new Ui::DBLogin)
{
ui->setupUi(this);
DBpointer=(new DataBases())->getDataBasePointer();
initLoginWindow();
}
DBLogin::~DBLogin()
{
delete ui;
}
QString DBLogin::getUserName()
{
QString userName=ui->DB_LineEdit_username->text();
iniPointer->setValue("USERNAME/username",userName);
return userName;
}
QString DBLogin::getPassWord()
{
QString passWord= ui->DB_lineEdit_password->text();
iniPointer->setValue("PASSWORD/password",passWord);
return passWord;
}
QString DBLogin::getHostName()
{
QString hostName=ui->DB_lineEdit_hostname->text();
iniPointer->setValue("HOSTNAME/hostname",hostName);
return hostName;
}
QString DBLogin::getPort()
{
QString port=ui->DB_lineEdit_port->text();
iniPointer->setValue("PORT/port",port);
return port;
}
QString DBLogin::getDataBaseName()
{
DataBases::DataBasesName=ui->DB_lineEdit_DBname->text();
iniPointer->setValue("DATABASESNAME/databasesname",DataBases::DataBasesName);
return DataBases::DataBasesName;
}
void DBLogin::on_DB_pushButton_commit_clicked()
{
if(login()==false)
{
QDialog::done(LOGINFAILURE);
}
else
{
QDialog::done(LOGINSUCCESS);
}
}
void DBLogin::initLoginWindow()
{
setWindowTitle("数据库管理工具登录窗口");
ui->DB_lineEdit_password->setEchoMode(QLineEdit::PasswordEchoOnEdit);
readINIFile();
}
bool DBLogin::login()
{
DBpointer->setHostName(getHostName());
DBpointer->setPort(getPort().toInt());
DBpointer->setUserName(getUserName());
DBpointer->setPassword(getPassWord());
DBpointer->setDatabaseName(getDataBaseName());
return DBpointer->open();
}
void DBLogin::readINIFile()
{
QString fileName="DataBases.ini";
//读取配置文件
iniPointer=new QSettings(fileName,QSettings::IniFormat);
ui->DB_lineEdit_hostname->setText(iniPointer->value("HOSTNAME/hostname").toString());
ui->DB_LineEdit_username->setText(iniPointer->value("USERNAME/username").toString());
ui->DB_lineEdit_port->setText(iniPointer->value("PORT/port").toString());
ui->DB_lineEdit_password->setText(iniPointer->value("PASSWORD/password").toString());
ui->DB_lineEdit_DBname->setText(iniPointer->value("DATABASESNAME/databasesname").toString());
}
main.cpp
#include "databases.h"
#include "dblogin.h"
#include <QApplication>
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
DataBases dataBases;
DBLogin login;
if(login.exec()==LOGINSUCCESS)
{
dataBases.initWindow();
dataBases.show();
}
else
{
qDebug()<< dataBases.getDataBasePointer()->lastError().text()<<endl;
}
return a.exec();
}