二进制文件到 SQL 数据库 Apache Camel

2021-11-11 00:00:00 file-upload mysql apache-camel

我需要一些关于使用 Camel 将二进制文件从文件夹加载到 MySQL 数据库的方法的指导.基本上我想将我们的 PBX 系统中的语音日志存储到数据库中.包含语音日志的目录将是远程目录

I need some guidance around which approach to use to load binary files from a folder into a MySQL Database using Camel. Basically I want to store voice logs from our PBX system into a database. The directory with the voice logs will be a remote directory

我设计了一个原型，但我不确定这是否真的有效，它有效，但我对设计不满意.让我解释一下我在做什么.骆驼路线如下:

I have designed a prototype but I am not sure if this is really efficient, it works but I am not happy with the design. Let me explain what I am doing. Camel route as follows:

    <camelContext xmlns="http://camel.apache.org/schema/spring">
    <package>com.hia.camelone</package>
      <route>
            <from uri="file://c:/CTest/Inbox?noop=true&amp;recursive=true&amp;delay=3000"/>
            <to uri="bean://fileToSQL"/>
            <to uri="jdbc://timlogdb"/>

       </route>

</camelContext>

<bean id="timlogdb" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value=" com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/TimLog" />
    <property name="username" value="root" />
    <property name="password" value="blahblah" />
</bean>
<bean id="fileToSQL" class="com.hia.camelone.fileToSQL"/>

fileToSQL bean的代码是:

And the code to fileToSQL bean is:

public class fileToSQL {

public String toString(@Headers Map<String,Object> header, @Body Object body){
    StringBuilder sb = new StringBuilder();
    String filename =(String)header.get("CamelFileNameOnly");
    String escapedFileName = StringEscapeUtils.escapeJava(filename).replace("\'", "");
    String filePath = StringEscapeUtils.escapeJava((String)header.get("CamelFilePath"));

    sb.append("insert into FileLog ");
    sb.append("(FileName,FileData) values (");
    sb.append("'").append(escapedFileName).append("',").append("LOAD_FILE(\"").append(filePath).append("\")");
    sb.append(")");
    System.out.println(sb.toString());
    System.out.println(body);
    System.out.println(header.toString());
    return sb.toString();
}
}

好的简短说明我让文件组件使用文件，然后我使用 MySQL LOAD_FILE() 函数构建一个 SQL 字符串来加载文件.

Ok short explanation I get the file component to consume the files then I build a SQL string using the MySQL LOAD_FILE() function to load the file.

我对此的看法:

LOAD_FILE 函数仅适用于本地机器，因此此路由仅适用于本地机器上的文件.我可以使用文件生成器将文件从某个远程目录复制到本地目录，然后使用该路由.我的路线将是这样的:

The LOAD_FILE function only works on the local machine and thus this route will only with the files being on the local machine. I could use a file producer to copy the files from some remote directory to a local directory and then use the route. My route would be something like this then:

<route>
            <from uri="file://c:/CTest/Inbox?noop=true&amp;recursive=true&amp;delay=3000"/>
            <to uri="file://c:/outbox"/>
            <to uri="bean://fileToSQL"/>
            <to uri="jdbc://timlogdb"/>

</route>

但是，由于我可以访问来自文件使用者的消息中的文件内容，因此理论上我应该能够访问字符串的正文/内容并构建不使用 LOAD_FILE() 函数的 SQL 命令.

However since I have access to the files content in the message from the files consumer I should be able to theoretically be able to access the body/content of the string and build a SQL command that does NOT use the LOAD_FILE() function.

我知道如何构建这样一个字符串的唯一方法是使用 JDBC 的预准备语句.如果我能以某种方式使用来自文件使用者的内容构建一个插入语句，这将是一等奖.

The only way I know how to build such a string is by using the prepared statement of JDBC. This would be first prize if I could somehow build a insert statement with the content from the file consumer.

我可以在我的 fileToSQL bean 中创建一个准备好的语句并将它传递给我的 jdbc 组件吗?或者如何在没有 LOAD_FILE() 函数的情况下构建 INSERT 语句?

Can I create a prepared statement in my fileToSQL bean and pass it to my jdbc component? Or how do I build a INSERT statement without the LOAD_FILE() function?

由于我必须使用 LOAD_FILE() 函数，我现在必须同时满足 unix 和 windows 文件路径.虽然这应该不难，但我只是不喜欢将特定于操作系统的代码放入我的应用程序的想法(感觉像是一种解决方法).

Since I have to use the LOAD_FILE() function I would now have to cater for both unix and windows filepaths. While this should not be difficult I just dont like the idea of putting OS specific code into my applications(feels like a work around).

这里的任何人都曾使用 Camel 将二进制文件上传到 MySQL 数据库，他们可以就上述几点给我一些指导.虽然我可以解决这些问题，但我只是想确保我不会错过一个明显的做事方式.

Anybody here ever uploaded binary files to a MySQL database using Camel who can give me some guidance on the points above. While I could work around the problems I just want to make sure I dont miss a obvious way of doing things.

我环顾四周，只发现人们主要使用文本文件.伙计们，请不要走我将文件存储在文件系统上并将其链接到数据库的路线.我们有一些非常具体的灾难恢复要求和法律要求，强制要求我将其存储在数据库中.

I had a look around here and only found people working with mostly text files. Guys please don't even go down the route of me storing the file on the files system and linking it to the database. We have some very specific disaster recovery requirements and legal requirements that enforce the need for me to store it in a database.

推荐答案

是的，所以我设法找到了一种方法，并没有那么困难.我基本上做的是摆脱路由中的 JDBC Camel 组件.然后我将数据源 bean 注入到我的 fileToSQL bean 中.然后我使用一个简单的预处理语句将文件及其名称插入到 MySQL 中.

Right so I managed to find a way and it was not that difficult. What I essentially did was get rid of the JDBC Camel Component in the route. I then injected the data source bean into my fileToSQL bean. I then used a simple prepared statement to insert the file and its name into MySQL.

一如既往，代码比我的英语更明确.

As always code is much more explicit than my english.

 <camelContext xmlns="http://camel.apache.org/schema/spring">
    <package>com.hia.camelone</package>

      <route>
            <from uri="file://c:/CTest/Inbox?noop=true&amp;recursive=true&amp;delay=3000"/>
            <to uri="bean://fileToSQL"/>
            <!--<to uri="jdbc://timlogdb"/>-->

       </route>

</camelContext>

<bean id="timlogdb" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value=" com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/TimLog" />
    <property name="username" value="root" />
    <property name="password" value="lalala" />
</bean>
<bean id="fileToSQL" class="com.hia.camelone.fileToSQL">
    <property name="dataSource" ref="timlogdb"/>
</bean>

如您所见，我将 timlogdb bean 注入到我的 fileToSQL bean 中.春天的岩石！

As you can see I inject my timlogdb bean into my fileToSQL bean. Spring ROCKS!

这里是我的 fileToSQL bean.

So here is my fileToSQL bean.

public class fileToSQL {
private DriverManagerDataSource dataSource;
private static final String SQL_INSERT="insert into FileLog(FileName,FileData)values(?,?)";
@Handler
public void toString(@Headers Map<String,Object> header,Exchange exchange){
    Connection conn = null;
    PreparedStatement stmt=null;
    String filename =StringEscapeUtils.escapeJava(((String)header.get("CamelFileNameOnly")).replace("\'", ""));

    try {
        conn= dataSource.getConnection();
        stmt =conn.prepareStatement(SQL_INSERT);
        stmt.setString(1, filename);
        byte[] filedata = exchange.getIn().getBody(byte[].class);
        stmt.setBytes(2,filedata );
        int s = stmt.executeUpdate();

    }
    catch (Exception e)
    {
        System.out.println(e.getMessage());
    }
    finally{
        try
        {
                if (stmt!=null)
                {
                    stmt.close();
                }
                if (conn!=null)
                {
                    conn.close();
                }
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
        }
    }


}

/**
 * @param dataSource the dataSource to set
 */
public void setDataSource(DriverManagerDataSource dataSource) {
    this.dataSource = dataSource;
}
}

Camel 的人做得很好.Camel 非常灵活，尤其是当您将它与 Spring 结合使用时.

The guys from Camel did a great job. Camel is truly flexible especially when you combine it with Spring.

多么美妙的旅程！

相关文章