chp

99점이 100점이 되기 위해 일을 더하는 개발자

9 minute read

개요


실행 함수

  • execute
    • SELECT면 true, INSERT/UPDATE/DELETE는 false를 반환
  • executeQuery
    • sql::ResultSet을 반환
  • executeUpdate
    • 영향 받은 행 수를 반환


예제

  • 코드 
    • #include "cppconn/driver.h"
#include "cppconn/prepared_statement.h"
#include <mutex>
#include <thread>
       
using namespace std;
       
class MySQL {
	private:
		string hostName;
		string userName;
		string password;
       
		mutex mutexForInfo;
		map<thread::id, shared_ptr<sql::Connection>> connectionInfos;
       
	public:
		MySQL(const string &hostName, const string &userName,
			  const string &password);
		~MySQL();
       
		shared_ptr<sql::Connection> GetConnection();
       
		unique_ptr<sql::Statement> GetStatement();
       
		unique_ptr<sql::PreparedStatement>
		GetPreparedStatement(const string &sql);
       
		void SetHostName(const string &hostName);
		void SetUserName(const string &userName);
		void SetPassword(const string &password);
};
       
MySQL::MySQL(const string &hostName, const string &userName,
			 const string &password)
	: hostName(hostName), userName(userName), password(password),
	  connectionInfos({}) {}
MySQL::~MySQL() { this->connectionInfos.clear(); }
       
shared_ptr<sql::Connection> MySQL::GetConnection() {
	lock_guard<mutex> lock(this->mutexForInfo);
       
	if (this->connectionInfos.find(this_thread::get_id()) ==
		this->connectionInfos.end()) {
		try {
			this->connectionInfos[this_thread::get_id()].reset(
				get_driver_instance()->connect(this->hostName, this->userName,
											   this->password));
		} catch (sql::SQLException &e) {
			throw e;
		}
	}
       
	return this->connectionInfos[this_thread::get_id()];
}
       
unique_ptr<sql::Statement> MySQL::GetStatement() {
	return unique_ptr<sql::Statement>(this->GetConnection()->createStatement());
}
       
unique_ptr<sql::PreparedStatement>
MySQL::GetPreparedStatement(const string &sql) {
	return unique_ptr<sql::PreparedStatement>(
		this->GetConnection()->preparedStatement(sql));
}
       
void MySQL::SetHostName(const string &hostName) {
	lock_guard<mutex> lock(this->mutexForInfo);
       
	if (this->hostName == hostName) {
		return;
	}
       
	this->hostName = hostName;
       
	this->connectionInfos.clear();
}
       
void MySQL::SetUserName(const string &userName) {
	lock_guard<mutex> lock(this->mutexForInfo);
       
	if (this->userName == userName) {
		return;
	}
       
	this->userName = userName;
       
	this->connectionInfos.clear();
}
       
void MySQL::SetPassword(const string &password) {
	lock_guard<mutex> lock(this->mutexForInfo);
       
	if (this->password == password) {
		return;
	}
       
	this->password = password;
       
	this->connectionInfos.clear();
}
       
const auto hostName = string("tcp://127.0.0.1:3306");
const auto userName = string("root");
const auto password = string("root");
const auto databaseName = string("test");
const auto tableName = databaseName + ".temp";
       
MySQL mysql(hostName, userName, password);
       
bool initialize() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	try {
		auto statement = mysql.GetStatement();
       
		statement->execute(string("DROP DATABASE IF EXISTS ") + databaseName +
						   ";");
		statement->execute(string("CREATE DATABASE IF NOT EXISTS ") +
						   databaseName + ";");
		statement->execute(string("CREATE TABLE ") + tableName +
						   "(id INT(10), name VARCHAR(50));");
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool finalize() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	try {
		mysql.GetStatement()->execute(string("DROP DATABASE IF EXISTS ") +
									  databaseName + ";");
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool execute_test() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto statementTest = []() {
		auto statement = mysql.GetStatement();
       
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(1, 'a');");
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(2, 'b');");
		statement->execute(string("DELETE FROM ") + tableName + ";");
	};
       
	auto preparedStatementTest = []() {
		{
			auto preparedStatement =
				mysql.GetPreparedStatement(string("INSERT INTO ") + tableName +
										   "(id, name) VALUES(?, ?);");
       
			preparedStatement->setInt(1, 1);
			preparedStatement->setString(2, "a");
			preparedStatement->execute();
       
			preparedStatement->setInt(1, 2);
			preparedStatement->setString(2, "b");
			preparedStatement->execute();
		}
       
		{
			auto preparedStatement = mysql.GetPreparedStatement(
				string("DELETE FROM ") + tableName + ";");
			preparedStatement->execute();
		}
	};
       
	try {
		statementTest();
		preparedStatementTest();
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool executeQuery_test() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto insert_data = []() {
		auto statement = mysql.GetStatement();
       
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(1, 'a');");
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(2, 'b');");
		cout << "~~~ : (" << statement->getUpdateCount() << ")" << endl;
	};
       
	auto statementTest = []() {
		auto result =
			unique_ptr<sql::ResultSet>(mysql.GetStatement()->executeQuery(
				string("SELECT * FROM ") + tableName + " ORDER BY id ASC;"));
		while (result->next()) {
			cout << result->getInt(1) << ", " << result->getString(2) << endl;
		}
	};
       
	auto preparedStatementTest = []() {
		auto preparedStatement = mysql.GetPreparedStatement(
			string("SELECT * FROM ") + tableName + ";");
       
		auto result =
			unique_ptr<sql::ResultSet>(preparedStatement->executeQuery());
		while (result->next()) {
			cout << result->getInt(1) << ", " << result->getString(2) << endl;
		}
	};
       
	auto delete_data = []() {
		mysql.GetStatement()->execute(string("DELETE FROM ") + tableName + ";");
	};
       
	try {
		insert_data();
		statementTest();
		preparedStatementTest();
		delete_data();
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool executeUpdate_test() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto insert_data = []() {
		auto statement = mysql.GetStatement();
       
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(1, 'a');");
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(2, 'b');");
	};
       
	auto statementTest = []() {
		auto statement = mysql.GetStatement();
       
		cout << statement->executeUpdate(string("UPDATE ") + tableName +
										 " SET name='c' WHERE id=1;")
			 << endl;
       
		cout << statement->executeUpdate(string("UPDATE ") + tableName +
										 " SET name='d';")
			 << endl;
	};
       
	auto preparedStatementTest = []() {
		auto preparedStatement = mysql.GetPreparedStatement(
			string("UPDATE ") + tableName + " SET name=? WHERE id=?;");
       
		preparedStatement->setString(1, "a");
		preparedStatement->setInt(2, 1);
		cout << preparedStatement->executeUpdate() << endl;
	};
       
	auto delete_data = []() {
		cout << mysql.GetStatement()->executeUpdate(string("DELETE FROM ") +
													tableName + ";")
			 << endl;
	};
       
	try {
		insert_data();
		statementTest();
		preparedStatementTest();
		delete_data();
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool transaction_test_1() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto select = [](MySQL &mysql) {
		auto result =
			unique_ptr<sql::ResultSet>(mysql.GetStatement()->executeQuery(
				string("SELECT * FROM ") + tableName + " ORDER BY id ASC;"));
		while (result->next()) {
			cout << "\t" << result->getInt(1) << ", " << result->getString(2)
				 << endl;
		}
	};
       
	try {
		auto connection = mysql.GetConnection();
		auto statement = mysql.GetStatement();
       
		connection->setAutoCommit(false);
       
		{
			statement->execute(string("INSERT INTO ") + tableName +
							   "(id, name) VALUES(1, 'a');");
       
			cout << "commit before start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			MySQL temp(hostName, userName, password);
			select(temp);
			cout << "commit before end" << endl;
       
			connection->commit();
       
			cout << "commit after start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "commit after end" << endl;
		}
       
		{
			statement->execute(string("INSERT INTO ") + tableName +
							   "(id, name) VALUES(2, 'b');");
       
			cout << "rollback before start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			MySQL temp(hostName, userName, password);
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "rollback before end" << endl;
       
			connection->rollback();
       
			cout << "rollback after start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "rollback after end" << endl;
		}
       
		connection->setAutoCommit(true);
       
		statement->execute(string("DELETE FROM ") + tableName + ";");
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool transaction_test_2() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto select = [](MySQL &mysql) {
		auto result =
			unique_ptr<sql::ResultSet>(mysql.GetStatement()->executeQuery(
				string("SELECT * FROM ") + tableName + " ORDER BY id ASC;"));
		while (result->next()) {
			cout << "\t" << result->getInt(1) << ", " << result->getString(2)
				 << endl;
		}
	};
       
	try {
		auto statement = mysql.GetStatement();
       
		{
			statement->execute("START TRANSACTION;");
			statement->execute(string("INSERT INTO ") + tableName +
							   "(id, name) VALUES(1, 'a');");
       
			cout << "commit before start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			MySQL temp(hostName, userName, password);
			select(temp);
			cout << "commit before end" << endl;
       
			statement->execute("COMMIT;");
       
			cout << "commit after start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "commit after end" << endl;
		}
       
		{
			statement->execute("START TRANSACTION;");
			statement->execute(string("INSERT INTO ") + tableName +
							   "(id, name) VALUES(2, 'b');");
       
			cout << "rollback before start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			MySQL temp(hostName, userName, password);
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "rollback before end" << endl;
       
			statement->execute("ROLLBACK;");
       
			cout << "rollback after start" << endl;
			cout << "\t~~~ transaction connection ~~~" << endl;
			select(mysql);
			cout << "\t~~~ other connection ~~~" << endl;
			select(temp);
			cout << "rollback after end" << endl;
		}
       
		statement->execute(string("DELETE FROM ") + tableName + ";");
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
bool savepoint_test() {
	cout << "----- " << __func__ << " start -----" << endl;
       
	auto select = [](MySQL &mysql) {
		auto result =
			unique_ptr<sql::ResultSet>(mysql.GetStatement()->executeQuery(
				string("SELECT * FROM ") + tableName + " ORDER BY id ASC;"));
		while (result->next()) {
			cout << "\t" << result->getInt(1) << ", " << result->getString(2)
				 << endl;
		}
	};
       
	try {
		auto connection = mysql.GetConnection();
		auto statement = mysql.GetStatement();
       
		connection->setAutoCommit(false);
       
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(1, 'a');");
       
		auto savePoint = unique_ptr<sql::Savepoint>(
			connection->setSavepoint("save_point_1"));
       
		statement->execute(string("INSERT INTO ") + tableName +
						   "(id, name) VALUES(2, 'b');");
       
		cout << "rollback before start" << endl;
		select(mysql);
		cout << "rollback before end" << endl;
       
		connection->rollback(savePoint.get());
		connection->releaseSavepoint(savePoint.get());
       
		cout << "rollback after start" << endl;
		select(mysql);
		cout << "rollback after end" << endl;
       
		connection->commit();
       
		connection->setAutoCommit(true);
	} catch (sql::SQLException &e) {
		cout << e.what() << endl;
       
		return false;
	}
       
	cout << "----- " << __func__ << " end -----" << endl << endl;
       
	return true;
}
       
int main() {
	cout << "main start" << endl << endl;
       
	if (initialize() == false) {
		return EXIT_FAILURE;
	}
       
	if (execute_test() == false) {
		return EXIT_FAILURE;
	}
       
	if (executeQuery_test() == false) {
		return EXIT_FAILURE;
	}
       
	if (executeUpdate_test() == false) {
		return EXIT_FAILURE;
	}
       
	if (transaction_test_1() == false) {
		return EXIT_FAILURE;
	}
       
	if (transaction_test_2() == false) {
		return EXIT_FAILURE;
	}
       
	if (savepoint_test() == false) {
		return EXIT_FAILURE;
	}
       
	if (finalize() == false) {
		return EXIT_FAILURE;
	}
       
	cout << "main end" << endl;
       
	return EXIT_SUCCESS;
}
  • 실행 결과 
    • main start
       
----- initialize start -----
----- initialize end -----
       
----- execute_test start -----
----- execute_test end -----
       
----- executeQuery_test start -----
~~~ : (1)
1, a
2, b
1, a
2, b
----- executeQuery_test end -----
       
----- executeUpdate_test start -----
1
2
1
2
----- executeUpdate_test end -----
       
----- transaction_test_1 start -----
commit before start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
commit before end
commit after start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
        1, a
commit after end
rollback before start
        ~~~ transaction connection ~~~
        1, a
        2, b
        ~~~ other connection ~~~
        1, a
rollback before end
rollback after start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
        1, a
rollback after end
----- transaction_test_1 end -----
       
----- transaction_test_2 start -----
commit before start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
commit before end
commit after start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
        1, a
commit after end
rollback before start
        ~~~ transaction connection ~~~
        1, a
        2, b
        ~~~ other connection ~~~
        1, a
rollback before end
rollback after start
        ~~~ transaction connection ~~~
        1, a
        ~~~ other connection ~~~
        1, a
rollback after end
----- transaction_test_2 end -----
       
----- savepoint_test start -----
rollback before start
        1, a
        2, b
rollback before end
rollback after start
        1, a
rollback after end
----- savepoint_test end -----
       
----- finalize start -----
----- finalize end -----
       
main end

You may also enjoy

[database] PG Back Web

1 minute read

개요 GitHub 사용자 친화적인 웹 인터페이스로 손쉽게 PostgreSQL을 백업하는 툴 데이터의 보안과 가용성을 보장

[database] DuckDB

2 minute read

개요 사이트 SQL(Structured Query Language)을 지원하는 RDBMS

[Kubernetes] External Secrets Operator

1 minute read

개요 사이트 외부 비밀 관리 시스템을 통합하는 Kubernetes operator AWS Secrets Manager HashiCorp Vault Google Secrets Manager Azure Key Vault ...

[messaging] Centrifugo

4 minute read

개요 사이트 오픈소스 실시간 메시징 서버 Centrifuge(Go) 라이브러리를 기반으로 구축 다양한 실시간 전송(WebSocket, HTTP-streaming, SSE/EventSource, WebTransport, GRPC)을 통해 연결된 애플리케이션 온라인 사...