Wednesday, December 12, 2007

How to insert and retrieve files in hsqldb

How to save files into hsqldb? How to restore them back?


String calibrationZip = "calibrazione.zip";
String newcalibrationZip = "new_calibrazione.zip";
String insertStr = "INSERT INTO CALIBRAZIONE_BLOB (ID, DATA) VALUES (##, ?)"
String sql = "select data as DATA from CALIBRAZIONE_BLOB where id = ##"
int runId = 1;


/*
* insert into blob
*/
FileInputStream fis = null;
PreparedStatement st = null;
try {
File fl = new File(calibrationZip);
fis = new FileInputStream(fl);
insertStr = insertStr.replaceFirst("##", String.valueOf(runId));
st = con.prepareStatement(insertStr);
st.setBinaryStream(1, fis, (int) fl.length());
st.executeUpdate();
} catch (Exception ex) {
System.out.println(ex);
} finally {
try {
if (null != st)
st.close();
} catch (Exception ex) {
}
try {
if (null != fis)
fis.close();
} catch (Exception ex) {
}
}

/*
* extract the blob
*/
sql = sql.replaceFirst("##", String.valueOf(runId));
try {
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery(sql);
if (rs.next()) {
InputStream is = rs.getBinaryStream("DATA");
FileOutputStream fos = new FileOutputStream(newcalibrationZip);
byte[] buff = new byte[8192];
int len;
while( 0 < (len = is.read(buff)) )
fos.write(buff, 0, len);
fos.close();
is.close();
}
rs.close();
statement.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

No comments: