C++ GUI : Console에서 mariaDB C/C++ Connector 사용하기

 

1. mariaDB C/C++ Connector 설치는 다음을 참고하세요.

https://taekho.tistory.com/18

C++ : mariaDB C/C++ Connector 설치하기

 

2. DTO(Data Transfer Object)를 추가한다.

 

2.1 MemberDTO.h파일을 추가한다.

#pragma once
#include <string>

class MemberDTO
{
public:
	int id;
	std::string forename;
	std::string surname;
	std::string email;
	std::string password;
	std::string joined;
	std::string picture;
	MemberDTO() : id(0) {}
};

 

3. DAO(Data Access Object)를 추가한다.

 

3.1 MemberDAO.h 파일을 추가한다.

#pragma once

#include <vector>
#include <memory>

#include "MemberDTO.h"

#include <mariadb/conncpp.hpp>

class MemberDAO
{
private:
    sql::Driver* driver;

    std::string host;
    std::string database;
    std::string user;
    std::string password;

public:
    MemberDAO();

    std::unique_ptr<sql::Connection> getConnection();

    int insert(MemberDTO& dto);

    bool update(MemberDTO& dto);

    bool remove(int id);

    std::vector<MemberDTO> selectAll();
};

 

 

3.2 MemberDAO.cpp 파일을 추가한다.

#include "MemberDAO.h"

#include <iostream>

MemberDAO::MemberDAO()
{
    driver = sql::mariadb::get_driver_instance();

    host = "jdbc:mariadb://localhost:3306";
    database = "database1";

    user = "user1";
    password = "password1";
}

std::unique_ptr<sql::Connection> MemberDAO::getConnection()
{
    std::unique_ptr<sql::Connection> conn{driver->connect(host, user, password)};

    conn->setSchema(database);

    return conn;
}

int MemberDAO::insert(MemberDTO& dto)
{
    try
    {
        auto conn = getConnection();

        std::unique_ptr<sql::PreparedStatement> pstmt{
            conn->prepareStatement("INSERT INTO member (forename, surname, email) VALUES (?, ?, ?)") };

        pstmt->setString(1, dto.forename);
        pstmt->setString(2, dto.surname);
        pstmt->setString(3, dto.email);

        pstmt->executeUpdate();

        auto stmt =
            std::unique_ptr<sql::Statement>{
                conn->createStatement()};

        auto res =
            std::unique_ptr<sql::ResultSet>{
                stmt->executeQuery(
                    "SELECT LAST_INSERT_ID()")};

        if (res->next())
        {
            return res->getInt(1);
        }

        return 0;
    }
    catch (sql::SQLException& e)
    {
        std::cout << e.what() << std::endl;
        return -1;
    }
}

bool MemberDAO::update(MemberDTO& dto)
{
    try
    {
        auto conn = getConnection();

        std::unique_ptr<sql::PreparedStatement> pstmt{
            conn->prepareStatement(
                "UPDATE member SET "
                "forename=?, "
                "surname=?, "
                "email=?, "
                "password=?, "
                "picture=? "
                "WHERE id=?")};

        pstmt->setString(1, dto.forename);
        pstmt->setString(2, dto.surname);
        pstmt->setString(3, dto.email);
        pstmt->setInt(6, dto.id);

        pstmt->executeUpdate();

        return true;
    }
    catch (sql::SQLException& e)
    {
        std::cout << e.what() << std::endl;
        return false;
    }
}

bool MemberDAO::remove(int id)
{
    try
    {
        auto conn = getConnection();

        std::unique_ptr<sql::PreparedStatement> pstmt{
            conn->prepareStatement("DELETE FROM member WHERE id=?")};

        pstmt->setInt(1, id);

        pstmt->executeUpdate();

        return true;
    }
    catch (sql::SQLException& e)
    {
        std::cout << e.what() << std::endl;
        return false;
    }
}

std::vector<MemberDTO> MemberDAO::selectAll()
{
    std::vector<MemberDTO> list;

    try
    {
        auto conn = getConnection();

        std::unique_ptr<sql::Statement> stmt{conn->createStatement() };

        std::unique_ptr<sql::ResultSet> res{stmt->executeQuery("SELECT * FROM member")};

        while (res->next())
        {
            MemberDTO dto;

            dto.id = res->getInt("id");

            dto.forename = res->getString("forename");
            dto.surname = res->getString("surname");
            dto.email = res->getString("email");
            dto.password = res->getString("password");
  
            list.push_back(dto);
        }
    }
    catch (sql::SQLException& e)
    {
        std::cout << e.what() << std::endl;
    }

    return list;
}

 

4. main.cpp에 아래의 코드를 입력한다.

#include <iostream>
#include <windows.h>

#include "MemberDAO.h"

int main()
{
    SetConsoleOutputCP(CP_UTF8);

    MemberDAO dao;

    // INSERT
    MemberDTO dto;

    dto.forename = u8"길";
    dto.surname = u8"동";
    dto.email = "hong@test.com";
    dto.password = "1234";
    dto.picture = "hong.jpg";

	int lastId = dao.insert(dto);

    if (lastId != -1)
    {
        std::cout << u8"INSERT 성공, ID: " << lastId << std::endl;
    }

    // SELECT
    std::vector<MemberDTO> list = dao.selectAll();

    std::cout << "==================" << std::endl;

    for (auto& m : list)
    {
        std::cout
            << m.id << "\t"
            << m.forename << "\t"
            << m.surname << "\t"
            << m.email << "\t"
            << m.picture
            << std::endl;
    }

    // UPDATE
    MemberDTO updateDto;

    updateDto.id = lastId;

    updateDto.forename = u8"수정";
    updateDto.surname = u8"회원";
    updateDto.email = "update@test.com";
    updateDto.password = "9999";
    updateDto.picture = "update.jpg";

    if (dao.update(updateDto))
    {
        std::cout << u8"UPDATE 성공" << std::endl;
    }

    // DELETE
    if (dao.remove(lastId))
    {
        std::cout << u8"DELETE 성공" << std::endl;
    }

    return 0;
}