Como armazenar/recuperar imagens para/do SQL Server

Como armazenar/recuperar imagens para/do SQL Server

O jTDS é um driver JDBC 3.0 de código aberto para o Microsoft SQL Server (6.5, 7, 2000 e 2005). O pacote java.sql junto com o driver acima ajuda em armazenar dados binários na base de dados do Microsoft SQL Server.

O exemplo abaixo mostra como armazenar e recuperar os dados de uma imagem no SQL Server.

import java.sql.*;
<B><SPAN lang=EN-US >import </SPAN></B><SPAN lang=EN-US >java.io.*;</SPAN>

<B><SPAN lang=EN-US >public class </SPAN></B><SPAN lang=EN-US >saveImage </SPAN>
<SPAN lang=EN-US >{</SPAN>
<B><SPAN lang=EN-US >public static void </SPAN></B><SPAN lang=EN-US >main(String[] args) </SPAN>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >DB db = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >DB();</SPAN>
<SPAN lang=EN-US >Connection conn=db.dbConnect(</SPAN>
<SPAN lang=EN-US >"jdbc:jtds:sqlserver://localhost:1433/test"</SPAN><SPAN lang=EN-US >,</SPAN><SPAN lang=EN-US >"sa"</SPAN><SPAN lang=EN-US >,</SPAN><SPAN lang=EN-US >""</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >db.insertImage(conn,</SPAN><SPAN lang=EN-US >"d://filepath//test.JPG"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >db.getImageData(conn);</SPAN>
<SPAN lang=EN-US >}</SPAN>

<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >class </SPAN></B><SPAN lang=EN-US >DB</SPAN>
<SPAN lang=EN-US >{</SPAN>
<B><SPAN lang=EN-US >public </SPAN></B><SPAN lang=EN-US >DB() {}</SPAN>

<B><SPAN lang=EN-US >public </SPAN></B><SPAN lang=EN-US >Connection dbConnect(String db_connect_string,</SPAN>
<SPAN lang=EN-US >String db_userid, String db_password)</SPAN>
<SPAN lang=EN-US >{</SPAN>
<B><SPAN lang=EN-US >try</SPAN></B>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >Class.forName(</SPAN><SPAN lang=EN-US >"net.sourceforge.jtds.jdbc.Driver"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >Connection conn = DriverManager.getConnection(</SPAN>
<SPAN lang=EN-US >db_connect_string, db_userid, db_password);</SPAN>

<SPAN lang=EN-US >System.out.println(</SPAN><SPAN lang=EN-US >"conectado"</SPAN><SPAN lang=EN-US >);</SPAN>
<B><SPAN lang=EN-US >return </SPAN></B><SPAN lang=EN-US >conn;</SPAN>

<SPAN lang=EN-US >}</SPAN>
<B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e)</SPAN>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<B><SPAN lang=EN-US >return null</SPAN></B><SPAN lang=EN-US >;</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >public void </SPAN></B><SPAN lang=EN-US >insertImage(Connection conn,String img)</SPAN>
<SPAN lang=EN-US >{</SPAN>
<B><SPAN lang=EN-US >int </SPAN></B><SPAN lang=EN-US >len;</SPAN>
<SPAN lang=EN-US >String query;</SPAN>
<SPAN lang=EN-US >PreparedStatement pstmt;</SPAN>

<B><SPAN lang=EN-US >try</SPAN></B>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >File file = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >File(img);</SPAN>
<SPAN lang=EN-US >FileInputStream fis = </SPAN><B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >FileInputStream(file);</SPAN>
<SPAN lang=EN-US >len = (</SPAN><B><SPAN lang=EN-US >int</SPAN></B><SPAN lang=EN-US >)file.length();</SPAN>

<SPAN lang=EN-US >query = (</SPAN><SPAN lang=EN-US >"insert into TableImage VALUES(?,?,?)"</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >pstmt = conn.prepareStatement(query);</SPAN>
<SPAN lang=EN-US >pstmt.setString(</SPAN><SPAN lang=EN-US >1</SPAN><SPAN lang=EN-US >,file.getName());</SPAN>
<SPAN lang=EN-US >pstmt.setInt(</SPAN><SPAN lang=EN-US >2</SPAN><SPAN lang=EN-US >, len);</SPAN>
<SPAN lang=EN-US >  </SPAN>
<SPAN lang=EN-US >//Método usado para inserir uma stream de bytes</SPAN>
<SPAN lang=EN-US >pstmt.setBinaryStream(</SPAN><SPAN lang=EN-US >3</SPAN><SPAN lang=EN-US >, fis, len); </SPAN>
<SPAN lang=EN-US >pstmt.executeUpdate();</SPAN>

<SPAN lang=EN-US >}</SPAN>
<B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e)</SPAN>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>

<B><SPAN lang=EN-US >public void </SPAN></B><SPAN lang=EN-US >getImageData(Connection conn)</SPAN>
<SPAN lang=EN-US >{</SPAN>

<B><SPAN lang=EN-US >byte</SPAN></B><SPAN lang=EN-US >[] fileBytes;</SPAN>
<SPAN lang=EN-US >String query;</SPAN>
<B><SPAN lang=EN-US >try</SPAN></B>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >query = </SPAN><SPAN lang=EN-US >"select data from tableimage"</SPAN><SPAN lang=EN-US >;</SPAN>
<SPAN lang=EN-US >Statement state = conn.createStatement();</SPAN>
<SPAN lang=EN-US >ResultSet rs = state.executeQuery(query);</SPAN>
<B><SPAN lang=EN-US >if </SPAN></B><SPAN lang=EN-US >(rs.next())</SPAN>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >fileBytes = rs.getBytes(</SPAN><SPAN lang=EN-US >1</SPAN><SPAN lang=EN-US >);</SPAN>
<SPAN lang=EN-US >OutputStream targetFile=  </SPAN>
<B><SPAN lang=EN-US >new </SPAN></B><SPAN lang=EN-US >FileOutputStream(</SPAN><SPAN lang=EN-US >"d://filepath//new.JPG"</SPAN><SPAN lang=EN-US >);</SPAN>

<SPAN lang=EN-US >targetFile.write(fileBytes);</SPAN>
<SPAN lang=EN-US >targetFile.close();</SPAN>
<SPAN lang=EN-US >}        </SPAN>

<SPAN lang=EN-US >}</SPAN>
<B><SPAN lang=EN-US >catch </SPAN></B><SPAN lang=EN-US >(Exception e)</SPAN>
<SPAN lang=EN-US >{</SPAN>
<SPAN lang=EN-US >e.printStackTrace();</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >}</SPAN>
<SPAN lang=EN-US >};</SPAN>