Can someone answer on my dilemma which method to use for connecting Android device to mySQL or Postgresql?

I can do it in both ways without any errors and problems, with no noticeable difference but everyone recommend web service instead of using jdbc driver and direct connection,

Can someone explain why with some facts?

EDIT: I did'n mention that is more simple and needs less time to do it over jdbc. So, why web service, or why not?

Solution 1

You think it's simpler and faster to do it with JDBC because you aren't considering the real world operating environment of phones and portable devices. They often have flakey connectivity through buggy traffic rewriting proxies and insane firewalls. They're typically using a network transport layer that has high and variable packet loss rates and latencies that vary over many orders of magnitude in short spans of time. TCP really isn't great in this environment and particularly struggles with long lived connections.

The key benefit of a web service is that it:

  • Has short-lived connections with minimal state, so it's easy to get back to where you were when the device switches WiFi networks, to/from cellular, loses connectivity briefly, etc; and

  • Can pass through all but the most awful and draconian web proxies

You will routinely encounter problems with a direct JDBC connection. One challenge is reliably timing out dead connections, re-establishing sessions and releasing locks held by the old session (as the server may not decide it's dead at the same time the client does). Another is packet loss causing very slow operations, long-running database transactions, and consequent problems with lock durations and transactional cleanup tasks. You'll also meet every variety of insane and broken proxy and firewall under the sun - proxies that support CONNECT but then turn out to assume all traffic is HTTPs and mangle it if it isn't; firewalls with buggy stateful connection tracking that cause connections to fail or go to a half-open zombie state; every NAT problem you can imagine; carriers "helpfully" generating TCP ACKs to reduce latency, never mind the problems that causes with packet loss discovery and window sizing; wacky port blocking; etc.

Because everyone uses HTTP, you can expect that to work - at least, vastly more often than anything else does. This is particularly true now that common websites use REST+JSON communication style even in mobile web apps.

You can also write your web service calls to be idempotent using unique request tokens. That lets your app re-send modification requests without fear that it'll perform an action against the database twice. See idempotence and definining idempotence.

Seriously, JDBC from a mobile device might look like a good idea now - but the only way I'd even consider it would be if the mobile devices were all on a single high-reliably WiFi network under my direct control. Even then I'd avoid it for reasons of database performance management if I possibly could. You can use something like PgBouncer to pool connections among many devices at the server side so connection pooling isn't a big problem, but cleanup of lost and abandoned connections is, as is the tcp keepalive traffic required to make it work and the long stalled transactions from abandoned connections.

Solution 2

I can think of a few reasons

  1. JDBC android driver support for your database.
  2. Connection pooling across various Android devices make it difficult to monitor and cap them.
  3. Result sets sent from the DB to android will consume a lot of bandwidth and battery power.
  4. Proxies usuall allow HTTP access to your device.
  5. Exposing your database directly to the client has security implications.

Web services can provide additional features on top of the JDBC connection like authentication / quality of service / authorization / conditional GET requests / error handling etc. JDBC cannot do any of these.

Solution 3

Besides all things Craig Ringer said, which I completely agree, JDBC has another problem: it will force to expose your database to the world. If you want android devices to access it, you will need to provide your app with database credentials, and the database will have to have public access.

Using a WebService or RESTful API is clearly the way to go to make your application secure.

Solution 4

Another option would be to use a database sync tool like SymmetricDS.

This would let you have say a Postgres database on your server, and a SQLite database on your tablet.

SymmetricDS would synchronize the databases over HTTP, when a connection is available. You don't have to sync the whole db of course, just the relevant parts.

(I am not affiliated with SymmetricDS)

Solution 5

TL;DR: It depends! (Sorry to all the "never ever ever ever do it, direct conns are always evil"-devs)

When creating a public domain / general app for the playstore kind of thing, I am mainly with my fellow responders. Opening your DB to "everyone" (especially when permissions are badly or not at all configured) is typically not a great idea!!

However(!), the story might be totally different, when you e.g. create something for internal use within the network boundaries of your company, like Android handheld devices for logistics, inventory, etc. In these cases I would even most of the time definately recommend going with JDBC or a similar direct connection. Reaons being:

  • One less point of failure
  • One less development (sub-)project
  • One less thing to maintain and keep up to date with your data-structure
  • One less thing to keep up and running, CI/CD, test, etc. (you get the draft)

Which - im my humble opinion - is worse than the (implement it once) effort of connection pooling, reestablishment, etc. (if it really becomes necesseary, be careful with premature optimization there).

But for public projects ... well, if they only ever require read access, I could possibly imagine it as well, or if there are only certain tables were you allow adding, but not delete or modifications. There are some tricks you could apply to make it still secure (allowing adds but not reads with id-secrets for a certain table, triggers, and general reads for other tables, etc.), but there is a lot to think and a lot to miss about these. So generally, I would say it is bad practice to allow your public domain client to get hold of your SQL connection. But still, don't let that hinder you to ask yourself (and understand) "why" and look at the specific situation. There might even be good cause/use for that. Especially since it is "less", which is also often better. It definately depends.

Just be careful and aware that (even if permissions are set correctly) a lot can be misused (and only little hindered), with a direct connection at your client. (Plus possible connection issues to be taken care of.)

As a sidenote: A lot of these considerations are relevant again with the use of technologies like GraphQL, which shares some similarities (however without connection issues and with a little bit more secure control).