# qt-ndatabase

**Repository Path**: yjihrp/qt-ndatabase

## Basic Information

- **Project Name**: qt-ndatabase
- **Description**: 简化 Qt 数据库操作
- **Primary Language**: C++
- **License**: MIT
- **Default Branch**: dev
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 4
- **Forks**: 4
- **Created**: 2021-04-29
- **Last Updated**: 2024-08-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Qt, Sqlite, MySQL, QDataBase

## README

# qt-ndatabase 介绍

1、对 QSqlDatabase 进行封装

使用时自动打开连接

释放时自动关闭连接

2、简化 sql 的拼写

提供对单表的查询、添加、删除、修改、count、exist、limit 方法

3、简化 sql 参数绑定

添加数据时，支持将 QVariantMap、QList<QVariantMap> 绑定在 insert 的 values，也就是说支持批量添加数据

删除数据时，支持将 QVariantMap 绑定在 where 条件上，使用 and 连接

修改数据时，支持将 QVariantMap 绑定在 set 条件上

4、封装查询结果 NDataSet,NDataTable 方便读取数据

NDataSet 的 tables 字段包含多个结果

NDataTable 单个结果

# 查询-自定义 sql

```c++
NSqlite session(this->db());
// 全部列
auto sql = QString("select * from student");
auto query = session.list(sql);
this->print(query);

// 部分列
sql = QString("select id,name from student;");
query = session.list(sql);
this->print(query);

// where 条件
sql = QString("select * from address where id = 1;");
query = session.list(sql);
this->print(query);

// where 条件,QT 绑定参数方式1
sql = QString("select * from address where id = ?;");
query = session.list(sql,QVariantList{1});
this->print(query);

// where 条件,QT 绑定参数方式2
sql = QString("select * from address where id = :id;");
query = session.list(sql,QVariantList{1});
this->print(query);
```

# 查询-单表
```c++
NSqlite session(this->db());
auto table = session.table("student");
this->print(table);

// where 条件-字符串
table = session.table("student","id=1");
this->print(table);

table = session.table("student",QString(),"id desc");
this->print(table);

table = session.table("student",QString(),QStringList{"id desc"});
this->print(table);

// where 条件-QVariantMap
auto table = session.table("student",QVariantMap{{"id",1}});
this->print(table);

table = session.table("student",QVariantMap{{"id",2},{"name","n2"}});
this->print(table);

table = session.table("student",QVariantMap{},"id desc");
this->print(table);

// 选择列
QStringList columns = QStringList{"id","name","address"};
auto table = session.table("student",columns);
this->print(table);

columns = QStringList{"id","name"};
table = session.table("student",columns);
this->print(table);

columns = QStringList{"id","name","address"};
table = session.table("student",columns,"id = 1");
this->print(table);

columns = QStringList{"id","name"};
table = session.table("student",columns,"","id desc");
this->print(table);

columns = QStringList{"id","name"};
table = session.table("student",columns,"",QStringList{"id desc"});
this->print(table);

// 选择列 + where 条件
QStringList columns = QStringList{"id","name","address"};
auto table = session.table("student",columns,QVariantMap{{"id",1}});
this->print(table);

table = session.table("student",columns,QVariantMap{{"id",1}},"id desc");
this->print(table);

table = session.table("student",columns,QVariantMap(),"id desc");
this->print(table);

table = session.table("student",columns,QVariantMap{{"id",1}},QStringList{"id desc"});
this->print(table);
```

# 添加数据

```c++
NSqlite session(this->db());
QStringList columns = QStringList{"id","name","address"};
auto data = QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}};
// 全部列
auto added = session.add("student",columns,data);
qDebug() << "added" << added;
auto query = session.list("select * from student where id=:id;",QVariantList{1});
this->print(query);

// 部分列
columns = QStringList{"id","address"};
data = QVariantMap{{"id",2},{"name","n2"},{"address","addr2"}};
added = session.add("student",columns,data);
qDebug() << "added" << added;
query = session.list("select * from student where id=:id;",QVariantList{2});
this->print(query);

```
# 添加-批量添加-单表
```c++
NSqlite session(this->db());
// 全部列
QList<QVariantMap> data = QList<QVariantMap>{
    QVariantMap{{"id",1},{"name","n1"},{"address","addr1"}},
    QVariantMap{{"id",2},{"name","n2"},{"address","addr2"}},
    QVariantMap{{"id",3},{"name","n3"},{"address","addr3"}}
};

auto added = session.add("student",data);
qDebug() << "added" << added;
auto query = session.list("select * from student;");
this->print(query);

// 部分列
data = QList<QVariantMap>{
    QVariantMap{{"id",4},{"address","add4"}},
    QVariantMap{{"id",5},{"name","n5"}},
    QVariantMap{{"id",6},{"name","n6"},{"address","addr6"}}
};

added = session.add("student",data);
qDebug() << "added" << added;
query = session.list("select * from student;");
this->print(query);
```

# 删除-单表
```c++
NSqlite session(this->db());
auto removed = session.remove("student");
qDebug() << "remove" << removed;

NSqlite session(this->db());
// 正确条件,返回 true
auto removed = session.remove("student",QVariantMap{{"id",1},{"name","n1"}});
qDebug() << "remove" << removed;

// 错误条件,返回 true
removed = session.remove("student",QVariantMap{{"id",1},{"name","n2"}});
qDebug() << "remove" << removed;
```

# 更新
```c++
NSqlite session(this->db());
// 一个字符串条件
auto data = QVariantMap{{"name","name1"},{"address","address1"}};
auto updated = session.update("student",data,"id=1");
qDebug() << "update" << updated;
auto query = session.list("select * from student where id = 1;");
this->print(query);

// 一个 QVariantMap 条件
data = QVariantMap{{"name","name2"},{"address","address2"}};
updated = session.update("student",data,QVariantMap{{"id",2}});
qDebug() << "update" << updated;
query = session.list("select * from student where id = 2;");
this->print(query);

// 多个字符串条件
data = QVariantMap{{"address","address3"}};
updated = session.update("student",data,"id=3 and name='n3'");
qDebug() << "update" << updated;
query = session.list("select * from student where id = 3;");
this->print(query);

// 多个 QVariantMap 条件
data = QVariantMap{{"address","address5"}};
updated = session.update("student",data,QVariantMap{{"id",5},{"name","n5"}});
qDebug() << "update" << updated;
query = session.list("select * from student where id = 5;");
this->print(query);
```