1
2
3
4
5 package example.filestorage;
6
7 import java.io.ByteArrayOutputStream;
8 import java.io.InputStream;
9 import java.io.OutputStream;
10 import java.util.*;
11 import java.sql.*;
12
13 /***
14 *
15 * @author Sean C. Sullivan
16 *
17 *
18 */
19 class DerbyFileStorageDAO implements FileStorageDAO
20 {
21 public FileInfo getFileInfo(long id)
22 {
23 FileInfo result = null;
24 Connection conn = null;
25 PreparedStatement ps = null;
26 ResultSet rs = null;
27
28 try
29 {
30 conn = DBUtil.getConnection();
31 ps = conn.prepareStatement("SELECT * FROM "
32 + Constants.TABLE_NAME
33 + " WHERE "
34 + Constants.COLUMN_FILE_ID
35 + " = ?");
36 ps.setLong(1, id);
37 rs = ps.executeQuery();
38
39 if (rs.next())
40 {
41 result = buildFileInfo(rs);
42 }
43 else
44 {
45 result = null;
46 }
47 }
48 catch (SQLException ex)
49 {
50 throw new DataAccessException(
51 "id = " + id,
52 ex);
53 }
54 finally
55 {
56 DBUtil.close(conn, ps, rs);
57 }
58
59 return result;
60 }
61
62 public FileInfo getFileInfo(String filename)
63 {
64 FileInfo result = null;
65 Connection conn = null;
66 PreparedStatement ps = null;
67 ResultSet rs = null;
68
69 try
70 {
71 conn = DBUtil.getConnection();
72 ps = conn.prepareStatement("SELECT * FROM "
73 + Constants.TABLE_NAME
74 + " WHERE "
75 + Constants.COLUMN_FILENAME
76 + " = ?");
77 ps.setString(1, filename);
78 rs = ps.executeQuery();
79
80 if (rs.next())
81 {
82 result = buildFileInfo(rs);
83 }
84 else
85 {
86 result = null;
87 }
88 }
89 catch (SQLException ex)
90 {
91 throw new DataAccessException(
92 "filename = " + filename,
93 ex);
94 }
95 finally
96 {
97 DBUtil.close(conn, ps, rs);
98 }
99
100 return result;
101 }
102
103 public byte[] getFileData(long id)
104 {
105 ByteArrayOutputStream baos = new ByteArrayOutputStream();
106 getFileData(id, baos);
107 return baos.toByteArray();
108 }
109
110 public void getFileData(long id, OutputStream out)
111 {
112 Connection conn = null;
113 PreparedStatement ps = null;
114 ResultSet rs = null;
115
116 try
117 {
118 conn = DBUtil.getConnection();
119 ps = conn.prepareStatement("SELECT * FROM "
120 + Constants.TABLE_NAME
121 + " WHERE "
122 + Constants.COLUMN_FILE_ID
123 + " = ?");
124 ps.setLong(1, id);
125 rs = ps.executeQuery();
126
127 if (rs.next())
128 {
129 Blob b = rs.getBlob(Constants.COLUMN_FILEDATA);
130 InputStream in = b.getBinaryStream();
131 byte[] buffer = new byte[32768];
132 int n = 0;
133 while ( ( n = in.read(buffer)) != -1)
134 {
135 out.write(buffer, 0, n);
136 }
137 in.close();
138 }
139 else
140 {
141 throw new DataAccessException("not found: id=" + id);
142 }
143 }
144 catch (java.io.IOException ex)
145 {
146 throw new DataAccessException(
147 "id = " + id,
148 ex);
149 }
150 catch (SQLException ex)
151 {
152 throw new DataAccessException(
153 "id = " + id,
154 ex);
155 }
156 finally
157 {
158 DBUtil.close(conn, ps, rs);
159 }
160 }
161
162 private static FileInfo buildFileInfo(ResultSet rs) throws SQLException
163 {
164 FileInfo info = new FileInfo();
165 info.setFilename(rs.getString(Constants.COLUMN_FILENAME));
166 info.setFileSize(rs.getLong(Constants.COLUMN_FILESIZE));
167 info.setId(rs.getLong(Constants.COLUMN_FILE_ID));
168 return info;
169 }
170
171 public java.util.List getAllFiles()
172 {
173 List result = new ArrayList();
174
175 Connection conn = null;
176 PreparedStatement ps = null;
177 ResultSet rs = null;
178
179 try
180 {
181 conn = DBUtil.getConnection();
182 ps = conn.prepareStatement("SELECT * FROM "
183 + Constants.TABLE_NAME
184 + " ORDER BY "
185 + Constants.COLUMN_FILENAME);
186 rs = ps.executeQuery();
187
188 while (rs.next())
189 {
190 FileInfo info = buildFileInfo(rs);
191 result.add(info);
192 }
193 }
194 catch (SQLException ex)
195 {
196 throw new DataAccessException(ex);
197 }
198 finally
199 {
200 DBUtil.close(conn, ps, rs);
201 }
202
203 return result;
204 }
205
206 public long saveFile(String filename,
207 InputStream input,
208 int filesize)
209 {
210 long id = -1;
211
212 FileInfo info = getFileInfo(filename);
213
214 if (info == null)
215 {
216 id = insertNewFile(filename, input, filesize);
217 }
218 else
219 {
220 overwriteFile(info.getId(), input, filesize);
221 id = info.getId();
222 }
223 return id;
224 }
225
226 protected long insertNewFile(String filename,
227 InputStream input,
228 int filesize)
229 {
230 Connection conn = null;
231 PreparedStatement ps = null;
232 ResultSet rs = null;
233 OutputStream out = null;
234
235 long id = -1;
236
237 try
238 {
239 conn = DBUtil.getConnection();
240
241 ps = conn.prepareStatement("INSERT INTO "
242 + Constants.TABLE_NAME
243 + " ( "
244 + Constants.COLUMN_FILENAME
245 + ", "
246 + Constants.COLUMN_FILESIZE
247 + ", "
248 + Constants.COLUMN_FILEDATA
249 + " ) VALUES ( ?, ?, ?) ",
250 Statement.RETURN_GENERATED_KEYS);
251 ps.setString(1, filename);
252 ps.setInt(2, filesize);
253 ps.setBinaryStream(3, input, filesize);
254 ps.execute();
255
256 rs = ps.getGeneratedKeys();
257 rs.next();
258 id = rs.getLong(1);
259
260 }
261 catch (SQLException ex)
262 {
263 throw new DataAccessException(ex);
264 }
265 finally
266 {
267 try
268 {
269 DBUtil.close(conn, ps, rs);
270 }
271 finally
272 {
273 if (out != null)
274 {
275 try
276 {
277 out.close();
278 }
279 catch (java.io.IOException ignored)
280 {
281
282 }
283 }
284 }
285 }
286
287 return id;
288 }
289
290 public boolean fileExists(final String filename)
291 {
292 FileInfo info = getFileInfo(filename);
293
294 if (info == null)
295 {
296 return false;
297 }
298 else
299 {
300 return true;
301 }
302 }
303
304 public void overwriteFile(
305 final long id,
306 final InputStream input,
307 final int filesize)
308 {
309 Connection conn = null;
310 PreparedStatement ps = null;
311 ResultSet rs = null;
312 OutputStream out = null;
313
314 try
315 {
316 conn = DBUtil.getConnection();
317
318 ps = conn.prepareStatement("UPDATE "
319 + Constants.TABLE_NAME
320 + " SET "
321 + Constants.COLUMN_FILESIZE
322 + " = ?, "
323 + Constants.COLUMN_FILEDATA
324 + "= ? WHERE "
325 + Constants.COLUMN_FILE_ID
326 + " = ?");
327 ps.setInt(1, filesize);
328 ps.setBinaryStream(2, input, filesize);
329 ps.setLong(3, id);
330 int rowCount = ps.executeUpdate();
331 }
332 catch (SQLException ex)
333 {
334 throw new DataAccessException(ex);
335 }
336 finally
337 {
338 try
339 {
340 DBUtil.close(conn, ps, rs);
341 }
342 finally
343 {
344 if (out != null)
345 {
346 try
347 {
348 out.close();
349 }
350 catch (java.io.IOException ignored)
351 {
352
353 }
354 }
355 }
356 }
357
358 }
359
360 public boolean deleteFile(long id)
361 {
362 boolean result = false;
363
364 Connection conn = null;
365 PreparedStatement ps = null;
366 ResultSet rs = null;
367
368 try
369 {
370 conn = DBUtil.getConnection();
371 ps = conn.prepareStatement("DELETE FROM "
372 + Constants.TABLE_NAME
373 + " WHERE "
374 + Constants.COLUMN_FILE_ID
375 + " = ?");
376 ps.setLong(1, id);
377 int rowCount = ps.executeUpdate();
378 if (rowCount > 0)
379 {
380 result = true;
381 }
382 else
383 {
384 result = false;
385 }
386 }
387 catch (SQLException ex)
388 {
389 throw new DataAccessException(
390 "error while deleting id = " + id,
391 ex);
392 }
393 finally
394 {
395 DBUtil.close(conn, ps, rs);
396 }
397
398 return result;
399 }
400
401 }