QT操作SQLite数据库
.pro文件中加入以下语句QT+= sql头文件#include <QSqlDatabase>#include <QSqlError>#include <QSqlQuery>创建数据库//创建数据库bool SqliteHelper::CreateDataBase(){//建立并打开数据库QSqlDatabase database = QSqlDatabase:
·
.pro文件中加入以下语句
QT += sql
头文件
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
创建数据库
//创建数据库
bool SqliteHelper::CreateDataBase()
{
//建立并打开数据库
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName(dbPath);//dbPath为数据库文件地址 如:"C:\\Users\\Desktop\\MyDB.db"
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);//dbPath为数据库文件地址 如:"C:\\Users\\Desktop\\MyDB.db"
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
//拼接创建表格的sql语句
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);//dbPath为数据库文件地址 如:"C:\\Users\\Desktop\\MyDB.db"
if (!db.open())
{
qDebug() << "Error: Failed to connect database." << db.lastError();
return false;
}
/*insert into student(Sno, Sname, Ssex, Sage, InsertDate, UpdateDate) Values('1', 'bob', '男', 18, datetime(CURRENT_TIMESTAMP, 'localtime'), datetime(CURRENT_TIMESTAMP, 'localtime'))*/
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);//dbPath为数据库文件地址 如:"C:\\Users\\Desktop\\MyDB.db"
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;
}
更多推荐
已为社区贡献1条内容
所有评论(0)