sqlexecutor.cpp

很好用的网络封装库,不熟悉网络编程的人也可以使用。使用风格良好的标准c++编写。

	Statement stmt = (*_pSession << "SELECT * FROM Strings", into(retData), limit(50));
	assert (retData.size() == 0);
	assert (!stmt.done());

	try { stmt.execute(); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (!stmt.done());
	assert (retData.size() == 50);

	try { stmt.execute(); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (stmt.done());
	assert (retData.size() == 100);

	try { stmt.execute(); }// will restart execution!
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (!stmt.done());
	assert (retData.size() == 150);
	for (int x = 0; x < 150; ++x)
	{
		assert(data[x%100] == retData[x]);
	}
}



void SQLExecutor::prepare()
{
	std::string funct = "prepare()";
	std::vector<int> data;
	for (int x = 0; x < 100; x += 2)
	{
		data.push_back(x);
	}

	{
		Statement stmt((*_pSession << "INSERT INTO Strings VALUES (?)", use(data)));
	}
	// stmt should not have been executed when destroyed
	int count = 100;
	try { *_pSession << "SELECT COUNT(*) FROM Strings", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 0);
}


void SQLExecutor::setSimple()
{
	std::string funct = "setSimple()";
	std::set<std::string> lastNames;
	std::set<std::string> firstNames;
	std::set<std::string> addresses;
	std::set<int> ages;
	std::string tableName("Person");
	lastNames.insert("LN1");
	lastNames.insert("LN2");
	firstNames.insert("FN1");
	firstNames.insert("FN2");
	addresses.insert("ADDR1");
	addresses.insert("ADDR2");
	ages.insert(1);
	ages.insert(2);
	int count = 0;
	std::string result;

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::set<std::string> lastNamesR;
	std::set<std::string> firstNamesR;
	std::set<std::string> addressesR;
	std::set<int> agesR;
	try { *_pSession << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (ages == agesR);
	assert (lastNames == lastNamesR);
	assert (firstNames == firstNamesR);
	assert (addresses == addressesR);
}


void SQLExecutor::setComplex()
{
	std::string funct = "setComplex()";
	std::set<Person> people;
	people.insert(Person("LN1", "FN1", "ADDR1", 1));
	people.insert(Person("LN2", "FN2", "ADDR2", 2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::set<Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result == people);
}


void SQLExecutor::setComplexUnique()
{
	std::string funct = "setComplexUnique()";
	std::vector<Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	people.push_back(p1);
	people.push_back(p1);
	people.push_back(p1);
	people.push_back(p1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.push_back(p2);

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 5);

	std::set<Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result.size() == 2);
	assert (*result.begin() == p1);
	assert (*++result.begin() == p2);
}

void SQLExecutor::multiSetSimple()
{
	std::string funct = "multiSetSimple()";
	std::multiset<std::string> lastNames;
	std::multiset<std::string> firstNames;
	std::multiset<std::string> addresses;
	std::multiset<int> ages;
	std::string tableName("Person");
	lastNames.insert("LN1");
	lastNames.insert("LN2");
	firstNames.insert("FN1");
	firstNames.insert("FN2");
	addresses.insert("ADDR1");
	addresses.insert("ADDR2");
	ages.insert(1);
	ages.insert(2);
	int count = 0;
	std::string result;

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::multiset<std::string> lastNamesR;
	std::multiset<std::string> firstNamesR;
	std::multiset<std::string> addressesR;
	std::multiset<int> agesR;
	try { *_pSession << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (ages.size() == agesR.size());
	assert (lastNames.size() == lastNamesR.size());
	assert (firstNames.size() == firstNamesR.size());
	assert (addresses.size() == addressesR.size());
}


void SQLExecutor::multiSetComplex()
{
	std::string funct = "multiSetComplex()";
	std::multiset<Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	people.insert(p1);
	people.insert(p1);
	people.insert(p1);
	people.insert(p1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(p2);

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 5);

	std::multiset<Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result.size() == people.size());
}


void SQLExecutor::mapComplex()
{
	std::string funct = "mapComplex()";
	std::map<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN2", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);

	std::map<std::string, Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result == people);
}


void SQLExecutor::mapComplexUnique()
{
	std::string funct = "mapComplexUnique()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN2", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 5);

	std::map<std::string, Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result.size() == 2);
}


void SQLExecutor::multiMapComplex()
{
	std::string funct = "multiMapComplex()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN2", p2));
	
	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 5);

	std::multimap<std::string, Person> result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result.size() == people.size());
}


void SQLExecutor::selectIntoSingle()
{
	std::string funct = "selectIntoSingle()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);
	Person result;
	try { *_pSession << "SELECT * FROM PERSON", into(result), limit(1), now; }// will return 1 object into one single result
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (result == p1);
}


void SQLExecutor::selectIntoSingleStep()
{
	std::string funct = "selectIntoSingleStep()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);
	Person result;
	Statement stmt = (*_pSession << "SELECT * FROM PERSON", into(result), limit(1)); 
	stmt.execute();
	assert (result == p1);
	assert (!stmt.done());
	stmt.execute();
	assert (result == p2);
	assert (stmt.done());
}


void SQLExecutor::selectIntoSingleFail()
{
	std::string funct = "selectIntoSingleFail()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), limit(2, true), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);
	Person result;
	try
	{
		*_pSession << "SELECT * FROM PERSON", into(result), limit(1, true), now; // will fail now
		fail("hardLimit is set: must fail");
	}
	catch(Poco::Data::LimitException&)
	{
	}
}


void SQLExecutor::lowerLimitOk()
{
	std::string funct = "lowerLimitOk()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);
	Person p2("LN2", "FN2", "ADDR2", 2);
	people.insert(std::make_pair("LN1", p1));
	people.insert(std::make_pair("LN1", p2));

	try { *_pSession << "INSERT INTO PERSON VALUES (?,?,?,?)", use(people), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }

	int count = 0;
	try { *_pSession << "SELECT COUNT(*) FROM PERSON", into(count), now; }
	catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail (funct); }
	catch(StatementException& se){ std::cout << se.toString() << std::endl; fail (funct); }
	assert (count == 2);
	Person result;
	try
	{
		*_pSession << "SELECT * FROM PERSON", into(result), lowerLimit(2), now; // will return 2 objects into one single result but only room for one!
		fail("Not enough space for results");
	}
	catch(Poco::Exception&)
	{
	}
}


void SQLExecutor::singleSelect()
{
	std::string funct = "singleSelect()";
	std::multimap<std::string, Person> people;
	Person p1("LN1", "FN1", "ADDR1", 1);