.pro文件中加入以下语句
QT += sql
头文件
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
创建数据库
bool SqliteHelper::CreateDataBase()
{
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName(dbPath);
if (!database.open())
{
qDebug() << "Error: Failed to connect database." << database.lastError();
return false;
}
database.close();
return true;
}
创建表
bool SqliteHelper::CreateTable()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "CREATE TABLE IF NOT EXISTS student(\
Sno VARCHAR(255)\
, Sname VARCHAR(255)\
, Ssex VARCHAR(255)\
, Sage INTEGER\
, InsertDate DATETIME\
, UpdateDate DATETIME)";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
db.close();
return true;
}
插入数据
bool SqliteHelper::InsertMapData(Student stu)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "INSERT INTO student(\
Sno \
, Sname \
, Ssex \
, Sage\
, InsertDate \
, UpdateDate) \
Values(";
sql = sql + "\'" + stu.m_stuNo + "\'"
+ ",\'" + stu.m_name + "\'"
+ ",\'" + stu.sexEnumToString() +"\'"
+ "," + QString::number(stu.m_age) + " "
+ ","+"datetime(CURRENT_TIMESTAMP, \'localtime\')" + " "
+","+"datetime(CURRENT_TIMESTAMP, \'localtime\')" + ")";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
db.close();
return true;
}
删除数据
bool SqliteHelper::DeleteData(const QString v_sno)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "DELETE FROM student WHERE Sno = \'" + v_sno + "\'";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
db.close();
return true;
}
修改数据
bool SqliteHelper::UpdateData(Student stu)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "update student set Sname = \'"
+ stu.m_name + "\'"
+ " ,Ssex = \'" + stu.sexEnumToString() + "\'"
+ " ,Sage = " + QString::number(stu.m_age) + " "
+ " ,UpdateDate = datetime(CURRENT_TIMESTAMP, \'localtime\')"
+ " where Sno=\'"+ stu.m_stuNo+"\'";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
db.close();
return true;
}
查询数据 查询单条数据
bool SqliteHelper::SelectStudentBySno(Student& stu, const QString v_Sno)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "SELECT Sno,Sname, Ssex, Sage,InsertDate,UpdateDate FROM student WHERE Sno = \'" + v_Sno + "\'";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
while (sql_query.next())
{
stu.m_stuNo = sql_query.value(0).toString();
stu.m_name = sql_query.value(1).toString();
QString strSex = sql_query.value(2).toString();
if (strSex == QString("男"))
{
stu.m_sex = boy;
}
else if(strSex == QString("女"))
{
stu.m_sex = girl;
}
stu.m_age = sql_query.value(3).toInt();
stu.insertDate = sql_query.value(4).toString();
stu.updateDate = sql_query.value(5).toString();
}
db.close();
return true;
}
查询数据 查询多条数据
bool SqliteHelper::SelectStudentAll(QList<Student>& stuList)
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbPath);
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
QString sql = "SELECT Sno,Sname, Ssex, Sage,InsertDate,UpdateDate FROM student";
QSqlQuery sql_query;
if (!sql_query.exec(sql))
{
qDebug() << "Error: Fail to create table." << sql_query.lastError();
db.close();
return false;
}
while (sql_query.next())
{
Student stu;
stu.m_stuNo = sql_query.value(0).toString();
stu.m_name = sql_query.value(1).toString();
QString strSex = sql_query.value(2).toString();
if (strSex == QString("男"))
{
stu.m_sex = boy;
}
else if (strSex == QString("女"))
{
stu.m_sex = girl;
}
stu.m_age = sql_query.value(3).toInt();
stu.insertDate = sql_query.value(4).toString();
stu.updateDate = sql_query.value(5).toString();
stuList.append(stu);
}
db.close();
return true;
}
示例工程