实训五

上海第二工业大学计算机科学与技术系学生实训报告一、实训目的1、理解数据库概念及数据库类型；2、掌握Qt中SQLite数据库的安装、连接、查询、查看表等功能的实现；3、熟练使用编程中应用的各种SQL指令执行的函数，掌握数据库的删除、添加、修改操作识。

二、设备和仪器装有Linux和Windows操作系统的PC机一台三、实训内容1、完成书本P：263中的例题cdcollection和cdtables；CdcollectionMainForm.cppMainForm::MainForm(QWidget *parent, const char *name): QDialog(parent, name){setCaption(tr("CD Collection")); //标题为CD Collectionsplitter = new QSplitter(Vertical, this);QSqlSelectCursor *cdCursor = new QSqlSelectCursor("SELECT cd.id, title, name, country, year " // 将cd表和artist表连接起来查询"FROM cd, artist WHERE cd.artistid = artist.id");if (!cdCursor->isActive()) {QMessageBox::critical(this, tr("CD Collection"),tr("The database has not been created.\n""Run the cdtables example to create a sample ""database, then copy cdcollection.dat into ""this directory and restart this application."));qApp->quit(); //若cursor查询失败，弹出消息框说明并终止程序}cdTable = new QDataTable(cdCursor, false, splitter);cdTable->addColumn("title", tr("CD")); //设置表的信息title CDcdTable->addColumn("name", tr("Artist")); //name ArtistcdTable->addColumn("country", tr("Country")); //country CountrycdTable->addColumn("year", tr("Year")); //year YearcdTable->setAutoDelete(true);cdTable->refresh();QSqlCursor *trackCursor = new QSqlCursor("track");trackCursor->setMode(QSqlCursor::ReadOnly); // QDataTable 为只读trackTable = new QDataTable(trackCursor, false, splitter);trackTable->setSort(trackCursor->index("number")); //调用setSort（）使用音轨序号对音轨进行排序trackTable->addColumn("title", tr("Track"));trackTable->addColumn("duration", tr("Duration"));trackTable->setAutoDelete(true);trackTable->refresh();//进行更新//用户界面添加按钮部件addButton = new QPushButton(tr("&Add"), this);editButton = new QPushButton(tr("&Edit"), this);deleteButton = new QPushButton(tr("&Delete"), this);refreshButton = new QPushButton(tr("&Refresh"), this);quitButton = new QPushButton(tr("&Quit"), this);//将信号与槽进行连接connect(addButton, SIGNAL(clicked()),this, SLOT(addCd()));connect(editButton, SIGNAL(clicked()),this, SLOT(editCd()));.......QHBoxLayout *buttonLayout = new QHBoxLayout;buttonLayout->addWidget(addButton);.........QVBoxLayout *mainLayout = new QVBoxLayout(this);mainLayout->setMargin(11);........}void MainForm::addCd(){CdForm form(this); //弹出对话框if (form.exec()) {cdTable->refresh(); //若用户单击它上面的update就刷新QDataTabletrackTable->refresh();}}void MainForm::editCd(){QSqlRecord *record = cdTable->currentRecord(); //使用当前的数据组装光盘的IDif (record) {CdForm form(record->value("id").toInt(), this);if (form.exec()) {cdTable->refresh();trackTable->refresh();}}}void MainForm::deleteCd(){QSqlRecord *record = cdTable->currentRecord();if (record) {QSqlQuery query;query.exec("DELETE FROM track WHERE cdid = "+ record->value("id").toString());query.exec("DELETE FROM cd WHERE id = "+ record->value("id").toString()); //从track表中移除当前光盘中所有的音轨，从cd表中移除当前光盘cdTable->refresh(); //刷新cd 表trackTable->refresh();//刷新track表}}void MainForm::currentCdChanged(QSqlRecord *record){//当用户修改当前光盘或者用户变化当前光盘为另一个光盘是，这个信号才会被发送trackTable->setFilter("cdid = "+ record->value("id").toString());//只有当前光盘发生变化，对track表调用setFilter（）并且刷新他来显示当前光盘相关的音轨trackTable->refresh(); //调用refresh（）强制这个表使用相关的数据重新组装自己}ArtistForm.cppArtistForm::ArtistForm(QWidget *parent, const char *name): QDialog(parent, name){setCaption(tr("Update Artists"));db = QSqlDatabase::database("ARTIST"); //从使用“ARTIST”数据库链接的事务处理开始db->transaction();QSqlCursor *artistCursor = new QSqlCursor("artist", true, db);artistTable = new QDataTable(artistCursor, false, this); //创建一个在数据库artist表上的QSqlCursor，并且用QDataTable来显示artistTable->addColumn("name", tr("Name"));artistTable->addColumn("country", tr("Country"));artistTable->setAutoDelete(true); //删除项目artistTable->setConfirmDelete(true);//设置是否确认删除artistTable->setSorting(true); //设置sorting属性为trueartistTable->refresh(); //artist表刷新，使用数据库中的数据组装入QDTABLEupdateButton = new QPushButton(tr("Update"), this); //创建Update按钮updateButton->setDefault(true); //Update按钮的默认为选中cancelButton = new QPushButton(tr("Cancel"), this);connect(artistTable, SIGNAL(beforeDelete(QSqlRecord *)),//连接动作槽共三个this, SLOT(beforeDeleteArtist(QSqlRecord *)));connect(artistTable, SIGNAL(primeInsert(QSqlRecord *)),this, SLOT(primeInsertArtist(QSqlRecord *)));connect(artistTable, SIGNAL(beforeInsert(QSqlRecord *)),this, SLOT(beforeInsertArtist(QSqlRecord *)));connect(updateButton, SIGNAL(clicked()), //连接按钮槽共两个this, SLOT(accept()));connect(cancelButton, SIGNAL(clicked()),this, SLOT(reject()));QHBoxLayout *buttonLayout = new QHBoxLayout; //新建布局buttonLayout->addStretch(1);buttonLayout->addWidget(updateButton); //创建update按钮buttonLayout->addWidget(cancelButton); //创建cancel按钮QVBoxLayout *mainLayout = new QVBoxLayout(this);mainLayout->setMargin(11); //设置边距为11mainLayout->setSpacing(6); //设置前面的空距mainLayout->addWidget(artistTable);mainLayout->addLayout(buttonLayout); //将按钮布局放置到垂直布局中}void ArtistForm::accept(){db->commit(); //提交数据事物QDialog::accept(); //调用accept()基类}void ArtistForm::reject(){db->rollback(); //回滚到事务处理QDialog::reject(); //调用基类reject()函数}void ArtistForm::beforeDeleteArtist(QSqlRecord *buffer){QSqlQuery query(db);query.exec("DELETE FROM track WHERE track.id IN ""(SELECT track.id FROM track, cd ""WHERE track.cdid = cd.id AND cd.artistid = "+ buffer->value("id").toString() + ")");//查询是在CD中删除这个艺术家的所有音轨query.exec("DELETE FROM cd WHERE artistid = "+ buffer->value("id").toString()); }//删除这个艺术家的所有CDvoid ArtistForm::primeInsertArtist(QSqlRecord *buffer){//当用户开始输入一个新的记录，此信号被发送buffer->setValue("country", "USA"); //设置新纪录的country字段的默认值为USA}void ArtistForm::beforeInsertArtist(QSqlRecord *buffer){//当用户编辑完成新记录并且保存后，此信号被发送buffer->setValue("id", generateId("artist", db));//设置id字段的值为一个被生成值，generateld()函数生成一个唯一的主键}Cdtablescdtables.cpp#include <qapplication.h>#include <qsqldatabase.h>#include "../cdcollection/connection.h"int main(int argc, char *argv[]){QApplication app(argc, argv);if (!createConnections())return 1;QSqlQuery query;query.exec("DROP TABLE track");query.exec("DROP TABLE cd");query.exec("DROP TABLE artist");//创建srtist表，此表中有id,name,country.query.exec("CREATE TABLE artist (""id INTEGER PRIMARY KEY, ""name VARCHAR(40) NOT NULL, ""country VARCHAR(40))");//创建trackt表，此表中有id,cdid,number,title,durationquery.exec("CREATE TABLE cd (""id INTEGER PRIMARY KEY, ""artistid INTEGER NOT NULL, ""title VARCHAR(40) NOT NULL, ""year INTEGER NOT NULL)");query.exec("CREATE TABLE track (""id INTEGER PRIMARY KEY, ""cdid INTEGER NOT NULL, ""number INTEGER NOT NULL, ""title VARCHAR(40) NOT NULL, ""duration INTEGER NOT NULL)");#if 1//向artist表中输入内容query.exec("INSERT INTO artist ""VALUES (1001, 'Gluecifer', 'Norway')");query.exec("INSERT INTO artist ""VALUES (1002, 'The Sounds', 'Sweden')");query.exec("INSERT INTO artist ""VALUES (1003, 'Melvins', 'USA')");query.exec("INSERT INTO artist ""VALUES (1004, 'Strapping Young Lads', 'Canada')"); //向cd表中输入内容query.exec("INSERT INTO cd ""VALUES (2001, 1001, 'Riding the Tiger', 1997)");query.exec("INSERT INTO cd ""VALUES (2002, 1001, 'Easy Living', 2002)");query.exec("INSERT INTO cd ""VALUES (2003, 1002, 'Living in America', 2002)");query.exec("INSERT INTO cd ""VALUES (2004, 1003, 'Bullhead', 1991)");query.exec("INSERT INTO cd ""VALUES (2005, 1003, 'Stag', 1996)");query.exec("INSERT INTO cd ""VALUES (2006, 1003, 'Hostile Ambient Takeover', ""2002)");query.exec("INSERT INTO cd ""VALUES (2007, 1003, '26 Songs', 2003)");query.exec("INSERT INTO cd ""VALUES (2008, 1004, 'City', 1997)");query.exec("INSERT INTO cd ""VALUES (2009, 1004, 'No Sleep Till Bedtime', ""1998)");query.exec("INSERT INTO cd ""VALUES (2010, 1004, 'Syl', 2003)");//向track表中输入内容query.exec("INSERT INTO track ""VALUES (3001, 2004, 01, 'Boris', 514)");query.exec("INSERT INTO track ""VALUES (3002, 2004, 02, 'Anaconda', 143)");query.exec("INSERT INTO track ""VALUES (3003, 2004, 03, 'Ligature', 229)");query.exec("INSERT INTO track ""VALUES (3004, 2004, 04, 'It''s Shoved', 155)");query.exec("INSERT INTO track ""VALUES (3005, 2004, 05, 'Zodiac', 254)");query.exec("INSERT INTO track ""VALUES (3006, 2004, 06, 'If I Had An Exorcism', ""187)");query.exec("INSERT INTO track ""VALUES (3007, 2004, 07, 'Your Blessed', 339)");query.exec("INSERT INTO track ""VALUES (3008, 2004, 08, 'Cow', 271)");#endifreturn 0;}2、SQLite数据库的连接与使用●SQLite在WinXP下数据库的创建●在qt3中连接sqlite3在Qt3中新建一个dialog设置connection新建main.cpp双击form，在mainform.ui.h文件中添加代码：将sqlite3.h sqlite3.lib sqlite3.dll文件复制到工程目录中。