mysql blob 字段_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;

}

}

0

顶

0

踩

分享到：

2012-06-16 14:46

浏览 1215

评论

1 楼

maco87

2012-09-22

sql语句字段的顺序与建表的顺序不一至