|
#ifndef BASIC_BUSINESS_TABLE_BASEMANAGER_H
|
#define BASIC_BUSINESS_TABLE_BASEMANAGER_H
|
|
#include <list>
|
#include <vector>
|
#include <string>
|
#include <iostream>
|
#include <mysql++.h>
|
#include <AppConfig.h>
|
#include <QtCore/QMutex>
|
|
using namespace std;
|
|
class BaseDao {
|
public:
|
static QMutex m_mutexVisit;
|
private:
|
static mysqlpp::Connection* conn;
|
public:
|
static mysqlpp::SimpleResult add(std::map<std::string, std::string>& columns, string tableName) {
|
initConnection();
|
mysqlpp::SimpleResult simpleResult;
|
try {
|
mysqlpp::Query query = conn->query(getInsertSql(columns, tableName));
|
simpleResult = query.execute();
|
if (simpleResult.insert_id() <= 0) {
|
cout << "add error " << query.error() << endl;
|
}
|
} catch (exception &e) {
|
cout << e.what() << endl;
|
}
|
return simpleResult;
|
}
|
static bool del(string tableName, std::map<std::string, std::string>& whereColumns) {
|
initConnection();
|
string sql = "DELETE FROM " + tableName + " where 1=1 " + getWhereColumnNameValuePair(whereColumns);
|
mysqlpp::Query query = conn->query(sql);
|
bool ret = query.exec();
|
if (!ret) {
|
cout << "error " <<query.error() << endl;
|
}
|
return ret;
|
}
|
static bool update(std::map<std::string, std::string>& columns, string tableName, std::map<std::string, std::string>& whereColumns) {
|
string updateSql = getUpdateSql(columns, tableName, whereColumns);
|
initConnection();
|
mysqlpp::Query query = conn->query(updateSql);
|
bool ret = query.exec();
|
if (!ret) {
|
cout << "add error " << query.error() << endl;
|
}
|
return ret;
|
}
|
|
static vector<map<string, string>> findList(string sql) {
|
initConnection();
|
vector<map<string, string>> rowDataList;
|
mysqlpp::Query query = conn->query(sql);
|
if (auto res = query.store()) {
|
for (auto it = res.begin(); it != res.end(); ++it) {
|
map<string, string> rowData;
|
mysqlpp::Row row = *it;
|
auto field_list = row.field_list();
|
const mysqlpp::FieldNames* fieldNames = field_list.list;
|
for (int i = 0; i < fieldNames->size(); i++) {
|
string columnValue;
|
row[i].to_string(columnValue);
|
string columnName = fieldNames[0].at(i);
|
rowData[columnName] = columnValue;
|
}
|
rowDataList.emplace_back(rowData);
|
}
|
} else {
|
cout << "query failed" << endl;
|
}
|
return rowDataList;
|
}
|
|
static Json::Value findJsonArray(string sql) {
|
initConnection();
|
mysqlpp::Query query = conn->query(sql);
|
Json::Value rowList;
|
if (auto res = query.store()) {
|
for (auto it = res.begin(); it != res.end(); ++it) {
|
Json::Value row;
|
const mysqlpp::FieldNames* fieldNames = it->field_list().list;
|
for (int i = 0; i < fieldNames->size(); i++) {
|
string columnValue;
|
(*it)[i].to_string(columnValue);
|
if (columnValue == "NULL") {
|
columnValue = "";
|
}
|
string columnName = fieldNames[0].at(i);
|
row[columnName] = columnValue;
|
}
|
rowList.append(row);
|
}
|
} else {
|
cout << "query failed" << endl;
|
}
|
return rowList;
|
}
|
|
static bool exec(string sql) {
|
initConnection();
|
mysqlpp::Query query = conn->query(sql);
|
bool ret = query.exec();
|
if (!ret) {
|
cout << "error " <<query.error() << endl;
|
}
|
return ret;
|
}
|
static string getInsertSql(std::map<std::string, std::string>& columns, string tableName) {
|
string insertSql = "INSERT INTO "+tableName+" ("
|
+ getColumnNames(columns) +
|
" ) values ( "
|
+ getColumnValues(columns) +
|
" )";
|
cout << "insertSql " << insertSql << endl;
|
return insertSql;
|
}
|
static string getUpdateSql(std::map<std::string, std::string>& columns, string tableName, std::map<std::string, std::string>& whereColumns) {
|
string updateSql = "update "+tableName+" set "
|
+ getColumnNameValuePair(columns) +
|
" where 1=1 "+
|
getWhereColumnNameValuePair(whereColumns)
|
;
|
cout << "updateSql " << updateSql << endl;
|
return updateSql;
|
}
|
static void doConnect() {
|
if (conn->connect(
|
appConfig.getStringProperty("database").c_str(),
|
appConfig.getStringProperty("db_host").c_str(),
|
appConfig.getStringProperty("db_user").c_str(),
|
appConfig.getStringProperty("db_password").c_str(),
|
appConfig.getIntProperty("db_port")
|
)) {
|
// if (conn->connect(
|
// "EGEyesForVSS",
|
// "192.168.1.148",
|
// "root",
|
// "123456",
|
// 3306
|
// )) {
|
cout << "connect success" << endl;
|
} else {
|
cout << "connect failed" << endl;
|
}
|
}
|
static void initConnection() {
|
static bool inited = false;
|
if (!inited) {
|
inited = true;
|
conn = new mysqlpp::Connection(false);
|
doConnect();
|
}
|
if (!conn->connected() || !conn->ping()) {
|
doConnect();
|
}
|
}
|
static string getColumnNames(std::map<std::string, std::string>& columns) {
|
string columnNames;
|
auto size = columns.size();
|
int i = 0;
|
for (auto column : columns) {
|
columnNames.append(column.first);
|
if (i != size - 1) {
|
columnNames.append(",");
|
}
|
i++;
|
}
|
return columnNames;
|
}
|
static string getColumnValues(std::map<std::string, std::string>& columns) {
|
string columnValues;
|
auto size = columns.size();
|
int i = 0;
|
for (auto column : columns) {
|
columnValues.append("'"+column.second+"'");
|
if (i != columns.size() - 1) {
|
columnValues.append(",");
|
}
|
i++;
|
}
|
return columnValues;
|
}
|
static string getColumnNameValuePair(std::map<std::string, std::string>& columns) {
|
string columnNameValuePair;
|
auto size = columns.size();
|
int i = 0;
|
for (auto column : columns) {
|
columnNameValuePair.append(column.first);
|
columnNameValuePair.append("=");
|
columnNameValuePair.append("'"+column.second+"'");
|
|
if (i != size - 1) {
|
columnNameValuePair.append(",");
|
}
|
i++;
|
}
|
return columnNameValuePair;
|
}
|
static string getWhereColumnNameValuePair(std::map<std::string, std::string>& columns) {
|
string columnNameValuePair;
|
auto size = columns.size();
|
int i = 0;
|
for (auto column : columns) {
|
columnNameValuePair.append(" and ");
|
columnNameValuePair.append(column.first);
|
columnNameValuePair.append("=");
|
columnNameValuePair.append("'"+column.second+"' ");
|
|
i++;
|
}
|
return columnNameValuePair;
|
}
|
};
|
|
|
#endif //BASIC_BUSINESS_TABLE_BASEMANAGER_H
|
