Qt c++中使用mysql小工具

#ifndef DBUTIL_H
#define DBUTIL_H

#include "mysql.h"

#include <map>
#include <vector>

#include <QString>



/**
 * database util
 */
class DBUtil
{

public:
    DBUtil();
    ~DBUtil();


public:
    bool connect();

    void close();

    bool create_table(
            const std::string& sql_str);

    // return id of insert data
    // -1 invalid
    int insert_data(
            const std::string& sql_str);

    bool update_data(
            const std::string& sql_str);

    std::map<std::string, std::string> select_line_data(
            const std::string& sql_str);

    // select many row data
    const std::vector<std::map<std::string, std::string> >
    select_data(
            const std::string& sql_str);

    bool select_exist(const std::string& sql_str);

    bool delete_data(const std::string& sql_str);


private:
    // create database: database
    // if not exists
    bool create_database(
            const std::string& database_name);


    QString get_database_name();




private:
    QString get_sql_error();



private:
    MYSQL* d_mysql;

    // is connect ok?
    bool d_is_connected;




};

#endif // DBUTIL_H

#include "DBUtil.h"
#include "TableNameConstant.h"
#include "GLOBAL/util/CommonFileUtil.h"


#include <iostream>
//#include <sqlite3.h>

#include <QDebug>



DBUtil::DBUtil()
    : d_mysql(0),
      d_is_connected(false)
{

}



DBUtil::~DBUtil()
{}



void
DBUtil::close()
{
    if( d_mysql )
    {
        mysql_close(d_mysql);
        //qDebug() << __FILE__ << __LINE__ << " mysql = " << d_mysql;
        d_mysql = 0;
        d_is_connected = false;
    }
}



bool
DBUtil::connect()
{
    // already connected
    if( d_is_connected )
    {
        return true;
    }

    //qDebug() << __FILE__ << __LINE__ << " mysql = " << d_mysql << " d_is_connected " << d_is_connected;
    d_mysql = mysql_init(nullptr);
    //qDebug() << __FILE__ << __LINE__ << " mysql = " << d_mysql << " d_is_connected " << d_is_connected;
    if( !d_mysql )
    {
        return false;
    }

    if( !mysql_real_connect(d_mysql, "localhost", "root", "123456", "mysql", 3306, "/var/run/mysqld/mysqld.sock", 0) )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: " << get_sql_error();

        d_is_connected = false;
        return false;
    }

    d_is_connected = create_database(HELWSP::TableNameConstant::s_DATABASE_NAME);

    //qDebug() << __FILE__ << __LINE__ << " mysql = " << d_mysql << " d_is_connected " << d_is_connected;
    return d_is_connected;
}



bool
DBUtil::create_database(
        const std::string& database_name)
{
    std::string sql_str = "create database if not exists " + database_name;
    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return false;
    }

    sql_str = "use " + database_name;
    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return false;
    }

    return true;
}




QString
DBUtil::get_database_name()
{
    return CommonFileUtil::get_db_dir() + HELWSP::TableNameConstant::s_SQLITE3_DATABASE_NAME;
}



bool
DBUtil::create_table(
        const std::string& sql_str)
{
    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return false;
    }

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return false;
    }

    return true;
}



int
DBUtil::insert_data(
        const std::string& sql_str)
{
    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return -1;
    }

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return -1;
    }

    //qDebug() << __FILE__ << __LINE__ << " insert id =  " << d_mysql->insert_id;
    return d_mysql->insert_id;
}



bool
DBUtil::update_data(
        const std::string& sql_str)
{
    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return false;
    }

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return false;
    }

    //qDebug() << __FILE__ << __LINE__ << " insert id =  " << d_mysql->insert_id;
    return true;
}



std::map<std::string, std::string>
DBUtil::select_line_data(
        const std::string& sql_str)
{
    std::map<std::string, std::string> map_result_data;

    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return map_result_data;
    }

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return map_result_data;
    }

    MYSQL_RES* result;

    result = mysql_store_result(d_mysql);

    const my_ulonglong& num_row = mysql_num_rows(result);
    const unsigned int& num_column = mysql_num_fields(result);

    if( num_row == 0 || num_column == 0 )
    {
         return map_result_data;
    }

    MYSQL_FIELD* field;
    MYSQL_ROW row;

    row = mysql_fetch_row(result);
    if( row )
    {
        for(unsigned int i = 0; i < num_column; i++)
        {
            field = mysql_fetch_field(result);
            map_result_data[field->name] = row[i];

            //qDebug() << __FILE__ << __LINE__ << " name = " << field->name << " row[i] = " << row[i];
        }
    }

    return map_result_data;
}



const std::vector<std::map<std::string, std::string> >
DBUtil::select_data(
        const std::string& sql_str)
{
    std::vector<std::map<std::string, std::string> > result_data;

    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return result_data;
    }

    std::map<std::string, std::string> column_data;

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return result_data;
    }

    MYSQL_RES* result;

    result = mysql_store_result(d_mysql);
    const my_ulonglong& num_row = mysql_num_rows(result);
    const unsigned int& num_column = mysql_num_fields(result);

    if( num_row == 0 || num_column == 0 )
    {
         return result_data;
    }

    MYSQL_FIELD* field;
    MYSQL_ROW row = nullptr;

    //qDebug() << __FILE__ << __LINE__ <<  " num_row = " << num_row << " num_column = " << num_column;
    std::vector<std::string> key_vector;
    while( field = mysql_fetch_field(result) )
    {
        key_vector.push_back(field->name);
    }

    for(int i = 0; i < num_row; i++)
    {
        column_data.clear();
        row = mysql_fetch_row(result);

        for(int j = 0; j < num_column; j++)
        {
            column_data[key_vector.at(j)] = row[j];
        }

        result_data.push_back(column_data);
    }

    mysql_free_result(result);

    return result_data;
}



bool
DBUtil::select_exist(const std::string& sql_str)
{
    const std::vector<std::map<std::string, std::string> >& data = select_data(sql_str);

    return (!data.empty());
}



bool
DBUtil::delete_data(const std::string& sql_str)
{
    const bool& connect_ok = connect();
    if( !connect_ok )
    {
        qDebug() << __FILE__ << __LINE__ << "warning: mariadb not connect.";
        return false;
    }

    if( mysql_query(d_mysql, sql_str.c_str()) )
    {
        qDebug() << __FILE__ << __LINE__ << "error: " << get_sql_error();
        return false;
    }

    return true;
}



QString
DBUtil::get_sql_error()
{
    return QString("Error(%1) [%2] \"%3\"").arg(mysql_errno(d_mysql)).arg(mysql_sqlstate(d_mysql)).arg(mysql_error(d_mysql));
}

发现c++是一种很容易写整齐的语言，java的话有ctrl+shift+f format快捷键