二、QxOrm之增删改查

一、项目通用文件

ormDemo.pro

#-------------------------------------------------
#
# Project created by QtCreator 2020-06-30T22:46:06
#
#-------------------------------------------------

QT       += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

TARGET = ormDemo
TEMPLATE = app

# The following define makes your compiler emit warnings if you use
# any feature of Qt which as been marked as deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS

# You can also make your code fail to compile if you use deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0


#通过它可以知道项目是否正在编译
DEFINES += _BUILDING_QX_BLOG

# 预编译头文件
!contains(DEFINES, _QX_NO_PRECOMPILED_HEADER) {
    PRECOMPILED_HEADER = precompiled.h
}

# QxOrm 库相关配置
INCLUDEPATH += $$PWD/QxOrm/include
LIBS += -L$$PWD/QxOrm/lib

# 设置生成的目标名称、添加依赖库
CONFIG(debug, debug|release) {
    LIBS += -lQxOrmd
} else {
    LIBS += -lQxOrm
}



SOURCES += \
        main.cpp \
        dialog.cpp \
    author.cpp \
    category.cpp \
    blog.cpp \
    comment.cpp

HEADERS += \
        dialog.h \
    export.h \
    precompiled.h \
    author.h \
    category.h \
    blog.h \
    comment.h

FORMS += \
        dialog.ui

export.h

#ifndef EXPORT_H
#define EXPORT_H

#ifdef _BUILDING_QX_BLOG
#define QX_BLOG_DLL_EXPORT  QX_DLL_EXPORT_HELPER
#else
#define QX_BLOG_DLL_EXPORT  QX_DLL_IMPORT_HELPER
#endif

#ifdef _BUILDING_QX_BLOG
#define QX_REGISTER_HPP_QX_BLOG  QX_REGISTER_HPP_EXPORT_DLL
#define QX_REGISTER_CPP_QX_BLOG  QX_REGISTER_CPP_EXPORT_DLL
#else
#define QX_REGISTER_HPP_QX_BLOG  QX_REGISTER_HPP_IMPORT_DLL
#define QX_REGISTER_CPP_QX_BLOG  QX_REGISTER_CPP_IMPORT_DLL
#endif

#endif // EXPORT_H

precompiled.h

#ifndef PRECOMPILED_H
#define PRECOMPILED_H

#include <QxOrm.h>
#include "export.h"

#endif // PRECOMPILED_H

二、准备数据库表对应的类

1.Author

Author.h

#ifndef AUTHOR_H
#define AUTHOR_H


#include "precompiled.h"

#include <QSharedPointer>

class Blog;
/**
 * @brief 作者
 */
//class QX_BLOG_DLL_EXPORT Author
class Author
{
public:
    //1对多关系需定义如下
    typedef QSharedPointer<Blog> BlogPtr;
    typedef QVector<BlogPtr> ListBlog;

    enum EnumSex{
        MALE,
        FEMALE,
        UNKOWN
    };

    QString authorId;
    QString name;
    QDate birthday;
    EnumSex sex;
    //1对多关系需定义如下属性
    ListBlog blogs;

public:
    Author();
    virtual ~Author();
    int age() const;
};

//主键不是整数类型的时候使用
QX_REGISTER_PRIMARY_KEY(Author, QString)
/*
 * 用于将 Author 类注册到 QxOrm 的上下文中：
 * 参数一：表示要注册的当前类 - Author；
 * 参数二：基类，如果没有基类，则使用 qx::trait::no_base_class_defined；
 * 参数三：用于序列化的类版本。
 */
QX_REGISTER_HPP_QX_BLOG(Author, qx::trait::no_base_class_defined, 0)

typedef QSharedPointer<Author> AuthorPtr;
typedef qx::QxCollection<QString, AuthorPtr> ListAuthor;

#endif // AUTHOR_H

Author.cpp

#include "author.h"

#include "blog.h"
/*
 *     QxOrm_Impl.h，它的作用是检测内存泄露。如果使用 QxMemLeak 模块或 boost::serialization 引擎，
 * 应该在所有的 *.cpp 中包含它；否则，它便是可选的（非必须）
 */
#include <QxOrm_Impl.h>

QX_REGISTER_CPP_QX_BLOG(Author)

namespace qx {
    template <> void register_class(QxClass<Author> & t) {

        //主键
        t.id(& Author::authorId, "author_id");

        //字段映射
        t.data(& Author::name, "name");
        t.data(& Author::birthday, "birthday");
        t.data(& Author::sex, "sex");

        //表与表关系：1对多，参数一：多属性，参数二：属性类型，参数三：1的主键
        t.relationOneToMany(& Author::blogs, "ListBlog", "author_id");

        //不太理解
        t.fct_0<int>(std::mem_fn(& Author::age), "age");
    }
}

Author::Author() : authorId("0"), sex(UNKOWN)
{

}

Author::~Author()
{

}

int Author::age() const
{
    if (!birthday.isValid()) {
        return -1;
    }
    return (QDate::currentDate().year() - birthday.year());
}

2.Category

Category.h

#ifndef CATEGORY_H
#define CATEGORY_H

#include "precompiled.h"
#include <QSharedPointer>

class Blog;
/**
 * @brief 博客类别
 */
class Category
{
public:
    //多对多关系需定义如下
    typedef QSharedPointer<Blog> BlogPtr;
    typedef qx::QxCollection<long, BlogPtr> ListBlog;

    long categotyId;
    QString name;
    QString desc;
    //多对多关系需定义如下属性
    ListBlog blogs;

public:
    Category();
    virtual ~Category();
};

QX_REGISTER_HPP_QX_BLOG(Category, qx::trait::no_base_class_defined, 0)

typedef QSharedPointer<Category> CategoryPtr;
typedef qx::QxCollection<long, CategoryPtr> ListCategory;

#endif // CATEGORY_H

Category.cpp

#include "category.h"

#include "blog.h"
#include "QxOrm_Impl.h"

QX_REGISTER_CPP_QX_BLOG(Category)

namespace qx {
    template <> void register_class(QxClass<Category> & t) {

        //主键
        qx::IxDataMember * pId = t.id(& Category::categotyId, "category_id");
        //关闭主键自增
        //pId->setAutoIncrement(false);

        //字段映射
        t.data(& Category::name, "name");
        t.data(& Category::desc, "description");

        /*
         * 表与表关系：多对多
         *     是取出两个表（blog 和 category）的主键再构建另外一个表，“category_blog”，category_id，blog_id
         * 都是表 “category_blog” 字段，外键关联表 blog 和 category
         *
         */
        t.relationManyToMany(& Category::blogs, "ListBlog", "category_blog", "category_id", "blog_id");
    }
}

Category::Category() : categotyId(0)
{

}

Category::~Category()
{

}

3.Comment

Comment.h

#ifndef COMMENT_H
#define COMMENT_H

#include "precompiled.h"
#include <QSharedPointer>

class Blog;

/**
 * @brief 博客评论
 */
class Comment
{
public:
    //多对1关系需定义如下
    typedef QSharedPointer<Blog> BlogPtr;

    long commentId;
    QString text;
    QDateTime createTime;
    //多对1关系需定义如下属性
    BlogPtr blog;
public:
    Comment();
    virtual ~Comment();
};

QX_REGISTER_HPP_QX_BLOG(Comment, qx::trait::no_base_class_defined, 0)

typedef QSharedPointer<Comment> CommentPtr;
typedef QList<CommentPtr> ListComment;

#endif // COMMENT_H

Comment.cpp

#include "comment.h"

#include "blog.h"
#include "QxOrm_Impl.h"

QX_REGISTER_CPP_QX_BLOG(Comment)

namespace qx {
    template <> void register_class(QxClass<Comment> & t) {

        //主键
        t.id(& Comment::commentId, "comment_id");

        //字段映射
        t.data(& Comment::text, "comment_text");
        t.data(& Comment::createTime, "date_creation");

        //多对1关系需定义如下
        t.relationManyToOne(& Comment::blog, "blog_id");
    }
}

Comment::Comment() : commentId(0)
{

}

Comment::~Comment()
{

}

4.Blog

Blog.h

#ifndef BLOG_H
#define BLOG_H

#include "precompiled.h"

#include "author.h"
#include "category.h"
#include "comment.h"

/**
 * @brief 博客
 */
class Blog
{
public:
    long blogId;
    QString text;
    QDateTime createTime;
    //多对1关系
    AuthorPtr author;
    //1对多关系
    ListComment comments;
    //多对多关系
    ListCategory categories;
public:
    Blog();
    virtual ~Blog();
};
/*
 * 用于将 Blog 类注册到 QxOrm 的上下文中：
 * 参数一：表示要注册的当前类 - Blog；
 * 参数二：基类，如果没有基类，则使用 qx::trait::no_base_class_defined；
 * 参数三：用于序列化的类版本。
 */
QX_REGISTER_HPP_QX_BLOG(Blog, qx::trait::no_base_class_defined, 0)

typedef QSharedPointer<Blog> BlogPtr;
typedef QVector<BlogPtr> ListBlog;

#endif // BLOG_H

Blog.cpp

#include "blog.h"
/*
 *     QxOrm_Impl.h，它的作用是检测内存泄露。如果使用 QxMemLeak 模块或 boost::serialization 引擎，
 * 应该在所有的 *.cpp 中包含它；否则，它便是可选的（非必须）
 */
#include "QxOrm_Impl.h"

//和 QX_REGISTER_HPP_QX_BLOG  相同，QX_REGISTER_CPP_QX_BLOG 宏也是必需的，用于将 Blog 类注册到 QxOrm 的上下文中。
QX_REGISTER_CPP_QX_BLOG(Blog)

namespace qx {
    //在 **.cpp 文件中，需要实现 qx::register_class()，它是一个设置函数：
    template <> void register_class(QxClass<Blog> & t) {

        // 注册 Blog::blogId <=> 数据库中的主键
        t.id(& Blog::blogId, "blog_id");

        //字段映射
        t.data(& Blog::text, "blog_text");
        t.data(& Blog::createTime, "date_creation");
        //多对1关系需定义如下
        t.relationManyToOne(& Blog::author, "author_id");
        //表与表关系：1对多，参数一：多属性，参数二：属性类型，参数三：1的主键
        t.relationOneToMany(& Blog::comments, "ListComment", "blog_id");
        /*
         * 表与表关系：多对多
         *     是取出两个表（blog 和 category）的主键再构建另外一个表，“category_blog”，category_id，blog_id
         * 都是表 “category_blog” 字段，外键关联表 blog 和 category
         *
         */
        t.relationManyToMany(& Blog::categories, "ListCategory", "category_blog", "blog_id", "category_id");
    }
}

Blog::Blog() : blogId(0)
{

}

Blog::~Blog()
{

}

三、连接数据库，测试增删改查

注意：单个功能单独测试，总体测试可能随着数据的更改会出错

1.连接数据库创建表

#include "precompiled.h"
/*
 *     QxOrm_Impl.h，它的作用是检测内存泄露。如果使用 QxMemLeak 模块或 boost::serialization 引擎，
 * 应该在所有的 *.cpp 中包含它；否则，它便是可选的（非必须）
 */
#include "QxOrm_Impl.h"
#include "blog.h"
#include "author.h"
#include "comment.h"
#include "category.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    /*==================================连接 sqlite 数据库=================================*/
//    qx::QxSqlDatabase::getSingleton()->setDriverName("QSQLITE");
//    qx::QxSqlDatabase::getSingleton()->setDatabaseName("./Users.db");
//    qx::QxSqlDatabase::getSingleton()->setHostName("localhost");
//    qx::QxSqlDatabase::getSingleton()->setUserName("root");
//    qx::QxSqlDatabase::getSingleton()->setPassword("");

    /*==================================连接 MySql 数据库=================================*/
    qx::QxSqlDatabase::getSingleton()->setDriverName("QMYSQL");
    qx::QxSqlDatabase::getSingleton()->setDatabaseName("test");
    qx::QxSqlDatabase::getSingleton()->setHostName("127.0.0.1");
    qx::QxSqlDatabase::getSingleton()->setUserName("root");
    qx::QxSqlDatabase::getSingleton()->setPassword("root");
    qx::QxSqlDatabase::getSingleton()->setPort(3306);
    //在打印日志之前，格式化 sql 语句
    qx::QxSqlDatabase::getSingleton()->setFormatSqlQueryBeforeLogging(true);
    //显示执行时间
    qx::QxSqlDatabase::getSingleton()->setDisplayTimerDetails(true);
    //只是为了调试：若检测到取得的关系中无效的 offset ，断言中断
    qx::QxSqlDatabase::getSingleton()->setVerifyOffsetRelation(true);

    //创建表
    QSqlError daoError;
    /*==================================创建表（PS：但是对于 mysql 数据库，若主键是 QString，转换成 sql 类型是 text，这个
     * 时候创建表会报错，因为 mysql 主键必须有长度，得改成 varchar(20) 这样的形式，解决办法两个：1.手工创建表；2.更改源码，更改映射类型）
     * =================================*/
//    daoError = qx::dao::create_table<Author>();
//    daoError = qx::dao::create_table<Comment>();
//    daoError = qx::dao::create_table<Category>();
//    daoError = qx::dao::create_table<Blog>();
	//.......(下面的增删改查)
	return a.exec();
}

2.增

	QSqlError daoError;
/*==================================以集合的形式插入数据库=================================*/
    AuthorPtr author1;
    AuthorPtr author2;
    AuthorPtr author3;
    //这是访问 QSharedPointer 方法，用 .
    author1.reset(new Author());
    author2.reset(new Author());
    author3.reset(new Author());

    //这是访问 Author 属性，用 ->
    author1->authorId = "author_id_1";
    author1->name = "author_1";
    author1->sex = Author::MALE;
    author1->birthday = QDate::currentDate();

    author2->authorId = "author_id_2";
    author2->name = "author_2";
    author2->sex = Author::FEMALE;
    author2->birthday = QDate::currentDate();

    author3->authorId = "author_id_3";
    author3->name = "author_3";
    author3->sex = Author::FEMALE;
    author3->birthday = QDate::currentDate();

    ListAuthor authors;
    authors.insert(author1->authorId, author1);
    authors.insert(author2->authorId, author2);
    authors.insert(author3->authorId, author3);

    //将 Author 集合插入数据库
    daoError = qx::dao::insert(authors);
    //查询表中所有记录总数
    qAssert(qx::dao::count<Author>() == 3);

    /*=============================用 qx::dao::save() 插入数据===========================*/
    //利用工厂方法 + 类名，创建类
    qx::any blogAny = qx::create("Blog");
    BlogPtr blog1;
    try {
        blog1 = qx::any_cast<BlogPtr>(blogAny);
    } catch (...) {
        blog1.reset(new Blog());
    }

    blog1->text = "blog_text_1";
    blog1->createTime = QDateTime::currentDateTime();
    blog1->author = author1;
    //用 save() 方法插入数据记录
    daoError = qx::dao::save(blog1);
    
/*=============================单个插入数据===========================*/
    CommentPtr comment1 = CommentPtr(new Comment());
    CommentPtr comment2 = CommentPtr(new Comment());
    comment1->text = "comment_1 text";
    comment1->createTime = QDateTime::currentDateTime();
    comment1->blog = blog1;
    comment2->text = "comment_2 text";
    comment2->createTime = QDateTime::currentDateTime();
    comment2->blog = blog1;

    daoError = qx::dao::insert(comment1);
    daoError = qx::dao::insert(comment2);
    //查询表中记录总数
    qAssert(qx::dao::count<Comment>() == 2);

/*=============================插入，测试数据库事务===========================*/
    CategoryPtr category1 = CategoryPtr(new Category());
    CategoryPtr category2 = CategoryPtr(new Category());
    CategoryPtr category3 = CategoryPtr(new Category());

    category1->name = "category_1";
    category1->desc = "desc_1";
    category2->name = "category_2";
    category2->desc = "desc_2";
    category3->name = "category_3";
    category3->desc = "desc_3";

    {
        //创建一个 scope 来销毁临时的数据库连接

        //打开数据库事务
        QSqlDatabase db = qx::QxSqlDatabase::getDatabase();
        //QxOrm 在连接 mysql 时，默认不打开事务（后面得考虑如何打开事务）
        bool commit = db.transaction();

        daoError = qx::dao::insert(category1, &db);
        commit = (commit && !daoError.isValid());
        daoError = qx::dao::insert(category2, &db);
        commit = (commit && !daoError.isValid());
        daoError = qx::dao::insert(category3, &db);
        commit = (commit && !daoError.isValid());

//        qAssert(commit);
        qAssert(category1->categotyId != 0);
        qAssert(category2->categotyId != 0);
        qAssert(category3->categotyId != 0);

        //决定事物是提交还是回滚
        if (commit) {
            db.commit();
        } else {
            db.rollback();
        }
    } //结束 scope，‘db’ 就被销毁了
    /*==================================保存关联数据=================================*/
    //插入多对多关系
    blog1->categories.insert(category1->categotyId, category1);
    blog1->categories.insert(category2->categotyId, category3);
    //参数一：多方属性类型  参数二：插入对象
    daoError = qx::dao::save_with_relation("ListCategory", blog1);

3.删

/*==================================删除=================================*/
    daoError = qx::dao::delete_by_id(author3);
    qAssert(qx::dao::count<Author>() == 2);

4.改

/*==================================更新指定属性=================================*/
    ListBlog blogs3;
    //查询所有 blog 表数据
    daoError = qx::dao::fetch_all(blogs3);
    qAssert(blogs3.count() == 1);
    BlogPtr blog5 = blogs3.at(0);
    qAssert(blog5->text == "blog_text_1");
    //包装，因为 qx::dao::update_optimized() 只识别这样的参数类型：qx::dao::ptr<Blog>
    qx::dao::ptr<Blog> blog6 = qx::dao::ptr<Blog>(blog5);
    blog6->text = "update blog_text_1";
    //更新指定属性
    daoError = qx::dao::update_optimized(blog6);
    qAssert(!daoError.isValid());

5.查

/*==================================查询=================================*/
    qx::QxSqlQuery query("where author.sex = :sex");
    query.bind(":sex", Author::FEMALE);

    ListAuthor femaleAuthors;
    daoError = qx::dao::fetch_by_query(query, femaleAuthors);
    qAssert(femaleAuthors.count() == 2);

    //转储集合，参数二：是否采用 json 格式，默认采用 xml 格式（就是在控制台显示）
    qx::dump(femaleAuthors, true);
    qx::dump(femaleAuthors, false);

    /*===============测试 qx::QxSqlQuery::freeText() 不带/带 占位符=============*/
    query = qx_query();
    query.freeText("where author.sex = " + QString::number(static_cast<int>(Author::FEMALE)));
    daoError = qx::dao::fetch_by_query(query, femaleAuthors);
    //第二种参数形式
    query = qx_query();
    query.freeText("where author.sex = :sex", QVariantList() << Author::MALE);
    daoError = qx::dao::fetch_by_query(query, femaleAuthors);
    qAssert(femaleAuthors.count() == 1);
    //多个参数形式
    query = qx_query();
    query.freeText("where author.sex = :sex and author.author_id = :authorId",
                   QVariantList() << Author::FEMALE << "author_id_2");
    daoError = qx::dao::fetch_by_query(query, femaleAuthors);
    qAssert(femaleAuthors.count() == 1);

/*==================================查询关联数据=================================*/
    BlogPtr blogTemp;
    blogTemp.reset(new Blog());
    blogTemp->blogId = 6;
    //根据对象 id 获取该对象所有关联数据
    daoError = qx::dao::fetch_by_id_with_all_relation(blogTemp);

    qAssert(blogTemp->comments.count() == 2);
    qAssert(blogTemp->categories.count() == 2);
    qAssert(blogTemp->text == "blog_text_1");
    qAssert(blogTemp->author && blogTemp->author->authorId == "author_id_1");

    //转储集合，参数二：是否采用 json 格式，默认采用 xml 格式
    qx::dump(blogTemp, false);
    qx::dump(blogTemp, true);

    /*==================================看不太懂=================================*/
    ListBlog blogs;
    daoError = qx::dao::fetch_all_with_relation(QStringList() << "{ blog_text }" << "author_id {name, birthday}"
                                                << "ListComment {comment_text} -> blog_id -> *", blogs);
    qx::dump(blogs, true);

/*==================================看不太懂=================================*/
    ListBlog blogs1;
    daoError = qx::dao::fetch_all_with_relation(QStringList() << "<blog_alias> {blog_text}"
                                                << "author_id <author_alias> {name, birthday}"
                                                << "ListComment <ListComment_alias> {comment_text} -> blog_id <blog_alias_2> -> *<..._my_alias_suffix>",
                                                blogs1);
    qx::dump(blogs1);

    /*==================================增加子查询=================================*/
    ListBlog blogs2;
    query = qx_query().where("blog_alias.blog_text").isEqualTo("blog_text_1");
    query.addJoinQuery("ListComment_alias", "and ListComment_alias.comment_text is not null");
    query.addJoinQuery("author_alias", qx_query().freeText("and author_alias.sex = :sex", QVariantList() << Author::FEMALE));
    daoError = qx::dao::fetch_by_query_with_relation(QStringList() << "<blog_alias> {blog_text}"
                                                     << "author_id <author_alias> {name, birthday, sex}"
                                                     << "ListComment <ListComment_alias> {comment_text}",
                                                     query, blogs2);
    qx::dump(blogs2);

/*==================================只取指定列数据=================================*/
    ListBlog blogs4;
    //只取 “date_creation” 列数据
    qx::dao::fetch_all(blogs4, NULL, QStringList() << "date_creation");
    qx::dump(blogs4);

/*==================================执行自定义 sql 语句/存储过程=================================*/
    qx_query customSql("select * from author");
    daoError = qx::dao::call_query(customSql);
    customSql.dumpSqlResult();

    /*==================================执行自定义 sql 语句/存储过程，并将结果放入集合中=================================*/
    ListAuthor authors1;
    daoError = qx::dao::execute_query(customSql, authors1);
    qx::dump(authors1);
    /*==================================执行自定义 sql 语句/存储过程，并取结果集合中的第一个返回=================================*/
    qx_query customSqlCategory("select name, category_id from category");
    CategoryPtr category6 = CategoryPtr(new Category());
    daoError = qx::dao::execute_query(customSqlCategory, category6);
    qx::dump(category6);

6.其他

/*==================================对象与json字符串互相转换=================================*/
#ifndef _QX_NO_JSON
    //将对象转换成 json 字符串
    QString jsonFull = qx::serialization::json::to_string(blogTemp, 1);
    //参数三：过滤条件，只查找条件中的字段
    QString jsonFiltered = qx::serialization::json::to_string(blogTemp, 1,
                                                              "filter: { blog_text } | author_id {name, birthday}"
                                                              "| ListComment {comment_text} -> blog_id -> *");
    qDebug("full : \n%s", qPrintable(jsonFull));
    qDebug("filter : \n%s", qPrintable(jsonFiltered));

    BlogPtr blogFull;
    blogFull.reset(new Blog());
    BlogPtr blogFilter;
    blogFilter.reset(new Blog());

    //将 json 字符串转换成对象
    qx::serialization::json::from_string(blogFull, jsonFull, 1);
    qx::serialization::json::from_string(blogFilter, jsonFull, 1, "filter: { blog_text } | author_id {name, birthday}"
                                                                "| ListComment {comment_text} -> blog_id -> *");
#endif

/*==================================循环保存，我的理解是把所有相关联的数据都保存到数据库中=================================*/
    /*==================================看不出来有啥用=================================*/
    daoError = qx::dao::save_with_relation_recursive(blogTemp);
    qAssert(!daoError.isValid());
    daoError = qx::dao::save_with_relation_recursive(blogTemp, qx::dao::save_mode::e_update_only);
    qAssert(!daoError.isValid());

    /*==================================通过类名+方法名，调用 ‘age()’ 反射=================================*/
    qx_bool localInvoke = qx::QxClassX::invoke("Author", "age", author1);
    qAssert(localInvoke);

/*==================================显示所有注册到 QxOrm 上下文中的类=================================*/
    qx::QxClassX::dumpAllClasses();

详细代码，参见 https://github.com/pc-nju/QxOrmDemo