mysql blob字段存储图片操作示例

史蒂夫和斯凯

　　

表结构：

　　


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

java类操作：

　　


import java.awt.Image;
import java.io.*;
import java.nio.ByteBuffer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.ImageIcon;
import org.bean.View;
/**
*
* @author weijian.zhongwj
*/
public class ViewPointDao {
public static View getView(Integer catId) {
View view = new View();
try {
String sql2 = "SELECT title, content, picture FROM view where catid=? limit 1";
PreparedStatement stmt2 = BaseDaoFactory.getInstance().prepareStatement(sql2);
stmt2.setInt(1, catId);
ResultSet resultSet = stmt2.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
String description = resultSet.getString(2);
ByteBuffer bb = ByteBuffer.allocate(1024 * 1024);
byte[] buffer = new byte[1];
InputStream is = resultSet.getBinaryStream(3);
while (is != null && is.read(buffer) > 0) {
bb.put(buffer);
}
ImageIcon icon = new ImageIcon(bb.array());
view.setImage(icon.getImage());
view.setTitle(name);
view.setContent(description);
return view;
}
} catch (IOException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
public static boolean addView(View view) {
FileInputStream fis = null;
try {
if (exit(view.getCatId())) {
return update(view);
}
String sql = "INSERT INTO view (title, content, catid, picture) VALUES (?, ?, ?, ?)";
PreparedStatement stmt = BaseDaoFactory.getInstance().prepareStatement(sql);
stmt.setString(1, view.getTitle());
stmt.setString(2, view.getContent());
stmt.setInt(3, view.getCatId());
if (view.getImageFile() != null) {
File image = new File(view.getImageFile());
fis = new FileInputStream(image);
//image.length(),返回文件的大小
stmt.setBinaryStream(4, fis, (int) image.length());
} else {
stmt.setBinaryStream(4, null, 0);
}
int count = stmt.executeUpdate();
if (count > 0) {
return true;
} else {
return false;
}
} catch (IOException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (fis != null) {
fis.close();
}
} catch (IOException ex) {
}
}
return false;
}
public static boolean update(View view) {
FileInputStream fis = null;
try {
String sql = "update view set title= ? ,content= ? " + (view.getImageFile() != null ? (",picture= ? ") : " ") + "where catid= ? ";
PreparedStatement stmt = BaseDaoFactory.getInstance().prepareStatement(sql);
stmt.setString(1, view.getTitle());
stmt.setString(2, view.getContent());
if (view.getImageFile() != null) {
stmt.setInt(4, view.getCatId());
File image = new File(view.getImageFile());
fis = new FileInputStream(image);
//image.length(),返回文件的大小
stmt.setBinaryStream(3, fis, (int) image.length());
} else {
stmt.setInt(3, view.getCatId());
}
int count = stmt.executeUpdate();
if (count > 0) {
return true;
} else {
return false;
}
} catch (IOException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (fis != null) {
fis.close();
}
} catch (IOException ex) {
}
}
return false;
}
public static boolean exit(Integer catId) {
try {
String sql2 = "SELECT title, content, picture FROM view where catid=? limit 1";
PreparedStatement stmt2 = BaseDaoFactory.getInstance().prepareStatement(sql2);
stmt2.setInt(1, catId);
ResultSet resultSet = stmt2.executeQuery();
while (resultSet.next()) {
return true;
}
} catch (SQLException ex) {
Logger.getLogger(ViewPointDao.class.getName()).log(Level.SEVERE, null, ex);
}
return false;
}
}
　　
　　

bean：

　　import java.awt.Image;


public class View {
/**
* 景点标题
*/
private String title;
/**
* 景点内容
*/
private String content;
/**
* 景点图片
*/
private Image image;
/**
* 景点图片上传路径
*/
private String imageFile;
/**
* 分类id
*/
private int catId;
public int getCatId() {
return catId;
}
public void setCatId(int catId) {
this.catId = catId;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Image getImage() {
return image;
}
public void setImage(Image image) {
this.image = image;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getImageFile() {
return imageFile;
}
public void setImageFile(String imageFile) {
this.imageFile = imageFile;
}
}