postgresql JDBC 与 Android 的 Web 服务

JDBC vs Web Service for Android

提问by fenix

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

有人可以回答我的困境使用哪种方法将Android设备连接到 mySQL 或 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,

两种方式我都可以做到，没有任何错误和问题，没有明显区别，但每个人都推荐使用 Web 服务而不是使用 jdbc 驱动程序和直接连接，

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?

编辑：我没有提到这更简单，并且通过 jdbc 完成它需要的时间更少。那么，为什么是 Web 服务，或者为什么不呢？

回答by Craig Ringer

You thinkit'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.

您认为使用 JDBC 更简单、更快捷，因为您没有考虑手机和便携式设备的真实操作环境。他们通常通过有问题的流量重写代理和疯狂的防火墙来实现不稳定的连接。他们通常使用具有高且可变的丢包率和延迟的网络传输层，这些延迟在短时间内会在多个数量级上发生变化。TCP 在这种环境中真的不是很好，尤其是在长期连接的情况下。

The key benefit of a web service is that it:

Web 服务的主要优势在于：

• 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

• 具有最短状态的短暂连接，因此当设备切换 WiFi 网络、进/出蜂窝网络、短暂失去连接等时，很容易回到原来的状态；和

• 可以通过除最糟糕和最严厉的网络代理之外的所有代理

You will routinelyencounter 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 CONNECTbut 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.

您经常会遇到直接 JDBC 连接的问题。一个挑战是可靠地超时死连接，重新建立会话并释放旧会话持有的锁（因为服务器可能不会在客户端确定它已死的同时）。另一个是数据包丢失导致操作非常缓慢、数据库事务长时间运行，以及随之而来的锁定持续时间和事务清理任务的问题。您还将在阳光下遇到各种疯狂和损坏的代理和防火墙 - 支持的代理CONNECT但结果是假设所有流量都是 HTTPS，如果不是，则对其进行处理；带有错误状态连接跟踪的防火墙会导致连接失败或进入半开僵尸状态；您可以想象的每个 NAT 问题；运营商“有帮助地”生成 TCP ACK 以减少延迟，不用担心丢包发现和窗口大小导致的问题；古怪的端口阻塞；等等。

Because everyoneuses 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.

因为每个人都使用 HTTP，所以您可以预期它会起作用 - 至少，比其他任何东西都更频繁。现在尤其如此，即使在移动 Web 应用程序中，常见网站也使用 REST+JSON 通信风格。

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

您还可以使用唯一的请求令牌将 Web 服务调用编写为幂等的。这让您的应用程序可以重新发送修改请求，而不必担心它会对数据库执行两次操作。请参阅幂等和定义幂等。

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.

说真的，来自移动设备的 JDBC 现在看起来可能是个好主意 - 但我什至考虑的唯一方法是，如果移动设备都在我直接控制的单个高可靠性 WiFi 网络上。即便如此，如果可能的话，我也会出于数据库性能管理的原因避免使用它。您可以使用类似 PgBouncer 之类的东西在服务器端的许多设备之间建立连接池，因此连接池不是一个大问题，但清理丢失和废弃的连接是一个大问题，这是使其工作所需的 tcp keepalive 流量和长期停滞的来自废弃连接的交易。

回答by Deepak Bala

I can think of a few reasons

我能想到几个原因

1. JDBC android driver supportfor your database.
2. Connection poolingacross 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 bandwidthand batterypower.
4. Proxiesusuall allow HTTP access to your device.
5. Exposing your database directly to the client has securityimplications.

1. JDBC android 驱动程序支持您的数据库。
2. 跨各种 Android 设备的连接池使得监控和限制它们变得困难。
3. 从 DB 发送到 android 的结果集会消耗大量带宽和电池电量。
4. 代理通常允许 HTTP 访问您的设备。
5. 将您的数据库直接暴露给客户端具有安全隐患。

Web services can provide additional features on top of the JDBC connection like authentication/ qualityof service / authorization/ conditional GETrequests / errorhandling etc. JDBC cannot do any of these.

Web 服务可以在 JDBC 连接之上提供附加功能，例如身份验证/服务质量/授权/条件 GET请求/错误处理等。 JDBC 不能做任何这些。

回答by Pablo Santa Cruz

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.

除了 Craig Ringer 所说的所有事情之外，我完全同意，JDBC 还有另一个问题：它会强制将您的数据库暴露给全世界。如果您希望 Android 设备访问它，您需要为您的应用程序提供数据库凭据，并且数据库必须具有公共访问权限。

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

使用 WebService 或 RESTful API 显然是使您的应用程序安全的方法。

回答by Neil McGuigan

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

另一种选择是使用像 SymmetricDS 这样的数据库同步工具。

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

这将让您可以说服务器上有一个 Postgres 数据库，平板电脑上有一个 SQLite 数据库。

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.

当连接可用时，SymmetricDS 将通过 HTTP 同步数据库。当然，您不必同步整个数据库，只需同步相关部分。

(I am not affiliated with SymmetricDS)

（我不隶属于 SymmetricDS）