lundi 14 mars 2011

Save images into database Postgres with Java

This exemple show you how to store byte array in data base and retrive it again.

To do this we need to use the Utils Java Class in my last post ( image to byte array and byte [] to image using java ) and we can chose Postgres or MySql data base to store data .

Many devlopper try to store data in Varchar format or string varbinary,
Saving the byte[] as VarBinary(MAX) is ok. But, the problem comes when retrieving it as byte array.

In this exemple i will explain how to

switch between images, bytes postgres, and image again using java

( you can use other data base )

The first step, we need to make a Postgres databases to store our images data. Make a database called “imagedb” on your Postgres or MySQL  database, then create a table called “image” the sql create script :

CREATE TABLE image
(
  id integer NOT NULL,
  name character varying(255),
  image bytea,
)


if you are using MySql change type of image from bytea to blob.

The id field represent id of image, name field represent name of image, and the dates is field that used to store information when the image uploaded.

Now we need to use ower Utils.ImageToByte methode in my last post from image to bytes

public static byte [] ImageToByte(File file) throws FileNotFoundException{
        FileInputStream fis = new FileInputStream(file);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] buf = new byte[1024];
        try {
            for (int readNum; (readNum = fis.read(buf)) != -1;) {
                bos.write(buf, 0, readNum);      
                System.out.println("read " + readNum + " bytes,");
            }
        } catch (IOException ex) {
        }
        byte[] bytes = bos.toByteArray();
     
     return bytes; 
    }


Next step is to make a JDBC connection, i prefer to use JPA but now just to show you how we can insert byte to database

To do this i created two class: the first one is to create connection to data base named ConnectDB.java, the second one for SQL Query and finaly we need to create ower main methode for test

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectDB {

 Connection mySqlConnection;
 String URL = "jdbc:postgresql://localhost:5432/imagedb";
 String pwd = "root";
 String user = "postgres";

 public Connection getConnection() {

  try {
   Class.forName("org.postgresql.Driver");

  } catch (Exception e) {
   System.out.println("Driver Problem");
   e.printStackTrace();
  }
  try {
   if (mySqlConnection == null) {
    mySqlConnection = DriverManager.getConnection(URL, user, pwd);
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return mySqlConnection;
 }

}


Don't forget to add the JDBC jar into your class path.

The second Java Class is the Class used to execute SQL Query for insert and select Data :

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;

import java.sql.Statement;

public class ConnectionBdImage {

 public byte[] getImage(int id) {
  byte[] byteImg = null;
  bd connect = new bd();
  Connection connection = null;
  try {
   connection = connect.getConnection();
   // String byteImg="";
   PreparedStatement ps = connection
     .prepareStatement("SELECT image FROM image WHERE id = ?");
   ps.setInt(1, id);
   ResultSet rs = ps.executeQuery();
   while (rs.next()) {
    byteImg = rs.getBytes(1);
    // use the data in some way here
   }
   rs.close();
   ps.close();

   return byteImg;
  } catch (Exception e) {
   // TODO: handle exception
   return null;
  }

 }

 public void addImage(byte[] img, int id) {
  bd connect = new bd();
  Connection connection = null;
  try {
   connection = connect.getConnection();

   Statement statement = connection.createStatement();

   PreparedStatement ps = connection
     .prepareStatement("INSERT INTO image VALUES (?, ?, ?)");
   ps.setInt(1, id);
   ps.setBytes(2, img);
   ps.setString(3, "test");

   ps.executeUpdate();
   ps.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    connection.close();

   } catch (Exception e) {
    // TODO: handle exception
   }
  }

 }
    


getImage methode is to get image from data base and addImage is to store it into DB.

Finaly we need main methode to store Data :

public class ConvertImage {   
 
    public static void main(String[] args) throws FileNotFoundException, IOException {
     
     ConnectionBdImage bd = new ConnectionBdlogin();
     
     String fileSource = "C:/imgSource/image.jpg";
     String fileDestination = "C:/imgDestination/destination.jpeg";
     
     try {
      
      byte [] byteImage = Utils.ImageToByte(new File(fileSource));
      
      bd.addImage(byteImage, 1);
      System.out.println(org.postgresql.util.Base64.encodeBytes(bd.getImage(1)));
     Utils.byteToImage(bd.getImage(1), new File(fileDestination));
  } catch (Exception e) {
   e.printStackTrace();
  }      
    }
     
}

Using the above steps you can retrieve and display the image from the database and store it into file or display to the web page.

3 commentaires: