Qt 数据库相关操作

#include "QtDataBase.h"
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include <QSqlQuery>		//数据库操作类
#include <QVariantList>

QtDataBase::QtDataBase(QWidget *parent)
	: QWidget(parent)
{
	ui.setupUi(this);

	connect(ui.buttonDel, &QPushButton::clicked, this, &QtDataBase::slotDel);
	connect(ui.buttonSure, &QPushButton::clicked, this, &QtDataBase::slotSure);
	connect(ui.buttonCancel, &QPushButton::clicked, this, &QtDataBase::slotCancel);

	//打印Qt支持的数据库驱动
	qDebug() << QSqlDatabase::drivers();

	//添加MySql数据库
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL","a");
	//连接数据库
	db.setHostName("127.0.0.1");		//数据库服务器IP
	db.setUserName("root");	//数据库用户名
	db.setPassword("");	//密码
	db.setDatabaseName("runoob");	//使用哪个数据库

	//打开数据库
	if (!db.open())		//数据库打开失败
	{
		QMessageBox::warning(this, QString::fromLocal8Bit("错误"), db.lastError().text());
		return;
	}
	
	QSqlQuery query(db);		//数据库操作对象
	bool d = query.exec("create table student(id int primary key auto_increment,name varchar(255),age int,score int);");	//创建一张表
	//query.exec("INSERT into student(id,`name`,age,score) VALUES (1,'mike',18,59);");		//插入数据

	//批量插入  odbc风格  
	// ?  相当于占位符
	//query.prepare("INSERT into student(`name`,age,score) VALUES (?,?,?);");
	给字段设置内容   给字段绑定相应的值
	//QVariantList nameList;
	//nameList << "xiaoming" << "xiaozhang" << "xiaowang";
	//QVariantList ageList;
	//ageList << 11 << 22 << 33;
	//QVariantList scoreList;
	//scoreList << 59 << 69 << 79;
	给字段绑定相应的值 按顺序绑定
	//query.addBindValue(nameList);
	//query.addBindValue(ageList);
	//query.addBindValue(scoreList);
	执行预处理命令
	//query.execBatch();

	//oracle 风格
	//占位符 ： + 自定义名字
	query.prepare("INSERT into student(`name`,age, score) VALUES(:name , :age , :score );");
	QVariantList nameList;
	nameList << "xiaoyi" << "xiaoer" << "xiaosan";
	QVariantList ageList;
	ageList << 56 << 60 << 58;
	QVariantList scoreList;
	scoreList << 98 << 99 << 100;
	//给字段绑定相应的值 不一定要按顺序绑定
	query.bindValue(":name", nameList);
	query.bindValue(":age", ageList);
	query.bindValue(":score", scoreList);
	//执行预处理命令
	query.execBatch();

	query.exec("select * from student");
	while (query.next())		//一行一行遍历
	{
		//取出当前行的内容
		qDebug() << query.value(0).toInt()
			<< query.value(1).toString()
			<< query.value("age").toInt()
			<< query.value("score").toInt();
	}
}
void QtDataBase::slotDel()
{
	QString name = ui.lineEdit->text();
	QString sql = QString("DELETE FROM student WHERE `name`= '%1';").arg(name);
	//开启一个事务
	QSqlDatabase::database().transaction();	//获取当前操作的数据库
	QSqlQuery query;
	query.exec(sql);
}
void QtDataBase::slotSure()
{
	//确定删除
	QSqlDatabase::database().commit();
}
void QtDataBase::slotCancel()
{
	//回滚 撤销
	QSqlDatabase::database().rollback();
}