Java Mysql BLOB reading/writing

The following tested code reads a file using fileinputstream stores in blob format in mysql table. It then reads the blob object and writes to some location in the form of a file. Due to limitation of blob this might not support for huge files.If you need to process with large files be on safe side to use LONGBLOB data type.
/**
*
*mysql table creation
*create table stb(id integer,doc blob) engine=innodb;

*
*/

package com.test;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StreamtoBinary {

/**
* @param args
*/
public static void main(String[] args) {
System.out.println("reading inputstream");
Statement stmt = null;
Connection con = null;
try {
File file = new File(
"srcfilepath");
FileInputStream iStream = new FileInputStream(file);

Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
con = DriverManager.getConnection(url, "root", "");
stmt = con.createStatement();
String sql = "insert into stb values(?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, iStream, (int) file.length());
// pstmt.executeUpdate();
// con.commit();
System.out.println("written to table:" + (int) file.length());
// now read the blob object and write to a file
sql = "SELECT id, doc FROM stb";
PreparedStatement stmt1 = con.prepareStatement(sql);
ResultSet resultSet = stmt1.executeQuery();
int i = 0;
while (resultSet.next()) {
String id = resultSet.getString(1);
File doc = new File(
"destfilepath");

FileOutputStream fos = new FileOutputStream(doc);
Blob b = resultSet.getBlob(2);
BufferedOutputStream os;

os = new BufferedOutputStream(new FileOutputStream(doc));
os.write(b.getBytes(1, (int) b.length()), 0, (int) b.length());
os.flush();
os.close();
System.out.println("writing record:" + id);
System.out.println("written to file:" + doc.length());
// }
fos.close();
i++;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
stmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

}

Thanks,
Ramesh
.

1 comment:

  1. Merci Beaucoup Ramesh from Jamal (MOROCCO)

    ReplyDelete