SQLSERVER2005读取图片类型字段

下午向数据库SQL SERVER中遇到添加图片类型数据的问题，本以为在界面管理器中可以直接（通过指定路径把图片的数据保存起来）添加图片却不然。网上搜了下找到了相关的较专业的资料，整合解读一下：

两种方法：

      第一就是在数据库中存储图片的路径，然后在程序中根据读取的路径读取图片；这种方法简单、容易使用，但是在图片过多时不好管理。
      第二种就是将图片转换成二进制存储于数据库中，sql server 2005有个image数据类型,专门用于存储图片，Image数据类型存储的是长度不确定的二进制数据，最大长度是2GB。

第二种方法（详情请参阅：详述的过程）

简述过程：

当然首先得有存储图片字段的数据库、表。

如何插入图片：

大致流程：其他图片格式--》image类型的转化--》到MemoryStream流的转换--》到二进制的转换--》插入到数据库。

如何得到图片：

大致流程：选取数据库中的image类型字段--》转化为二进制流--》转化为MemoryStream流--》转化为Image图片格式类型。

其它：如何插入读取一个文件、如何保存数据库中的image字段到一个文件。。
 

 

Connection conn ;
  conn= ConnectionFactory.getConn();
  PreparedStatement ps;
  //建立Statement对象
String name=request.getParameter("picname");
String img=request.getParameter("pic");
//获得所要显示图片的标题、存储路径、内容，并进行中文编码
FileInputStream str=new FileInputStream(img);
//String sql="insert into p(picname,pic) values(?,?)";

String sql="INSERT INTO test_Img(name,img) VALUES(?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1,name);

ps.setBinaryStream(2,str,str.available());
ps.execute();
//将数据存入数据库
out.println("Success,You Have Insert an Image Successfully");

 

/**   
  * Created by IntelliJ IDEA.   
  * User: ljt   
  * Date: 2003-3-31   
  * Time: 18:51:38   
  * To change this template use Options | File Templates.   
  */   
  import oracle.jdbc.driver.OraclePreparedStatement;   
  import oracle.jdbc.driver.OracleResultSet;   
    
  import java.sql.Connection;   
  import java.sql.DriverManager;   
  import java.sql.Statement;   
  import java.sql.Clob;   
    
    
    
  public class TestOpenDoc {   
  public OracleResultSet ors = null; //**这里rs一定要用Oracle提供的   
  public OraclePreparedStatement opst = null; //**PreparedStatement用   
  public Connection conn = null;   
  public Statement stmt = null;   
    
  public TestOpenDoc() {   
  }   
    
  public boolean getConnect() {   
  //这是我的数据库所在   
  String serverName = "prosrv";   
  try {   
  Class.forName("oracle.jdbc.driver.OracleDriver");   
  String url = "jdbc:oracle:thin:@" + serverName + ":1521:BOHDATA";   
  conn = DriverManager.getConnection(url, "appuser", "appuser");   
  }   
  catch (Exception e) {   
  System.out.println(e);   
  return false;   
  }   
  return true;   
  }   
    
  public static void main(String[] args) {   
  TestOpenDoc test = new TestOpenDoc();   
  if (!test.getConnect()) {   
  System.out.println("数据库连结错误");   
  return ;   
  }   
  try{   
    
  test.conn.setAutoCommit(false);   
  byte a[] = null; //**将测试文件test.doc读入此字节数组   
  java.io.FileInputStream fin = null;   
  java.io.FileOutputStream fout = null;   
    
  //Oracle提供的   
  try {   
  java.io.File f1 = new java.io.File("c:/test.doc");   
  java.io.File f2 = new java.io.File("d:/testout.doc"); //**从BLOB读出的信息写   
    
  //入该文 件，和源文件对比测试用   
  fin = new java.io.FileInputStream(f1);   
  fout = new java.io.FileOutputStream(f2);   
    
  int flength = (int) f1.length(); //**读入文件的字节长度   
  System.out.println("file length::" + flength);   
  a = new byte[flength];   
    
  int i = 0;   
  int itotal = 0;   
  //* 将文件读入字节数组   
  for (; itotal < flength; itotal = i + itotal) {   
  i = fin.read(a, itotal, flength - itotal);   
  }   
  fin.close();   
    
  System.out.println("read itotal::" + itotal);   
  //**注意Oracle的 BLOB一定要用EMPTY_BLOB()初始化   
  String mysql =   
  "insert into filelist (FileName,FileSize,FileBody) values (?,?,EMPTY_BLOB())";   
  OraclePreparedStatement opst = (OraclePreparedStatement) test.conn.   
  prepareStatement(mysql);   
  opst.setString(1, "wordtemplate2");   
  opst.setInt(2, flength);   
  opst.executeUpdate();   
  opst.clearParameters();   
  // /**插入其它数据后，定位BLOB字段   
  mysql = "select filebody from filelist where filename=?";   
  opst = (OraclePreparedStatement) test.conn.prepareStatement(mysql);   
  opst.setString(1, "wordtemplate2");   
  OracleResultSet ors = (OracleResultSet) opst.executeQuery();   
    
  if (ors.next()) {   
  oracle.sql.BLOB blob = ors.getBLOB(1); //**得到BLOB字段   
  int j = blob.putBytes(1, a); //**将字节数组写入BLOB字段   
  System.out.println("j:" + j);   
  test.conn.commit();   
  ors.close();   
  Clob clob;   
  clob = ors.getClob("");   
  String str;   
  str = clob.toString();   
  str = clob.getSubString(0L,(int)clob.length());   
  System.out.println(str);   
  }   
    
  System.out.println("insert into ok");   
    
  byte b[] = null; //**保存从BLOB读出的字节   
  opst.clearParameters();   
  mysql = "select filebody from filelist where filename=?";   
  opst = (OraclePreparedStatement) test.conn.   
  prepareStatement(mysql);   
  opst.setString(1, "wordtemplate2");   
  ors = (OracleResultSet) opst.executeQuery();   
  if (ors.next()) {   
  oracle.sql.BLOB blob2 = ors.getBLOB(1);   
    
  System.out.println("blob2 length:" + blob2.length());   
  b = blob2.getBytes(1, flength); //**从BLOB取出字节流数据   
  System.out.println("b length::" + b.length);   
  test.conn.commit();   
  }   
  ors.close();   
  // 将从BLOB读出的字节写入文件   
  fout.write(b, 0, b.length);   
  fout.close();   
    
  System.out.println("write itotal::" + b.length);   
    
  }   
  catch (Exception e) {   
  System.out.println("errror :" + e.toString());   
  e.printStackTrace();   
    
  }   
  finally { //**关闭所有数据联接   
  test.conn.commit();   
  }   
  }   
  catch(Exception e){   
  System.out.println(e);   
    
  }   
  }   
    
  }

方式二： 

package com.lizhe;
import java.io.*;
import java.sql.*;
public class PutImg {
public void putimg() {
    try {
     Class.forName("org.gjt.mm.mysql.Driver").newInstance();
     String url = "jdbc:mysql://localhost/img?user=root&password=root&useUnicode=true&characterEncoding=gbk";
     Connection conn = DriverManager.getConnection(url);
     Statement stmt = conn.createStatement();
     //stmt.execute("insert     into     imgt (id)     values     (5)");
     stmt.close();
     PreparedStatement pstmt = null;
     String sql = "";
     File file = new File("c:\\blog.jpg");
     InputStream photoStream = new FileInputStream(file);
     //sql = "     UPDATE     imgt     SET     img     =     ?     ";
     
     sql = "INSERT INTO imgtable    (img) VALUES (?)";
   
     pstmt = conn.prepareStatement(sql);
     pstmt.setBinaryStream(1, photoStream, (int) file.length());
     pstmt.executeUpdate();
     pstmt.close();
     conn.close();
    } catch (Exception e) {
     e.printStackTrace();
    }
}
public static void main(String args[]){
    PutImg pi=new PutImg();
    pi.putimg();
}
}

InputStream photoStream = new FileInputStream(file);
可以很清楚的看到我们首先把一个图片文件(当然也可以是别的什么文件)转换成了一个二进制输入流
pstmt.setBinaryStream(1, photoStream, (int) file.length());
这个方法建议大家去查一下API文档,第一个参数是通配符位置没的说,第二个参数是流,这和以往的string类型的参数不太一样,
我刚看到的时候也觉得豁然开朗了,但是到这里还没完,不同于以往的字符串参数,这里我们还需要第三个参数来设置这个流的长度,
这里也就是这个文件的长度,导出数据库中的sql,一切都清楚了
INSERT INTO `m_diy` VALUES (2,?\0 JFIF\0     \0H\0H\0\0?? Exif\0\0MM\0*\0\0\0 \0    \0 \0\0\0 \0 \0\0    \0 \0\0\0 \0\0\0b 
\0 \0\0\0 \0\0\0j (\0 \0\0\0 \0 \0\0 1\0 \0\0\0 \0\0\0r 2\0 \0\0\0 \0\0\0?i\0 \0\0\0 \0\0\0\0\0\0\0\0\0H\0\0\0 
\0\0\0H\0\0\0 Adobe Photoshop CS Windows\02007:03:18 23:08:15\0\0\0\0\0 ?\0 \0\0\0 ??\0\0?\0 \0\0\0 \0\0\0? \0 
........等等
其实就是将文件先转换成了二进制的流,然后插入到了sql语言中,向数据库写入了很长很长的一段sql语句



然后我们再来写一个app程序将这个文件读出来,存储成一个图片文件
package com.lizhe;
import java.io.*;
import java.sql.*;
class GetImg {

private static final String URL = "jdbc:mysql://localhost/img?user=root&password=root&useUnicode=true&characterEncoding=gbk";
private Connection conn = null; 
private PreparedStatement pstmt = null; 
private ResultSet rs = null; 
private File file = null;

public void blobRead(String outfile, int picID) throws Exception {
    FileOutputStream fos = null;
    InputStream is = null;
    byte[] Buffer = new byte[4096];
    try {
     Class.forName("org.gjt.mm.mysql.Driver").newInstance();
     conn = DriverManager.getConnection(URL);
     pstmt = conn.prepareStatement("select img from imgt where id=?");
     pstmt.setInt(1, picID); // 传入要取的图片的ID
     rs = pstmt.executeQuery();
     rs.next();
     file = new File(outfile);
     if (!file.exists()) {
      file.createNewFile(); // 如果文件不存在，则创建
     }
     fos = new FileOutputStream(file);
     is = rs.getBinaryStream("img");
     int size = 0;
   
     while ((size = is.read(Buffer)) != -1) {
      // System.out.println(size);
      fos.write(Buffer, 0, size);
     }
    } catch (Exception e) {
     System.out.println( e.getMessage());
    } finally {
     // 关闭用到的资源
     fos.close();
     rs.close();
     pstmt.close();
     conn.close();
    }
}
public static void main(String[] args) {
    try {
     GetImg gi=new GetImg();
     gi.blobRead("c:/getimgs/1.jpg", 5);
    } catch (Exception e) {
     System.out.println("[Main func error: ]" + e.getMessage());
    }
}
}
这里需要注意的是
is = rs.getBinaryStream("img");
img是数据库中相应的列名,其实和rs.getString()方法差不多,只不过这个方法是读取二进制流的
最后在帖两个bs系统上用的文件给大家参考
通过struts的action向数据库写入二进制图片
/*
* Generated by MyEclipse Struts
* Template path: templates/java/JavaClass.vtl
*/
package com.lizhe.struts.action;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;
import com.lizhe.struts.form.UpimgForm;
/** 
* MyEclipse Struts
* Creation date: 05-18-2007
* 
* XDoclet definition:
* @struts.action path="/upimg" name="upimgForm" input="/userhomepage.jsp"
* @struts.action-forward name="userhome" path="/userhomepage.jsp" redirect="true" contextRelative="true"
*/
public class UpimgAction extends Action {
/*
    * Generated Methods
    */
/** 
    * Method execute
    * @param mapping
    * @param form
    * @param request
    * @param response
    * @return ActionForward
    * @throws IOException 
    * @throws FileNotFoundException 
    */
public ActionForward execute(ActionMapping mapping, ActionForm form,
     HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException, IOException {
    UpimgForm upimgForm = (UpimgForm) form;// TODO Auto-generated method stub
  
    FormFile file=upimgForm.getFile();
    InputStream is=file.getInputStream();
  
    try {
     Class.forName("org.gjt.mm.mysql.Driver").newInstance();
     String url = "jdbc:mysql://localhost/blog?user=root&password=root&useUnicode=true&characterEncoding=gb2312";
     Connection conn = DriverManager.getConnection(url);
     Statement stmt = conn.createStatement();
     //stmt.execute("insert     into     img (id)     values     (5)");
     stmt.close();
     PreparedStatement pstmt = null;
     String sql = "";
     //File file = new File("c:\\blog.jpg");
     //InputStream photoStream = new FileInputStream(file);
     //sql = "     UPDATE     imgt     SET     img     =     ?     ";
     
     sql = "INSERT INTO img (img) VALUES (?)";
   
     pstmt = conn.prepareStatement(sql);
     pstmt.setBinaryStream(1, is, (int) file.getFileSize());
     pstmt.executeUpdate();
     pstmt.close();
     conn.close();
    } catch (Exception e) {
     e.printStackTrace();
    }
  
    return mapping.findForward("userhomepage");
}
}
和app的方式几乎是一样的
第二个文件是通过jsp将数据库中的图片显示在页面上
这个有些不同
<%@     page     contentType="text/html;charset=gb2312"%>     
    <%@     page     import="java.sql.*"     %>     
    <%@     page     import="java.util.*"%>     
    <%@     page     import="java.text.*"%>     
    <%@     page     import="java.io.*"%>     
<%@     page     import="java.awt.*"%> 
    <html>     
    <body>     
    <%   
     Class.forName("org.gjt.mm.mysql.Driver").newInstance();
      String url="jdbc:mysql://localhost/img?user=root&password=root";
     Connection     con     =     DriverManager.getConnection(url);      
     String     sql     =     "select     *     from    imgt where id=5";     
     Statement stmt = con.createStatement(); 
   
     ResultSet rs = stmt.executeQuery(sql); 
     if(rs.next()) {  
      InputStream in = rs.getBinaryStream("img"); 
      ServletOutputStream op = response.getOutputStream(); 
      int len; 
      byte[] buf=new byte[1024]; 
      while((len= in.read(buf))!=-1) { 
       op.write(buf, 0, len); 
      } 
      op.close(); 
      in.close(); 
     } 
   
     rs.close(); 
     stmt.close(); 
      con.close(); 
    %>     
    </body>     
    </html>