QT操作SQLite数据库

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;
}

示例工程