CODEWITHSUNDEEP
javamysqljdbcresultset
Lowb
Lowb

Reputation: 172

Java Resultset.getInt() not returning properly?

I'm making a program that has to alert an user if some threshold has passed for an ammount of money.

I've made a method to check if the user has been alerted already for that threshold so we dont repeat alerts, for that I've setup a MySQL table that fills with 1s and 0s based on whether the alert has been sent already or not.

This is the table:

mysql> describe avisos;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| CLI            | varchar(9) | NO   | PRI | NULL    |       |
| mes            | int(2)     | YES  |     | NULL    |       |
| AvisadoPrimero | int(11)    | NO   |     | NULL    |       |
| AvisadoSegundo | int(11)    | NO   |     | NULL    |       |
| AvisadoTercero | int(11)    | NO   |     | NULL    |       |
| AvisadoCuarto  | int(11)    | NO   |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

However when in my program I do a query to check if the alarm was already sent, no matter what, the code will always return 0 (hence stating that the alarm was not sent).

Here you can see the value for the field I'm looking for:

mysql> SELECT AvisadoPrimero from avisos where CLI=#########; //(hidden for security reasons)
+----------------+
| AvisadoPrimero |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

And here is my code that runs within my program:

         try {
        Statement sentencia = conexion.createStatement();
        PreparedStatement avisosQuery = conexion.prepareStatement(avisosString);
        PreparedStatement subtotalesQuery = conexion.prepareStatement(subtotalesString);
        //ResultSet sobrepasados = sentencia.executeQuery("SELECT cli FROM subtotales where Precio >=" + umbralInferior + " and Precio <" + umbralSuperior + ";");
        subtotalesQuery.setInt(1, umbralInferior);
        subtotalesQuery.setInt(2, umbralSuperior);
        sobrepasados = subtotalesQuery.executeQuery();
        while (sobrepasados.next()) {
            clis.add(sobrepasados.getString("CLI"));
        }
        if (!clis.isEmpty()) {
            for (int i = 0; i < clis.size(); i++) {

                switch (umbralInferior) {
                    case UMBRAL1:
                        avisosQuery.setString(1, "AvisadoPrimero");
                        avisosQuery.setString(2,clis.get(i));
                        resultado = avisosQuery.executeQuery();

                        resultado.first();

                        //System.out.println("Entrando en prueba\n\nConsulta realizada:");
                        //System.out.println(resultado.getInt("CLI"));
                        System.out.println(resultado.getInt(1));

So, as you can see given the table with the field set as 1, the last print will always print 0.

Any tips? What am I doing wrong?

==============

Finally, by using the PreparedStatement and fixing the column name, worked out.

(also I believe there was something wrong with some trigger in the MySQL table that was initializing to 0 the field always, but this is unconfirmed)

Here's the final code:

try {
        Statement sentencia = conexion.createStatement();
        PreparedStatement avisosQuery = conexion.prepareStatement(avisosString);
        PreparedStatement subtotalesQuery = conexion.prepareStatement(subtotalesString);
        //ResultSet sobrepasados = sentencia.executeQuery("SELECT cli FROM subtotales where Precio >=" + umbralInferior + " and Precio <" + umbralSuperior + ";");
        subtotalesQuery.setInt(1, umbralInferior);
        subtotalesQuery.setInt(2, umbralSuperior);
        sobrepasados = subtotalesQuery.executeQuery();
        while (sobrepasados.next()) {
            clis.add(sobrepasados.getString("CLI"));
        }
        if (!clis.isEmpty()) {
            for (int i = 0; i < clis.size(); i++) {

                switch (umbralInferior) {
                    case UMBRAL1:
                        //avisosQuery.setString(1, "AvisadoPrimero");
                        avisosQuery.setString(1,clis.get(i));
                        resultado = avisosQuery.executeQuery();

                        resultado.first();

                        //System.out.println("Entrando en prueba\n\nConsulta realizada:");
                        //System.out.println(resultado.getInt("CLI"));
                        System.out.println(resultado.getBoolean(1));

Thanks all!!

Upvotes: 0

Views: 3314

Answers (1)

Joni
Joni

Reputation: 111269

MySQL prepared statements don't allow parametric column or table names. The code, as it stands now, selects the string "AvisadoPrimero" rather than the value of the column. That is, when you write:

PreparedStatement avisosQuery = conexion.prepareStatement(
        "SELECT ? FROM avisos WHERE CLI=?");
avisosQuery.setString(1, "AvisadoPrimero");
avisosQuery.setString(2,clis.get(i));

the database receives:

SELECT "AvisadoPrimero" FROM avisos WHERE CLI=something

Then the call to getInt fails because the text "AvisadoPrimero" cannot be converted to a number.

Upvotes: 1

Related Questions