包含头文件 #include <QtSql>
在工程的.pro文件中加入 QT += sql
一、连接数据库
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL","ConnA");//驱动名,连接名
db.setHostName("music");
db.setDatabaseName("qt532");
db.setUserName("mysql");
db.setPassword("mysql");
db.open(); //打开数据库连接
二、操作数据库
1、查询:
QSqlQuery query;
query.exec("SELECT title,year FROM cd WHERE year >= 1998");
//QSqlQuery query("SELECT title,year FROM cd WHERE year >= 1998");
//可以通过对查询调用isActive()来检查是否有错误发生,若没有,查询会变成“激活”状态,可用next()
if(!query.isActive())
QMessageBox::warning(this,tr("Database error"),
query.lastError().text());
while(query.next()){ //遍历查询结果
QString title = query.value(0).toString();
int year = query.value(1).toInt();
std::cerr << qPrintable(title) << ":" << year << std::endl;
}
2、插入:
QSqlQuery query("INSERT INTO cd(id,artistid,title,year)"
"VALUES (203,102,"living",2002)");
//之后调用numRowsAffected()返回受SQL语句影响的行数(error,return -1)
* 插入多条记录(或想避免将数值转换成字符串):
//Oracle风格语法及命名占位符实例:
QSqlQuery query;
query.prepare("INSERT INTO cd(id,artistid,title,year)"
"VALUES (:id, :artistid, :title, :year)");
query.bindValue(":id",203);
query.bindValue(":artistid",102);
query.bindValue(":title","Living");
query.bindValue(":year",2002);
query.exec();
//ODBC风格:
QSqlQuery query;
query.prepare("INSERT INTO cd(id,artistid,title,year)"
"VALUES (?,?,?,?)");
query.addBindValue(203);
query.addBindValue(102);
query.addBindValue("Living");
query.addBindValue(2002);
query.exec();
//exec()调用之后,可以用bindValue()或addBindValue()来赋值绑定新值,然后再次调用exec()
//并利用这些新值进行查询.
3、更新操作:
query.exec("UPDATE cd SET artistid = 100 WHERE id = 30");
4、删除操作:
query.exec("DELETE FROM cd where id = 121");
三、事务操作:
发起一个事务操作:代表数据库连接的QSqlDatabase对象调用transaction()
结束一个事务操作:commit()或rollback()
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM artistid WHERE name = 'kaka'");
if(query.next()){
int artistId = query.value(0).toInt();
query.exec("INSERT INTO cd(id,artistid,title,year)"
"VALUES(201,"+QString::number(art
在工程的.pro文件中加入 QT += sql
一、连接数据库
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL","ConnA");//驱动名,连接名
db.setHostName("music");
db.setDatabaseName("qt532");
db.setUserName("mysql");
db.setPassword("mysql");
db.open(); //打开数据库连接
二、操作数据库
1、查询:
QSqlQuery query;
query.exec("SELECT title,year FROM cd WHERE year >= 1998");
//QSqlQuery query("SELECT title,year FROM cd WHERE year >= 1998");
//可以通过对查询调用isActive()来检查是否有错误发生,若没有,查询会变成“激活”状态,可用next()
if(!query.isActive())
QMessageBox::warning(this,tr("Database error"),
query.lastError().text());
while(query.next()){ //遍历查询结果
QString title = query.value(0).toString();
int year = query.value(1).toInt();
std::cerr << qPrintable(title) << ":" << year << std::endl;
}
2、插入:
QSqlQuery query("INSERT INTO cd(id,artistid,title,year)"
"VALUES (203,102,"living",2002)");
//之后调用numRowsAffected()返回受SQL语句影响的行数(error,return -1)
* 插入多条记录(或想避免将数值转换成字符串):
//Oracle风格语法及命名占位符实例:
QSqlQuery query;
query.prepare("INSERT INTO cd(id,artistid,title,year)"
"VALUES (:id, :artistid, :title, :year)");
query.bindValue(":id",203);
query.bindValue(":artistid",102);
query.bindValue(":title","Living");
query.bindValue(":year",2002);
query.exec();
//ODBC风格:
QSqlQuery query;
query.prepare("INSERT INTO cd(id,artistid,title,year)"
"VALUES (?,?,?,?)");
query.addBindValue(203);
query.addBindValue(102);
query.addBindValue("Living");
query.addBindValue(2002);
query.exec();
//exec()调用之后,可以用bindValue()或addBindValue()来赋值绑定新值,然后再次调用exec()
//并利用这些新值进行查询.
3、更新操作:
query.exec("UPDATE cd SET artistid = 100 WHERE id = 30");
4、删除操作:
query.exec("DELETE FROM cd where id = 121");
三、事务操作:
发起一个事务操作:代表数据库连接的QSqlDatabase对象调用transaction()
结束一个事务操作:commit()或rollback()
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM artistid WHERE name = 'kaka'");
if(query.next()){
int artistId = query.value(0).toInt();
query.exec("INSERT INTO cd(id,artistid,title,year)"
"VALUES(201,"+QString::number(art