void MergedAuthorDao::add(vector<int>& froms, int to)
{
Connection* conn = NULL;
PreparedStatement* stat = NULL;
try
{
conn = ConnectionPool::getInstance()->getConnection();
conn->setAutoCommit(false);
stat = conn->prepareStatement("insert into na_author_map(from_naid,to_naid) values(?,?)");
stat->setInt(2, to);
for (int from : froms)
{
stat->setInt(1, from);
stat->executeUpdate();
}
conn->commit();
conn->setAutoCommit(true);
ConnectionPool::close(conn, stat, NULL);
}
catch (sql::SQLException &e)
{
LOG(ERROR) << boost::str(boost::format("# ERR: SQLException in %1% %2%") % __FILE__ %__FUNCTION__);
LOG(ERROR) << boost::str(boost::format("%1% error code %2%") %e.what() % e.getErrorCode());
}
}
void JDBCTemplate::executeUpdate(std::string sql, int type, object *obj)
{
Connection *conn = 0;
try
{
//调用钩子方法
conn = this->getConnection();
PreparedStatement *pstmt = conn->prepareStatement(sql);
//调用原语操作
this->setUpdateSqlValue(type,pstmt,obj);
pstmt->executeUpdate();
pstmt->close();
}
catch(Exception *err)
{
err->printStackTrace();
}
//JAVA TO C++ CONVERTER TODO TASK: There is no native C++ equivalent to the exception 'finally' clause:
finally
{
try
{
conn->close();
}
catch (SQLException *e)
{
e->printStackTrace();
}
}
}
/**
* Insert the sheet into the database
*/
int SheetController::AddSheet(Sheet& sheet)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO sheets (name, username, layoutid) VALUES (?,?,?)");
//Populate the query with the values from the passed sheet
stmt->setString(1, sheet.name);
stmt->setString(2, sheet.username);
stmt->setInt(3, sheet.layoutId);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted sheet
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
* Insert the stat into the database
*/
int StatController::AddStat(Stat& stat)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO stats (users, sheets, feeds, items, comments) VALUES (?,?,?,?,?)");
//Populate the query based on the passed stat
stmt->setInt(1, stat.users);
stmt->setInt(2, stat.sheets);
stmt->setInt(3, stat.feeds);
stmt->setInt(4, stat.items);
stmt->setInt(5, stat.comments);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted stat
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
int Call::DBwrite(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?) WHERE id=(?)");
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
pstmt->setInt(1, false);
pstmt->setString(4, COUNTRY_UNKNOWN);
pstmt->setString(5, COUNTRY_UNKNOWN);
pstmt->setString(6, translateLang.c_str());
pstmt->setInt(7, price);
char *time = asctime(localtime(&start_time));
if (start_time)
pstmt->setDateTime(8, time);
else
pstmt->setNull(8, 0);
pstmt->setInt(9, accounted);
pstmt->setInt(10, cost);
pstmt->setInt(11, getState() == ERROR);
pstmt->setInt(12, id);
int ret;
try {
ret = pstmt->executeUpdate();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
return ret == 1;
}
/**
* Delete the layout from the database
*/
void LayoutController::RemoveLayout(Layout& layout)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM layouts WHERE id = ?");
stmt->setInt(1, layout.id);
//Delete by ID
stmt->executeUpdate();
}
int nuodb_statement_execute(struct nuodb *db, struct nuodb_statement *st,
int64_t *rows_affected, int64_t *last_insert_id) {
PreparedStatement *stmt = reinterpret_cast<PreparedStatement *>(st);
try {
stmt->executeUpdate();
return fetchExecuteResult(db, stmt, rows_affected, last_insert_id);
} catch (SQLException &e) {
return setError(db, e);
}
}
/**
* Delete the content placeholder from the database
*/
void ContentPlaceholderController::RemoveContentPlaceholder(int id)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM cph WHERE id = ?");
stmt->setInt(1, id);
//Remove the record by ID
stmt->executeUpdate();
delete stmt;
}
/**
* Delete the stat from the database
*/
void StatController::RemoveStat(int id)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM stats WHERE id = ?");
stmt->setInt(1, id);
//Delete by ID
stmt->executeUpdate();
delete stmt;
}
int MySQL::Update(StringList* _list){
assert(NULL != _list);
try{
if(conn_->isClosed()){
Connect();
}
conn_->setAutoCommit(0);
StringList::iterator it = _list->begin();
for(; it != _list->end(); it++){
PreparedStatement* pstmt = conn_->prepareStatement(*it);
assert(pstmt);
pstmt->executeUpdate();
}
conn_->commit();
}
catch(sql::SQLException &e){
conn_->rollback();
Error("MySQL exception: func=%s, line=%d, expno=%d, sqlstate=%s, expstr=%s",
__func__, __LINE__, e.getErrorCode(), e.getSQLState().c_str(), e.what());
return -1;
}
catch (std::runtime_error &e) {
conn_->rollback();
Error("Update runtime error: errstr=%s", e.what());
return -1;
}
return 1;
} //Update List
string generateToken(string login)
{
try
{
PreparedStatement * prepstmt;
prepstmt = conn -> prepareStatement("delete from tokens where userid=(select id from users where login=?);");
prepstmt->setString(1, login);
prepstmt->executeUpdate();
prepstmt = conn->prepareStatement("insert into tokens (userid, token, expiry) values ((select id from users where login=?), ?, now()+interval ? hour);");
string token = genRandomAlphanumericStr(TOKEN_SIZE);
prepstmt->setString(1, login);
prepstmt->setString(2, token);
prepstmt->setInt(3, TOKEN_VALID_TIME);
prepstmt->executeUpdate();
conn->commit();
return token;
}
catch(exception e)
{
throw 2;
}
}
/**
* Update the relevant content placeholder in the database
*/
void ContentPlaceholderController::UpdateContentPlaceholder(ContentPlaceholder& cph)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE cphs SET sheetid = ?, type = ?, column = ?, order = ? WHERE id = ?");
//Update with all parameters
stmt->setInt(1, cph.sheetId);
stmt->setInt(2, cph.type);
stmt->setInt(3, cph.column);
stmt->setInt(4, cph.order);
stmt->setInt(5, cph.id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant sheet in the database
*/
void SheetController::UpdateSheet(Sheet& sheet)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE Sheets SET name = ?, username = ?, updated = ?, layoutid = ? WHERE id = ?");
//Populate the query with the values from the passed sheet
stmt->setString(1, sheet.name);
stmt->setString(2, sheet.username);
stmt->setString(3, sheet.updated.ExportToMySQL());
stmt->setInt(4, sheet.layoutId);
stmt->setInt(5, sheet.id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant stat in the database
*/
void StatController::UpdateStat(Stat& stat)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE stats SET users = ?, sheets = ?, feeds = ?, items = ?, comments = ? WHERE id = ?");
//Populate the query values using the passed stat
stmt->setInt(1, stat.users);
stmt->setInt(2, stat.sheets);
stmt->setInt(3, stat.feeds);
stmt->setInt(4, stat.items);
stmt->setInt(5, stat.comments);
stmt->setInt(6, stat.id);
stmt->executeUpdate();
delete stmt;
}
int PhoneCall::DBwrite(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?), request_time=(?), confirm_time=(?), accepted=(?) WHERE id=(?)");
pstmt->setInt(1, true);
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
pstmt->setString(4, ((PhoneCall *)this)->getClientCountry().c_str());
pstmt->setString(5, ((PhoneCall *)this)->getTranslatorCountry().c_str());
pstmt->setString(6, translateLang.c_str());
pstmt->setInt(7, price);
char time[512];
strftime(time, 512, SQLTIME_FMT, localtime(&start_time));
if (start_time)
pstmt->setDateTime(8, time);
else
pstmt->setNull(8, 0);
pstmt->setInt(9, getAccountedTime());
pstmt->setInt(10, cost);
pstmt->setInt(11, getState() == ERROR);
strftime(time, 512, SQLTIME_FMT, localtime(&request_time));
if (request_time)
pstmt->setString(12, time);
else
pstmt->setNull(12, 0);
strftime(time, 512, SQLTIME_FMT, localtime(&confirm_time));
if (confirm_time)
pstmt->setDateTime(13, time);
else
pstmt->setNull(13, 0);
pstmt->setBoolean(14, accepted);
pstmt->setInt(15, id);
int ret;
try {
ret = pstmt->executeUpdate();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
return ret == 1;
}
void commitNewMessage(Document & request)
{
validateTokenByName(request["data"][0]["sender"].GetString(), request["usertoken"].GetString());
try
{
PreparedStatement * prepstmt;
prepstmt = conn -> prepareStatement("insert into msgs (senderuid, receiveruid, msg, datesent) values ((select id from users where (login = ?)), (select id from users where (login = ?)), ?, now());");
prepstmt->setString(1, request["data"][0]["sender"].GetString());
prepstmt->setString(2, request["data"][0]["receiver"].GetString());
prepstmt->setString(3, request["data"][0]["message"].GetString());
prepstmt->executeUpdate();
conn->commit();
return;
throw 3;
}
catch(exception e)
{
throw 2;
}
}
vector<map<string, string>> retrievePendingMessages(Document & request)
{
int recvid= validateToken(request["usertoken"].GetString());
try
{
PreparedStatement * prepstmt;
prepstmt = conn->prepareStatement("select senderuid, receiveruid, msg, datesent from msgs where (receiveruid=? and status=0);");
prepstmt->setInt(1, recvid);
ResultSet * rs;
rs = prepstmt->executeQuery();
prepstmt = conn->prepareStatement("update msgs set status=1 where receiveruid=?;"); //ATTENTION! Threading error can occur!
prepstmt->setInt(1, recvid);
prepstmt->executeUpdate();
conn->commit();
ResultSet * namers;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, recvid);
namers = prepstmt->executeQuery();
namers->next();
string receiver = namers->getString("login");
vector<map<string, string>> msgs;
while(rs->next()){
map<string, string> onemsg;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, rs->getInt("senderuid"));
namers = prepstmt->executeQuery();
namers->next();
onemsg.insert(make_pair("sender", namers->getString("login")));
onemsg.insert(make_pair("receiver", receiver));
onemsg.insert(make_pair("message", rs->getString("msg")));
onemsg.insert(make_pair("datesent", rs->getString("datesent")));
//cout << namers->getString("login") << " " << rs->getString("msg") << endl;
msgs.push_back(onemsg);
}
return msgs;
}
catch(exception e)
{
throw 2;
}
}
bool registerUser(Document & request)
{
try
{
PreparedStatement * prepstmt;
string login, pwd, email;
login = request["data"][0]["login"].GetString();
pwd = request["data"][0]["password"].GetString();
email = request["data"][0]["email"].GetString();
prepstmt = conn->prepareStatement("insert into users (login, pwdhash, email, dateregister) values (?, sha1(?), ?, now());");
prepstmt->setString(1, login);
prepstmt->setString(2, pwd);
prepstmt->setString(3, email);
prepstmt->executeUpdate();
conn->commit();
return true;
}
catch(exception e)
{
throw 2; //todo make different exceptions processing
}
}
/**
* Insert the content placeholder into the database
*/
int ContentPlaceholderController::AddContentPlaceholder(ContentPlaceholder& cph)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO cphs (sheetid, type, column, order) VALUES (?,?,?,?)");
//Set provided parameters
stmt->setInt(1, cph.sheetId);
stmt->setInt(2, cph.type);
stmt->setInt(3, cph.column);
stmt->setInt(4, cph.order);
//Insert
stmt->executeUpdate();
delete stmt;
//Perform another query to get the index of the inserted content placeholder
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
* Update the relevant content placeholder in the database, NULLs passed if that parameter is not to be updated
*/
void ContentPlaceholderController::UpdateContentPlaceholder(int id, int* sheetId, int* wid, bool* column, int* order)
{
string query = "UPDATE cph SET ";
//For each parameter, check if it is provided, if it is add to the UPDATE statement
if(sheetId != NULL)
{
query += "sheetid = ?";
}
else
{
query += "sheetid = sheetid";
}
if(wid != NULL)
{
query += ", wid = ?";
}
else
{
query += ", wid = wid";
}
if(column != NULL)
{
query += ", column = ?";
}
else
{
query += ", column = column";
}
if(order != NULL)
{
query += ", order = ?";
}
else
{
query += ", order = order";
}
query += " WHERE id = ?";
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
//Populate the parameters in the statement
if(sheetId != NULL)
{
stmt->setInt(param, *sheetId);
param++;
}
if(wid != NULL)
{
stmt->setInt(param, *wid);
param++;
}
if(column != NULL)
{
stmt->setInt(param, *column);
param++;
}
if(order != NULL)
{
stmt->setInt(param, *order);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant sheet in the database, NULLs passed if that parameter is not to be updated
*/
void SheetController::UpdateSheet(int id, string* name, string* username, nfrd::misc::DateTime* updated, int* layoutId)
{
//Generate the update query
string query = "UPDATE Sheets SET ";
//If the value is null, set it to the existing value
if(name != NULL)
{
query += "name = ?";
}
else
{
query += "name = name";
}
if(username != NULL)
{
query += ", username = ?";
}
else
{
query += ", username = username";
}
if(updated != NULL)
{
query += ", updated = ?";
}
else
{
query += ", updated = updated";
}
if(layoutId != NULL)
{
query += ", layoutid = ?";
}
else
{
query += ", layoutid = layoutid";
}
query += " WHERE id = ?";
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
//Add the values to the query
if(name != NULL)
{
stmt->setString(param, *name);
param++;
}
if(username != NULL)
{
stmt->setString(param, *username);
param++;
}
if(updated != NULL)
{
stmt->setString(param, updated->ExportToMySQL());
param++;
}
if(layoutId != NULL)
{
stmt->setInt(param, *layoutId);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant stat in the database, NULLs passed if that parameter is not to be updated
*/
void StatController::UpdateStat(int id, int* users, int* sheets, int* feeds, int* items, int* comments)
{
//Generate the update query
string query = "UPDATE stats SET ";
//If values are null, set the columns to equal their existing values
if(users != NULL)
{
query += "users = ?";
}
else
{
query += "users = users";
}
if(sheets != NULL)
{
query += ", sheets = ?";
}
else
{
query += ", sheets = sheets";
}
if(feeds != NULL)
{
query += ", feeds = ?";
}
else
{
query += ", feeds = feeds";
}
if(items != NULL)
{
query += ", items = ?";
}
else
{
query += ", items = items";
}
if(comments != NULL)
{
query += ", comments = ?";
}
else
{
query += ", comments = comments";
}
query += " WHERE id = ?";
//Add the values to the statement
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
if(users != NULL)
{
stmt->setInt(param, *users);
param++;
}
if(sheets != NULL)
{
stmt->setInt(param, *sheets);
param++;
}
if(feeds != NULL)
{
stmt->setInt(param, *feeds);
param++;
}
if(items != NULL)
{
stmt->setInt(param, *items);
param++;
}
if(comments != NULL)
{
stmt->setInt(param, *comments);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
