How to recover the id inserted with callablestatement from a storeProcedure in java

Question

How to recover the id inserted with callablestatement from a storeProcedure in java

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

good day, I hope and you can help me, what happens is that I want to recover the id inserted when I execute a function in postgresql, but the problem is that it brings a 0 since the sp runs and adds it to the view of the datatable but then I reload the page and add it to me with the maximum Id (of the inserted record), what I want more than anything is to recover that inserted id and add it to the view, this is my code where I send to call the sp:

   public int guardarPelicula(moviesDTO peliculas) {
        int ultimoRegistro =0;
        try {
            CallableStatement prcProcedimientoAlmacenado = conn.getConnection().prepareCall("{ call sp_insert_update_peliculas(?,?,?)}");
            prcProcedimientoAlmacenado.setInt(1, peliculas.getId_movie());
            System.out.println("Que traes 3.." + peliculas.getId_movie());
            prcProcedimientoAlmacenado.setString(2, peliculas.getMovie_name());
            prcProcedimientoAlmacenado.setInt(3, peliculas.getMovie_year());
            System.out.println("Executando Store Procedure Insert..");
            prcProcedimientoAlmacenado.execute();
            System.out.println("Que traes2..." + ultimoRegistro);
            return ultimoRegistro;

        } catch (SQLException ex) {
            Logger.getLogger(MovieDAOImplements.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

And this is my SP:

-- FUNCTION: public.sp_insert_update_peliculas(integer, character varying, integer)

-- DROP FUNCTION public.sp_insert_update_peliculas(integer, character varying, integer);

CREATE OR REPLACE FUNCTION public.sp_insert_update_peliculas(
    out_id_movie integer,
    out_nombrepelicula character varying,
    out_aniopelicula integer)
    RETURNS TABLE(mensaje character varying, success boolean, out_idmovies integer) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

DECLARE
var_mensaje character varying;
var_success boolean;
var_max_idmovie int;
var_n integer;
BEGIN
        IF(out_id_movie =0)THEN
        var_max_idmovie := (SELECT COALESCE (MAX(id_movie)+1,1) 
        FROM cat_movies);
        INSERT INTO cat_movies(
            id_movie,
            movie_name,
            movie_year)
            VALUES (var_max_idmovie,
                    $2,
                    $3);        
        GET DIAGNOSTICS var_n= ROW_COUNT;
                GET DIAGNOSTICS var_n = ROW_COUNT;
                IF(var_n>0)THEN
                    var_success=true;
                    var_mensaje='Registro agregado satisfactoriamente.';
                ELSE
                    var_success=false;
                    var_mensaje='Error en el sistema favor de contactar a su administrador.';

END IF;
ELSE
    UPDATE cat_movies 
    SET movie_name=$2, 
        movie_year=$3 
            WHERE id_movie=$1; 
            GET DIAGNOSTICS var_n= ROW_COUNT;
                GET DIAGNOSTICS var_n = ROW_COUNT;
                IF(var_n>0)THEN
                    var_success=true;
                    var_mensaje='Registro ACTUALIZADO satisfactoriamente.';
                ELSE
                    var_success=false;
                    var_mensaje='Error en el sistema favor de contactar a su administrador.';

END IF;

    RETURN QUERY SELECT var_mensaje,var_success, var_max_idmovie;
END IF;
END;

$BODY$;

ALTER FUNCTION public.sp_insert_update_peliculas(integer, character varying, integer)
    OWNER TO postgres;

In advance, I would appreciate it if you can help me, greetings and stay tuned.

java postgresql

asked by cratus666jose 01.11.2018 в 19:43

source

2 answers

Why are 65535 logical ports? [closed] take part of an Android Studio string

score 1 · Answer 1

To return the last id inserted in postgres (or any other information in the registry) you can use the clause RETURNING

DECLARE
  v_id INTEGER;
  ...
BEGIN
  ...
  INSERT INTO cat_movies (...) VALUES (...) RETURNING id INTO v_id;

score 0 · Answer 2

Call to sp from java:

 public int guardarPelicula(moviesDTO peliculas) {
        int ultimoRegistro = 0;
        try {
            CallableStatement prcProcedimientoAlmacenado = conn.getConnection().prepareCall("{ call sp_insert_update_peliculas(?,?,?)}");

            prcProcedimientoAlmacenado.setInt(1, peliculas.getId_movie());
            prcProcedimientoAlmacenado.setString(2, peliculas.getMovie_name());
            prcProcedimientoAlmacenado.setInt(3, peliculas.getMovie_year());
            ResultSet rs = prcProcedimientoAlmacenado.executeQuery();
            while (rs.next()) {
                ultimoRegistro = rs.getInt("out_idmovies");
            }


            // prcProcedimientoAlmacenado.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(MovieDAOImplements.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }

        return ultimoRegistro;

    }

On the bean:

 public void insertarPelicula() {

    if (peliculas.getMovie_name() == null || peliculas.getMovie_name().equals("")) {
        FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Debe poner un nombre de pelicula."));
        RequestContext.getCurrentInstance().update("messages");
        RequestContext.getCurrentInstance().execute("ocultaMsj(3000)");

    } else if (peliculas.getMovie_year() == null) {
        FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Debe poner un año de pelicula."));
        RequestContext.getCurrentInstance().update("messages");
        RequestContext.getCurrentInstance().execute("ocultaMsj(3000)");
    } else {

        //Valida si es insert
        if (peliculas.getId_movie() == 0) {

            //En la variable ultimoRegistro, desde el dao se asigna el valor nuevo.
            int ultimoRegistro = dao.guardarPelicula(peliculas);
            peliculas.setId_movie(ultimoRegistro);
            peliculasList.add(peliculas);
            peliculasFitradas.add(peliculas);

            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Exito: ", "Pelicula agregada exitosamente."));
            RequestContext.getCurrentInstance().update("messages");
            RequestContext.getCurrentInstance().execute("ocultaMsj(3000)");

            //En caso contrario actualiza.
        } else {

            for (int i = 0; i < peliculasList.size(); i++) {
                if (peliculasList.get(i).getId_movie() == peliculas.getId_movie()) {

                    peliculasList.get(i).setMovie_name(peliculas.getMovie_name());
                    peliculasList.get(i).setMovie_year(peliculas.getMovie_year());
                }
                if (peliculasList.get(i).getId_movie() == peliculas.getId_movie()) {

                    peliculasList.get(i).setMovie_name(peliculas.getMovie_name());
                    peliculasList.get(i).setMovie_year(peliculas.getMovie_year());
                }

            }

            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Exito: ", "Pelicula actualizada exitosamente."));
            RequestContext.getCurrentInstance().update("messages");
            RequestContext.getCurrentInstance().execute("ocultaMsj(3000)");
            Cancelar();
        }

    }

}

I hope someone will greet you.