title: JavaBean SQL query to MySQL db, shows twice the results only in JSF
body: <p>I'm having a weird problem when querying a MySQL database from a Javabean and showing it in JSF. (Java code at the bottom)</p>
  
  <p>The table contains the following registers:</p>
  
  <pre><code>mysql&gt; select * from trescols;
  +----+---------+---------+
  | id | camp1 | camp2 |
  +----+---------+---------+
  | 1 | fila1A | fila1B |
  | 2 | fila2A | fila2B |
  | 3 | fila3A | fila3B |
  | 4 | fila4A | fila4B |
  ...
  | 20 | fila20A | fila20B |
  +----+---------+---------+
  </code></pre>
  
  <p>Ok! Let's go with the process done until it failed:</p>
  
  <p>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.</p>
  
  <p>First of all, I queryed the db with the following query:</p>
  
  <pre><code>SELECT * FROM trescols;
  </code></pre>
  
  <p>Everything OK, it was showed well in the resultpage with JSF.</p>
  
  <p>Next I queryed the following:</p>
  
  <pre><code>SELECT * FROM tabla WHERE id%2=1;
  </code></pre>
  
  <p>Everything OK, it just showed the records with odd id.</p>
  
  <p>The problem came when I queryed:</p>
  
  <pre><code>SELECT * FROM tabla WHERE id%2=1 AND campo1 LIKE '%7%';
  </code></pre>
  
  <p>I expected it to show registers with odd id and containing the string '7' somewhere in "camp1" column.</p>
  
  <p>The result with JSF has been:</p>
  
  <pre><code>ID Camp1   Camp2
  7  fila7A  fila7B
  17 fila17A fila17B
  7  fila7A  fila7B
  17 fila17A fila17B 
  </code></pre>
  
  <p>While the same query from mysql cli returns:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>I added <code>LIMIT 1, 1</code> to the query string and removed the <code>id%2=1 AND</code>, and returns:</p>
  
  <pre><code>ID Camp1   Camp2
  17 fila17A fila17B
  17 fila17A fila17B 
  </code></pre>
  
  <p>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.</p>
  
  <p>Then I made a stored procedure as follows:</p>
  
  <pre><code>mysql&gt; CREATE PROCEDURE getTrescols3()
  -&gt; BEGIN
  -&gt; SELECT * FROM trescols WHERE camp1 LIKE '%7%';
  -&gt; END
  -&gt; //
  </code></pre>
  
  <p>Calling this from MySQL CLI works fine:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>But calling it from JSF shows it twice again!!!</p>
  
  <pre><code>ID Camp1   Camp2
  7  fila7A  fila7B
  17 fila17A fila17B
  7  fila7A  fila7B
  17 fila17A fila17B 
  </code></pre>
  
  <p>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 <code>&lt;field&gt; LIKE '%7%'</code>, 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 from java bean, and showed duplicated results.</p>
  
  <p>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 <code>&lt;field&gt; LIKE '%7%'</code> 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...</p>
  
  <p>I was trying to get all results from table "trescols" where <code>id%2=1</code> and where the field "camp1" contains a 7.</p>
  
  <p>I'm using mysql jdbc connector to connect to db.</p>
  
  <p>Could you help me, please.</p>
  
  <p>Kind regards,
  Carles</p>
  
  <p>Java code:</p>
  
  <pre><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
  </code></pre>
  
  <hr>
  
  <pre><code>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
  </code></pre>