mysql操作blob

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/JAVA2010CZP/article/details/7078961

jsp+mysql 记住 要用mysql的longblob类型来存默认的blob大小不够

数据库字段:id (char) pic (longblob)

转载请注明出处,这时我与我的知己的合作的结过

原来操作blob字段时都要先差个空值,在查blob,好麻烦,用prepareStatment就不用那么麻烦了,哈哈

postblob.heml页面

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>
<body>
<form action="testblob.jsp" method="post" >
<table width="291" border="1">
  <tr>
    <td width="107">id </td>
    <td width="168"><input name="id" type="text" /></td>
  </tr>
  <tr>
    <td>file</td>
    <td><input name="file" type="file" /></td>
  </tr>
  <tr>
    <td><input  type="submit"  value="提交"/></td>
   
  </tr>
</table>
</form>
</body>
</html>

testblob.jsp

<%@ page contentType="text/html;charset=gb2312"%> 
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.io.*"%> 
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>
<body>
<% 
 String id=request.getParameter("id");
 String file=request.getParameter("file");
 out.print(id);
 out.print(file);
 FileInputStream str=new FileInputStream(file);
 out.print(str.available());
   java.sql.Connection conn; 
   java.lang.String strConn; 
   Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
   conn= java.sql.DriverManager.getConnection("jdbc:mysql://localhost/test","root",""); 
 String sql="insert into test(id,pic) values(?,?)"; 
 PreparedStatement pstmt=conn.prepareStatement(sql); 
 pstmt.setString(1,id);
 pstmt.setBinaryStream(2,str,str.available()); 
pstmt.execute(); 
out.println("Success,You Have Insert an Image Successfully");
 pstmt.close();
%> 
<a href="readblob.jsp">查看图片</a>
<a href="postblob.html">返回</a>
</body>
</html>

********************************************************

readblob.jsp

<%@ page contentType="text/html;charset=gb2312"%> 
<%@ page import="java.sql.*, javax.sql.*" %>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.io.*"%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>无标题文档</title>
</head>

<body>
<%
 
 java.sql.Connection conn;
 ResultSet rs=null;
  Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
   conn= java.sql.DriverManager.getConnection("jdbc:mysql://localhost/test","root",""); 
   Statement stmt=conn.createStatement(); 
   rs=stmt.executeQuery("select * from test where id='1'");
  if(rs.next())
  {
    Blob b = rs.getBlob("pic");
   
 int size =(int)b.length();
      out.print(size);
  InputStream in=b.getBinaryStream();
  byte[] by= new byte[size];
  response.setContentType("image/jpeg"); 
  ServletOutputStream sos = response.getOutputStream();
     int bytesRead = 0;
       while ((bytesRead = in.read(by)) != -1) {
             sos.write(by, 0, bytesRead);
          }
         in.close();
         sos.flush();
    
  }
  
 
%>
</body>
</html>

在用sos.write(by, 0, bytesRead);时,该方法把inputstream中的内容在一个新的页面中输出,

如果本页中还有别的内容要输出的话,只有把上述方法改为,bytesRead = in.read(by)) ;

再用out.print(new String(by));方法输出结果,注意在这里不能用by.toString()方法,该方法返回的是要输出内容的内存地址。mysql中有blob textarea类型大小了66536基本上放点小的东东就足够了

展开阅读全文

Pb操作BLOB出现问题

04-22

integer bmpno,icountrn//变量bmpno存放给定的图片编号rnstring filepath,filenamernblob blb_tmprn//…此处对图片编号bmpno赋值,例如bmpno=101;可以通过程序实现为弹出对话框窗口提供一个图片编号rn//查询指定的图片编号是否已经存在rnicount=0rnGetFileOpenName("请选择一个需要插入的BMP图片文件",filepath,filename,"jpg","jpg图片文件(*.bmp),*.bmp")rnIf Len(filepath)=0 Thenrn ReturnrnEnd IfrnrnSetPointer(HOURGLASS!)rnint li_filenum, li_loops, li_counterrnlong ll_filelen,ll_bytes_read,ll_new_posrnblob lb_our_blob, lb_tot_brnll_filelen = FileLength(filepath)rnrnli_filenum= FileOpen(filepath,STREAMMODE!,READ!,LOCKREAD!)rnrnIF ll_filelen > 32766 THENrn li_loops = ((ll_filelen -1)/32766) + 1rnELSErnli_loops = 1rnEND IFrnrnFOR li_counter = 1 to li_loopsrn ll_bytes_read=FileRead(li_filenum,lb_our_blob )rn lb_tot_b=lb_tot_b+lb_our_blobrn ll_new_pos=ll_new_pos+ll_bytes_readrn FileSeek(li_filenum,ll_new_pos,FROMBEGINNING!)rnNEXTrnFileClose(li_filenum)rnrn//p_1.SetPicture(lb_tot_b)rnrnINSERT INTO img (id) values(11)rnusing sqlca;rn//EXECUTE IMMEDIATE :sql;rnIf SQLCA.SQLDBCode=0 Thenrn COMMIT USING SQLCA;//提交事务rnElsern MessageBox("数据库错误","插入失败")rn ROLLBACK USING SQLCA;rn //事务回滚rn ReturnrnEnd Ifrnrnsqlca.AUTOCOMMIT=truernUPDATEBLOB img SET img=:lb_tot_brn   WHERE img.id=11rn   USING SQLCA; //更新存放图片的字段rnIf SQLCA.SQLDBCode=0 Thenrn COMMIT;rn //提交事务rn MessageBox("插入成功","图片入库成功")rnElsern MessageBox("数据库错误","更新图片失败")rn ROLLBACK;//事务回滚 rn ReturnrnEnd Ifrnsqlca.AUTOCOMMIT=falsernrnrn请问大虾们rn以上的程序为什么会出现“更新图片失败”rn我用数据库是SQL SERVER 2000rn表名(img) 字段名如下rnid int not nullrnimg image null 论坛

没有更多推荐了,返回首页