Jdbc VS Web Service For Android

JDBC vs Web Service for Android

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.

Why it is wrong to use jdbc in android application?

The problem with JDBC is that it requires a very stable connection and high bandwidth - two things which definitely are not guaranteed on mobile devices. You're absolutely correct - it would be much better to create a web service.

Updated

A web service has the added benefit of being more secure since you don't leave your database open to attackers, as well being more versatile - so if you in the future want to access it from elsewhere you won't have to write a specific implementation for iOS, WindowsPhone (or a good ol' fashioned website).

Another good reason is that doing work client side on phones can be a heavy operation which is a drain on the system resources (and battery). On modern phones, it might not be noticeable, but can be hugely beneficial to the user experience, especially on older models. There's nothing that kills the user experience more than when it feels "sluggish".

Why should a developer use web services instead of direct connections to a db?

1. Security. You're not granting DB access to anyone but web server/app user.

   This is extra important when you have tons of users. You avoid the pain of user/role maintenance on DB side.

2. DB load reduction. Web service can cache the data it retrieved from DB.

3. Database connection pooling (hat/tip @Dogs).

   A web service can use a small pool of permanently opened DB connections. The helps in a variety of ways:

   • DB connection pool is limited on database server side.

   • opening a new DB connection is VERY costly (especially to database server).

4. Ability for fault tolerance - the service can switch between primary/DR data sources without having details of fail-over be implemented by service consumers.

5. Scalability - the service can spread requests between several parallel data sources without having details of the resource picking be implemented by service consumers.

6. Encapsulation. You can change underlying DB implementation without impacting service users.

7. Data enrichment (this includes anything from client customization to localization to internalization). Basically any of these might be useful but any of them is a major load on database and often very hard to implement inside a DB.

8. May or may not apply to you - certain architecture decisions are not DB acces friendly.
   E.g. Java Servers running on Unix have an easy access to a database, whereas a java client running on a Windows PC is not database aware nor do you possibly want it to be.

9. Portability. Your clients may not all be on the same platform/architecture/language. Re-creating a good data access layer in each one of those is harder (since it must take into account such issues as above-mentioned failovers/etc...) than building a consumer layer for a web service.

10. Performance tuning. Assuming the alternative is clients running their own queries (and not pre-canned stored procedures), you can be 100% sure that they will start using less than optimal queries. Also, if the web service bounds the set of allowable queries, it can help with your database tuning significantly. I must add that this logic is equally applicable to stored procedures, not unique to web services.

A good list can also be found on this page: 'Encapsulating Database Access: An Agile "Best" Practice'

Just to be crystal clear - some of these issues may not be applicable to ALL situations. Some people don't care about portability. Some people don't need to worry about DB security. Some people don't need to worry about DB scalability.

Using JDBC with online MySQL DB for an Android app

You are failing to find good tutorials because with Android there are no native classes to interact with a MySQL Database (only SQLite). You need a RESTful service to serve up the data using JSON or XML. See this link for a good read on how you might go about doing this.

I recently had a similar requirement except I had to interact with a SQL Server 2008 database. My very naive implementation used ASP.NET MVC to return the JSON. They have recently released a very nice Web API to do just that, without out all of the MVC. See this and this and browse around asp.net for some good information.

Also more good links:

Creating a RESTful API with php

https://stackoverflow.com/questions/238125/best-framework-for-php-and-creation-of-restful-based-web-services

Another PHP restful service example

Whats that you ask? How do you now call these "RESTful" services from Android? Here is a great read.

Also this answer utilizes some of the code from the previous link.

---

Related Topics