[JSP] 用程式新增圖片到MSSQL

2011-06-21

9594
0
J2EE
2011-11-27

[JSP] 用程式新增圖片到MSSQL

index.jsp 制作上傳檔案的頁面(使用檔案上傳 - Oreilly MultiPartRequest 套件)


<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>檔案上傳頁</title>
    </head>
    <body>
      <!--enctype="multipart/form-data"必填-->  
      <form name="form1" enctype="multipart/form-data" method="post" action="upload.jsp">
      <input type="file" name="clientFile" size="20" maxlength="20"> 
      <br/>
      
      <input type="submit"value="上傳" />
     
      </form>

    </body>
</html>

upload.jsp

上傳檔案，並Insert into圖片到資料表


<%@page import="java.sql.*"%>
<%@page import="java.util.Enumeration"%>
<%@page import="java.io.*"%>
<%@page import="com.oreilly.servlet.MultipartRequest" %>
<%
    //檔案上傳到網站根目錄底下的upload資料夾
    String saveDirectory = request.getRealPath("/upload"); 
    // 限制上傳之檔案大小為 5 MB
    int maxFileSize = 5 * 1024 * 1024 ;
    //檔案上傳完畢
    MultipartRequest multi = new MultipartRequest(request , saveDirectory , maxFileSize, "UTF-8");
    File file = multi.getFile("clientFile");//取得剛剛上傳的檔案物件
    //out.print(file.getAbsolutePath());//秀出檔案總管中的路徑
     

    FileInputStream fs = new FileInputStream(file);
    byte[] buffer = new byte[fs.available()];
    fs.read(buffer,0,buffer.length);
    fs.close();
    //讀取檔案到buffer陣列
      
      // Establish the connection.
    Connection conn=null;
       try
       {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Driver 3.0的寫法
         //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//SQL Server 2000 Driver的寫法
        
          conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1\\sqlexpress:1433;" +
			    "database=NorthwindChinese;", "sa", "123");
         
       }catch(Exception ex)
       {
         out.print(ex.toString());
       }
    
   
    PreparedStatement stmt = conn.prepareStatement("Insert into Categories (CategoryName,Picture) Values (?,?)");
    stmt.setString(1, "myPic");
    stmt.setBytes(2, buffer);
    //以下兩道敘述試過都不行
    //stmt.setBinaryStream(parameterIndex, x);
    //stmt.setBlob(parameterIndex, inputStream);
    stmt.execute();
    stmt.close();
    conn.close();
    
%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>

showPic.jsp

撈資料表裡的圖檔


<%@page import="java.sql.*"%>
<%@page import="java.io.FileInputStream"%>
<%
String CategoryID = request.getParameter("CategoryID").replace("'", "''");


    // Establish the connection.
    Connection conn=null;
       try
       {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Driver 3.0的寫法
         //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//SQL Server 2000 Driver的寫法
        
          conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1\\sqlexpress:1433;" +
			    "database=NorthwindChinese;", "sa", "123");
         
       }catch(Exception ex)
       {
         out.print(ex.toString());
       }
    

    PreparedStatement stmt = conn.prepareStatement("Select Top 1 Picture From Categories Where CategoryID = '"+CategoryID+"'");
    ResultSet rs  = stmt.executeQuery();
    byte[] file=null;
    if(rs.next())
    {
      file = rs.getBytes(1);
    }
      rs.close();
      stmt.close();
      conn.close();
  
  
  
  
      
      
  out.clear();
  response.setContentType("image/jpeg");
  //當要跳出視窗給User下載時才用以下敘述
  //response.addHeader("Content-Disposition ","attachment; filename=one.jpg");
  response.getOutputStream().write(file);
  response.getOutputStream().close();
%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>

執行結果：

網頁秀出圖片的方法：


<img src="showPic.jsp?CategoryID=16" />

回首頁