Binary for Apache Camel SQL Database
I need some tutorial on which to use uploading binaries from folder to MySQL database using Camel. Basically I want to store voice logs from our PBX system in a database. The directory with voice logs will be the remote directory
I've developed a prototype but I'm not sure if it is really effective, but it works, but I'm not happy with the design. Let me explain what I am doing. Camel as follows:
<camelContext xmlns="http://camel.apache.org/schema/spring">
<package>com.hia.camelone</package>
<route>
<from uri="file://c:/CTest/Inbox?noop=true&recursive=true&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"/>
And the fileToSQL bean code:
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();
}
}
Nice short explanation. I get a file component to use files, then I create a SQL string using the MySQL LOAD_FILE () function to load the file.
My thoughts on this:
The LOAD_FILE function only works on the local computer, and thus this route will only work with files residing on the local computer. I could use a file producer to copy files from some remote directory to a local directory and then use a route. Then my route would be something like this:
<route>
<from uri="file://c:/CTest/Inbox?noop=true&recursive=true&delay=3000"/>
<to uri="file://c:/outbox"/>
<to uri="bean://fileToSQL"/>
<to uri="jdbc://timlogdb"/>
</route>
However, since I have access to the content of the files in the message from the users of the files, I should in theory be able to access the body / content of the string and create a SQL command that does NOT use the LOAD_FILE () function.
The only way I know how to construct such a string is by using a JDBC prepared statement. This would be the first prize if I could somehow create an insert statement with content from the user of the file.
Is it possible to create a prepared statement in the fileToSQL bean and pass it to my jdbc component? Or how can I create an INSERT statement without the LOAD_FILE () function?
Since I have to use the LOAD_FILE () function, I now have to serve both unix and windows file paths. While it shouldn't be hard, I just don't like the idea of injecting OS code into my apps (it seems to work).
Anyone have ever uploaded binaries to MySQL database using Camel who can give me some advice on the above points. While I could work around the problems, I just want to make sure I don't miss the obvious way of doing things.
I have looked around here and found people who work with text files. Guys, please don't even come down with me by saving the file to the filesystem and linking it to the database. We have some very specific disaster recovery and legal requirements that require me to save to the database.
source to share
Correct, so I managed to find a way and it wasn't that hard. What I basically did was get rid of the JDBC Camel component in the route. Then I injected the datasource bean into my ToSQL bean file. Then I used a simple prepared statement to insert the file and its name into MySQL.
As always the 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&recursive=true&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>
As you can see, I am injecting my timlogdb bean into my ToSQL bean file. Spring ROCKY!
So here is my ToSQL bean file.
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 guys did a great job. Camel is really flexible, especially if you combine it with Spring.
What a trip!
source to share