我们使用 JDBC+XMLRPC+Tomcat+MySQL 来执行潜在的大型 MySQL 查询。 有什么更好的办法呢？
我正在开发一个基于 Java 的项目，该项目有一个客户端程序，需要连接到远程服务器上的 MySQL 数据库。 实现方式如下：
使用 JDBC 编写要执行的 SQL 查询，然后使用 Apache Tomcat 作为 servlet 托管，并通过 XML-RPC 进行访问。 客户端代码使用 XML-RPC 远程执行这些基于 JDBC 的功能。 这使我们能够保持 MySQL 数据库的非公开性，限制对预定义函数的使用，并允许 Tomcat 管理数据库事务（有人告诉我这比让 MySQL 单独完成要好，但我真的不这样做）不明白为什么）。 然而，这种方法需要大量的样板代码，而且 Tomcat 在我们的服务器上占用了大量内存。
我正在寻找更好的方法来做到这一点。 我正在考虑的一种方法是使 MySQL 数据库可公开访问，将基于 JDBC 的代码重写为存储过程，并限制公众只能使用这些过程。 我看到的问题是，将所有 JDBC 代码转换为存储过程将是困难且耗时的。 我对MySQL的权限也不太熟悉。 是否可以授予对在表上执行 select 语句的存储过程的访问权限，但又拒绝在同一表上执行任意 select 语句？
欢迎任何其他想法，以及关于存储过程解决方案的想法和/或建议。
谢谢你！
I'm working on a Java based project that has a client program which needs to connect to a MySQL database on a remote server. This was implemented is as follows:
Use JDBC to write the SQL queries to be executed which are then hosted as a servlet using Apache Tomcat and made accessible via XML-RPC. The client code uses XML-RPC to remotely execute these JDBC based functions. This allows us to keep our MySQL database non-public, restricts use to the pre-defined functions, and allows Tomcat to manage the database transactions (which I've been told is better than letting MySQL do it alone, but I really don't understand why). However, this approach requires a lot of boiler-plate code, and Tomcat is a huge memory hog on our server.
I'm looking for a better way to do this. One way I'm considering is to make the MySQL database publicly accessible, re-writing the JDBC based code as stored procedures, and restricting public use to these procedures only. The problem I see with this are that translating all the JDBC code to stored procedures will be difficult and time consuming. I'm also not too familiar with MySQL's permissions. Can one grant access to a stored procedure which performs select statements on a table, but also deny arbitrary select statements on that same table?
Any other ideas are welcome, as are thoughts and or sugguestions on the stored procedure solution.
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可能可以在服务器中升级 RAM，而花费的成本甚至比几天的开发时间还要少，因此，如果您从练习中得到的只是这些，请不要编写任何代码。 另外，仅仅因为内存在 tomcat 内部使用，并不意味着 tomcat 本身也在使用它。 内存可能会被数据或代码中的技术缺陷耗尽。
如果您尝试过额外的 RAM 并且它被耗尽，那么这听起来像是编码问题，所以我建议使用分析器或记录数据来尝试找出根本原因，然后再进行更改。 如果原因是大数据集，那么直接使用数据库只会延迟不可避免的事情，相反，您需要考虑分页、摘要、客户端缓存或重新设计客户端等内容以减少昂贵查询的使用。 使用探查器，或者简单地检查代码库，还可以告诉您是否有东西创建了太多对象（特别是字符串或 XML 节点）或泄漏内存。
可以通过创造性地重构来避免样板代码，并且避免重复是件好事。 目前尚不清楚您可能已经拥有多少结构，但只需做一些工作，就可以轻松集中样板 JDBC 调用。 没有根本原因应该重复 JDBC 代码，也许您可以告诉我们哪些代码被重复？
最后，我敢说有很多充分的理由在数据库上放置 Web 层。 （部署的）灵活性、兼容性、（对 SQL 的）控制和安全性都是保留 Web 层的充分理由。
You can probably get the RAM upgraded in your server for less than the cost of even a few days development time, so don't write any code if that's all you're getting from the exercise. Also, just because the memory is used inside of tomcat, it doesn't mean that tomcat itself is using it. The memory could be used up by data or by technical flaws in your code.
If you've tried additional RAM and it is being eaten up, then that smells like a coding issue, so I'd suggest using a profiler, or log data to try and work out what the root cause is before changing anything. If the cause is large data sets then using the database directly will only delay the inevitable, instead you'd need to look at things like paging, summarisation, client side caching, or redesigning clients to reduce the use of expensive queries. Using a profiler, or simply reviewing the code base, will also tell you if something is creating too many objects (especially strings, or XML nodes) or leaking memory.
Boiler plate code can be avoided by refactoring creatively, and its good that you do avoid repetition. Its unclear how much structure you might already have, but with a little work its easy to centralise boilerplate JDBCs calls. There is no fundamental reason JDBC code should be repeated, perhaps you could tell us what code is being repeated?
Finally, I'll venture that there are many good reasons to put a web tier over your database. Flexibility (of deployment), compatibility, control (over the SQL) and security are all good reasons to keep the web tier.
MySQL 5.0.3+ 确实具有您可以设置的执行权限（无需设置选择权限），这应该允许您获得所需的功能。
但是，请注意这个 mysql bug 报告（以及许多其他驱动程序）。
解决方法是：
MySQL 5.0.3+ does have an execute privilege that you can set (without setting select privileges) that should allow you to get the functionality you seek.
However, note this mysql bug report with JDBC (well and a lot of other drivers).
the workaround is:
我相信您可以在没有太多样板的情况下实现您的解决方案，尤其是。 使用 Spring 的远程处理之类的东西。 另外，Tomcat 占用了多少内存？ 坦率地说，我相信，如果它只是按照你所描述的那样进行，它可以在不到 128mb 的情况下工作（保守猜测）。
您的替代方案是“按照书本正确”的方式解决问题。 我说构建一个原型并看看它是如何工作的。 您可能遇到的主要问题是：
我可能是 MySQL 讨厌者之一，所以这种情况可能比我想象的更好。
I am sure you could implement your solution without much boiler-plate, esp. using something like Spring's remoting. Also, how much memory is Tomcat eating? I frankly believe that if it's just doing what you are describing, it could work in less than 128mb (conservative guess).
Your alternative is the "correct by the book" way of solving the problem. I say build a prototype and see how it works. The major problems you could have are:
I'm probably one of those MySQL haters, so the situation might be better than I think.