/**************************************************************************** ** ** https://www.qxorm.com/ ** Copyright (C) 2013 XDL Team (ic-east.com) ** ** This file is part of the QxOrm library ** ** This software is provided 'as-is', without any express or implied ** warranty. In no event will the authors be held liable for any ** damages arising from the use of this software ** ** Commercial Usage ** Licensees holding valid commercial QxOrm licenses may use this file in ** accordance with the commercial license agreement provided with the ** Software or, alternatively, in accordance with the terms contained in ** a written agreement between you and XDL Team ** ** GNU General Public License Usage ** Alternatively, this file may be used under the terms of the GNU ** General Public License version 3.0 as published by the Free Software ** Foundation and appearing in the file 'license.gpl3.txt' included in the ** packaging of this file. Please review the following information to ** ensure the GNU General Public License version 3.0 requirements will be ** met : http://www.gnu.org/copyleft/gpl.html ** ** If you are unsure which license is appropriate for your use, or ** if you have questions regarding the use of this file, please contact : ** ic-east.com ** ****************************************************************************/ #ifndef _QX_SQL_QUERY_H_ #define _QX_SQL_QUERY_H_ #ifdef _MSC_VER #pragma once #endif /*! * \file QxSqlQuery.h * \author XDL Team * \ingroup QxDao * \brief Define a user SQL query added to default SQL query builded by QxOrm library, and used by qx::dao::xxx functions to filter elements fetched from database */ #ifdef Q_COMPILER_INITIALIZER_LISTS #include #endif // Q_COMPILER_INITIALIZER_LISTS #ifdef _QX_ENABLE_BOOST_SERIALIZATION #include #include #include #endif // _QX_ENABLE_BOOST_SERIALIZATION #include #ifndef _QX_NO_JSON #include #include #include #include #endif // _QX_NO_JSON #include #include #include #include #include #include #include namespace qx { class QxSqlQuery; } // namespace qx #ifdef _QX_ENABLE_BOOST_SERIALIZATION namespace boost { namespace serialization { template inline void qx_save(Archive &ar, const qx::QxSqlQuery &t, const unsigned int file_version); template inline void qx_load(Archive &ar, qx::QxSqlQuery &t, const unsigned int file_version); } // namespace serialization } // namespace boost #endif // _QX_ENABLE_BOOST_SERIALIZATION QX_DLL_EXPORT QDataStream &operator<<(QDataStream &stream, const qx::QxSqlQuery &t) QX_USED; QX_DLL_EXPORT QDataStream &operator>>(QDataStream &stream, qx::QxSqlQuery &t) QX_USED; #ifndef _QX_NO_JSON namespace qx { namespace cvt { namespace detail { template <> struct QxConvert_ToJson; template <> struct QxConvert_FromJson; QX_DLL_EXPORT QJsonValue QxConvert_ToJson_Helper(const qx::QxSqlQuery &t, const QString &format) QX_USED; QX_DLL_EXPORT qx_bool QxConvert_FromJson_Helper(const QJsonValue &j, qx::QxSqlQuery &t, const QString &format) QX_USED; } // namespace detail } // namespace cvt } // namespace qx #endif // _QX_NO_JSON namespace qx { /*! * \ingroup QxDao * \brief qx::QxSqlQuery : define a user SQL query added to default SQL query builded by QxOrm library, and used by qx::dao::xxx functions to filter elements fetched from database * * The class qx::QxSqlQuery (or its typedef qx_query) is used to communicate with database (to filter, to sort, etc.) in two different ways : * - writing manually SQL query ; * - using C++ methods with a syntax similar to SQL (same concept than the great library SubSonic for .Net). * * With the first method (writing manually SQL query), you can use some optimizations specific for each database. * The second method (using C++ code to build SQL query) binds automatically SQL parameters without using qx::QxSqlQuery::bind() function. * * Here is an example with qx::QxSqlQuery class writing manually a SQL query : * \code // Build a SQL query to fetch only 'author' of type 'female' qx::QxSqlQuery query("WHERE author.sex = :sex"); query.bind(":sex", author::female); QList list_of_female; QSqlError daoError = qx::dao::fetch_by_query(query, list_of_female); // Here we can work with the collection provided by database for (long l = 0; l < list_of_female.count(); l++) { ; } * \endcode * * QxOrm library provides 3 styles to write SQL parameters. * This style can be modified for a project using the following method qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle() : * - ph_style_2_point_name : "WHERE author.sex = :sex" (default style) ; * - ph_style_at_name : "WHERE author.sex = @sex" ; * - ph_style_question_mark : "WHERE author.sex = ?". * * Here is the same example using C++ code of the class qx::QxSqlQuery (or its typedef qx_query) to build query automatically : * \code // Build a SQL query to fetch only 'author' of type 'female' qx_query query; query.where("author.sex").isEqualTo(author::female); QList list_of_female; QSqlError daoError = qx::dao::fetch_by_query(query, list_of_female); // Here we can work with the collection provided by database for (long l = 0; l < list_of_female.count(); l++) { ; } * \endcode * * With C++ methods of qx::QxSqlQuery class, you don't have to bind any SQL parameter, and the syntax is similar to real SQL. * All SQL parameters will be provided to database automatically with the following style : qx::QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle(). * * Here is an example with many methods of qx::QxSqlQuery class (or its typedef qx_query) : * \code qx_query query; query.where("sex").isEqualTo(author::female) .and_("age").isGreaterThan(38) .or_("last_name").isNotEqualTo("Dupont") .or_("first_name").like("Alfred") .and_OpenParenthesis("id").isLessThanOrEqualTo(999) .and_("birth_date").isBetween(date1, date2) .closeParenthesis() .or_("id").in(50, 999, 11, 23, 78945) .and_("is_deleted").isNotNull() .orderAsc("last_name", "first_name", "sex") .limit(50, 150); * \endcode * * This code will produce following SQL for MySQL, PostgreSQL and SQLite databases (for Oracle and SQLServer, there is a specific process for limit() method) : * \code WHERE sex = :sex_1_0 AND age > :age_3_0 OR last_name <> :last_name_5_0 OR first_name LIKE :first_name_7_0 AND ( id <= :id_10_0 AND birth_date BETWEEN :birth_date_12_0_1 AND :birth_date_12_0_2 ) OR id IN (:id_15_0_0, :id_15_0_1, :id_15_0_2, :id_15_0_3, :id_15_0_4) AND is_deleted IS NOT NULL ORDER BY last_name ASC, first_name ASC, sex ASC LIMIT :limit_rows_count_19_0 OFFSET :offset_start_row_19_0 * \endcode * * Here is the list of all functions available to use qx::QxSqlQuery class (or its typedef qx_query) : * \code // with functions into namespace qx::dao qx::dao::count() qx::dao::fetch_by_query() qx::dao::update_by_query() qx::dao::delete_by_query() qx::dao::destroy_by_query() qx::dao::fetch_by_query_with_relation() qx::dao::fetch_by_query_with_all_relation() qx::dao::update_by_query_with_relation() qx::dao::update_by_query_with_all_relation() qx::dao::update_optimized_by_query() // with qx::QxSession class qx::QxSession::count() qx::QxSession::fetchByQuery() qx::QxSession::update() qx::QxSession::deleteByQuery() qx::QxSession::destroyByQuery() // with qx::QxRepository class qx::QxRepository::count() qx::QxRepository::fetchByQuery() qx::QxRepository::update() qx::QxRepository::deleteByQuery() qx::QxRepository::destroyByQuery() * \endcode * * Note : those functions have 2 other optionals parameters : * - const QStringList & columns : to indicate columns to fetch (by default, all columns are fetched) ; * - const QStringList & relation : to indicate relations to fetch (one-to-one, one-to-many, many-to-one and many-to-many defined into void qx::register_class() mapping function by class), by default there is no relation fetched. * * Other note : it's possible to call a stored procedure using qx::QxSqlQuery class, for example : * \code qx_query query("CALL MyStoredProc(:param1, :param2)"); query.bind(":param1", "myValue1"); query.bind(":param2", 5024, QSql::InOut); QSqlError daoError = qx::dao::call_query(query); QVariant vNewValue = query.boundValue(":param2"); query.dumpSqlResult(); * \endcode * * If the stored procedure returns a resultset, you can iterate over each rows and fields using the following methods (after calling qx::dao::call_query() function) : * - long qx::QxSqlQuery::getSqlResultRowCount() const; * - long qx::QxSqlQuery::getSqlResultColumnCount() const; * - QVariant qx::QxSqlQuery::getSqlResultAt(long row, long column) const; * - QVariant qx::QxSqlQuery::getSqlResultAt(long row, const QString & column) const; * - QVector qx::QxSqlQuery::getSqlResultAllColumns() const; * - void qx::QxSqlQuery::dumpSqlResult(); * * Other note : to add your own SQL query methods (for example, some databases provide non-standard specifics SQL functions) : * - create a derived class based on qx::QxSqlQuery class ; * - if your C++ compiler supports covariant return type, add the macro QX_SQL_QUERY_DERIVED_IMPL_COVARIANT_RETURN_TYPE_HPP(myClass) in the definition of your class (myClass.h) ; * - if your C++ compiler supports covariant return type, add the macro QX_SQL_QUERY_DERIVED_IMPL_COVARIANT_RETURN_TYPE_CPP(myClass) in the implementation of your class (myClass.cpp) ; * - add all SQL specifics functions in your derived class. */ class QX_DLL_EXPORT QxSqlQuery { #ifdef _QX_ENABLE_BOOST_SERIALIZATION template friend inline void boost::serialization::qx_save(Archive &ar, const qx::QxSqlQuery &t, const unsigned int file_version); template friend inline void boost::serialization::qx_load(Archive &ar, qx::QxSqlQuery &t, const unsigned int file_version); #endif // _QX_ENABLE_BOOST_SERIALIZATION friend QX_DLL_EXPORT QDataStream & ::operator<<(QDataStream &stream, const qx::QxSqlQuery &t); friend QX_DLL_EXPORT QDataStream & ::operator>>(QDataStream &stream, qx::QxSqlQuery &t); #ifndef _QX_NO_JSON friend struct qx::cvt::detail::QxConvert_ToJson; friend struct qx::cvt::detail::QxConvert_FromJson; friend QX_DLL_EXPORT QJsonValue qx::cvt::detail::QxConvert_ToJson_Helper(const qx::QxSqlQuery &t, const QString &format); friend QX_DLL_EXPORT qx_bool qx::cvt::detail::QxConvert_FromJson_Helper(const QJsonValue &j, qx::QxSqlQuery &t, const QString &format); #endif // _QX_NO_JSON public: typedef std::function type_fct_on_before_sql_prepare; protected: struct QxSqlResult { QHash positionByKey; QHash positionByKeyUpper; QVector> values; }; typedef std::tuple type_bind_value; QStringList m_sQuery; //!< Query SQL with place-holder QxCollection m_lstValue; //!< Bind value in this array qx::dao::detail::IxSqlElement_ptr m_pSqlElementTemp; //!< Temporary SQL element QList m_lstSqlElement; //!< List of all SQL elements to build SQL query int m_iSqlElementIndex; //!< Current index of SQL element int m_iParenthesisCount; //!< Current parenthesis count bool m_bDistinct; //!< Replace SELECT by SELECT DISTINCT in SQL query std::shared_ptr m_pSqlResult; //!< All results returning by SQL query or stored procedure (after calling qx::dao::call_query function) QVariant m_vResponse; //!< Can be used to store some responses (from MongoDB database for example in JSON format) QString m_sType; //!< Query type (for example : 'aggregate' or 'cursor' for MongoDB database) QHash> m_lstJoinQueryUser; //!< List of SQL queries defined by user to add inside relationships joins (LEFT OUTER JOIN, INNER JOIN), for example : INNER JOIN my_table2 m2 ON (m1.id = m2.parent_id AND (XXX)) QList> m_lstJoinQueryToResolve; //!< List of SQL queries to resolve (in the right order) to add inside relationships joins (LEFT OUTER JOIN, INNER JOIN), for example : INNER JOIN my_table2 m2 ON (m1.id = m2.parent_id AND (XXX)) type_fct_on_before_sql_prepare m_fctOnBeforeSqlPrepare; //!< Custom callback function to modify SQL query before preparing in database public: QxSqlQuery(); QxSqlQuery(const char *query, const QVariantList &values = QVariantList()); QxSqlQuery(const QString &query, const QVariantList &values = QVariantList()); QxSqlQuery(const QStringList &query); QxSqlQuery(const QString &type, const QString &query); QxSqlQuery(const QString &type, const QStringList &query); virtual ~QxSqlQuery(); #ifndef _QX_NO_JSON #ifdef Q_COMPILER_INITIALIZER_LISTS QxSqlQuery(std::initializer_list> json); QxSqlQuery(std::initializer_list> json, std::initializer_list> opts); QxSqlQuery(const QString &type, std::initializer_list> json); QxSqlQuery(const QString &type, std::initializer_list> json, std::initializer_list> opts); #endif // Q_COMPILER_INITIALIZER_LISTS #endif // _QX_NO_JSON QString query(); QString queryAt(int idx) const; void queryAt(int idx, const QString &query); QVariant response() const; QString type() const; bool isEmpty() const; bool isDistinct() const; void clear(); void resolve(QSqlQuery &query, qx::QxCollection *pLstExecBatch = NULL) const; void resolveOutput(QSqlQuery &query, bool bFetchSqlResult); void postProcess(QString &sql) const; void setResponse(const QVariant &v); void setType(const QString &s); QString getJoinQuery(const QString &relationKey, const QString &relationAlias); QString getJoinQueryHash(); QxSqlQuery &query(const QString &sQuery); QxSqlQuery &bind(const QVariant &vValue, QSql::ParamType paramType = QSql::In); QxSqlQuery &bind(const QString &sKey, const QVariant &vValue, QSql::ParamType paramType = QSql::In); QVariant boundValue(const QString &sKey) const; QVariant boundValue(int iPosition) const; long getSqlResultRowCount() const; long getSqlResultColumnCount() const; QVariant getSqlResultAt(long row, long column) const; QVariant getSqlResultAt(long row, const QString &column, bool caseSensitive = false) const; QVector getSqlResultAt(long row) const; QVector getSqlResultAllColumns() const; void dumpSqlResult(); static void dumpBoundValues(const QSqlQuery &query); QxSqlQuery &setFctOnBeforeSqlPrepare(type_fct_on_before_sql_prepare fct); void onBeforeSqlPrepare(QString &sql); private: void verifyQuery() const QX_USED; void fetchSqlResult(QSqlQuery &query); public: /* -- All methods to build SQL query using C++ syntax -- */ virtual QxSqlQuery &distinct(); virtual QxSqlQuery &where(const QString &column); virtual QxSqlQuery &where_OpenParenthesis(const QString &column); virtual QxSqlQuery &and_(const QString &column); virtual QxSqlQuery &and_OpenParenthesis(const QString &column); virtual QxSqlQuery &or_(const QString &column); virtual QxSqlQuery &or_OpenParenthesis(const QString &column); virtual QxSqlQuery &openParenthesis(); virtual QxSqlQuery &closeParenthesis(); virtual QxSqlQuery &orderAsc(const QStringList &columns); virtual QxSqlQuery &orderAsc(const QString &col1); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); virtual QxSqlQuery &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); virtual QxSqlQuery &orderDesc(const QStringList &columns); virtual QxSqlQuery &orderDesc(const QString &col1); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); virtual QxSqlQuery &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); virtual QxSqlQuery &groupBy(const QStringList &columns); virtual QxSqlQuery &groupBy(const QString &col1); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); virtual QxSqlQuery &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); virtual QxSqlQuery &limit(int rowsCount, int startRow = 0, bool withTies = false); virtual QxSqlQuery &like(const QString &val); virtual QxSqlQuery ¬Like(const QString &val); virtual QxSqlQuery &startsWith(const QString &val); virtual QxSqlQuery &endsWith(const QString &val); virtual QxSqlQuery &containsString(const QString &val); virtual QxSqlQuery &isEqualTo(const QVariant &val); virtual QxSqlQuery &isNotEqualTo(const QVariant &val); virtual QxSqlQuery &isGreaterThan(const QVariant &val); virtual QxSqlQuery &isGreaterThanOrEqualTo(const QVariant &val); virtual QxSqlQuery &isLessThan(const QVariant &val); virtual QxSqlQuery &isLessThanOrEqualTo(const QVariant &val); virtual QxSqlQuery &customOperator(const QString &sCustomOperator, const QVariant &val); virtual QxSqlQuery &in(const QVariantList &values); virtual QxSqlQuery &in(const QVariant &val1); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8); virtual QxSqlQuery &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9); virtual QxSqlQuery ¬In(const QVariantList &values); virtual QxSqlQuery ¬In(const QVariant &val1); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8); virtual QxSqlQuery ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9); virtual QxSqlQuery &in_Select(const QxSqlQuery &query); virtual QxSqlQuery ¬In_Select(const QxSqlQuery &query); virtual QxSqlQuery &isEqualTo_Select(const QxSqlQuery &query); virtual QxSqlQuery &isNotEqualTo_Select(const QxSqlQuery &query); virtual QxSqlQuery &isNull(); virtual QxSqlQuery &isNotNull(); virtual QxSqlQuery &isBetween(const QVariant &val1, const QVariant &val2); virtual QxSqlQuery &isNotBetween(const QVariant &val1, const QVariant &val2); virtual QxSqlQuery &freeText(const QString &text, const QVariantList &values = QVariantList()); virtual QxSqlQuery &addJoinQuery(const QString &relationKeyOrAlias, const QxSqlQuery &joinQuery); private: QxSqlQuery &addSqlExpression(const QString &column, qx::dao::detail::QxSqlExpression::type type); QxSqlQuery &addSqlCompare(const QVariant &val, qx::dao::detail::QxSqlCompare::type type, const QString &sCustomOperator = QString()); QxSqlQuery &addSqlSort(const QStringList &columns, qx::dao::detail::QxSqlSort::type type); QxSqlQuery &addSqlIn(const QVariantList &values, qx::dao::detail::QxSqlIn::type type); QxSqlQuery &addSqlIsNull(qx::dao::detail::QxSqlIsNull::type type); QxSqlQuery &addSqlIsBetween(const QVariant &val1, const QVariant &val2, qx::dao::detail::QxSqlIsBetween::type type); QxSqlQuery &addFreeText(const QString &text, const QVariantList &values); QxSqlQuery &addEmbedQuery(const QxSqlQuery &query, qx::dao::detail::QxSqlEmbedQuery::type type, bool requirePreviousElement); }; } // namespace qx typedef qx::QxSqlQuery qx_query; namespace qx { namespace dao { /*! * \ingroup QxDao * \brief qx::dao::call_query function can be used to call a custom SQL query or a stored procedure * * To get an output value parameter (must be pass as QSql::Out or QSql::InOut) returned by a stored procedure, just call the following method : QVariant qx::QxSqlQuery::boundValue(const QString & sKey) const;.
* To iterate over all resultset, just use the following methods : * - long qx::QxSqlQuery::getSqlResultRowCount() const; * - long qx::QxSqlQuery::getSqlResultColumnCount() const; * - QVariant qx::QxSqlQuery::getSqlResultAt(long row, long column) const; * - QVariant qx::QxSqlQuery::getSqlResultAt(long row, const QString & column) const; * - QVector qx::QxSqlQuery::getSqlResultAllColumns() const; * - void qx::QxSqlQuery::dumpSqlResult(); * * Here is an example of code using qx::dao::call_query function : * \code qx_query query("CALL MyStoredProc(:param1, :param2)"); query.bind(":param1", "myValue1"); query.bind(":param2", 5024, QSql::InOut); QSqlError daoError = qx::dao::call_query(query); QVariant vNewValue = query.boundValue(":param2"); query.dumpSqlResult(); * \endcode */ QX_DLL_EXPORT QSqlError call_query(qx::QxSqlQuery &query, QSqlDatabase *pDatabase = NULL); /*! * \ingroup QxDao * \brief qx::dao::call_query_without_prepare function can be used to call a custom SQL query or a stored procedure : same as qx::dao::call_query() function without calling prepare() QSqlQuery class method (can be useful to execute some specific SQL queries) */ QX_DLL_EXPORT QSqlError call_query_without_prepare(qx::QxSqlQuery &query, QSqlDatabase *pDatabase = NULL); namespace helper { QX_DLL_EXPORT QSqlError call_query_helper(qx::QxSqlQuery &query, QSqlDatabase *pDatabase, bool bPrepare); } // namespace helper } // namespace dao } // namespace qx QX_REGISTER_CLASS_NAME(qx_query) QX_CLASS_VERSION(qx::QxSqlQuery, 0) #ifdef _QX_ENABLE_BOOST_SERIALIZATION QX_SERIALIZE_FAST_COMPIL_SAVE_LOAD_HPP(QX_DLL_EXPORT, qx::QxSqlQuery) #endif // _QX_ENABLE_BOOST_SERIALIZATION #define QX_SQL_QUERY_DERIVED_IMPL_COVARIANT_RETURN_TYPE_HPP(className) \ public: \ virtual className &distinct(); \ \ virtual className &where(const QString &column); \ virtual className &where_OpenParenthesis(const QString &column); \ virtual className &and_(const QString &column); \ virtual className &and_OpenParenthesis(const QString &column); \ virtual className &or_(const QString &column); \ virtual className &or_OpenParenthesis(const QString &column); \ \ virtual className &openParenthesis(); \ virtual className &closeParenthesis(); \ \ virtual className &orderAsc(const QStringList &columns); \ virtual className &orderAsc(const QString &col1); \ virtual className &orderAsc(const QString &col1, const QString &col2); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); \ virtual className &orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); \ \ virtual className &orderDesc(const QStringList &columns); \ virtual className &orderDesc(const QString &col1); \ virtual className &orderDesc(const QString &col1, const QString &col2); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); \ virtual className &orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); \ \ virtual className &groupBy(const QStringList &columns); \ virtual className &groupBy(const QString &col1); \ virtual className &groupBy(const QString &col1, const QString &col2); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8); \ virtual className &groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9); \ \ virtual className &limit(int rowsCount, int startRow = 0, bool withTies = false); \ \ virtual className &like(const QString &val); \ virtual className ¬Like(const QString &val); \ virtual className &startsWith(const QString &val); \ virtual className &endsWith(const QString &val); \ virtual className &containsString(const QString &val); \ \ virtual className &isEqualTo(const QVariant &val); \ virtual className &isNotEqualTo(const QVariant &val); \ virtual className &isGreaterThan(const QVariant &val); \ virtual className &isGreaterThanOrEqualTo(const QVariant &val); \ virtual className &isLessThan(const QVariant &val); \ virtual className &isLessThanOrEqualTo(const QVariant &val); \ virtual className &customOperator(const QString &sCustomOperator, const QVariant &val); \ \ virtual className &in(const QVariantList &values); \ virtual className &in(const QVariant &val1); \ virtual className &in(const QVariant &val1, const QVariant &val2); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8); \ virtual className &in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9); \ \ virtual className ¬In(const QVariantList &values); \ virtual className ¬In(const QVariant &val1); \ virtual className ¬In(const QVariant &val1, const QVariant &val2); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8); \ virtual className ¬In(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9); \ \ virtual className &in_Select(const QxSqlQuery &query); \ virtual className ¬In_Select(const QxSqlQuery &query); \ virtual className &isEqualTo_Select(const QxSqlQuery &query); \ virtual className &isNotEqualTo_Select(const QxSqlQuery &query); \ \ virtual className &isNull(); \ virtual className &isNotNull(); \ \ virtual className &isBetween(const QVariant &val1, const QVariant &val2); \ virtual className &isNotBetween(const QVariant &val1, const QVariant &val2); \ \ virtual className &freeText(const QString &text, const QVariantList &values = QVariantList()); \ \ virtual className &addJoinQuery(const QString &relationKeyOrAlias, const QxSqlQuery &joinQuery); #define QX_SQL_QUERY_DERIVED_IMPL_COVARIANT_RETURN_TYPE_CPP(className) \ \ className &className::distinct() { return static_cast(qx::QxSqlQuery::distinct()); } \ \ className &className::where(const QString &column) { return static_cast(qx::QxSqlQuery::where(column)); } \ className &className::where_OpenParenthesis(const QString &column) { return static_cast(qx::QxSqlQuery::where_OpenParenthesis(column)); } \ className &className::and_(const QString &column) { return static_cast(qx::QxSqlQuery::and_(column)); } \ className &className::and_OpenParenthesis(const QString &column) { return static_cast(qx::QxSqlQuery::and_OpenParenthesis(column)); } \ className &className::or_(const QString &column) { return static_cast(qx::QxSqlQuery::or_(column)); } \ className &className::or_OpenParenthesis(const QString &column) { return static_cast(qx::QxSqlQuery::or_OpenParenthesis(column)); } \ \ className &className::openParenthesis() { return static_cast(qx::QxSqlQuery::openParenthesis()); } \ className &className::closeParenthesis() { return static_cast(qx::QxSqlQuery::closeParenthesis()); } \ \ className &className::orderAsc(const QStringList &columns) { return static_cast(qx::QxSqlQuery::orderAsc(columns)); } \ className &className::orderAsc(const QString &col1) { return static_cast(qx::QxSqlQuery::orderAsc(col1)); } \ className &className::orderAsc(const QString &col1, const QString &col2) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4, col5)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4, col5, col6)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4, col5, col6, col7)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4, col5, col6, col7, col8)); } \ className &className::orderAsc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9) { return static_cast(qx::QxSqlQuery::orderAsc(col1, col2, col3, col4, col5, col6, col7, col8, col9)); } \ \ className &className::orderDesc(const QStringList &columns) { return static_cast(qx::QxSqlQuery::orderDesc(columns)); } \ className &className::orderDesc(const QString &col1) { return static_cast(qx::QxSqlQuery::orderDesc(col1)); } \ className &className::orderDesc(const QString &col1, const QString &col2) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4, col5)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4, col5, col6)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4, col5, col6, col7)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4, col5, col6, col7, col8)); } \ className &className::orderDesc(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9) { return static_cast(qx::QxSqlQuery::orderDesc(col1, col2, col3, col4, col5, col6, col7, col8, col9)); } \ \ className &className::groupBy(const QStringList &columns) { return static_cast(qx::QxSqlQuery::groupBy(columns)); } \ className &className::groupBy(const QString &col1) { return static_cast(qx::QxSqlQuery::groupBy(col1)); } \ className &className::groupBy(const QString &col1, const QString &col2) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4, col5)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4, col5, col6)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4, col5, col6, col7)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4, col5, col6, col7, col8)); } \ className &className::groupBy(const QString &col1, const QString &col2, const QString &col3, const QString &col4, const QString &col5, const QString &col6, const QString &col7, const QString &col8, const QString &col9) { return static_cast(qx::QxSqlQuery::groupBy(col1, col2, col3, col4, col5, col6, col7, col8, col9)); } \ \ className &className::limit(int rowsCount, int startRow, bool withTies) { return static_cast(qx::QxSqlQuery::limit(rowsCount, startRow, withTies)); } \ \ className &className::like(const QString &val) { return static_cast(qx::QxSqlQuery::like(val)); } \ className &className::notLike(const QString &val) { return static_cast(qx::QxSqlQuery::notLike(val)); } \ className &className::startsWith(const QString &val) { return static_cast(qx::QxSqlQuery::startsWith(val)); } \ className &className::endsWith(const QString &val) { return static_cast(qx::QxSqlQuery::endsWith(val)); } \ className &className::containsString(const QString &val) { return static_cast(qx::QxSqlQuery::containsString(val)); } \ \ className &className::isEqualTo(const QVariant &val) { return static_cast(qx::QxSqlQuery::isEqualTo(val)); } \ className &className::isNotEqualTo(const QVariant &val) { return static_cast(qx::QxSqlQuery::isNotEqualTo(val)); } \ className &className::isGreaterThan(const QVariant &val) { return static_cast(qx::QxSqlQuery::isGreaterThan(val)); } \ className &className::isGreaterThanOrEqualTo(const QVariant &val) { return static_cast(qx::QxSqlQuery::isGreaterThanOrEqualTo(val)); } \ className &className::isLessThan(const QVariant &val) { return static_cast(qx::QxSqlQuery::isLessThan(val)); } \ className &className::isLessThanOrEqualTo(const QVariant &val) { return static_cast(qx::QxSqlQuery::isLessThanOrEqualTo(val)); } \ className &className::customOperator(const QString &sCustomOperator, const QVariant &val) { return static_cast(qx::QxSqlQuery::customOperator(sCustomOperator, val)); } \ \ className &className::in(const QVariantList &values) { return static_cast(qx::QxSqlQuery::in(values)); } \ className &className::in(const QVariant &val1) { return static_cast(qx::QxSqlQuery::in(val1)); } \ className &className::in(const QVariant &val1, const QVariant &val2) { return static_cast(qx::QxSqlQuery::in(val1, val2)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4, val5)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4, val5, val6)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4, val5, val6, val7)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4, val5, val6, val7, val8)); } \ className &className::in(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9) { return static_cast(qx::QxSqlQuery::in(val1, val2, val3, val4, val5, val6, val7, val8, val9)); } \ \ className &className::notIn(const QVariantList &values) { return static_cast(qx::QxSqlQuery::notIn(values)); } \ className &className::notIn(const QVariant &val1) { return static_cast(qx::QxSqlQuery::notIn(val1)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2) { return static_cast(qx::QxSqlQuery::notIn(val1, val2)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4, val5)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4, val5, val6)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4, val5, val6, val7)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4, val5, val6, val7, val8)); } \ className &className::notIn(const QVariant &val1, const QVariant &val2, const QVariant &val3, const QVariant &val4, const QVariant &val5, const QVariant &val6, const QVariant &val7, const QVariant &val8, const QVariant &val9) { return static_cast(qx::QxSqlQuery::notIn(val1, val2, val3, val4, val5, val6, val7, val8, val9)); } \ \ className &className::in_Select(const QxSqlQuery &query) { return static_cast(qx::QxSqlQuery::in_Select(query)); } \ className &className::notIn_Select(const QxSqlQuery &query) { return static_cast(qx::QxSqlQuery::notIn_Select(query)); } \ className &className::isEqualTo_Select(const QxSqlQuery &query) { return static_cast(qx::QxSqlQuery::isEqualTo_Select(query)); } \ className &className::isNotEqualTo_Select(const QxSqlQuery &query) { return static_cast(qx::QxSqlQuery::isNotEqualTo_Select(query)); } \ \ className &className::isNull() { return static_cast(qx::QxSqlQuery::isNull()); } \ className &className::isNotNull() { return static_cast(qx::QxSqlQuery::isNotNull()); } \ \ className &className::isBetween(const QVariant &val1, const QVariant &val2) { return static_cast(qx::QxSqlQuery::isBetween(val1, val2)); } \ className &className::isNotBetween(const QVariant &val1, const QVariant &val2) { return static_cast(qx::QxSqlQuery::isNotBetween(val1, val2)); } \ \ className &className::freeText(const QString &text, const QVariantList &values) { return static_cast(qx::QxSqlQuery::freeText(text, values)); } \ \ className &className::addJoinQuery(const QString &relationKeyOrAlias, const QxSqlQuery &joinQuery) { return static_cast(qx::QxSqlQuery::addJoinQuery(relationKeyOrAlias, joinQuery)); } #endif // _QX_SQL_QUERY_H_