This question has been converted to Community Wiki and wiki locked
because it is an example of a question that seeks a list of answers
and appears to be popular enough to protect it from closure. It
should be treated as a special case and should not be viewed as the
type of question that is encouraged on this, or any Stack Exchange
site, but if you wish to contribute more content to it then feel free
to do so by editing this answer.
For non-GIS background devs creating single-function mapping apps - I'd probably recommend Leaflet (now supported by MapBox). Easy to use and small. More functionality relies on plugins of varying quality and support.
GIS online type application - use OpenLayers3 - has the full suite of data sources, controls etc. in a single library. It can also be used for simple mapping apps, so if you have a mix then I'd use this for everything.
Google still has the library/data combination (e.g. StreetView is not available anywhere else).
Esri has GUI web app builders, and as noted below, why complicate things if you already use their stack.
Commercial APIs (Google, Bing, Yahoo)
Using any commercial API leaves you at the mercy of whatever changes the provider makes to the API or Terms of Service. What happens for example if suddenly your local government portal that uses Google Maps suddenly has adverts popping up all over it? Want to reuse your JavaScript Google Maps code for an Intranet site? You'll find yourself having to pay the $10,000 licencing fee.
Microsoft often have more defined and fixed terms for their services (if paying) so there may be less of a risk here.
The recent backlash against Twitter is a good example of developers having an API that changed beneath them. If you aren't paying for a service then you (or your system) are the one being sold.
Google started charging for use of their maps from October 2011.
Esri
As others have mentioned if you are using an Esri stack then the ArcGIS API for JavaScript will no doubt work well on top of it. Coming from a GIS background Esri have probably thought more about traditional GIS tasks and features than the "neo-geography" web-giants (though this is just an opinion/feeling).
OpenLayers has built in support for ArcGIS REST layers, and if you are looking to reuse your code for non-ESRI based websites then again an open API serves you best.
Use OpenLayers...
I can't really think why developers would use an API other than OpenLayers. Open Source projects lead to related Open Source projects so there are a wealth of reusable components out there such as the GeoExt library, MapQuery, and GeoPrisma.
I'll just add that just because a project is Open Source does not automatically make it better than commercial equivalents - but the OpenLayers API matches the commercial competition in this case, and the ability to see how the source works, the unit tests, the build scripts etc. mean that you can easily build new features on top of it.
There has been some recent criticism of OpenLayers, mainly related to complexity, styling, and size. There have been counter-arguments made to these here and here by Christopher Schmidt one of the lead developers of OpenLayers.
It is worth noting that if you need a simple Open Source mapping API then take a look at Cloudmade's Leaflet.
The comment smaller, faster, newer, and more straightforward can also be read as less features and less tested.
Scan the API documentation for Leaflet and OpenLayers. The latter includes items such as WFS layers, editing tools, and SLD support. It has also been tested in many different environments, and works in IE6 (allowing government and local authority users).
For simpler display of spatial data Leaflet looks ideal, and easier to get started. However I'll be sticking with OpenLayers for more feature-rich GIS applications.
Caveats
One possible downside is often new innovations are seen first in commercial providers' systems - however these nearly always filter through to OpenLayers in time.
Finally I'm sure there are certain scenarios where other APIs are more suitable - on custom hardware, to fit in with an organisations other IT systems, or if you already know an API inside-out and can get a system developed in half the time.
All the APIs you mentioned are capable of producing great online mapping systems, but your choice should also fit in with you or your company's future development needs.
The only ORM for PostGIS that I'm aware of is the one used in GeoDjango. If you can, move the spatial querying functionality to the server with GeoDjango on the backend and send HTTP queries from Android.
The Android app would send POST or GET requests with the parameter e.g. object id and a distance to search. The GeoDjango views would do all of the PostGIS query construction for you. A search for ZIP codes within 5km of a point (where 'geom' is the Point object) would look like:
Zipcode.objects.filter(poly__dwithin=(geom, D(km=5)))
No SQL to write. You can get the geometries back as GeoJSON, KML, EWKB, WKB, WKT, etc.
Best Answer
Here are the results of my brief research on this Issue:
ArcGIS runtime for Android. Seems to be oriented towards showing ArcGIS Server's Mapservices. It can show data in an offline mode, by storing tiles in the compact cache storage format, or by using tile packages.
OSMDroid A component which can be used to show data from Open Street Maps when on-line, and can use tiles for off-line access. It is a raster based option, and you can use Mobile Atlas creator to create tiles from pretty much any WMS service.
MapsForge An excellent library for rendering OSM data on the fly. It has a special optimized tile format, and it's rendering performance is quite good.
Google Maps Android API This is the default mapView in Android, and uses the data from Google Maps. While the Google map application itself can be used in offline mode, as far as I am aware, is not possible to use Google Data in the offline mode. However it is possible to show your own tiles in offline mode. Here is an Excellent example.
Nutiteq This is fully featured library that has many unique features like support for 3D, various OGC services, custom Map API's, offline routing and maps in any projection.
Mapbox has recently released an Android SDK, which can be used for online and offline maps.
HERE has mobile sdks for Android and iOS, which can be used for online and offline maps.
CARTO has mobile sdks for Android, iOS, and Windows Mobile 10, which can be used for online and offline maps.
Scout has mobile sdks for Android and iOS, which can be used for online and offline maps.