?
开发过程中,有时候会用到用mysql存储image图片,今天螃蟹就详细分析一下这一过程。
需求描述
1、将指定目录下或指定的路径文件存储到mylsq数据库中
2、读该取数据库中的数据,将存储的图片文件读取并存储到指定目录下
需求分析
1、图片的内容以二进制流的形成存到mysql的blob字段中
2、获取输出流,生成图片,并且要检查从数据库读取的图片是否可以正常使用
数据库表设计及数据初始化
如下图,我们创建一张表itxxzImg,包含以下字段信息

然后插入测试数据,如以下代码,需要注意的就是存储二进制流需要用ps.setBinaryStream(...)方法,并且第三个参数为int类型,而file.length()为long类型,需求进行一下转换,这个在高版本的mysql驱动中应该得到了解决。
?
- public?static?void?insert()?{??
- ????????try?{??
- ????????????for?(int?i?=?0;?i?<?filePath.length;?i++)?{??
- ????????????????File?file?=?new?File(filePath[i]);??
- ????????????????if?(!file.exists())?{??
- ????????????????????continue;??
- ????????????????}??
- ????????????????ps?=?getConnection().prepareStatement(INSERT_SQL);??
- ????????????????ps.setInt(1,?0);??
- ????????????????ps.setString(2,?file.getName());??
- ????????????????ps.setBinaryStream(3,?new?FileInputStream(file),?(int)?file.length());??
- ????????????????ps.setLong(4,?file.length());??
- ????????????????ps.executeUpdate();??
- ????????????}??
- ??
- ????????}?catch?(SQLException?e)?{??
- ????????????e.printStackTrace();??
- ????????}?catch?(FileNotFoundException?e)?{??
- ????????????e.printStackTrace();??
- ????????}??
- ????}?
初始化后,数据如下:

剩余的工作就比较简单了,读取数据库中的content内容,IO流读取即可
完整代码
?
?
- package?com.itxxz.db;??
- ??
- import?java.io.File;??
- import?java.io.FileInputStream;??
- import?java.io.FileNotFoundException;??
- import?java.io.FileOutputStream;??
- import?java.io.IOException;??
- import?java.io.InputStream;??
- import?java.sql.Connection;??
- import?java.sql.DriverManager;??
- import?java.sql.PreparedStatement;??
- import?java.sql.ResultSet;??
- import?java.sql.SQLException;??
- import?java.sql.Statement;??
- ??
- ?
- ?
- ?
- ?
- ?
- ?
- ??
- public?class?ItxxzTest?{??
- ??????
- ??????
- ????private?static?final?String?SELECT_SQL?=?"SELECT?ID?,NAME,CONTENT,FILE_SIZE?FROM?ITXXZIMG";??
- ??????
- ????private?static?final?String?INSERT_SQL?=?"INSERT?INTO?ITXXZIMG(?ID?,NAME,CONTENT,FILE_SIZE)?VALUES(?,?,?,?)";??
- ??
- ??????
- ??????
- ????private?static?final?String?driver?=?"com.mysql.jdbc.Driver";??
- ??????
- ????private?static?final?String?userName?=?"root";??
- ??????
- ????private?static?final?String?passwrod?=?"root";??
- ??????
- ????private?static?final?String?url?=?"jdbc:mysql://localhost:3306/itxxz";??
- ??
- ??????
- ????private?static?Connection?conn?=?null;??
- ????private?static?PreparedStatement?ps?=?null;??
- ????private?static?ResultSet?rs?=?null;??
- ????private?static?Statement?stmt?=?null;??
- ??
- ??????
- ????private?static?final?String[]?filePath?=?{?"E:/itxxz/logo.png",??
- ????????????"E:/itxxz/banner.jpg",?"E:/itxxz/ad.png"?};??
- ??????
- ????private?static?final?byte[]?Buffer?=?new?byte[1024];??
- ??????
- ????private?static?final?String?filedir?=?"E:/itxxz/img/";??
- ??????
- ????private?static?FileOutputStream?fos;??
- ??
- ????public?static?void?main(String[]?args)?{??
- ??????????
- ????????selectAndExport();??
- ????}??
- ??
- ?????
- ?
- ??
- ????public?static?Connection?getConnection()?{??
- ????????try?{??
- ????????????Class.forName(driver);??
- ????????????conn?=?DriverManager.getConnection(url,?userName,?passwrod);??
- ????????}?catch?(ClassNotFoundException?e)?{??
- ????????????System.err.println(e.getMessage());??
- ????????}?catch?(SQLException?e)?{??
- ????????????System.err.println(e.getMessage());??
- ????????}??
- ????????return?conn;??
- ??
- ????}??
- ??
- ?????
- ?
- ??
- ????public?static?void?insert()?{??
- ????????try?{??
- ????????????for?(int?i?=?0;?i?<?filePath.length;?i++)?{??
- ????????????????File?file?=?new?File(filePath[i]);??
- ????????????????if?(!file.exists())?{??
- ????????????????????continue;??
- ????????????????}??
- ????????????????ps?=?getConnection().prepareStatement(INSERT_SQL);??
- ????????????????ps.setInt(1,?0);??
- ????????????????ps.setString(2,?file.getName());??
- ????????????????ps.setBinaryStream(3,?new?FileInputStream(file),?(int)?file.length());??
- ????????????????ps.setLong(4,?file.length());??
- ????????????????ps.executeUpdate();??
- ????????????}??
- ??
- ????????}?catch?(SQLException?e)?{??
- ????????????e.printStackTrace();??
- ????????}?catch?(FileNotFoundException?e)?{??
- ????????????e.printStackTrace();??
- ????????}??
- ????}??
- ??
- ?????
- ?
- ??
- ????public?static?void?selectAndExport()?{??
- ????????try?{??
- ????????????PreparedStatement?pst?=?getConnection().prepareStatement(SELECT_SQL);??
- ????????????rs?=?pst.executeQuery();??
- ??????????????
- ????????????while?(rs.next())?{??
- ????????????????String?fileName?=?rs.getString("name");??
- ????????????????InputStream?is?=?rs.getBinaryStream("content");??
- ????????????????exportImg(is,?fileName);??
- ????????????}??
- ??
- ????????}?catch?(SQLException?e)?{??
- ????????????e.printStackTrace();??
- ????????}??
- ????}??
- ??
- ?????
- ?
- ??
- ????public?static?void?close()?{??
- ????????try?{??
- ??????????????
- ????????????if?(rs?!=?null)?{??
- ????????????????rs.close();??
- ????????????}??
- ????????????if?(ps?!=?null)?{??
- ????????????????ps.close();??
- ????????????}??
- ????????????if?(stmt?!=?null)?{??
- ????????????????stmt.close();??
- ????????????}??
- ????????????if?(conn?!=?null)?{??
- ????????????????conn.close();??
- ????????????}??
- ????????}?catch?(SQLException?e)?{??
- ????????????System.err.println(e.getErrorCode());??
- ????????}??
- ????}??
- ??
- ?????
- ?
- ??
- ????public?static?void?exportImg(InputStream?is,?String?fileName)?{??
- ??????????
- ????????File?dir?=?new?File(filedir);??
- ????????if?(!dir.exists())?{??
- ????????????dir.mkdirs();??
- ????????}??
- ????????try?{??
- ????????????File?file?=?new?File(filedir+fileName);??
- ????????????if(!file.exists()){??
- ??????????????????
- ????????????????file.createNewFile();??
- ????????????}??
- ????????????fos?=?new?FileOutputStream(file);??
- ????????????int?size?=?0;??
- ??
- ????????????while?((size?=?is.read(Buffer))?!=?-1)?{??
- ????????????????fos.write(Buffer,?0,?size);??
- ????????????}??
- ????????}?catch?(IOException?e)?{??
- ????????????e.printStackTrace();??
- ????????}??
- ????}??
- } ?
?
mysql5存储图片和读取
原文:http://itxxz.iteye.com/blog/2175913