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

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

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

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/38658' title=' 高分MySql Update语句 ' target="_blank"> 高分MySql Update语句 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/37488' title=' mysql汉语言全文索引 ' target="_blank"> mysql汉语言全文索引 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/42458' title=' mysql 存储过程　怎么定义变量　请问 ' target="_blank"> mysql 存储过程　怎么定义变量　请问 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/43317' title=' 请问一个分类统计的有关问题.不用存储过程! ' target="_blank"> 请问一个分类统计的有关问题.不用存储过程! </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/43586' title=' SQL排序！太怪异了！该如何处理 ' target="_blank"> SQL排序！太怪异了！该如何处理 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/41812' title=' mysql 两个外键级联删除的有关问题 ' target="_blank"> mysql 两个外键级联删除的有关问题 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/40173' title=' mysql导入大的文件出现2006异常 ' target="_blank"> mysql导入大的文件出现2006异常 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/37888' title=' 如其数据库中已经有一张表了就要删除这张表 ' target="_blank"> 如其数据库中已经有一张表了就要删除这张表 </a></li></div> <div class="col-md-6 col-xs-12"> <li class="articleAbout"><a href='/mysql/36136' title=' ubuntu 12.04停mysql修改字符集 ' target="_blank"> ubuntu 12.04停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/make/?aiyiweb" target="_blank"><img class="img-responsive" src="/images/TdPageAd/oneweb.png" alt="网站建设一条龙做网站网站制作网站定制企业网站设计特价" /></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/35956 title=' mysql 起动，停止 ' target="_blank"> mysql 起动，停止 </a></li> <li class="articleAbout"><a href=/mysql/37287 title=' zerofill在mysql字段定义中的施用以及auto_increment的注意事项 ' target="_blank"> zerofill在mysql字段定义中的施用以及auto_increment的注意事项 </a></li> <li class="articleAbout"><a href=/mysql/38471 title=' MySQL删除更新数据时报1175异常 ' target="_blank"> MySQL删除更新数据时报1175异常 </a></li> <li class="articleAbout"><a href=/mysql/38529 title=' 怎么取出日期中时间的这个数字 ' target="_blank"> 怎么取出日期中时间的这个数字 </a></li> <li class="articleAbout"><a href=/mysql/38615 title=' MySQL5.x处置中文全解 ' target="_blank"> MySQL5.x处置中文全解 </a></li> <li class="articleAbout"><a href=/mysql/38897 title=' mysql 目录及优化 ' target="_blank"> mysql 目录及优化 </a></li> <li class="articleAbout"><a href=/mysql/39084 title=' （转）MySQL字符集 GBK、GB2312、UTF8区别解决 MYSQL中文乱码有关问题 ' target="_blank"> （转）MySQL字符集 GBK、GB2312、UTF8区别解决 MYSQL中文乱码有关问题 </a></li> <li class="articleAbout"><a href=/mysql/40346 title=' mysql乱码处置 ' target="_blank"> mysql乱码处置 </a></li> <li class="articleAbout"><a href=/mysql/40955 title=' [MySQL]MySQL的ALTER话语 ' target="_blank"> [MySQL]MySQL的ALTER话语 </a></li> <li class="articleAbout"><a href=/mysql/38371 title=' 新近学习plsql 找到了mysql的存储过程和函数看看了 ' target="_blank"> 新近学习plsql 找到了mysql的存储过程和函数看看了 </a></li> <li class="articleAbout"><a href=/mysql/37211 title=' mysql配备小全 ' target="_blank"> mysql配备小全 </a></li> <li class="articleAbout"><a href=/mysql/42407 title=' "删除无用的上传图片"应该是个什么逻辑 ' target="_blank"> "删除无用的上传图片"应该是个什么逻辑 </a></li> <li class="articleAbout"><a href=/mysql/41929 title=' MySQL prepare语句execute后结果的判断。解决办法 ' target="_blank"> MySQL prepare语句execute后结果的判断。解决办法 </a></li> <li class="articleAbout"><a href=/mysql/42313 title=' 大神们，帮小弟我看看 ' target="_blank"> 大神们，帮小弟我看看 </a></li> <li class="articleAbout"><a href=/mysql/39209 title=' 安装mysql出现The security setting could not be applied有关问题 ' target="_blank"> 安装mysql出现The security setting could not be applied有关问题 </a></li> <li class="articleAbout"><a href=/mysql/41079 title=' mysql proxy V0.8.2 测试有关问题记录 ' target="_blank"> mysql proxy V0.8.2 测试有关问题记录 </a></li> <li class="articleAbout"><a href=/mysql/43569 title=' 触发器有关问题,上次提问已石沉大海 ' target="_blank"> 触发器有关问题,上次提问已石沉大海 </a></li> <li class="articleAbout"><a href=/mysql/43851 title=' mysql-server和mysql-client和mysql-develop和mysql-share解决方法 ' target="_blank"> mysql-server和mysql-client和mysql-develop和mysql-share解决方法 </a></li> <li class="articleAbout"><a href=/mysql/40193 title=' Mysql平添多个索引方法 ' target="_blank"> Mysql平添多个索引方法 </a></li> <li class="articleAbout"><a href=/mysql/43389 title=' mysql 存储过程默认值解决办法 ' target="_blank"> mysql 存储过程默认值解决办法 </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 爱易网页当前在线：1364人　网站在18时16分50秒内访问总人数：233472人当前 39.12% 　<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>