Blob

package blob;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



// atributos para efetuar conexão com banco 
public final class TestCreateLob {
	protected static final String url = "jdbc:sqlserver://127.0.0.1:1433;"
			+ "databaseName=RH2;";

	protected static final String user = "sa";

	protected static final String pass = "joao";

	protected static Connection con = null;

	// Arquivos para os testes
	protected static final File file = new File("teste.txt");

	protected static final File fileDb = new File("db.txt");

	protected static final File byteFile = new File("teste.jpg");

	protected static final File byteFileDb = new File("db.jpg");

	static {
		// registra drive do bd
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").getName();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		// manten conexao estatica
		System.out.println("connectando");
		try {
			con = DriverManager.getConnection(url, user, pass);
			con.setAutoCommit(false);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		// deleta a tabela
		System.out.println("Criando a tabela");
		try {
			con.createStatement().execute("drop table teste");
		} catch (Exception e) {
			// aceita pois pode nao existir a tabela
		}

		// cria a tabela
		try {
			con
					.createStatement()
					.execute(
							"create table teste (cadastro tinyint, blob image, texto text)");
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		// cria arquivo binario
		System.out.println("criando arquivo");
		try{
			OutputStream out = new FileOutputStream(byteFile);
			byte[] data = new byte[]{100};
			for(int i = 0;i<1000;i++){
				out.write(data);
			}
			out.close();
		} catch (FileNotFoundException e) {
			throw new RuntimeException(e);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		
		//cria arquivo de  texto
		try{
			Writer out = new FileWriter(file);
			for(int i = 0;i<1000;i++){
				out.write('A');
			}
			out.close();
		} catch (FileNotFoundException e) {
			throw new RuntimeException(e);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	  public static void main(String[] args) {
		try{
			testCreateLob();	
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try{
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	public static void testCreateLob() throws Exception{
		// insere registro sem blob e clob
		System.out.println("inserindo dado");
		Statement stmt = con.createStatement();
		stmt.execute("insert into teste(cadastro, blob, texto) values (1,'0','')");
		
		//resgata para inserir blob e clob
		System.out.println("inserindo blob e clob");
		ResultSet rs = stmt.executeQuery("select cadastro, blob, texto from teste where cadastro = 1");
		rs.next();
		
		//copia arquivo para o campo blob
		Blob blob = rs.getBlob("blob");
		byte[] bbuf = new byte[1024];
		InputStream bin = new FileInputStream(byteFile);
		OutputStream bout = blob.setBinaryStream(0);
		int bytesRead = 0;
		while ((bytesRead = bin.read(bbuf))!= -1){
			bout.write(bbuf, 0, bytesRead);// o  expition aparece nessa linha
		}
		bin.close();
		bout.close();
		
		//copia arquivo para um campo clob
		Clob clob = rs.getClob("texto");
		char[] cbuf = new char[1024];
		Reader cin =  new FileReader(file);
		Writer cout = clob.setCharacterStream(0);
		int charsRead = 0;
		while((charsRead = cin.read(cbuf))!= -1){
			cout.write(cbuf, 0,charsRead);
		}
		cin.close();
		cout.close();
		
		// comita  transação
		con.commit();
		rs.close();
		
		// seleciona registro
		System.out.println("selecionando registro");
		rs = stmt.executeQuery("select cadastro, image, texto from teste where cadastro = 1");
		rs.next();
		
		//cria novo arquivo binario com blob
		blob = rs.getBlob("image");
		bin = blob.getBinaryStream();
		bout = new FileOutputStream(byteFileDb);
		while((bytesRead = bin.read(bbuf)) != -1){
			bout.write(bbuf, 0, bytesRead);
		}
		bin.close();
		bout.close();
		
		//cria novo arquivo de texto
		clob = rs.getClob("texto");
		cin = clob.getCharacterStream();
		cout = new FileWriter(fileDb);
		while((charsRead = cin.read(cbuf)) != -1){
			cout.write(cbuf, 0, charsRead);
		}
		cin.close();
		cout.close();
		
		//fecha  cursor
		rs.close();
		
		// arquivos binaros devem ser iguais
		System.out.println("verificando arquivos");
		if(equals(byteFile, byteFileDb)){
			System.out.println(byteFile.toString() + " == " + byteFileDb.toString());
		} else{
			System.out.println(file.toString() + " == " + fileDb.toString());
		}
	}
	/**
	 * Compara 2 arqivos charcter a caracter
	 * @param f1 arquivo 1
	 * @paran f2 arquivo 2 
	 * @return true se forem iguais, false se nao forem
	 */
	 
	private static boolean equals(File f1,File f2 ) throws IOException{
		
		FileReader r1 = new FileReader(f1);
		FileReader r2 = new FileReader(f2);
		
		while(true){
			int c1 = r1.read();
			int c2 = r2.read();
			if(c1 != c2){
				return false;
			}
			if(c1==-1 && c2 ==-1){
				break;
			}
		}
		return true;
	}
}