qt怎么连接已经存在的sqlite3数据库

发布网友发布时间：2022-04-08 04:57

共2个回答

懂视网时间：2022-04-08 09:18

#include <QtWidgets/QApplication> #include <QSqlDatabase> #include <QSqlError> #include <QSqlQuery> #include <QtCore/QDir> #include <QMessageBox> bool createdb(); int main(int argc, char *argv[]) { QApplication a(argc, argv); createdb(); mysql w; w.show(); return a.exec(); } bool createdb() { bool bret = false; //这里创建一个db目录存储数据库文件 QString strdbpath(""); strdbpath = QCoreApplication::applicationDirPath(); strdbpath += "/db"; QDir dir(""); dir.mkpath(strdbpath); strdbpath += "/mysqlite.db"; QSqlDatabase dbset = QSqlDatabase::addDatabase("QSQLITE", "file"); dbset.setDatabaseName(strdbpath); if (!dbset.open()) { //LOG_ALL_ERROR(QStringLiteral("failed open mysqlite.db")); return bret; } QSqlQuery setquery(QSqlDatabase::database("file", true)); if(!setquery.exec("PRAGMA foreign_keys = ON;")) { QSqlError sqlerror = setquery.lastError(); QString texterr = sqlerror.text(); QMessageBox::information(nullptr, "errormsg", texterr); return false; } //商品详情表 bret = setquery.exec("create table tb_goods(rfid varchar(33) primary key, name varchar(200) not null)"); if (!bret) { QSqlError sqlerror = setquery.lastError(); QString texterr = sqlerror.text(); if (texterr.contains("already exists", Qt::CaseInsensitive)) bret = true; else { //LOG_ALL_ERROR(QStringLiteral("failed create table tb_goods.")); return bret; } } //这里条码对照表和skuid对照表因为可能牵涉到多对多的情况故没有作为主键 //条码对照表 bret = setquery.exec("create table tb_barcode(id INTEGER PRIMARY KEY AUTOINCREMENT, " "barcode varchar(100) not null, rfid varchar(33) not null, " "FOREIGN KEY(rfid) REFERENCES tb_goods(rfid) ON DELETE cascade)"); if (!bret) { QSqlError sqlerror = setquery.lastError(); QString texterr = sqlerror.text(); if (texterr.contains("already exists", Qt::CaseInsensitive)) bret = true; else { return bret; } } return bret; }

mysql.h

#ifndef MYSQL_H
#define MYSQL_H

#include <QtWidgets/QMainWindow>
#include <QSqlQueryModel>
#include "ui_mysql.h"

class mysql : public QMainWindow
{
 Q_OBJECT

public:
 mysql(QWidget *parent = 0);
 ~mysql();

private slots:
 void on_insertButton_clicked();
 void on_deleteButton_clicked();
 void on_updateButton_clicked();

private:
 void reflushModel();
 void setAttibutes(QTableView* pView, int nHeaderHeight = 45, int nColumnHeight = 45);

private:
 Ui::mysqlClass ui;
 QSqlQueryModel m_goodsmodel;
 QSqlQueryModel m_barcodemodel;
};

#endif // MYSQL_H

mysql.cpp

#include "mysql.h"
#include <QSqlError>
#include <QSqlQuery>
#include <QScrollBar>

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

 m_goodsmodel.setQuery("select * from tb_goods", QSqlDatabase::database("file", true));
 m_goodsmodel.setHeaderData(0, Qt::Horizontal, QStringLiteral("id"));
 m_goodsmodel.setHeaderData(1, Qt::Horizontal, QStringLiteral("名称"));

 m_barcodemodel.setQuery("select * from tb_barcode", QSqlDatabase::database("file", true));
 m_barcodemodel.setHeaderData(0, Qt::Horizontal, QStringLiteral("id"));
 m_barcodemodel.setHeaderData(1, Qt::Horizontal, QStringLiteral("扫描码"));
 m_barcodemodel.setHeaderData(2, Qt::Horizontal, QStringLiteral("rfid"));

 ui.goodView->setModel(&m_goodsmodel);
 ui.skuidView->setModel(&m_barcodemodel);
 setAttibutes(ui.goodView);
 setAttibutes(ui.skuidView);
}

mysql::~mysql()
{
}

void mysql::on_insertButton_clicked()
{
 QSqlQuery setquery(QSqlDatabase::database("file", true));
 setquery.exec("insert into tb_goods values(‘123456789‘, ‘aaa‘)");
 setquery.exec("insert into tb_goods values(‘23456789‘, ‘aaeffdda‘)");

 setquery.exec("insert into tb_barcode(barcode,rfid) values(‘adddaeee‘, ‘123456789‘)");
 reflushModel();
}

//这里应该问题不是很大
void mysql::on_deleteButton_clicked()
{
 QSqlQuery setquery(QSqlDatabase::database("file", true));
 setquery.exec("delete from tb_goods where rfid=‘123456789‘");
 reflushModel();
}

void mysql::on_updateButton_clicked()
{
 QSqlQuery setquery(QSqlDatabase::database("file", true));
 setquery.exec("update tb_goods set name=‘redddd‘ where rfid=‘123456789‘");

 reflushModel();
}

void mysql::reflushModel()
{
 m_goodsmodel.setQuery("select * from tb_goods", QSqlDatabase::database("file", true));
 m_barcodemodel.setQuery("select * from tb_barcode", QSqlDatabase::database("file", true));
}

void mysql::setAttibutes(QTableView* pView, int nHeaderHeight, int nColumnHeight)
{
 if ( pView == NULL )
 {
 QString strTemp = QStringLiteral("pView== NULL！设置表格属性失败！");
 return;
 }

 pView->horizontalHeader()->setFixedHeight(nHeaderHeight);  //设置表头的高度
 pView->horizontalHeader()->setSectionsClickable(false);   //设置表头不可点击（默认点击后进行排序）
 pView->horizontalHeader()->setStretchLastSection(true);   //最后一列占满剩余空白
 pView->setSelectionBehavior(QAbstractItemView::SelectRows);  //设置选择行为时每次选择一行 
 pView->setEditTriggers(QAbstractItemView::NoEditTriggers);  //使表视图只读 
 pView->verticalHeader()->setDefaultSectionSize(nColumnHeight);  //设置行高
 pView->setAlternatingRowColors(true);     //可以交替颜色显示
 pView->setShowGrid(false);      //设置不显示格子线
 pView->setCornerButtonEnabled(false);     //左上角的按钮不可用，此按钮功能，一点击，全选

 pView->horizontalScrollBar()->setStyleSheet(
 "QScrollBar:horizontal{height:10px;background:transparent;background-color:rgb(248, 248, 248);margin:0px,0px,0px,0px;padding-left:10px;padding-right:10px;}"
 "QScrollBar::handle:horizontal{height:10px;background:lightgray;border-radius:5px;/*min-height:20;*/}"
 "QScrollBar::handle:horizontal:hover{height:10px;background:gray;border-radius:5px;/*min-height:20;*/}"
 "QScrollBar::add-line:horizontal{/*height:10px;width:10px;*/border-image:url(:/button/images/button/right.png);/*subcontrol-position:right;*/}"
 "QScrollBar::sub-line:horizontal{/*height:10px;width:10px;*/border-image:url(:/button/images/button/left.png);/*subcontrol-position:left;*/}"
 "QScrollBar::add-line:horizontal:hover{/*height:10px;width:10px;*/border-image:url(:/button/images/button/right_mouseDown.png);/*subcontrol-position:right;*/}"
 "QScrollBar::sub-line:horizontal:hover{/*height:10px;width:10px;*/border-image:url(:/button/images/button/left_mouseDown.png);/*subcontrol-position:left;*/}"
 "QScrollBar::add-page:horizontal,QScrollBar::sub-page:horizontal{background:transparent;border-radius:5px;}"
 );

 pView->verticalScrollBar()->setStyleSheet(
 "QScrollBar:vertical{width:10px;background:transparent;background-color:rgb(248, 248, 248);margin:0px,0px,0px,0px;padding-top:10px;padding-bottom:10px;}"
 "QScrollBar::handle:vertical{width:10px;background:lightgray ;border-radius:5px;min-height:20;}"
 "QScrollBar::handle:vertical:hover{width:10px;background:gray;border-radius:5px;min-height:20;}"
 "QScrollBar::add-line:vertical{height:10px;width:10px;border-image:url(:/button/images/button/down.png);subcontrol-position:bottom;}"
 "QScrollBar::sub-line:vertical{height:10px;width:10px;border-image:url(:/button/images/button/up.png);subcontrol-position:top;}"
 "QScrollBar::add-line:vertical:hover{height:10px;width:10px;border-image:url(:/button/images/button/down_mouseDown.png);subcontrol-position:bottom;}"
 "QScrollBar::sub-line:vertical:hover{height:10px;width:10px;border-image:url(:/button/images/button/up_mouseDown.png);subcontrol-position:top;}"
 "QScrollBar::add-page:vertical,QScrollBar::sub-page:vertical{background:transparent;border-radius:5px;}"
 );
}

4.备注

1.完整的代码下载http://download.csdn.net/detail/zhang_ruiqiang/8939977

2.还存在的问题使用自带的自增作为主键可能会出现越界的问题，不过integer最大为9223372036854775807如果数据量不是很大的话可以不必考虑

3.在vs2010+qt 5.40 + win7下编译通过

4.参考http://bbs.csdn.net/topics/391065975

qt实现sqlite3的级联删除

标签：qt sqlite3 级联删除

热心网友时间：2022-04-08 06:26

采用将项目中的sqlite文件拷到android内存卡上的固定位置，先判断位置上是否存在sqlite文件，不存在则复制文件。 sqlite可放在res的raw目录下，亦可放在assets下，放在assets下，可新建其他层次目录，本例选择assets
1.首先，添加sdcard权限在AndroidManifest.xml
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
在activity中有如下代码

2.Java代码

button2.setOnClickListener(new Button.OnClickListener() {

@Override
publicvoid onClick(View arg0) {
try{
String DATABASE_PATH = android.os.Environment.getExternalStorageDirectory().getAbsolutePath()
+ "/testdb"; //将要存放于的文件夹
String DATABASE_FILENAME = "testDatabase.db"; //文件名
String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
File dir = new File(DATABASE_PATH);
// 如果/sdcard/testdb目录中存在，创建这个目录
if (!dir.exists())
dir.mkdir();
// 如果在/sdcard/testdb目录中不存在
// test.db文件，则从asset\db目录中复制这个文件到
// SD卡的目录（/sdcard/testdb）
if (!(new File(databaseFilename)).exists()) {
// 获得封装testDatabase.db文件的InputStream对象
AssetManager asset=getAssets();
InputStream is=asset.open("db/testDatabase.db");
FileOutputStream fos = new FileOutputStream(databaseFilename);
byte[] buffer = newbyte[8192];
int count = 0;
// 开始复制testDatabase.db文件
while ((count = is.read(buffer)) > 0) {
fos.write(buffer, 0, count);
}
fos.close();
is.close();
asset.close();
}
SQLiteDatabase mSQLiteDatabase=openOrCreateDatabase(databaseFilename, Activity.MODE_PRIVATE, null);//有则打开，没有创建
Cursor cur=mSQLiteDatabase.rawQuery("select * from table1", null);
if(cur!=null){
if(cur.moveToFirst()){
do{
int idColumnIndex=cur.getColumnIndex("id");
int numColumnIndex=cur.getColumnIndex("num");
int dataColumnIndex=cur.getColumnIndex("data");
int id=cur.getInt(idColumnIndex);
int num=cur.getInt(numColumnIndex);
String data=cur.getString(dataColumnIndex);
System.out.println("id:"+id+";num:"+num+";data:"+data);
}while(cur.moveToNext());
cur.close();
}
}
mSQLiteDatabase.close();//关闭数据库连接
}catch(Exception e){
e.printStackTrace();
}
//deleteDatabase("testDatabase.db");//删除数据库
}
});