02_QT操作数据库-sqlite

作者：精一思维

个人薇信号：dreamsoft008

 1 显示信息用 tablewidget容器

 2 SQLite是一款开源、轻量级、跨平台的数据库，无需server，无需安装和管理配置。它的设计目标是嵌入式的，所以很适合小型应用，也是Qt应用开发种常用的一种数据库。

3 .Pro需要增加 qt+=sql

4 直接上代码：

#include "mainwindow.h"
#include "ui_mainwindow.h"


#include <QtSql/QSql>
#include <QtSql/qsqldatabase.h>
#include <QtSql/qsqlquery.h>
#include <QDebug.h>
#include <QTableWidget>
#include <QTableWidgetItem>
#include <QStringList>


MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    //创建数据库连接
    createConn();

    //创建表格
    createTable();



}

MainWindow::~MainWindow()
{
    delete ui;
}


//1.连接数据库
bool MainWindow::createConn()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("C:\\1.db");
    //db.setHostName("localhost");
    //db.setUserName("");
    //db.setPassword("");
    if (db.open())
    {
        qDebug() << "Connected successfully.";
    }
    else
    {
        qDebug() << "Can't connect to database.";
       // qDebug() << "The error is: " << db.lastError().databaseText();
        return false;
    }
    return true;
}


bool MainWindow::createTable()
{
    QString strQuery;
    strQuery = "create table JobInfo("
        "id integer primary key,"
        "name varchar(30), job varchar(30))";
    QSqlQuery query;
    if (query.exec(strQuery))
    {
        qDebug() << "Create Table Successfully!";
    }
    else
    {
        qDebug() << "Create Table Failed";
        return false;
    }
    return true;
}

bool MainWindow::insertData(QString id, QString name, QString job)
{
    QSqlQuery query;
    query.prepare("insert into JobInfo (name, job) values(:name, :job)");
    query.bindValue(":name", name);
    query.bindValue(":job", job);
    if (query.exec())
    {
        qDebug() << "Insert Successfully.";
    }
    else
    {
        qDebug() << "Insert failed.";

        return false;
    }
    return true;
}


void MainWindow::readData()
{
    //设置表格中每一行的表头
    ui->TableWidget->setHorizontalHeaderLabels(QStringList() << "姓名" << "职位");
    ui->TableWidget->setColumnCount(2);

    //统计现有的表格行
    int t_rowCount = ui->TableWidget->rowCount();
    int nRow =0;
    QSqlQuery query;
    query.exec("select name, job from JobInfo");
    while (query.next())
    {
        QString name = query.value(0).toString();
        QString job = query.value(1).toString();
        qDebug() << "The job of " << name.toStdString().c_str() << " is " << job.toStdString().c_str();



        //设置表格数据区内的所有单元格都不允许编辑
        //ui->TableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers);
        //设置表格中每一行的内容

        ui->TableWidget->insertRow(t_rowCount + nRow);		//按需求加行
        ui->TableWidget->setItem(t_rowCount+nRow,0,new QTableWidgetItem(name));
        ui->TableWidget->setItem(t_rowCount+nRow,1,new QTableWidgetItem(job));
        nRow++;
    }

      ui->TableWidget->show();
}

void MainWindow::on_pushButton_add_clicked()
{

    QString strName = ui->lineEditName->text();
    QString strJob = ui->lineEditJob->text();

    insertData("1",strName,strJob);

}

void MainWindow::on_pushButton_query_clicked()
{
    int rowNum=  ui->TableWidget->rowCount();
    for(int i = 0 ; i < rowNum ; i++)//清空列表
    {
        ui->TableWidget->removeRow(0);
    }

    readData();
}

#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();

    bool createTable();
    bool createConn();
    bool insertData(QString id, QString name, QString job);
    void readData();

private slots:
    void on_pushButton_add_clicked();

    void on_pushButton_query_clicked();

private:
    Ui::MainWindow *ui;
};

#endif // MAINWINDOW_H