QT学习之路七（qt的数据库操作）

　　上一篇博客说了，我在做银行管理系统，所以少不了数据的操作，刚开始的时候，我想用mysql做的，但是qtcreator自身并没有mysql的驱动，于是上网，找了好久，跟着网上的教程，把mysql编译了一下，我按着教程编译出来了，但是不知为什么，我的qtcreator还是没有识别mysql的驱动文件，为此，我将近花了两天的时间，qtcreate也被我卸了好几次，mysql的驱动也编译了好几次，但是，我尝尽了所有的办法，甚至将别人编译的文件也试着下载看看效果，很遗憾，还是没有用，无奈之下，我选择了sqlite3来做，我打算用qt把银行管理的系统做完后，再抽一点时间，用mfc和Windows下的网络编程实现一下，qt固然好用，但它已经把所有的东西都封装好了，我不能仅仅满足于qt提供的库，我还是要花点时间在Windows下的网络编程以及系统编程，Windows下的话mysql就能用了，哎，浪费了两天时间，最终还是没有装好，还是有点遗憾。

　　以下是我学qtsqlite3操作的一个例子，如果有需要的朋友可以看下：

/********************************************************************************
** Form generated from reading UI file 'sql.ui'
**
** Created: Thu Feb 16 14:03:41 2017
**      by: Qt User Interface Compiler version 4.8.1
**
** WARNING! All changes made in this file will be lost when recompiling UI file!
********************************************************************************/

#ifndef UI_SQL_H
#define UI_SQL_H

#include <QtCore/QVariant>
#include <QtGui/QAction>
#include <QtGui/QApplication>
#include <QtGui/QButtonGroup>
#include <QtGui/QHeaderView>
#include <QtGui/QMainWindow>
#include <QtGui/QMenuBar>
#include <QtGui/QStatusBar>
#include <QtGui/QToolBar>
#include <QtGui/QWidget>

QT_BEGIN_NAMESPACE

class Ui_sql
{
public:
    QMenuBar *menuBar;
    QToolBar *mainToolBar;
    QWidget *centralWidget;
    QStatusBar *statusBar;

    void setupUi(QMainWindow *sql)
    {
        if (sql->objectName().isEmpty())
            sql->setObjectName(QString::fromUtf8("sql"));
        sql->resize(400, 300);
        menuBar = new QMenuBar(sql);
        menuBar->setObjectName(QString::fromUtf8("menuBar"));
        sql->setMenuBar(menuBar);
        mainToolBar = new QToolBar(sql);
        mainToolBar->setObjectName(QString::fromUtf8("mainToolBar"));
        sql->addToolBar(mainToolBar);
        centralWidget = new QWidget(sql);
        centralWidget->setObjectName(QString::fromUtf8("centralWidget"));
        sql->setCentralWidget(centralWidget);
        statusBar = new QStatusBar(sql);
        statusBar->setObjectName(QString::fromUtf8("statusBar"));
        sql->setStatusBar(statusBar);

        retranslateUi(sql);

        QMetaObject::connectSlotsByName(sql);
    } // setupUi

    void retranslateUi(QMainWindow *sql)
    {
        sql->setWindowTitle(QApplication::translate("sql", "sql", 0, QApplication::UnicodeUTF8));
    } // retranslateUi

};

namespace Ui {
    class sql: public Ui_sql {};
} // namespace Ui

QT_END_NAMESPACE

#endif // UI_SQL_H

#ifndef CONNECTION_H
#define CONNECTION_H

#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("use.db");
    if(!db.open())
    {
        QMessageBox::critical(0,"cannot open database","unable to establsh a database connection.",QMessageBox::Cancel);
        return false;
    }
    QSqlQuery query;
    query.exec("create table customers(customerId INTEGER PRIMARY KEY,name varchar(50),city varchar(50),"
               "customerType varchar(50),addTime date,addFromDepartment  varchar(10),quantity INTEGER );");
    query.exec(QObject::tr("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('张志国','中国','普通客户','2011-10-22','财务部',3);"));
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('靳红浩','法国','主要客户','2012-11-1','销售部',2) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('高武明','中国','普通客户','2013-5-12','编辑部',12) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('王玲菲','德国','特殊客户','2007-1-12','编辑部',5) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('马达达','中国','主要客户','2008-2-19','财务部',3) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('马凯文','德国','特殊客户','2000-4-23','财务部',6) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('姚明明','美国','特殊客户','2011-5-23','编辑部',2) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('余铭宏','中国','主要客户','2012-12-2','销售部',7) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('张艺谋','法国','特殊客户','2013-1-2','编辑部',2) ;");
    query.exec("insert into customers(name,city,customerType,addTime,addFromDepartment,quantity) values('张斯五','中国','普通客户','2010-1-3','销售部',1);");

    query.exec("create table student(id INTEGER,name TEXT, primary key(id))");
    query.exec("insert into student values(1,'LiuTao');");
    query.exec("insert into student values(2,'WangHong');");
    query.exec("insert into student values(3,'WangHong');");
    query.exec("insert into student values(4,'WangHong');");
    query.exec("insert into student values(5,'WangHong');");
    query.exec("insert into student values(6,'WangHong');");

    query.exec("create table save_user(account TEXT, passward TEXT, name TEXT, moto TEXT, likes INTEGER, vip INTEGER, primary key(account))");
    query.exec("insert into save_user values('88888888','hujianfei','胡剑飞', '我想回家',8,1)");
    return true;
}

#endif // CONNECTION_H

#ifndef SQL_H
#define SQL_H

#include <QMainWindow>

namespace Ui {
class sql;
}

class sql : public QMainWindow
{
    Q_OBJECT
    
public:
    explicit sql(QWidget *parent = 0);
    ~sql();
    
private:
    Ui::sql *ui;
};

#endif // SQL_H

#include "sql.h"
#include "ui_sql.h"
#include <QDebug>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QSqlRelationalTableModel>
#include <QTableView>
#include <QMessageBox>
#include <QSqlError>

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

    QSqlQueryModel *model = new QSqlQueryModel(this);
    model->setQuery("select * from student");
    model->setHeaderData(0,Qt::Horizontal, tr("学号"));
    model->setHeaderData(1,Qt::Horizontal, tr("姓名"));

    QTableView *view = new QTableView(this);
    view->setModel(model);

    setCentralWidget(view);

}

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

#include <QtGui/QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include "connection.h"
#include <QVariant>
#include <QStringList>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QSqlRelationalTableModel>
#include <QTableView>
#include <QMessageBox>
#include <QSqlError>
#include <QTextCodec>
#include "sql.h"

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QTextCodec::setCodecForTr(QTextCodec::codecForLocale());           			 //将字体乱码变为中文
    //QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));     			//使用GB18030字符集 效果和上一样
    QTextCodec::setCodecForCStrings(QTextCodec::codecForLocale());      			//将之后用到的不是显示的中文字符不显示乱码

    if(!createConnection())
    {
        return 1;
    }

//    QSqlQuery query;
//    query.exec("select * from student");
//    while(query.next())
//    {
//        qDebug()<<query.value(0).toInt()<<query.value(1).toString();
//    }

    sql w;
    w.show();
    
    return a.exec();
}