SQL statement to get the ID of a given entity a name

Question

SQL statement to get the ID of a given entity a name

Navigation

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

0

In SQL, I have a table called COMPANIAS with id_compania and nombre .

Given the name, in a variable, I want to find the id_compania that has exactly that name.

Method to obtain the ID:

public int companiaNombreToInt(String nombre) {
        int id = 0;

        sSQL =  "SELECT id_compania FROM companias WHERE nombre = '" + nombre + "'";

        // Java 7 try-with-resources
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sSQL)) {

            id = rs.getInt("id_compania");

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: companiaNombreToInt()", JOptionPane.ERROR_MESSAGE);
            }

        return id;
    }

Error:

The result set has not current row.

I tried:

name = '% name%'
name = '% + name +%'
name = '+ name +'

What is the correct way ?, or is my mistake another?.

Thanks in advance.

java jdbc

asked by Robert Gomez 12.11.2017 в 00:24

source

2 answers

How to block the focus of a control from advancing to the next control in wpf Error SQL Server 2008 NUMERIC_ROUNDABORT

score 1 · Answer 1

Your code is possibly not working due to the way you read the result. Although I want to take the answer to indicate that it has security flaws.

For queries with WHERE criteria whose data may come from abroad, the use of PreparedStatement is recommended, to avoid SQL injection.

To read the results, when there are several rows, it is a good idea to open a loop, since Resultset returns a pointer, to go from record to record. In any case, you must move the resultset to the first record . If you want a single result you can do a if (rs.next()){...

The documentation says about it :

A ResultSet object keeps a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row . The next() method moves the cursor to the next row, and as returns false when there are no more rows in the object ResultSet , may used in a while cycle to iterate through the set of results .

Keep in mind that you could get several results that meet the criteria, since several people can have the same name. Trying to determine a unique id for such an ambiguous data as the name is not a very good idea.

Be careful with the names of tables and columns, which in some cases are case sensitive depending on the operating system where the application will be used. That is, it is not the same COMPANIAS as companias . I say this because in the question you put it in uppercase, but in the query it appears in lowercase.

Here you have a revised version.

public int companiaNombreToInt(String nombre) {
    int id = 0;

    sSQL =  "SELECT id_compania FROM companias WHERE nombre = ?";

    // Java 7 try-with-resources
    try (

         PreparedStatement stmt = conn.prepareStatement(sSQL);
         stmt.setString(1,name);
         ResultSet rs = stmt.executeQuery(sSQL);
        ) 
    {


        if (rs.next()) { //Para leer varias posibles filas se cambia el while por el if
            id = rs.getInt("id_compania");
        }

    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, "SQLException:\n" + e.getMessage(), "Error: companiaNombreToInt()", JOptionPane.ERROR_MESSAGE);
    }

    return id;
}

score 0 · Answer 2

The short answer is that you never call ResultSet.next() . If you do not call ResultSet.next() , the result set that returns the query is positioned just before of the first result, hence the error you receive.

It seems that for a given name, you expect to receive a single id . For this to be true, I imagine that you have defined the field nombre with a unique constraint in your database. At least, I hope that's what you've done.

If this is the case, it's just a matter of adding a call to rs.next() and making sure you handle the case where you return false , so you can not find any record with that name. In the example that I leave below, I throw an exception. You can decide to do something different.

Also, I agree with @A. Cedano that it is not correct to concatenate variable nombre directly in the SQL. The correct thing to do is to use a PreparedStatement . If you keep doing it as you have it, try putting a quote to the name you are looking for so you can see how it throws you another error.

public int companiaNombreToInt(String nombre) {
    try (PreparedStatement stmt = conn.prepareStatement("SELECT id_compania FROM companias WHERE nombre = ?")) {
        stmt.setString(1, nombre);

        try(ResultSet rs = stmt.executeQuery()) {
            if (!rs.next()) {
                throw new SQLException("No se encontró registro con el nombre: " + nombre);
            }

            return rs.getInt("id_compania");
        }
    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: companiaNombreToInt()", JOptionPane.ERROR_MESSAGE);
    }
}