MySQL blob字段存儲圖片操作示例

表結構：

1. create table view(id int unsigned NOT NULL AUTO_INCREMENT, catid int,title varchar(256),picture MEDIUMBLOB, content TEXT,PRIMARY KEY (id));

1. import java.awt.Image;
2. import java.io.*;
3. import java.nio.ByteBuffer;
4. import java.sql.PreparedStatement;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.util.logging.Level;
8. import java.util.logging.Logger;
9. import javax.swing.ImageIcon;
10. import org.bean.View;
12. /**
13. *
14. * @author weijian.zhongwj
15. */
16. public class ViewPointDao {
18. public static View getView(Integer catId) {
19. View view = new View();
20. try {
21. String sql2 = "SELECT title, content, picture FROM view where catid=? limit 1";
22. PreparedStatement stmt2 = BaseDaoFactory.getInstance().prepareStatement(sql2);
23. stmt2.setInt(1, catId);
24. ResultSet resultSet = stmt2.executeQuery();
25. while (resultSet.next()) {
26. String name = resultSet.getString(1);
27. String description = resultSet.getString(2);
29. ByteBuffer bb = ByteBuffer.allocate(1024 * 1024);
30. byte[] buffer = new byte[1];
31. InputStream is = resultSet.getBinaryStream(3);
33. while (is != null && is.read(buffer) > 0) {
34. bb.put(buffer);
35. }
36. ImageIcon icon = new ImageIcon(bb.array());
37. view.setImage(icon.getImage());
38. view.setTitle(name);
39. view.setContent(description);
40. return view;
41. }
42. } catch (IOException ex) {
43. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
44. } catch (SQLException ex) {
45. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
46. }
47. return null;
48. }
50. public static boolean addView(View view) {
51. FileInputStream fis = null;
52. try {
54. if (exit(view.getCatId())) {
55. return update(view);
56. }
58. String sql = "INSERT INTO view (title, content, catid, picture) VALUES (?, ?, ?, ?)";
59. PreparedStatement stmt = BaseDaoFactory.getInstance().prepareStatement(sql);
60. stmt.setString(1, view.getTitle());
61. stmt.setString(2, view.getContent());
62. stmt.setInt(3, view.getCatId());
64. if (view.getImageFile() != null) {
65. File image = new File(view.getImageFile());
66. fis = new FileInputStream(image);
67. //image.length(),返回文件的大小
68. stmt.setBinaryStream(4, fis, (int) image.length());
69. } else {
70. stmt.setBinaryStream(4, null, 0);
71. }
72. int count = stmt.executeUpdate();
73. if (count > 0) {
74. return true;
75. } else {
76. return false;
77. }
78. } catch (IOException ex) {
79. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
80. } catch (SQLException ex) {
81. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
82. } finally {
83. try {
84. if (fis != null) {
85. fis.close();
86. }
87. } catch (IOException ex) {
88. }
89. }
90. return false;
91. }
93. public static boolean update(View view) {
94. FileInputStream fis = null;
95. try {
96. String sql = "update view set title= ? ,content= ? " + (view.getImageFile() != null ? (",picture= ? ") : " ") + "where catid= ? ";
97. PreparedStatement stmt = BaseDaoFactory.getInstance().prepareStatement(sql);
98. stmt.setString(1, view.getTitle());
99. stmt.setString(2, view.getContent());
101. if (view.getImageFile() != null) {
102. stmt.setInt(4, view.getCatId());
103. File image = new File(view.getImageFile());
104. fis = new FileInputStream(image);
105. //image.length(),返回文件的大小
106. stmt.setBinaryStream(3, fis, (int) image.length());
107. } else {
108. stmt.setInt(3, view.getCatId());
109. }
111. int count = stmt.executeUpdate();
112. if (count > 0) {
113. return true;
114. } else {
115. return false;
116. }
117. } catch (IOException ex) {
118. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
119. } catch (SQLException ex) {
120. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
121. } finally {
122. try {
123. if (fis != null) {
124. fis.close();
125. }
126. } catch (IOException ex) {
127. }
128. }
129. return false;
130. }
132. public static boolean exit(Integer catId) {
133. try {
134. String sql2 = "SELECT title, content, picture FROM view where catid=? limit 1";
135. PreparedStatement stmt2 = BaseDaoFactory.getInstance().prepareStatement(sql2);
136. stmt2.setInt(1, catId);
137. ResultSet resultSet = stmt2.executeQuery();
138. while (resultSet.next()) {
139. return true;
140. }
141. } catch (SQLException ex) {
142. Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
143. }
144. return false;
145. }
146. }

1. import java.awt.Image;
3. public class View {
5. /**
6. * 景點標題
7. */
8. private String title;
9. /**
10. * 景點內容
11. */
12. private String content;
13. /**
14. * 景點圖片
15. */
16. private Image image;
18. /**
19. * 景點圖片上傳路徑
20. */
21. private String imageFile;
23. /**
24. * 分類id
25. */
26. private int catId;
28. public int getCatId() {
29. return catId;
30. }
32. public void setCatId(int catId) {
33. this.catId = catId;
34. }
36. public String getContent() {
37. return content;
38. }
40. public void setContent(String content) {
41. this.content = content;
42. }
44. public Image getImage() {
45. return image;
46. }
48. public void setImage(Image image) {
49. this.image = image;
50. }
52. public String getTitle() {
53. return title;
54. }
56. public void setTitle(String title) {
57. this.title = title;
58. }
60. public String getImageFile() {
61. return imageFile;
62. }
64. public void setImageFile(String imageFile) {
65. this.imageFile = imageFile;
66. }
67. }