Java MS SQL ->; mysql 转换
我正在工作中构建一个应用程序，需要一些建议。我有一个有点独特的问题，我需要收集 MS SQL Server 中的数据，并每 15 分钟将其移植到 mySQL Server。
我之前已经使用 DataGrid 在 C# 中完成了此操作，但现在尝试构建一个可以在 Ubuntu 服务器上运行的 Java 版本，但我找不到类似的 Java 模型。
只是提供一点背景知识
当我从 MS SQL Server 提取数据时，它总是有 9 列，但可能有 0 - 1000 行。
在盲目插入mySQL Server之前，我确实操作了一些数据。
- 我根据 STATE 列将时间列转换为 CST
- 我删除了一些字符以防止 SQL 注入
我尝试使用 ResultSet，但我遇到了“仅转发结果集”规则的问题。
保存该信息、操作它、然后解析它以稍后插入到 mySQL 中的最佳数据结构是什么？
I am building an application at work and need some advice. I have a somewhat unique problem in which I need to gather data housed in a MS SQL Server, and transplant it to a mySQL Server every 15 mins.
I have done this previously in C# with a DataGrid, but now am trying to build a Java version that I can run on an Ubuntu Server, but I can not find a similar model for Java.
Just to give a little background
When I pull the data from the MS SQL Server, it always has 9 columns, but could have anywhere from 0 - 1000 rows.
Before inserting into the mySQL Server blindly, I do manipulate some of the data.
- I convert a time column to CST based on a STATE column
- I strip some characters to prevent SQL injection
I tried using the ResultSet, but I am having issues with the "forward only result sets" rules.
What would be the best data structure to hold that information, manipulate it, and then parse it to insert later into mySQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这听起来像是PreparedStatements 的工作！
此处定义： http://download.oracle.com/ javase/6/docs/api/java/sql/PreparedStatement.html
简单示例：http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html
PreparedStatements 允许您在将数据集推送到目标数据库之前对其进行批处理。它们还允许您使用PreparedStatement.setString 方法来为您处理转义字符。
对于时间转换，我将从行中检索 STATE 值，然后检索时间值。在调用PreparedStatement.setDate 之前，如有必要，请将时间转换为CST。
我认为您不需要 ORM 工具所需的所有开销。
This sounds like a job for PreparedStatements!
Defined here: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html
Quick example: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html
PreparedStatements allows you to batch up sets of data before pushing them into the target database. They also allow you use the PreparedStatement.setString method which handles escaping characters for you.
For the time conversion thing, I would retrieve the STATE value from the row and then retrieve the time value. Before calling PreparedStatement.setDate, convert the time to CST if necessary.
I dont think that you would need all the overhead that an ORM tool requires.
您可以考虑使用像 Hibernate 这样的 ORM 技术。乍一看这可能有点重量级，但这意味着您可以轻松维护各种数据库的各种表映射，并且可以利用 Java 的 RegEx 库的强大功能来满足任何操作要求。
因此，您将有一个代表源表（及其 Hibernate 映射）的 Java 类和另一个代表目标表的 Java 类，最后是一个对该数据进行任何操作的转换实用程序类。 Hibernate 会为您处理 CRUD SQL，因此无需担心数据库特定的 SQL（只要您获得正确的映射）。
它还减少了 SQL 注入问题
You could consider using an ORM technology like Hibernate. This might seem a little heavyweight at first, but it means you can maintain the various table mappings for various databases with ease as well as having the power of Java's RegEx lib for any manipulation requirements.
So you'd have a Java class that represents the source table (with its Hibernate mapping) and another Java class that represents the target table and lastly a conversion utility class that does any manipulation of that data. Hibernate takes care of the CRUD SQL for you, so no need to worry about Database specific SQL (as long as you get the mapping correct).
It also lessens the SQL injection problem