database.h
#ifndef DATABASE_H
#define DATABASE_H
#include <QTextCodec>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QTime>
#include <QSqlError>
#include <QtDebug>
#include <QSqlDriver>
#include <QSqlRecord>
class DataBase
{
public:
bool createConnection(); //创建一个连接
bool createTable(); //创建数据库表
bool insert(); //出入数据
bool queryAll(); //查询所有信息
bool updateById(int id); //更新
bool deleteById(int id); //删除
bool sortById(); //排序
};
#endif // DATABASE_H
database.cpp
#include "database.h"
//建立一个数据库连接
bool DataBase::createConnection()
{
//以后就可以用"sqlite1"与数据库进行连接了
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "sqlite1");
db.setDatabaseName(".//qtDb.db");
if( !db.open())
{
qDebug() << "无法建立数据库连接";
return false;
}
return true;
}
//创建数据库表
bool DataBase::createTable()
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
bool success = query.exec("create table automobil(id int primary key,attribute varchar,"
"type varchar,kind varchar,nation int,carnumber int,elevaltor int,"
"distance int,oil int,temperature int)");
if(success)
{
qDebug() << QObject::tr("数据库表创建成功!\n");
return true;
}
else
{
qDebug() << QObject::tr("数据库表创建失败!\n");
return false;
}
}
//向数据库中插入记录
bool DataBase::insert()
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
query.prepare("insert into automobil values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
long records = 10;
for(int i=0; i<records; i++)
{
query.bindValue(0, i);
query.bindValue(1, "四轮");
query.bindValue(2, "轿车");
query.bindValue(3, "富康");
query.bindValue(4, rand()%100);
query.bindValue(5, rand()%10000);
query.bindValue(6, rand()%300);
query.bindValue(7, rand()%200000);
query.bindValue(8, rand()%52);
query.bindValue(9, rand()%100);
bool success=query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug() << lastError.driverText() << QString(QObject::tr("插入失败"));
return false;
}
}
return true;
}
//查询所有信息
bool DataBase::queryAll()
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
query.exec("select * from automobil");
QSqlRecord rec = query.record();
qDebug() << QObject::tr("automobil表字段数:" ) << rec.count();
while(query.next())
{
for(int index = 0; index < 10; index++)
qDebug() << query.value(index) << " ";
qDebug() << "\n";
}
}
//根据ID删除记录
bool DataBase::deleteById(int id)
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
query.prepare(QString("delete from automobil where id = %1").arg(id));
if(!query.exec())
{
qDebug() << "删除记录失败!";
return false;
}
return true;
}
//根据ID更新记录
bool DataBase::updateById(int id)
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
query.prepare(QString("update automobil set attribute=?,type=?,"
"kind=?, nation=?,"
"carnumber=?, elevaltor=?,"
"distance=?, oil=?,"
"temperature=? where id=%1").arg(id));
query.bindValue(0,"四轮");
query.bindValue(1,"轿车");
query.bindValue(2,"富康");
query.bindValue(3,rand()%100);
query.bindValue(4,rand()%10000);
query.bindValue(5,rand()%300);
query.bindValue(6,rand()%200000);
query.bindValue(7,rand()%52);
query.bindValue(8,rand()%100);
bool success=query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug() << lastError.driverText() << QString(QObject::tr("更新失败"));
}
return true;
}
//排序
bool DataBase::sortById()
{
QSqlDatabase db = QSqlDatabase::database("sqlite1"); //建立数据库连接
QSqlQuery query(db);
bool success=query.exec("select * from automobil order by id desc");
if(success)
{
qDebug() << QObject::tr("排序成功");
return true;
}
else
{
qDebug() << QObject::tr("排序失败!");
return false;
}
}
main.cpp
#include <QCoreApplication>
#include "database.h"
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QTextCodec::setCodecForLocale(QTextCodec::codecForLocale());
DataBase d;
d.createConnection(); //创建连接
//d.createTable();
//d.insert();
d.queryAll(); //已经完成过createTable(), insert(), 现在进行查询
return 0;
}
运行结果
项目名称为”sqlex”,然后就可以在与当前项目同一级目录下的“build-SQLEx-Desktop_Qt_5_7_0_MinGW_32bit-Debug”中可以看到qtDb.db数据库文件,该数据库可以用软件SQLite直接打开