#include "ORMLite.h" using namespace BOT_ORM; using namespace BOT_ORM::Expression; struct UserModel { int user_id; std::string user_name; double credit_count; Nullable<int> age; Nullable<double> salary; Nullable<std::string> title; // Inject ORM-Lite into this Class :-) ORMAP ("UserModel", user_id, user_name, credit_count, age, salary, title); };

// Open a Connection with *test.db* ORMapper mapper ("test.db"); // Create a table for "UserModel" mapper.CreateTbl (UserModel {}); // Drop the table "UserModel" mapper.DropTbl (UserModel {});

std::vector<UserModel> initObjs = { { 0, "John", 0.2, 21, nullptr, nullptr }, { 1, "Jack", 0.4, nullptr, 3.14, nullptr }, { 2, "Jess", 0.6, nullptr, nullptr, std::string ("Dr.") } }; // Insert Values into the table for (const auto &obj : initObjs) mapper.Insert (obj); initObjs[1].salary = nullptr; initObjs[1].title = "St."; // Update Entity by Primary Key (WHERE UserModel.id = 1) mapper.Update (initObjs[1]); // Delete Entity by Primary Key (WHERE UserModel.id = 2) mapper.Delete (initObjs[2]); // Transactional Statements try { mapper.Transaction ([&] () { mapper.Delete (initObjs[0]); // OK mapper.Insert (UserModel { 1, "Joke", 0 }); // Failed }); } catch (const std::exception &ex) { // If any statement Failed, throw an exception // "SQL error: UNIQUE constraint failed: UserModel.id" // Remarks: // mapper.Delete (initObjs[0]); will not applied :-) } // Select All to List auto result1 = mapper.Query (UserModel {}).ToList (); // result1 = [{ 0, 0.2, "John", 21, null, null }, // { 1, 0.4, "Jack", null, null, "St." }]

std::vector<UserModel> dataToSeed; for (int i = 50; i < 100; i++) dataToSeed.emplace_back ( UserModel { i, "July_" + std::to_string (i), i * 0.2 }); // Insert by Batch Insert mapper.Transaction ([&] () { mapper.InsertRange (dataToSeed); }); for (size_t i = 0; i < 20; i++) { dataToSeed[i + 30].age = 30 + (int) i / 2; dataToSeed[i + 20].title = "Mr. " + std::to_string (i); } // Update by Batch Update mapper.Transaction ([&] () { mapper.UpdateRange (dataToSeed); });

// Define a Query Helper Object and its Field Extractor UserModel helper; FieldExtractor field { helper }; // Select by Query auto result2 = mapper.Query (UserModel {}) .Where ( field (helper.user_name) & std::string ("July%") && (field (helper.age) >= 32 && field (helper.title) != nullptr) ) .OrderByDescending (field (helper.age)) .OrderBy (field (helper.user_id)) .Take (3) .Skip (1) .ToVector (); // Remarks: // sql = SELECT * FROM UserModel // WHERE (user_name LIKE 'July%' AND // (age>=32 AND title IS NOT NULL)) // ORDER BY age DESC // ORDER BY id // LIMIT 3 OFFSET 1 // result2 = [{ 89, 17.8, "July_89", 34, null, "Mr. 19" }, // { 86, 17.2, "July_86", 33, null, "Mr. 16" }, // { 87, 17.4, "July_87", 33, null, "Mr. 17" }] // Calculate Aggregate Function by Query auto avg = mapper.Query (UserModel {}) .Where (field (helper.user_name) & std::string ("July%")) .Select (Avg (field (helper.credit_count))); // Remarks: // sql = SELECT AVG (credit_count) FROM UserModel // WHERE (user_name LIKE 'July%') // avg = 14.9 auto count = mapper.Query (UserModel {}) .Where (field (helper.user_name) | std::string ("July%")) .Select (Count ()); // Remarks: // sql = SELECT COUNT (*) FROM UserModel // WHERE (user_name NOT LIKE 'July%') // count = 2 // Update by Condition mapper.Update ( UserModel {}, (field (helper.age) = 10) && (field (helper.credit_count) = 1.0), field (helper.user_name) == std::string ("July")); // Remarks: // sql = UPDATE UserModel SET age=10,credit_count=1.0 // WHERE (user_name='July') // Delete by Condition mapper.Delete (UserModel {}, field (helper.user_id) >= 90); // Remarks: // sql = DELETE FROM UserModel WHERE (id>=90)

// Define more Query Helper Objects and their Field Extractor UserModel user; SellerModel seller; OrderModel order; field = FieldExtractor { user, seller, order }; // Insert Values into the table // mapper.Insert (..., false) means Insert without Primary Key for (size_t i = 0; i < 50; i++) mapper.Insert ( OrderModel { 0, (int) i / 2 + 50, (int) i / 4 + 50, "Item " + std::to_string (i), i * 0.5 }, false); // Join Tables for Query auto joinedQuery = mapper.Query (UserModel {}) .Join (OrderModel {}, field (user.user_id) == field (order.user_id)) .LeftJoin (SellerModel {}, field (seller.seller_id) == field (order.seller_id)) .Where (field (user.user_id) >= 65); // Get Result to List // Results are Nullable-Tuples auto result3 = joinedQuery.ToList (); // Remarks: // sql = SELECT * FROM UserModel // JOIN OrderModel // ON UserModel.user_id=OrderModel.user_id // LEFT JOIN SellerModel // ON SellerModel.seller_id=OrderModel.seller_id // WHERE (UserModel.user_id>=65) // result3 = [(65, "July_65", 13, null, null, null, // 31, 65, 57, "Item 30", 15, // null, null, null), // (65, "July_65", 13, null, null, null, // 32, 65, 57, "Item 31", 15.5, // null, null, null), // ... ] // Group & Having ~ // Results are Nullable-Tuples auto result4 = joinedQuery .Select (field (order.user_id), field (user.user_name), Avg (field (order.fee))) .GroupBy (field (user.user_name)) .Having (Sum (field (order.fee)) >= 40.5) .Skip (3) .ToList (); // Remarks: // sql = SELECT OrderModel.user_id, // UserModel.user_name, // AVG (OrderModel.fee) // FROM UserModel // JOIN OrderModel // ON UserModel.user_id=OrderModel.user_id // LEFT JOIN SellerModel // ON SellerModel.seller_id=OrderModel.seller_id // WHERE (UserModel.user_id>=65) // GROUP BY UserModel.user_name // HAVING SUM (OrderModel.fee)>=40.5 // LIMIT ~0 OFFSET 3 // result4 = [(73, "July_73", 23.25), // (74, "July_74", 24.25)] // Compound Select // Results are Nullable-Tuples auto result5 = mapper.Query (OrderModel {}) .Select (field (order.product_name), field (order.user_id)) .Where (field (order.user_id) == 50) .Union ( joinedQuery .Select (field (user.user_name), field (order.order_id)) ) .Take (4) .ToList (); // sql = SELECT OrderModel.product_name, // OrderModel.user_id // FROM OrderModel // WHERE (OrderModel.user_id==50) // UNION // SELECT UserModel.user_name, // OrderModel.order_id // FROM UserModel // JOIN OrderModel // ON UserModel.user_id=OrderModel.user_id // LEFT JOIN SellerModel // ON SellerModel.seller_id=OrderModel.seller_id // WHERE (UserModel.user_id>=65) // LIMIT 4; // result5 = [("Item 0", 50), // ("Item 1", 50), // ("July_65", 31), // ("July_65", 32)]