JavaBean SQL query to MySQL db, shows twice the results only in JSF_问答_开发者

I'm having a weird problem when querying a MySQL database from a Javabean and showing it in JSF. (Java code at the bottom)

The table contains the following registers:

mysql> select * from trescols;
+----+---------+---------+
| id | camp1 | camp2 |
+----+---------+---------+
| 1 | fila1A | fila1B |
| 2 | fila2A | fila2B |
| 3 | fila3A | fila3B |
| 4 | fila4A | fila4B |
...
| 20 | fila20A | fila20B |
+----+---------+---------+

Ok! Let's go with the process done until it failed:

First of all I queryed the db with a large query, and it didn't work, so I began with a simple query and added stuff step-by-step.

First of all, I queryed the db with the following query:

SELECT * FROM trescols;

Everything OK, it was showed well in the resultpage with JSF.

Next I queryed the following:

SELECT * FROM tabla WHERE id%2=1;

Everything OK, it just showed the records with odd id.

The problem came when I queryed:

SELECT * FROM tabla WHERE id%2=1 AND campo1 LIKE '%7%';

I expected it to show registers with odd id and containing the string '7' somewhere in "camp1" column.

The result with JSF has been:

ID Camp1   Camp2
7  fila7A  fila7B
17 fila17A fila17B
7  fila7A  fila7B
17 fila17A fila17B

While the same query from mysql cli returns:

mysql> select * from trescols where id%2=1 and camp1 LIKE '%7%';
+----+---------+---------+
| id | camp1   | camp2   |
+----+---------+---------+
| 7  | fila7A  | fila7B  |
| 17 | fila17A | fila17B |
+----+---------+---------+
2 rows in set (0.10 sec)

I added LIMIT 1, 1 to the query string and removed the id%2=1 AND, and returns:

ID Camp1   Camp2
17 fila17A fila17B
17 fila17A fila17B

The expected result was just to show once the second register, not twice. Removing the first condition was just to verify that having double condition wasn't the reason for the duplicated results and exclude a wrong SQL query.

Then I made a stored procedure as follows:

mysql> CREATE PROCEDURE getTrescols3()
-> BEGIN
-> SELECT * FROM trescols WHERE camp1 LIKE '%7%';
-> END
-> //

Calling this from MySQL CLI works fine:

mysql> call gettrescols3();
+----+---------+---------+
| id | camp1   | camp2   |
+----+---------+---------+
| 7  | fila7A  | fila7B  |
| 17 | fila17A | fila17B |
+----+---------+---------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

But calling it from JSF shows it twice again!!!

ID Camp1   Camp2
7  fila7A  fila7B
17 fila17A fila17B
7  fila7A  fila7B
17 fila17A fila17B

And this is where I'm lost... I could understand that the query could -by some reason altough it shouldn't- show duplicated results, then I started by a simple query adding sentences to the string until it showed duplicated results. With just one condition it worked fine, with two it showed duplicated results. Then I removed the first of the two conditions with which it didn't had any problem and left just the second condition, it showed duplicated results. Then I though that maybe java had any trouble with the condition <field> LIKE '%7%', so I created a stored procedure in MySQL so Java shouldn't process the querystring and just call the SP and get the result, called it from CLI and worked fine, called it fro开发者_StackOverflow中文版m java bean, and showed duplicated results.

I don't know what more tests to do...I discarded a wrong SQL query because in CLI everything worked as expected, and it just failed when adding the condition <field> LIKE '%7%' and just failed in JSF not with mysql cli, also I ruled out wrong query again because calling a SP from java bean and from cli, it worked in cli but not from java bean...

I was trying to get all results from table "trescols" where id%2=1 and where the field "camp1" contains a 7.

I'm using mysql jdbc connector to connect to db.

Could you help me, please.

Kind regards, Carles

Java code:

package beans;

import java.sql.*;
import java.util.*;

public class beanNomesRetorna {
Connection con;
Statement ps;
ResultSet rs;
private List llista = new ArrayList();

public List getLlista() {
    int i=0;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/provesjsf","root","pa$$word");
        ps = con.createStatement();
        rs = ps.executeQuery("SELECT * FROM trescols WHERE id%2 = 1 AND camp1 LIKE '%7%' ORDER BY id LIMIT 0,2");

        while(rs.next()) {
            System.out.println(rs.getString(1));
            llista.add(i, new agafaInfo(Integer.parseInt(rs.getString("id")), rs.getString("camp1"), rs.getString("camp2")));
            i++;
        } // while
    } catch(Exception e) {
        System.out.println(e);
    } // try ... catch

    return llista;
} // getLlista

package beans;

public class agafaInfo {
    int id;
    String camp1;
    String camp2;

    agafaInfo(int parid, String parcamp1, String parcamp2) {
        this.id = parid;
        this.camp1 = parcamp1;
        this.camp2 = parcamp2;
    }

    public int getParid() {
        return id;
    }

    public String getCamp1() {
        return camp1;
    }

    public String getCamp2() {
        return camp2;
    }
} // agafaInfo

Don't do the DB operations in the getter of a bean. Do it in the constructor of a bean. The symptoms indicate that you've a List property and are filling it inside the getter instead of the constructor. A getter can be called multiple times during bean's life. Its sole task should be returning the data, not doing some business stuff.

Update as I predicted, you're doing business stuff inside the getter. That's not how it works. Move that code into the constructor.

public class Bean {
    public List list;

    public Bean() {
        // Fill the list here.
    }

    public List getList() {
        return list; // Do nothing else in the getter!
    }
}