no How to get the last value inserted in the BD Oraclexe?

Question

no How to get the last value inserted in the BD Oraclexe?

Navigation

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

0

I am working in JEE, with WebLogic and OracleXE as a database engine, I am using a DataSource (NO JPA), in this database you have to create sequences to generate autoincremental values, I already did it and it works well when I execute it because I also create a triger to generate the value automatically although that is another issue.

From my application I can insert data into the database, but I need to obtain the value of that ID that I mentioned in the previous paragraph.

and this example that I take from the internet, it executes it and it does not work for me, it does not even insert the values:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate ....) VALUES (1, 1 ...) RETURNING OrderID INTO: last_insert_id

Any other suggestions?

HERE A LITTLE CODE

  > PreparedStatement sttmt = conn.prepareStatement("INSERT INTO fabricantes (NOMBRE, PAIS) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);

    String nombre = "Colo Colo";
    String pais = "Chile";

    sttmt.setString(1, nombre);
    sttmt.setString(2, pais);


    int resultado = sttmt.executeUpdate();

    // ResultSet rs = sttmt.getGeneratedKeys();
    Long id = null;
    ResultSet rs = sttmt.getGeneratedKeys();

        if (rs.next()) {
            id = rs.getLong(1);
            // Long id = generatedKeys.getLong(1);
        }

    return id;

The table also has a data COD_FABRICANTE that is not seen in the code of insertion because it is generated automatically, it is the primary key that I want to obtain precisely. The value of COD_FABRICANTE is generated automatically through a sequence that I create, in the Oracle 11g database there is no IDENTITY value or SERIAL value as in other Databases. By restriction of the WebLogic Server, I am using the JDK 6, the platform does not support higher JDK.

sql java oracle oracle-11g jdbc

asked by Ricardo Gabriel 31.10.2017 в 14:31

source

2 answers

0

By jdbc and% insert without returning :

try(PreparedStatement st= connection.prepareStatement(miInsert, Statement.RETURN_GENERATED_KEYS)){
    st.setInt(1,XXX);
    ...
    st.executeUpdate();

    try (ResultSet rs = st.getGeneratedKeys()) {
        if (rs.next()) {
            Long id = rs.getLong(1);
        }
    }
}

answered by 31.10.2017 в 14:36

Permalink change_list of the administrative panel in Django I try to update two records at the same time in android studio and php pdo

score 0 · Accepted Answer

One way that should work correctly is by wrapping your statement INSERT with BEGIN ... END; so that it becomes a PL / SQL block. This should allow the use of the RETURNING clause.

Then you use a CallableStatement to be able to register an output parameter.

Code example:

CallableStatement st = conn.prepareCall("BEGIN INSERT INTO fabricantes (NOMBRE, PAIS) VALUES (:nombre,:pais) RETURNING cod_fabricante INTO :cod_fabricante; END;");
st.setString("nombre", nombre);
st.setString("pais", pais);
st.registerOutParameter("cod_fabricante", java.sql.Types.BIGINT);
st.execute();
long id = st.getLong("cod_fabricante");