問題描述

我需要一些關于使用 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.

多么美妙的旅程！

這篇關于二進制文件到 SQL 數據庫 Apache Camel的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！