文档文库

手机版

投诉建议

首页 > 实训五

实训五

发布时间：2013-12-27 13:26:37 来源：文档文库

小中大

字号：

手机查看

上海第二工业大学计算机科学与技术系

学生实训报告

一、实训目的

1、理解数据库概念及数据库类型；

2、掌握Qt中SQLite数据库的安装、连接、查询、查看表等功能的实现；

3、熟练使用编程中应用的各种SQL指令执行的函数，掌握数据库的删除、添加、修改操作识。

二、设备和仪器

装有Linux和Windows操作系统的PC机一台

三、实训内容

1、完成书本P：263中的例题cdcollection和cdtables；

Cdcollection

MainForm.cpp

MainForm::MainForm(QWidget *parent, const char *name)

: QDialog(parent, name)

{

setCaption(tr("CD Collection")); //标题为CD Collection

splitter = 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 CD

cdTable->addColumn("name", tr("Artist")); //name Artist

cdTable->addColumn("country", tr("Country")); //country Country

cdTable->addColumn("year", tr("Year")); //year Year

cdTable->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就刷新QDataTable

trackTable->refresh();

}

void MainForm::editCd()

{

QSqlRecord *record = cdTable->currentRecord(); //使用当前的数据组装光盘的ID

if (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.cpp

ArtistForm::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属性为true

artistTable->refresh(); //artist表刷新，使用数据库中的数据组装入QDTABLE

updateButton = 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); //设置边距为11

mainLayout->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()); }//删除这个艺术家的所有CD

void ArtistForm::primeInsertArtist(QSqlRecord *buffer)

{//当用户开始输入一个新的记录，此信号被发送

buffer->setValue("country", "USA"); //设置新纪录的country字段的默认值为USA

}

void ArtistForm::beforeInsertArtist(QSqlRecord *buffer)

{//当用户编辑完成新记录并且保存后，此信号被发送

buffer->setValue("id", generateId("artist", db));//设置id字段的值为一个被生成值，generateld()函数生成一个唯一的主键

}

Cdtables

cdtables.cpp

#include

#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,duration

query.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表中输入内容