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

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

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

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="41594">上一篇：求一SQL优化解决思路 </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/43117' title=' Mysql数据库汉字乱码有关问题 ' target="_blank"> Mysql数据库汉字乱码有关问题 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/42149' title=' mysql 中怎样实现这样需求的存储过程,该如何处理 ' target="_blank"> mysql 中怎样实现这样需求的存储过程,该如何处理 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/42077' title=' 如何去除多余的字段 ' target="_blank"> 如何去除多余的字段 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/39045' title=' 【转】MySQL雅量数据分页Limit优化 ' target="_blank"> 【转】MySQL雅量数据分页Limit优化 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/36013' title=' mysql存储过程执行追踪 ' target="_blank"> mysql存储过程执行追踪 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/35739' title=' [MySQL View]最有意义的视图view优化过程，从30分钟到0.08秒 ' target="_blank"> [MySQL View]最有意义的视图view优化过程，从30分钟到0.08秒 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/39654' title=' 请教mysql可以像一个dll这样被引用吗 ' target="_blank"> 请教mysql可以像一个dll这样被引用吗 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/38775' title=' PHP中mysql跟mysqli的区别 ' target="_blank"> PHP中mysql跟mysqli的区别 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/43936' title=' SHOW PROCESSLIST只需要死锁的部分信息,该怎么处理 ' target="_blank"> SHOW PROCESSLIST只需要死锁的部分信息,该怎么处理 </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/host/?aiyiweb" target="_blank"><img class="img-responsive" src="/images/TdPageAd/hostTest15dayCdn.png" alt="香港全能空间免费试用15天，送网站加速30M" /></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/42758 title=' MySQL里，存储过程的结果集，如何导入一个表内 ' target="_blank"> MySQL里，存储过程的结果集，如何导入一个表内 </a></li> <li class="articleAbout"><a href=/mysql/40562 title=' MySQL:联结索引与Where子句的优化 ' target="_blank"> MySQL:联结索引与Where子句的优化 </a></li> <li class="articleAbout"><a href=/mysql/44248 title=' mysql insert数据解决办法 ' target="_blank"> mysql insert数据解决办法 </a></li> <li class="articleAbout"><a href=/mysql/36335 title=' mysql 交互式模式记录操作 ' target="_blank"> mysql 交互式模式记录操作 </a></li> <li class="articleAbout"><a href=/mysql/40994 title=' mysql /*50001 诠释解释 ' target="_blank"> mysql /*50001 诠释解释 </a></li> <li class="articleAbout"><a href=/mysql/37195 title=' 惯用mysql ' target="_blank"> 惯用mysql </a></li> <li class="articleAbout"><a href=/mysql/35736 title=' MySQL5.1新增Event Scheduler效能【实现定时性的作业】 ' target="_blank"> MySQL5.1新增Event Scheduler效能【实现定时性的作业】 </a></li> <li class="articleAbout"><a href=/mysql/43759 title=' SQL语句效率有关问题 ' target="_blank"> SQL语句效率有关问题 </a></li> <li class="articleAbout"><a href=/mysql/38840 title=' mysql 像Oracle同样解决方案 ' target="_blank"> mysql 像Oracle同样解决方案 </a></li> <li class="articleAbout"><a href=/mysql/39719 title=' pgsql-批处理,该如何解决 ' target="_blank"> pgsql-批处理,该如何解决 </a></li> <li class="articleAbout"><a href=/mysql/43033 title=' mysql 小疑点 ' target="_blank"> mysql 小疑点 </a></li> <li class="articleAbout"><a href=/mysql/36218 title=' mysql 用户新建、受权、删除、密码修改 ' target="_blank"> mysql 用户新建、受权、删除、密码修改 </a></li> <li class="articleAbout"><a href=/mysql/40013 title=' MySQL 性能提醒 ' target="_blank"> MySQL 性能提醒 </a></li> <li class="articleAbout"><a href=/mysql/39837 title=' MySql中的几种资料简单说明 ' target="_blank"> MySql中的几种资料简单说明 </a></li> <li class="articleAbout"><a href=/mysql/42193 title=' update 为什么不修改数据了？大家看看解决方案 ' target="_blank"> update 为什么不修改数据了？大家看看解决方案 </a></li> <li class="articleAbout"><a href=/mysql/43822 title=' ER图和EER图,该怎么处理 ' target="_blank"> ER图和EER图,该怎么处理 </a></li> <li class="articleAbout"><a href=/mysql/37579 title=' Hibernate與MySQL開發中亂碼解決议案 ' target="_blank"> Hibernate與MySQL開發中亂碼解決议案 </a></li> <li class="articleAbout"><a href=/mysql/36473 title=' mysql 中的 charset跟 collate ' target="_blank"> mysql 中的 charset跟 collate </a></li> <li class="articleAbout"><a href=/mysql/42394 title=' mysql用sql语句插入中文乱码有关问题 ' target="_blank"> mysql用sql语句插入中文乱码有关问题 </a></li> <li class="articleAbout"><a href=/mysql/41658 title=' 多线程数据库加锁容易锁等待有关问题 ' target="_blank"> 多线程数据库加锁容易锁等待有关问题 </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-2024 爱易网页当前在线：1136人　网站在7时6分38秒内访问总人数：109970人当前 17.65% 　<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>