How to show my Photo from Mysql in JAVAFX

Question

I'm taking a picture from my web cam, and saving first in D: drive, and from the D: drive to my DB in MySQL as Blob. I want to show that picture when I change rows in a tableview. All the other values are working in the tableview, showing in textfields, but I can't retrieve the image from the DB. here is a picture of my tableview:

Tableview OK There is a ImageView, is where I want to show image corresponding to selected row.

First of all this is the code to save the picture to db.

//Guardar datos visitantes en DB
public void guardar(ActionEvent event) throws Exception{

    if ( txtcedula.getText().equals("") || 
         txtnombres.getText().equals("") ||
         txtapellidos.getText().equals("")|| 
         txtapartamento.getText().equals("")||
         txtcelular.getText().equals("") ||
         txtobservaciones.getText().equals("")) {

        lblcampos.setText("No debe haber campos vacios");
    } else {
        ConexionSQL cn = new ConexionSQL();
        Connection con = cn.conexion();
        FileInputStream fis = null;
        String ubicaimg = "@../../../../../../Kamui/Imagenes/foto.jpg";
        File archivo = new File(ubicaimg);
        fis = new FileInputStream(archivo);
        //fin busqueda de la foto
        String ced,nom,ape,conj,apto,cel,obs;
        String sql="";
        ced=txtcedula.getText();
        nom=txtnombres.getText();
        ape=txtapellidos.getText();
        conj=cmbconjunto.getSelectionModel().getSelectedItem().toString();
        apto=txtapartamento.getText();
        cel=txtcelular.getText();
        obs = txtobservaciones.getText();

        sql="INSERT INTO visitantes (cedula_visi,nombre_visi,apellidos_visi,nomconj_visi,apartamento_visi,celular_visi,observaciones_visi,foto_visi) VALUES (?,?,?,?,?,?,?,?)";
        try{
            PreparedStatement sta = con.prepareStatement(sql);
            sta.setString(1,ced);
            sta.setString(2,nom);
            sta.setString(3,ape);
            sta.setString(4,conj);
            sta.setString(5,apto);
            sta.setString(6,cel);
            sta.setString(7,obs);
            sta.setBinaryStream(8, fis, (int) archivo.length());//se convierte la imagen en binary y se guarda como BLOB
            sta.executeUpdate();
            lblcampos.setText("Guardado Con exito");
            lblinfocamara.setVisible(false);
        } catch(Exception e){
             String info = e.getMessage();
             lblinfo.setText(info);

        }
    }
}

And here is the code to show the other values in the textfields

package application;


import java.awt.Image;
import java.awt.Toolkit;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

import javax.imageio.ImageIO;
import javax.imageio.stream.FileImageOutputStream;
import javax.swing.ImageIcon;

import javafx.beans.property.ReadOnlyStringWrapper;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Label;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextField;
import javafx.scene.image.ImageView;



public class ver_visitantes implements Initializable {

    @FXML private TableView<visitantes> tbvisitantes;//enlazar tableview con el objeto visitantes
    @FXML private TableColumn<visitantes, String> clcedula;
    @FXML private TableColumn<visitantes, String> clnombres;
    @FXML private TableColumn<visitantes, String> clapellidos;
    @FXML private TableColumn<visitantes, String> clhentrada;
    @FXML private TableColumn<visitantes, String> clconjunto;
    @FXML private TableColumn<visitantes, String> clapartamento;
    @FXML private TableColumn<visitantes, String> clcelular;
    @FXML private TableColumn<visitantes, String> clobservaciones;
    @FXML private ImageView imgfotovisi;
    @FXML private TextField txtcedula;
    @FXML private TextField txtnombres;
    @FXML private TextField txtapellidos;
    @FXML private TextField txtconjunto;
    @FXML private TextField txtapto;
    @FXML private TextField txtcelular;
    @FXML private TextField txtobservaciones;
    @FXML private Label lbltest; 
    private ObservableList<visitantes> visitorlist;//dar nombre al observable list que se llena con el objeto visitantes

    @Override
    public void initialize(URL arg0, ResourceBundle arg1) {
        ConexionSQL cnt = new ConexionSQL();
        cnt.conexion();
        visitorlist = FXCollections.observableArrayList();
        visitantes.llenarlistavisitas(cnt.conexion(), visitorlist);
        tbvisitantes.setItems(visitorlist);//llenar table view con la lista
        imgfotovisi.getClass().getResourceAsStream("imagenes/huellabiometrico.jpg");
        //enlazar cada columna con el campo a llenar del resultset con los metodos getter 
        clcedula.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getcedula()));
        clnombres.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getnombres()));
        clapellidos.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getapellidos()));
        clconjunto.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getconjunto()));
        clapartamento.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getapartamento()));
        clcelular.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getcelular()));
        clobservaciones.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().getobservaciones()));
        clhentrada.setCellValueFactory(cellData -> new ReadOnlyStringWrapper(cellData.getValue().gethentrada()));
        gestionarEventos();
        /*visitantes visi = new visitantes(null, null, null, null, null, null, null, null, null);
        String image;
        image = visi.getfoto();

        imgfotovisi.setId(image);*/
    }


    public void gestionarEventos(){

        tbvisitantes.getSelectionModel().selectedItemProperty().addListener(new ChangeListener<visitantes>() {
            @Override
            public void changed(ObservableValue<? extends visitantes> arg0,
                    visitantes valorAnterior, visitantes valorSeleccionado) {
                if (valorSeleccionado!=null){
                    txtcedula.setText(String.valueOf(valorSeleccionado.getcedula()));
                    txtnombres.setText(valorSeleccionado.getnombres());
                    txtapellidos.setText(valorSeleccionado.getapellidos());
                    txtconjunto.setText(String.valueOf(valorSeleccionado.getconjunto()));
                    txtapto.setText(String.valueOf(valorSeleccionado.getapartamento()));
                    txtcelular.setText(String.valueOf(valorSeleccionado.getcelular()));
                    txtobservaciones.setText(String.valueOf(valorSeleccionado.getobservaciones()));
                 }
             }
         });
     }
 }

and this is the visitantes object

package application;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;
import javafx.collections.ObservableList;

public class visitantes {
    private StringProperty cedula;
    private StringProperty nombres;
    private StringProperty apellidos;
    private StringProperty hentrada;
    private StringProperty conjunto;
    private StringProperty apartamento;
    private StringProperty celular;
    private StringProperty observaciones;
    private Blob foto;


    public visitantes(String cedula,String nombres,String apellidos,String hentrada,String conjunto,String apartamento,String celular,String observaciones,Blob foto){

        this.cedula = new SimpleStringProperty(cedula);
        this.nombres = new SimpleStringProperty(nombres);
        this.apellidos = new SimpleStringProperty(apellidos);
        this.hentrada = new SimpleStringProperty(hentrada);
        this.conjunto = new SimpleStringProperty(conjunto);
        this.apartamento = new SimpleStringProperty(apartamento);
        this.celular = new SimpleStringProperty(celular);
        this.observaciones = new SimpleStringProperty(observaciones);
        this.foto = foto;
    }



    public String getnombres(){
        return nombres.get();
    }

    public void setnombres(String nombres){
        this.nombres = new SimpleStringProperty(nombres);
    }

    public String getcedula(){
        return cedula.get();
    }

    public void setcedula(String cedula){
        this.cedula = new SimpleStringProperty(cedula);
    }
    public String getapellidos(){
        return apellidos.get();
    }

    public void setapellidos(String apellidos){
        this.apellidos = new SimpleStringProperty(apellidos);
    }

    public String gethentrada(){
        return hentrada.get();
    }

    public void sethentrada(String hentrada){
        this.hentrada = new SimpleStringProperty(hentrada);
    }

    public String getconjunto(){
        return conjunto.get();
    }

    public void setconjunto(String conjunto){
        this.conjunto = new SimpleStringProperty(conjunto);
    }

    public String getapartamento(){
        return apartamento.get();
    }

    public void setapartamento(String apartamento){
        this.apartamento = new SimpleStringProperty(apartamento);
    }

    public String getcelular(){
        return celular.get();
    }

    public void setcelular(String celular){
        this.celular = new SimpleStringProperty(celular);
    }

    public String getobservaciones(){
        return observaciones.get();
    }

    public void setobservaciones(String observaciones){
        this.observaciones = new SimpleStringProperty(observaciones);
    }
public Blob getfoto(){
        return foto;
}

    public static void llenarlistavisitas(Connection connection, ObservableList<visitantes> lista){
        try {
            String sql="SELECT * FROM visitantes";
            Statement statement = connection.createStatement();
            ResultSet visitantes = statement.executeQuery(sql);
                while (visitantes.next()){
                lista.add (
                        new visitantes(

                                visitantes.getString("cedula_visi"),
                                visitantes.getString("nombre_visi"),
                                visitantes.getString("apellidos_visi"),
                                visitantes.getString("hentrada"),
                                visitantes.getString("nomconj_visi"),
                                visitantes.getString("apartamento_visi"),
                                visitantes.getString("celular_visi"),
                                visitantes.getString("observaciones_visi"),
                                visitantes.getBlob("foto_visi")

                                      )

                         );
                    }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

I hope you can help me with this. Thanks a lot.

Answer 1

A Blob object doesn't contain the data in the corresponding blob database field, but simply a pointer to the SQL BLOB data. Clearly, this pointer only works while there is a valid connection (via the open result set, or update statement) to the database. The Javadocs state this as

By default drivers implement Blob using an SQL locator(BLOB), which means that a Blob object contains a logical pointer to the SQL BLOB data rather than the data itself. A Blob object is valid for the duration of the transaction in which is [sic] was created.

(Emphasis is mine.)

Therefore, it makes no sense to store the Blob as part of your model object. By the time you access the Blob, the database transaction will have been completed, and the connection between the Java Blob instance and the database BLOB data will no longer be present.

One option might be to actually load the image for each instance when you retrieve the data from the database, and just create an Image field in the Visitantes model.

You would basically do this by changing the type of foto in the Visitante class from Blob to Image. Then when you load the data from the database, replace

new visitantes(         
    visitantes.getString("cedula_visi"),
    visitantes.getString("nombre_visi"),
    visitantes.getString("apellidos_visi"),
    visitantes.getString("hentrada"),
    visitantes.getString("nomconj_visi"),
    visitantes.getString("apartamento_visi"),
    visitantes.getString("celular_visi"),
    visitantes.getString("observaciones_visi"),
    visitantes.getBlob("foto_visi")
)

with

new visitantes(         
    visitantes.getString("cedula_visi"),
    visitantes.getString("nombre_visi"),
    visitantes.getString("apellidos_visi"),
    visitantes.getString("hentrada"),
    visitantes.getString("nomconj_visi"),
    visitantes.getString("apartamento_visi"),
    visitantes.getString("celular_visi"),
    visitantes.getString("observaciones_visi"),
    new Image(visitantes.getBlob("foto_visi").getBinaryStream(), false)
)

Then in the change listener for the selected item in the table, you can simply do

imgfotovisi.setImage(valorSeleccionado.getFoto());

---

However, images are typically very large as Java objects, and this approach is likely to use an unreasonable amount of memory: in fact, if the list of items is reasonably large (at all), you run the risk of the program failing with OufOfMemoryErrors.

Instead, I would strongly recommend reconnecting to the database to retrieve the new image when the selection changes. (I.e. get the image on an as-needed basis.) You need to think about some concurrency issues here (discussed further down), and deal with the possibility of the selection changing faster than can load images from the database.

I assume that cedula is the primary key in the database, I would start with a method in your data accessor class:

public Image getImageById(String id) throws SQLException, IOException  {

    try (
        Connection con = ... ;
        PreparedStatement ps = con.prepareStatement(
            "SELECT foto_visi FROM visitantes WHERE cedula_visi = ?");
    ) {

        ps.setString(1, id);
        ResultSet results = ps.executeQuery();
        Image img = null ;
        if (results.next()) {
            Blob foto = results.getBlob("foto_visi");
            InputStream is = foto.getBinaryStream();
            img = new Image(is, false) ; // false = no background loading
            is.close();
        }
        results.close();
        return img ;
    }
}

Retrieving the image is time-consuming and should be performed on a background thread. Additionally, if the user selects a new item in the table when image retrieval is already in progress, you need to cancel the existing retrieval of the image (else you will end up with race conditions, possibly resulting in the display of the incorrect image when everything is done). A Service is perfect for this. Back in the controller, I would do:

private final Service<Image> imageRetrievalService = new Service<Image>() {
    @Override
    protected Task<Image> createTask() {
        final String id ;
        final Visitante visitante = tbvisitantes.getSelectionModel().getSelectedItem();
        if (visitante == null) {
            id = null ;
        } else {
            id = visitante.getCedula();
        }
        return new Task<Image>() {
            @Override
            protected Image call() throws Exception {
                if (id == null) {
                    return null ;
                }
                return dataAccessor.getImageById(id);
            }
        } ;
    }
};

Now you can do:

public void gestionarEventos(){

    tbvisitantes.getSelectionModel().selectedItemProperty().addListener(new ChangeListener<visitantes>() {



        @Override
        public void changed(ObservableValue<? extends visitantes> arg0,
                visitantes valorAnterior, visitantes valorSeleccionado) {

            // cancel any image retrieval and start new one:
            imageRetrievalService.restart();

            if (valorSeleccionado!=null){
                txtcedula.setText(valorSeleccionado.getcedula());
                txtnombres.setText(valorSeleccionado.getnombres();
                txtapellidos.setText(valorSeleccionado.getapellidos());
                txtconjunto.setText(valorSeleccionado.getconjunto()));
                txtapto.setText(valorSeleccionado.getapartamento());
                txtcelular.setText(valorSeleccionado.getcelular());
                txtobservaciones.setText(valorSeleccionado.getobservaciones());
            }
        }
    });

    imgfotovisi.imageProperty().bind(imageRetrievalService.valueProperty());

}

Finally, just remove the foto field and its get/set methods from the Visitante class.

Answer 2

This is the final solution to show pictures from DB Mysql saved as BLob.

in my ver_visitantes class I addes this

            private final Service<Image> imageRetrievalService = new Service<Image>() {
            @Override
            protected Task<Image> createTask() {
                final String id ;
                final visitantes visitante = tbvisitantes.getSelectionModel().getSelectedItem();
                if (visitante == null) {
                    id = null ;
                } else {
                    id = visitante.getcedula();
                }
                return new Task<Image>() {
                    @Override
                    protected Image call() throws Exception {
                        if (id == null) {
                            return null ;
                        }
                        return visitante.getImageById(id);
                    }
                };
            }
        };

and added this code to visitantes class

//getter image
            public Image getImageById(String id) throws SQLException, IOException  {
                try (
                        ConexionSQL cn = new ConexionSQL();
                        Connection con = cn.conexion();
                    PreparedStatement ps = con.prepareStatement(
                        "SELECT foto_visi FROM visitantes WHERE cedula_visi = ?");
                ) {
                    ps.setString(1, id);
                    ResultSet results = ps.executeQuery();
                    Image img = null ;
                    if (results.next()) {
                        Blob foto = results.getBlob("foto_visi");
                        InputStream is = foto.getBinaryStream();
                        img = new Image(is) ; // false = no background loading 
                        is.close();
                    }
                    results.close();
                    return img ;
                } catch (Throwable e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                return null;
            }//fin getter image

in this line

  img = new Image(is, false) ; // false = no background loading

with the James_D code show me an error , it was fixed removing the ",false" as eclipse suggest.

img = new Image(is);

And off course this line in my gestionarEventos();

imgfotovisi.imageProperty().bind(imageRetrievalService.valueProperty());