C++ PreparedStatement::setInt方法代码示例

void Database::updatePlayer(Player *player)
{
    if (!isConnected())
        init();

    if (!m_isConnected)
        return;

    PreparedStatement *statement = 0;

    try
    {
        statement = m_database->prepareStatement("UPDATE accounts SET kills = ?, deaths = ? WHERE id = ?");
        statement->setInt(1, player->kills());
        statement->setInt(2, player->deaths());
        statement->setInt(3, player->userId());

        statement->executeQuery();
    }
    catch (SQLException &e)
    {
        emit writeToConsole("Query failed(updatePlayer): " + QLatin1String(e.getSQLStateCStr()));
    }

    delete statement;
}

/**
 * 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;
}

unsigned int CallManager::storeCall(Connection *sqlCon, bool phone, unsigned int client, unsigned int translator)
{
	if (!sqlCon)
		return 0;
	PreparedStatement *pstmt = sqlCon->prepareStatement(
			"INSERT INTO calls SET phone=(?), client=(?), translator=(?), request_time=NOW()");
	pstmt->setInt(1, phone);
	pstmt->setInt(2, client);
	pstmt->setInt(3, translator);

	try {
		pstmt->execute();
	} catch (SQLException &ex) {
		log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
		delete pstmt;
		return 0;
	}
	delete pstmt;

	pstmt = sqlCon->prepareStatement("SELECT LAST_INSERT_ID()");

	ResultSet *res;
	try {
		res = pstmt->executeQuery();
	} catch (SQLException &ex) {
		log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
		delete pstmt;
		return 0;
	}
	delete pstmt;
	res->first();
	unsigned int id = res->getInt(1);
	delete res;
	return id;
}

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());
	}
}

/**
 * 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 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;
}

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;
}

/**
 * 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;
}

void StatisticDAO::insert(const tableLogData& data)
{
	PreparedStatement* insertRow;
	PreparedStatement* queryID;
	switch (data.playerNums)
	{
		case 6:
			insertRow = connection->prepare("insert into TableLog values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			break;
		case 4:
			insertRow = connection->prepare("insert into TableLog values(null,?,?,?,?,?,?,?,?,?,?,?,?,null,null)"); 
			break;
	}
		

	int lastID=0;
	insertRow->setInt(TABLELOG_COL_TABLEMODE, data.tableMode);
	insertRow->setInt(TABLELOG_COL_PLAYERNUMS, data.playerNums);
	insertRow->setInt(TABLELOG_COL_WINNER, data.winner);
	insertRow->setInt(TABLELOG_COL_REDSCORE, data.redScore);
	insertRow->setInt(TABLELOG_COL_BLUESCORE, data.blueScore);
	insertRow->setInt(TABLELOG_COL_REDCUPNUM, data.redCupNum);
	insertRow->setInt(TABLELOG_COL_BLUECUPNUM, data.blueCupNum);
	insertRow->setString(TABLELOG_COL_CREATETIME, data.createTime);
	for (int i = 0; i < data.playerNums; i++)
		insertRow->setString(TABLELOG_COL_PLAYERID + i, data.tableDetail[i].playerID);  //Ö÷±íID¼Ç¼

	connection->executeUpdate(insertRow);

	queryID = connection->prepare("select LAST_INSERT_ID() from tableLog");
	ResultSet* res=connection->executeQuery(queryID);
	if (res->next()) { lastID=res->getInt(1); }

	for (int i = 0; i < data.playerNums; i++)
	{
		insertRow = connection->prepare("insert into TableDetail values(null,?,?,?,?,?,?)");
		insertRow->setInt(TABLEDETAIL_COL_TABLEID, lastID);
		insertRow->setString(TABLEDETAIL_COL_PLAYERID, data.tableDetail[i].playerID);
		insertRow->setInt(TABLEDETAIL_COL_PLAYERSERIAL, data.tableDetail[i].playerSerial);
		insertRow->setInt(TABLEDETAIL_COL_TEAM, data.tableDetail[i].team);
		insertRow->setInt(TABLEDETAIL_COL_ROLE, data.tableDetail[i].role);
		insertRow->setInt(TABLEDETAIL_COL_RESULT, data.tableDetail[i].result);
		connection->executeUpdate(insertRow);
		
	}
		

};

/**
 * Find the content placeholder with that id
 */
ContentPlaceholder* ContentPlaceholderController::GetContentPlaceholderById(int id)
{
	PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM cph WHERE id = ?");
	
	stmt->setInt(1, id);

	ResultSet* rs = stmt->executeQuery();

	delete stmt;

	if(rs != NULL)
	{
		while(rs->next())
		{
			//Return the content placeholder retrieved by the query
			ContentPlaceholder* c = GenerateContentPlaceholder(*rs);
			delete rs;
			return c;
		}
	}
	else
	{
		return NULL;
	}

	return NULL;
}

/**
 * Find all content placeholders that belong to the sheet of that id
 */
vector<ContentPlaceholder*> ContentPlaceholderController::GetContentPlaceholdersBySheetId(int sheetId)
{
	PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM cph WHERE sheetid = ?");
	
	stmt->setInt(1, sheetId);

	ResultSet* rs = stmt->executeQuery();

	delete stmt;

	vector<ContentPlaceholder*> cphs;
	if(rs != NULL)
	{
		while(rs->next())
		{
			//For all content placeholders matching the query, add them to the vector
			cphs.push_back(GenerateContentPlaceholder(*rs));
		}

		delete rs;
	}

	return cphs;

}

/**
 * Find the stat with that id
 */
Stat* StatController::GetStatById(int id)
{
	PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM stats WHERE id = ?");
	
	stmt->setInt(1, id);

	ResultSet* rs = stmt->executeQuery();

	delete stmt;

	if(rs != NULL)
	{
		while(rs->next())
		{
			//If a stat matches this id, return it
			Stat* s = GenerateStat(*rs);
			delete rs;
			return s;
		}
	}
	else
	{
		return NULL;
	}

	return NULL;
}

void Database::updatePlayerCount(int count)
{
    if (!isConnected())
        init();

    if (!m_isConnected)
        return;

    PreparedStatement *statement = 0;

    try
    {
        statement = m_database->prepareStatement("UPDATE servers SET connectedPlayers = ? WHERE id = ?");
        statement->setInt(1, count);
        statement->setString(2, m_serverID.toStdString());

        statement->executeQuery();
    }
    catch (SQLException &e)
    {
        emit writeToConsole("Query failed(updatePlayerCount): " + QLatin1String(e.getSQLStateCStr()));
    }

    delete statement;
}

/**
 * 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;
}

/**
 * Find the layout with that id
 */
Layout LayoutController::GetLayoutById(int id)
{
	PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM layouts WHERE id = ?");
	
	stmt->setInt(1, id);

	ResultSet* rs = stmt->executeQuery();
	delete stmt;
	
	if(rs != NULL)
	{
		while(rs->next())
		{
			//If a layout matches that id, return it
			return GenerateLayout(*rs);
		}
	}
	else
	{
		Layout l;
		return l;
	}

	Layout l;
	return l;
}