Java array problem to read from database

Question

I have a Java function where I weigh a phone number and it returns me which trunk the call will exit according to the regular expression that is saved in database in a column called "exten_discador".

If this query of mine can get n results, with it I will have to use an array storing $array[operadora] = id_tronco.

Should my code enter the if I need to see which operator was obtained from the database and search the id_tronco array.

Example:

------------------------------------------------------------------------
exten_discador                  id_tronco    Operadora
'^(00\d{2}[7-9]\d{7,8})$'           1           GVT
'^(00\d{2}[7-9]\d{7,8})$'           87          NET

---------------------------------------------------------------------------

My validation of the expression exten_discador is working ok, my problem and time to store in an array the carrier with stem value: $array[operadora] = id_tronco.

Here is my code:

public Integer getTroncoChamada(String numero) throws SQLException {

    Integer retorno = null;

    String sqlExten = "SELECT "
                 + " r.id_rota,"
                 + " r.rota,"
                 + " reo.exten_discador,"
                 + " reot.remove_left,"
                 + " reot.remove_right,"
                 + " reot.add_left,"
                 + " reot.add_right,"
                 + " reot.operadora,"
                 + " reot.prioridade,"
                 + " t.id_tronco,"
                 + " t.tronco,"
                 + " t.dial"
                 + " FROM asterisk.rota r"
                 + " LEFT JOIN asterisk.rota_exten_out reo ON (r.id_rota = reo.id_rota)"
                 + " LEFT JOIN asterisk.rota_exten_out_tronco reot ON (reo.id_rota_exten_out = reot.id_rota_exten_out)"
                 + " LEFT JOIN asterisk.tronco t ON (t.id_tronco = reot.id_tronco)"
                 + " WHERE r.habilitar = 1"
                 + " AND reot.datahora_fim is null"
                 + " AND reo.exten_discador is not null"
                 + " AND t.habilitar = 1"
                 + " AND r.tipo_rota = 'OUT' ";


    PreparedStatement psExten = connection.prepareStatement(sqlExten);
    ResultSet rsExten = psExten.executeQuery();

    String exten_discador;
    int i = 1;
    int[] id_tronco = null;
    String operadora;
    String operadora_numero;
    Integer prioridade = 0;

    while (rsExten.next()) {

        exten_discador = rsExten.getString("exten_discador");


        id_tronco[Integer.parseInt(rsExten.getString("operadora"))] = rsExten.getInt("id_tronco");  /*  PROBLEM  */

        if(numero.trim().matches(exten_discador)){

            if(i>1){
                operadora_numero = "GVT";
                operadora = "^"+rsExten.getString("operadora")+"$";                 
                prioridade = rsExten.getInt("prioridade");
                if(operadora_numero.trim().matches(operadora)){
                    retorno =  id_tronco[Integer.parseInt(rsExten.getString("operadora"))];  **/*  PROBLEM  */**
                }

            }else{
                retorno = rsExten.getInt("id_tronco");
                i++;
            }

        }

    }

Answer 1

The problem you are reporting

Look at this, here the array is null:

int[] id_tronco = null;

If he is null, then what will happen here?

id_tronco[Integer.parseInt(rsExten.getString("operadora"))] = rsExten.getInt("id_tronco");

Will give a java.lang.NullPointerException! You cannot access a position of an array that is null, because null not even a conversation starter array.

In fact, there is yet another problem. Before giving NullPointerException, will give a java.lang.NumberFormatException. If the field operadora can be "GVT" or "NET", try to give a Integer.parseInt that won’t work!

Other problems with your code

Also note this:

operadora_numero = "GVT";
// ...
operadora = "^"+rsExten.getString("operadora")+"$"
if(operadora_numero.trim().matches(operadora)){
    // ...

This is one of the worst ways to abuse regular expressions I have ever seen. Basically the purpose of all this is just to check if the operator in question is GVT. That would be a lot easier to do:

if ("GVT".equals(rsExten.getString("operadora")) {
    // ...

Your code also has other problems:

• There is no need to build SQL this way within the method since always the same SQL will be generated.

• You don’t close the PreparedStatement and the ResultSet properly. To do this, it is recommended to use the Try-with-Resources.

• The fact of your line id_tronco[Integer.parseInt(rsExten.getString("operadora"))] = rsExten.getInt("id_tronco"); be before the line if(numero.trim().matches(exten_discador)){ can probably pollute your array with id_troncowhich do not match the regular expression of exten_discador. That must not be what you want.

• The very idea of using integers read from the database as indexes of an array is a bad idea, as there is no guarantee that you will be accessing the array at valid positions.

• You seem to want to use the name of the carrier as the array key. Force the carrier name to be an integer with a Integer.parseInt won’t work and won’t help you, but use a Map would be a much better idea. I think your $array[operadora] = id_tronco indicates this.

• However, you only read data from the array if the key is "GVT", which means that you seem to be trying to read the array always from the same position. Now, if you only access the array at a single position, then you don’t even need an array at all and a simple variable with a idTroncoGVT would solve.

• The variable i only serves to separate the first element of the results from the others, assuming only the values 1 and 2. Use a boolean would be more efficient. If you want, you could also use, the method ResultSet.getRow() to obtain the line number on ResultSet.

• Since the priority will never be null, then there’s no reason for her to be Integer. Use the type int.

• You do SELECT in the columns r.id_rota, r.rota, reot.remove_left, reot.remove_right, reot.add_left, reot.add_right, t.tronco and t.dial and does not use any of these columns for anything.

• You have not posted the whole code of your method. I will assume that you have missed only one return retorno; }. If more is missing, there may be more problems than that.

• You read the field prioridade database and does nothing with it, assuming that you do not use it at the end of the missing method.

What you really wanted

From what I understand what you really want to do is:

Return the field value id_tronco of a line on which a telephone number is accepted by the field regular expression exten_discador. A id_tronco GVT operator, choosing some other only if you have none of GVT.

That is to say, you asked the wrong question! What you have is a example of the XY problem, where you asked about its logic in placing elements in the array, this was not your real problem.

You haven’t said what your database is, but almost all databases have built-in regular expression features and you can take advantage of the ORDER BY. So, assuming you’re using Mysql or Mariadb, you could do it all this way, using the operator REGEXP (source):

private static final String SQL_TRONCO_CHAMADA =
        "SELECT t.id_tronco"
             + " FROM asterisk.rota r"
             + " LEFT JOIN asterisk.rota_exten_out reo ON (r.id_rota = reo.id_rota)"
             + " LEFT JOIN asterisk.rota_exten_out_tronco reot ON (reo.id_rota_exten_out = reot.id_rota_exten_out)"
             + " LEFT JOIN asterisk.tronco t ON (t.id_tronco = reot.id_tronco)"
             + " WHERE r.habilitar = 1"
             + " AND reot.datahora_fim is null"
             + " AND reo.exten_discador is not null"
             + " AND t.habilitar = 1"
             + " AND r.tipo_rota = 'OUT'"
             + " AND ? REGEXP reo.extenDiscador"
             + " ORDER BY reot.operadora = 'GVT' DESC, t.id_tronco ASC";

public Integer getTroncoChamada(String numero) throws SQLException {
    try (PreparedStatement psExten = connection.prepareStatement(SQL_TRONCO_CHAMADA)) {
        psExten.setString(1, numero);
        try (ResultSet rsExten = psExten.executeQuery()) {
            if (!rsExten.next()) return null;
            return rsExten.getInt("id_tronco");
        }
    }
}

If you are using Oracle, then you can change the "AND ? REGEXP reo.extenDiscador" for "AND REGEXP_LIKE (?, reo.extenDiscador)" (source).

If you are in Postgresql, change the "AND ? REGEXP reo.extenDiscador" for "AND ? ~ reo.extenDiscador" (source).

If you’re using Firebird, change the "AND ? REGEXP reo.extenDiscador" for "AND ? SIMILAR TO reo.extenDiscador" (source).

SQL Server does not come with built-in regular expression features, but with a little bit of work, you can also convince it to accept them. 1, 2. Depending on which of these functions you install, you will end up using a "AND dbo.RegExpLike(?, reo.extenDiscador)" or something similar.

There’s one more detail in the code: You don’t make it clear where the variable comes from connection, then it may be that your connection is not being handled properly. Ideally you would have a method getConnection() that would open a connection. Having this, you would modify the beginning of the first try from the above code to look like this:

    try (
        Connection conn = getConnection();
        PreparedStatement psExten = conn.prepareStatement(SQL_TRONCO_CHAMADA);
    ) {

Ah, and as for your question, one last detail is that you speak in $array. That notation with the $ does not exist in Java. This is a notation of the PHP world, but since PHP is not Java, this notation should not be used in Java.