二进制文件到SQL数据库Apache的骆驼 [英] Binary File To SQL Database Apache 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"/>

而code到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的prepared声明。这将是第一名，如果我能以某种方式建立一个INSERT语句从文件消费者的内容。

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豆创建ppared语句$ P $并将它传递给我的jdbc的组成部分？
或如何构建一个INSERT语句没有LOAD_FILE（）函数？

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文件路径。虽然这应该不难我只是不喜欢把操作系统特定code到我的应用程序的想法（感觉像周围的工作）。

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).

在这里任何人都曾经上传的二进制文件使用骆驼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骆驼的组件。然后，我注入数据源豆到我fileToSQL豆。然后我用一个简单的prepared语句插入的文件，它的名字到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.

一如往常code比我的英语更清晰。

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豆到我fileToSQL豆。春天ROCKS！

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

因此​​，这里是我的fileToSQL豆。

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;
}
}

从骆驼球员的表现非常出色。尤其是当你使用Spring结合起来骆驼是真正灵活。

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

什么一程！

这篇关于二进制文件到SQL数据库Apache的骆驼的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！