40数据库练习
例子解析：
连接数据库，将数据库的数据映射到设计器，可以在设计器中进行买卖操作，或添加车辆。确定后更新到数据库，右方显示卖车记录。
数据库数据
用于厂家的组合框
用于各数据的查询，通过厂家和品牌为标识
源代码:
mainwindow.h
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <QMainWindow>
namespace Ui {
class MainWindow;
}
class MainWindow : public QMainWindow
{
Q_OBJECT
public:
explicit MainWindow(QWidget *parent = 0);
~MainWindow();
void connectDB(); //连接数据库
void initData(); //初始化数据库
private slots:
void on_menuCar_triggered();
void on_menuCalc_triggered();
void on_factory_currentIndexChanged(const QString &arg1);
void on_brand_currentIndexChanged(const QString &arg1);
void on_num_valueChanged(int arg1);
void on_sure_clicked();
void on_cancel_clicked();
private:
Ui::MainWindow *ui;
};
#endif // MAINWINDOW_H
mainwindow.cpp
#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QMessageBox>
#include <QtSql/QSqlQueryModel>
#include <QtSql/QSqlQuery>
#include <QDebug>
MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
{
ui->setupUi(this);
//初始开始页面 车辆管理
MainWindow::on_menuCar_triggered();
MainWindow::connectDB();
MainWindow::initData();
}
MainWindow::~MainWindow()
{
delete ui;
}
//车辆管理菜单
void MainWindow::on_menuCar_triggered()
{
ui->stackedWidget->setCurrentWidget(ui->car);
ui->label->setText("车辆管理");
}
//销售统计菜单
void MainWindow::on_menuCalc_triggered()
{
ui->stackedWidget->setCurrentWidget(ui->calc);
ui->label->setText("销售统计");
}
//连接数据库
void MainWindow::connectDB()
{
QSqlDatabase db=QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("127.0.0.1");
//db.setPassword("1234");
db.setUserName("root");
db.setDatabaseName("car");
if(!db.open())
{
QMessageBox::warning(this,"数据库打开失败",db.lastError().text());
return ;
}
}
//初始化数据库
void MainWindow::initData()
{
//新建模型
QSqlQueryModel *queryModel=new QSqlQueryModel(this);
queryModel->setQuery("SELECT sname FROM factory;");
ui->factory->setModel(queryModel);
ui->lastnum->setText("0");
ui->allprice->setEnabled(false);
}
//厂家组合框槽函数
void MainWindow::on_factory_currentIndexChanged(const QString &arg1)
{
if(arg1=="changjia")
{
ui->brand->clear();
ui->price->clear();
ui->num->setValue(0);
ui->allprice->clear();
ui->lastnum->setText("0");
ui->sure->setEnabled(false);
}
else
{
ui->brand->clear();
QSqlQuery query;
QString sql=QString("SELECT sname FROM brand WHERE factory ='%1'").arg(arg1);
//SELECT sname FROM brand WHERE factory ='yiqi';
//执行SQL语句
query.exec(sql);
//获取内容
while(query.next())
{
QString name=query.value(0).toString();
ui->brand->addItem(name);
}
}
}
//品牌组合框槽函数
void MainWindow::on_brand_currentIndexChanged(const QString &arg1)
{
QSqlQuery query;
QString sql=QString("SELECT price,llast FROM brand WHERE factory ='%1'"
"AND sname='%2'").arg(ui->factory->currentText()).arg(arg1);
//SELECT price FROM brand WHERE factory ='yiqi' AND sname='aodi';
//执行SQL语句
query.exec(sql);
//获取内容
while(query.next())
{
//报价
int price=query.value("price").toInt();
//剩余数目
int last=query.value("llast").toInt();
//更新
ui->price->setText(QString::number(price));
ui->lastnum->setText(QString::number(last));
}
}
//数据选择框槽函数
void MainWindow::on_num_valueChanged(int arg1)
{
//更新剩余数量
//1)厂家
QString factoryStr=ui->factory->currentText();
//2)品牌
QString brandStr=ui->brand->currentText();
if(arg1==0)
{
ui->sure->setEnabled(false);
}
else
{
ui->sure->setEnabled(true);
}
//获取数据库剩余数量
QSqlQuery query;
QString sql=QString("SELECT ssum,llast FROM brand WHERE factory ='%1'"
"AND sname='%2'").arg(factoryStr).arg(brandStr);
int lastnum=0;
//执行SQL语句
query.exec(sql);
while(query.next())
{
//剩余数目
lastnum=query.value("llast").toInt();
}
//选择数大于剩余数目
if(arg1>lastnum)
{
ui->num->setValue(lastnum);
return;
}
//更新剩余数量
int tempnum=lastnum-arg1;
ui->lastnum->setText(QString::number(tempnum));
//金额
int price=ui->price->text().toInt();
int sum=price*arg1;
ui->allprice->setText(QString::number(sum));
}
//确定按钮
void MainWindow::on_sure_clicked()
{
//获取信息
//1)销售数据
int num=ui->num->value();
//2)剩余数目
int last=ui->lastnum->text().toInt();
//获取数据库的销量
QSqlQuery query;
QString sql=QString("SELECT sell,price FROM brand WHERE factory ='%1'"
"AND sname='%2'").arg(ui->factory->currentText()).arg(ui->brand->currentText());
//SELECT sell FROM brand WHERE factory ='yiqi' AND sname='oudi';
//执行SQL语句
query.exec(sql);
int sell=0,price=0;
//获取内容
while(query.next())
{
//销量
sell=query.value("sell").toInt();
//单价
price=query.value("price").toInt();
}
sell+=num;
//更新数据库，剩余数量、销售总量
sql=QString("UPDATE brand SET sell=%1,llast=%2 where factory='%3' "
"AND sname='%4'")
.arg(sell)
.arg(last)
.arg(ui->factory->currentText())
.arg(ui->brand->currentText());
query.exec(sql);
//销售记录
QString str=QString("厂家：%1，品牌：%2，销售了%3辆，单价%4万元，总价%5万元")
.arg(ui->factory->currentText())
.arg(ui->brand->currentText())
.arg(num)
.arg(price)
.arg(num*price);
ui->info->append(str);
//更新数据库后
ui->sure->setEnabled(false);
MainWindow::on_cancel_clicked();
}
//取消按钮
void MainWindow::on_cancel_clicked()
{
//MainWindow::on_factory_currentIndexChanged("changjia");
ui->factory->setCurrentIndex(0);
ui->lastnum->setText("0");
}
结果图：
被修改了
