ubuntu-18.04 linux-QT版 演示sqlite3增删改查
- 【1】sqlite3_test1.pro
- 【2】mainwindow.ui
- 【3】mainwindow.h
- 【4】mainwindow.cpp
- 【5】效果演示
sqlite3_test1pro_3">【1】sqlite3_test1.pro
添加sql
【2】mainwindow.ui
【3】mainwindow.h
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <QDebug>
#include <QMainWindow>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QString>
#include <QTextCodec>
QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE
// 2022-11-5
typedef struct SQL_DATA{
QString car_id; /* 车牌号 */
QString car_pos; /* 车位号 */
QString card_id; /* 卡号 */
int age; /* 人脸特征数据大小 */
QByteArray t_featuredata; /* 人脸特征数据 */
QString t_datetime; /* 存车时间 */
} SQL_DATA_TypeDef;
/*Q_DECLARE_METATYPE ->这个宏只要提供公共默认构造函数、公共复制构造函数。需要在QVariant中使用类型作为自定义类型。*/
Q_DECLARE_METATYPE(SQL_DATA)
class MainWindow : public QMainWindow
{
Q_OBJECT
public:
MainWindow(QWidget *parent = nullptr);
~MainWindow();
private slots:
void on_pushButton_clicked();
void on_pushButton_2_clicked();
void on_pushButton_6_clicked();
void on_pushButton_3_clicked();
void on_pushButton_7_clicked();
void on_pushButton_4_clicked();
void on_pushButton_5_clicked();
private:
Ui::MainWindow *ui;
QSqlDatabase db1;
QSqlDatabase db2;
QSqlQuery *que;
QSqlRecord rec;
};
#endif // MAINWINDOW_H
【4】mainwindow.cpp
#include "mainwindow.h"
#include "ui_mainwindow.h"
MainWindow::MainWindow(QWidget *parent)
: QMainWindow(parent)
, ui(new Ui::MainWindow)
{
ui->setupUi(this);
//ZH_CHINA
QTextCodec *codec = QTextCodec::codecForName("UTF-8");
QTextCodec::setCodecForLocale(codec);
}
MainWindow::~MainWindow()
{
delete ui;
}
//create database
void MainWindow::on_pushButton_6_clicked()
{
db1.removeDatabase("RB");//every create database dalete
db1 = QSqlDatabase::addDatabase("QSQLITE","RB");
db1.setDatabaseName("1.db");
bool ret = db1.open();
if(ret == true)
ui->textEdit->append("open 1.db sucess");
else
ui->textEdit->append(db1.lastError().text());
que = new QSqlQuery(db1);
}
//create table
void MainWindow::on_pushButton_2_clicked()
{
if(que->exec("drop table if exists rb_tab1;"))
ui->textEdit->append("drop table sucess");
else
ui->textEdit->append(que->lastError().text());
bool t =que->exec("create table if not exists rb_tab1(id integer ,name text,height real);");
if(!t) ui->textEdit->append(que->lastError().text());
else ui->textEdit->append("create table rb_tab1 sucess");
}
//insert data
void MainWindow::on_pushButton_clicked()
{
QString str = QString("insert into rb_tab1 values(10086,'TDM',18.99);");
if(!que->exec(str)) ui->textEdit->append(que->lastError().text());
else ui->textEdit->append("insert rb_tab1 sucess");
}
//select data
void MainWindow::on_pushButton_3_clicked()
{
QString str = QString("select *from rb_tab1;");
if(que->exec(str))
{
ui->textEdit->append("select *from rb_tab1 sucessful;");
}
else
{
ui->textEdit->append(que->lastError().text());
}
//get [select *from rb_tab1] result
rec = que->record();
//return data values count
int count = rec.count();
ui->textEdit->append(QString("table data tatol number =%1").arg(count));
if(count == 3)
{
while (que->next()) {
ui->textEdit->append(QString("id =%1 \t name =%2 \t height =%3").arg(que->value(0).toInt()).arg(que->value(1).toString()).arg(que->value(2).toDouble()));
}
}
else
{
while (que->next()) {
ui->textEdit->append(QString("id =%1 \t name =%2 \t height =%3 \t card_id =%4").
arg(que->value(0).toInt()).arg(que->value(1).toString()).
arg(que->value(2).toDouble()).arg(que->value(3).toString()));
}
}
}
//show table values
void MainWindow::on_pushButton_7_clicked()
{
QString str = QString("select *from rb_tab1;");
if(que->exec(str))
{
ui->textEdit->append("select *from rb_tab1 sucessful;");
}
else
{
ui->textEdit->append(que->lastError().text());
}
//get [select *from rb_tab1] result
rec = que->record();
//return data values count
int count = rec.count();
if(count == 3)
{
ui->textEdit->append(QString("table data tatol number =%1").arg(count));
while (que->next()) {
ui->textEdit->append(QString("id =%1 \t name =%2 \t height =%3").arg(que->value(0).toInt()).arg(que->value(1).toString()).arg(que->value(2).toDouble()));
}
}
else if(count == 4)
{
ui->textEdit->append(QString("table data tatol number =%1").arg(count));
while (que->next()) {
ui->textEdit->append(QString("id =%1 \t name =%2 \t height =%3 \t char_id = %4").arg(que->value(0).toInt()).arg
(que->value(1).toString()).arg(que->value(2).toDouble()).arg(que->value(3).toString()));
}
}
}
//update table values
void MainWindow::on_pushButton_4_clicked()
{
int ret =que->exec("update rb_tab1 set id =200,name = 'NMD',height = 23.55;");
if(ret)
{
ui->textEdit->append("update table value sucessful");
}
else
{
ui->textEdit->append(que->lastError().text());
}
QString str = QString("select *from rb_tab1;");
if(que->exec(str))
{
ui->textEdit->append("select *from rb_tab1 sucessful;");
}
else
{
ui->textEdit->append(que->lastError().text());
}
//get [select *from rb_tab1] result
rec = que->record();
//return data values count
int count = rec.count();
if(count == 4)
{
int ret =que->exec("update rb_tab1 set id =200,name = 'NMD',height = 23.55,card_id = '222222';");
if(ret)
{
ui->textEdit->append("update table value sucessful");
}
else
{
ui->textEdit->append(que->lastError().text());
}
}
}
//add new column
void MainWindow::on_pushButton_5_clicked()
{
int ret =que->exec("alter table rb_tab1 add card_id char(18);");
if(ret)
{
ui->textEdit->append("alter table rb_tab1 add card_id char(18)");
}
else
{
ui->textEdit->append(que->lastError().text());
}
}
【5】效果演示
创建数据库
在此数据库下创建表格
插入一行 三列 第一行数据 ==
== 查询刚刚插入的数据
更新数据 覆盖第一行的所有值
查询更新后的值
新增一列
插入数据 包括新增的列
显示所有插入的值
本次小测试结束