Hi everybody. I have made a view in my MySQL Database to fetch some data to present to the user, but when I try to retrieve the data from that view with an entity what I get is not the same that I can see in my database. Here is the code to the view:
Code:
DROP VIEW IF EXISTS `copladem`.`confdependencias`;CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `confdependencias` AS select `configuraciones`.`Nombre` AS `Configuracion`,`dependencias`.`nombre` AS `Dependencia`,if((count(`configuracionesdependencias`.`idConfiguracionesDependencias`) > 0),'SI','NO') AS `Existe` from ((`dependencias` join `configuraciones` on((`configuraciones`.`idConfiguracion` = `configuraciones`.`idConfiguracion`))) left join `configuracionesdependencias` on(((`configuracionesdependencias`.`configuracion` = `configuraciones`.`idConfiguracion`) and (`configuracionesdependencias`.`dependencia` = `dependencias`.`idDependencia`)))) group by `dependencias`.`nombre`,`configuraciones`.`Nombre` order by `configuraciones`.`Nombre`;
With this view, executed in MySQL Query Browser, I get the follow:
configuracion dependencia existe
2009 Secretaría Municipal y del Ayuntamiento SI
2009 Contraloría Municipal SI
2010 Secretaría Municipal y del Ayuntamiento SI
2010 Contraloría Municipal NO
That's just what I want, so I generated an entity from this view using netbeans, it looks like this:
Code:
@Entity
@Table(name = "confdependencias")
@NamedQueries({
@NamedQuery(name = "Confdependencias.findAll", query = "SELECT c FROM Confdependencias c"),
@NamedQuery(name = "Confdependencias.findByConfiguracion", query = "SELECT c FROM Confdependencias c WHERE c.configuracion = :configuracion"),
@NamedQuery(name = "Confdependencias.findByDependencia", query = "SELECT c FROM Confdependencias c WHERE c.dependencia = :dependencia"),
@NamedQuery(name = "Confdependencias.findByExiste", query = "SELECT c FROM Confdependencias c WHERE c.existe = :existe")})
public class Confdependencias implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name = "Configuracion")
private String configuracion;
@Basic(optional = false)
@Column(name = "Dependencia")
private String dependencia;
@Basic(optional = false)
@Column(name = "Existe")
private String existe;
/*CONSTRUCTOR, GETTER's AND SETTER's*/
}
And then I Tried to get data using this:
Code:
Query q;
q = em.createNamedQuery("Confdependencias.findAll");
But when I see the q.getResultList() method, it returns a vector of Confdependencias objects with the follow data:
configuracion dependencia existe
2009 Contraloría Municipal SI
2009 Contraloría Municipal SI
2010 Contraloría Municipal SI
2010 Contraloría Municipal SI
As You can see, it's not the information that the view shows in the database, so, what I'm doing wrong? Thanks for your help
Login
FAQ
Search