mysql blob字段储存图片操作示例-MySQL教程-爱易网页

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

日期：2014-05-16　浏览次数：20930 次

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 (vi <div class="clearfix"></div> </div> <nav aria-label="..."> <ul class="pager"> <li class="previous"><a href="44373">上一篇：MYSQL锁处理</a></li> <li class="next"><a href="41592">下一篇： MySQL经验10-1-事务 </a></li> </ul> </nav> <div class="alert alert-warning" role="alert"> <strong>免责声明：</strong> 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。 </div> <div class="panel panel-default"> <div class="panel-heading"> <h3 class="panel-title">相关资料<span><a target="_blank" class="pull-right" href="/mysql/">更多></a></span></h3> </div> <div class="panel-body row"> <ol> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/43950' title=' 问一sql语句有关问题 ' target="_blank"> 问一sql语句有关问题 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/38890' title=' MySQL5的错误处理 ' target="_blank"> MySQL5的错误处理 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/37616' title=' 剔除了mysql的服务 ' target="_blank"> 剔除了mysql的服务 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/36158' title=' MySQL目录缺点&使用详细注意事项 ' target="_blank"> MySQL目录缺点&使用详细注意事项 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/37245' title=' MySQL ('root'@'%') does not exist的有关问题 ' target="_blank"> MySQL ('root'@'%') does not exist的有关问题 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/35999' title=' struts2+mysql乱码有关问题 ssi+mysql乱码 ' target="_blank"> struts2+mysql乱码有关问题 ssi+mysql乱码 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/38848' title=' MySQL 5.1 安装过程中报apply security setting异常的解决方法收 ' target="_blank"> MySQL 5.1 安装过程中报apply security setting异常的解决方法收 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/37277' title=' oracle跟mysql 积累 ' target="_blank"> oracle跟mysql 积累 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/36446' title=' mysql 乱码有关问题 ' target="_blank"> mysql 乱码有关问题 </a></li></div> </ol> </div> </div> </div> <div class="col-lg-4 hidden-sm hidden-xs">  <a id="AdPageRight1_AdPageRight" href="http://www.tdyun.com/cloud/?aiyiweb" target="_blank"><img class="img-responsive" src="/images/TdPageAd/vps2.png" alt="香港云服务器免备案云主机VPS 国内独立IP 独享15M 月付SSD" /></a> <script src='/Scripts/AdContentPage300_1.js' language='javascript'></script> <script src='/Scripts/AdContentPage300_2.js' language='javascript'></script> <script src='/Scripts/AdContentPage300_3.js' language='javascript'></script> <div class="panel panel-default"> <div class="panel-heading"> <h3 class="panel-title">推荐阅读<span><a target="_blank" class="pull-right" href="/mysql/">更多></a></span></h3> </div> <div class="panel-body"> <ol> <li class="articleAbout"><a href=/mysql/37155 title=' mysql null的变换 ' target="_blank"> mysql null的变换 </a></li> <li class="articleAbout"><a href=/mysql/37585 title=' Tomcat6 上 MySQL 5.1 数据源配制 ' target="_blank"> Tomcat6 上 MySQL 5.1 数据源配制 </a></li> <li class="articleAbout"><a href=/mysql/36077 title=' mysql使用存储过程回来多个值 ' target="_blank"> mysql使用存储过程回来多个值 </a></li> <li class="articleAbout"><a href=/mysql/38454 title=' mysql自定义函数有关问题。 ' target="_blank"> mysql自定义函数有关问题。 </a></li> <li class="articleAbout"><a href=/mysql/42577 title=' 请问一个用户不存在的有关问题 ' target="_blank"> 请问一个用户不存在的有关问题 </a></li> <li class="articleAbout"><a href=/mysql/38477 title=' mysql限制表的字段的值在每次插入时只能大于下一条记录的值 ' target="_blank"> mysql限制表的字段的值在每次插入时只能大于下一条记录的值 </a></li> <li class="articleAbout"><a href=/mysql/35989 title=' mysqldump使用经验 ' target="_blank"> mysqldump使用经验 </a></li> <li class="articleAbout"><a href=/mysql/43572 title=' 80分，MySQL中TIMESTAMP类型的数据到底要和什么类型的数据进行比较？该如何解决 ' target="_blank"> 80分，MySQL中TIMESTAMP类型的数据到底要和什么类型的数据进行比较？该如何解决 </a></li> <li class="articleAbout"><a href=/mysql/36158 title=' MySQL目录缺点&使用详细注意事项 ' target="_blank"> MySQL目录缺点&使用详细注意事项 </a></li> <li class="articleAbout"><a href=/mysql/41025 title=' MySQL主从复制配备 ' target="_blank"> MySQL主从复制配备 </a></li> <li class="articleAbout"><a href=/mysql/42783 title=' update 语句解决办法 ' target="_blank"> update 语句解决办法 </a></li> <li class="articleAbout"><a href=/mysql/43797 title=' 向,连接数错误 ' target="_blank"> 向,连接数错误 </a></li> <li class="articleAbout"><a href=/mysql/37533 title=' Hibernate联接Mysql无法自动创建表多种原因分析 ' target="_blank"> Hibernate联接Mysql无法自动创建表多种原因分析 </a></li> <li class="articleAbout"><a href=/mysql/38778 title=' 关于MySQL中的sql。is outside valid range for the datatype integer. 错误 ' target="_blank"> 关于MySQL中的sql。is outside valid range for the datatype integer. 错误 </a></li> <li class="articleAbout"><a href=/mysql/41037 title=' 超过mysql最大连接的异常 ' target="_blank"> 超过mysql最大连接的异常 </a></li> <li class="articleAbout"><a href=/mysql/44250 title=' excel导入到mysql数据库表,该怎么解决 ' target="_blank"> excel导入到mysql数据库表,该怎么解决 </a></li> <li class="articleAbout"><a href=/mysql/42592 title=' MYSQL INNODB死锁有关问题 ' target="_blank"> MYSQL INNODB死锁有关问题 </a></li> <li class="articleAbout"><a href=/mysql/41827 title=' mysql /tmp权限有关问题 ' target="_blank"> mysql /tmp权限有关问题 </a></li> <li class="articleAbout"><a href=/mysql/39107 title=' 【学问积累】MySQL与Oracle 基本sql语句的差异（一） ' target="_blank"> 【学问积累】MySQL与Oracle 基本sql语句的差异（一） </a></li> <li class="articleAbout"><a href=/mysql/40140 title=' 关于mysql跟declare ' target="_blank"> 关于mysql跟declare </a></li> </ol> </div> </div> </div> </div> <div class="clearfix"></div> <footer class=" footer navbar-bottom"> <em>友情链接：</em> <a href="http://www.aiyiweb.com/" target="_blank">爱易网 </a> <a href="http://www.aiyiweb.com/" target="_blank">云虚拟主机技术 </a> <a href="http://www.aiyiweb.com/" target="_blank">云服务器技术 </a> <a href="http://www.aiyiweb.com/" target="_blank">程序设计技术 </a> <a href="http://www.aiyiweb.com/" target="_blank">开发网站 </a> <a href="http://www.aiyiweb.com/" target="_blank">APP开发教程 </a> <br /> <script type="text/javascript">(function(){document.write(unescape('%3Cdiv id="bdcs"%3E%3C/div%3E'));var bdcs = document.createElement('script');bdcs.type = 'text/javascript';bdcs.async = true;bdcs.src = 'http://znsv.baidu.com/customer_search/api/js?sid=15239353030108964139' + '&plate_url=' + encodeURIComponent(window.location.href) + '&t=' + Math.ceil(new Date()/3600000);var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(bdcs, s);})();</script> Copyright © 2013-2025 爱易网页当前在线：316人　网站在12时54分22秒内访问总人数：136605人当前 31.02% 　<a href="https://beian.miit.gov.cn" target="_blank">粤ICP备18100884号-2 </a> <script charset="UTF-8" id="LA_COLLECT" src="//sdk.51.la/js-sdk-pro.min.js"></script> <script>LA.init({ id: "JszEmKrtpbU59dY5", ck: "JszEmKrtpbU59dY5" })</script> </footer> </div> </body> </html>