問題描述

我需要一些關(guān)于使用 Camel 將二進(jìn)制文件從文件夾加載到 MySQL 數(shù)據(jù)庫(kù)的方法的指導(dǎo).基本上我想將我們的 PBX 系統(tǒng)中的語(yǔ)音日志存儲(chǔ)到數(shù)據(jù)庫(kù)中.包含語(yǔ)音日志的目錄將是遠(yuǎn)程目錄

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

我設(shè)計(jì)了一個(gè)原型，但我不確定這是否真的有效，它有效，但我對(duì)設(shè)計(jì)不滿意.讓我解釋一下我在做什么.駱駝路線如下:

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

好的簡(jiǎn)短說明我讓文件組件使用文件，然后我使用 MySQL LOAD_FILE() 函數(shù)構(gòu)建一個(gè) 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.

我對(duì)此的看法:

LOAD_FILE 函數(shù)僅適用于本地機(jī)器，因此此路由僅適用于本地機(jī)器上的文件.我可以使用文件生成器將文件從某個(gè)遠(yuǎn)程目錄復(fù)制到本地目錄，然后使用該路由.我的路線將是這樣的:

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>

但是，由于我可以訪問來自文件使用者的消息中的文件內(nèi)容，因此理論上我應(yīng)該能夠訪問字符串的正文/內(nèi)容并構(gòu)建不使用 LOAD_FILE() 函數(shù)的 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.

我知道如何構(gòu)建這樣一個(gè)字符串的唯一方法是使用 JDBC 的預(yù)準(zhǔn)備語(yǔ)句.如果我能以某種方式使用來自文件使用者的內(nèi)容構(gòu)建一個(gè)插入語(yǔ)句，這將是一等獎(jiǎng).

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 中創(chuàng)建一個(gè)準(zhǔn)備好的語(yǔ)句并將它傳遞給我的 jdbc 組件嗎?或者如何在沒有 LOAD_FILE() 函數(shù)的情況下構(gòu)建 INSERT 語(yǔ)句?

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() 函數(shù)，我現(xiàn)在必須同時(shí)滿足 unix 和 windows 文件路徑.雖然這應(yīng)該不難，但我只是不喜歡將特定于操作系統(tǒng)的代碼放入我的應(yīng)用程序的想法(感覺像是一種解決方法).

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 將二進(jìn)制文件上傳到 MySQL 數(shù)據(jù)庫(kù)，他們可以就上述幾點(diǎn)給我一些指導(dǎo).雖然我可以解決這些問題，但我只是想確保我不會(huì)錯(cuò)過一個(gè)明顯的做事方式.

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.

我環(huán)顧四周，只發(fā)現(xiàn)人們主要使用文本文件.伙計(jì)們，請(qǐng)不要走我將文件存儲(chǔ)在文件系統(tǒng)上并將其鏈接到數(shù)據(jù)庫(kù)的路線.我們有一些非常具體的災(zāi)難恢復(fù)要求和法律要求，強(qiáng)制要求我將其存儲(chǔ)在數(shù)據(jù)庫(kù)中.

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.

推薦答案

是的，所以我設(shè)法找到了一種方法，并沒有那么困難.我基本上做的是擺脫路由中的 JDBC Camel 組件.然后我將數(shù)據(jù)源 bean 注入到我的 fileToSQL bean 中.然后我使用一個(gè)簡(jiǎn)單的預(yù)處理語(yǔ)句將文件及其名稱插入到 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.

一如既往，代碼比我的英語(yǔ)更明確.

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 非常靈活，尤其是當(dāng)您將它與 Spring 結(jié)合使用時(shí).

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

多么美妙的旅程！

這篇關(guān)于二進(jìn)制文件到 SQL 數(shù)據(jù)庫(kù) Apache Camel的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！