java - JPA query not returning expected result set -
in project i'm working have ejb persistence unit multiple entitys have update entities complex id (2 fields forming id) , change query not behave in way expect. have entity correspond intermediate table relationship many 1 one of entities changed.
publicic class bapeconvcatezonas implements serializable { private static final long serialversionuid = 1l; @size(max = 1) @column(name = "b_interinidad") private string binterinidad; @size(max = 1) @column(name = "b_sustit_larga") private string bsustitlarga; @size(max = 1) @column(name = "b_sustit_corta") private string bsustitcorta; @id @basic(optional = false) @notnull @column(name = "c_codigo") private long ccodigo; @joincolumns({ @joincolumn(name = "c_cod_gsa", referencedcolumnname = "c_cod_gsa", updatable=false,insertable=false), @joincolumn(name = "c_cod_centro", referencedcolumnname = "c_codigo", updatable=false,insertable=false)}) @manytoone private bapecentrossanitarios bapecentrossanitarios; @joincolumns({ @joincolumn(name = "c_cod_conv", referencedcolumnname = "c_cod_conv"), @joincolumn(name = "c_cod_cate", referencedcolumnname = "c_cod_cate")}) @manytoone(optional = false) private bapeconvcatetopes bapeconvcatetopes; @joincolumns({ @joincolumn(name = "c_cod_gsa", referencedcolumnname = "c_cod_gsa", updatable=false,insertable=false), @joincolumn(name = "c_cod_centro", referencedcolumnname = "c_cod_centro", updatable=false,insertable=false), @joincolumn(name = "c_cod_depto", referencedcolumnname = "c_codigo", updatable=false,insertable=false)}) @manytoone private bapedeptsanitarios bapedeptsanitarios; @joincolumn(name = "c_cod_gsa", referencedcolumnname = "c_codigo", updatable=false,insertable=false) @manytoone private bapegerenciasaludarea ccodgsa; and entity changed:
public class bapecentrossanitarios implements serializable { @onetomany(mappedby = "bapecentrossanitarios") private list<bapeconvcatezonas> bapeconvcatezonaslist; private static final long serialversionuid = 1l; @embeddedid protected bapecentrossanitariospk bapecentrossanitariospk; @basic(optional = false) @notnull @size(min = 1, max = 150) @column(name = "a_descripcion") private string adescripcion; @joincolumn(name = "c_cod_gsa", referencedcolumnname = "c_codigo", insertable = false, updatable = false) @manytoone(optional = false) private bapegerenciasaludarea bapegerenciasaludarea; @onetomany(cascade = cascadetype.all, mappedby = "bapecentrossanitarios") private list<bapedeptsanitarios> bapedeptsanitarioslist; the id in entity changed from:
@id @basic(optional = false) @notnull @size(min = 1, max = 4) @column(name = "c_codigo") private string ccodigo; to:
@embeddedid protected bapecentrossanitariospk bapecentrossanitariospk; and:
@embeddable public class bapecentrossanitariospk implements serializable {
@basic(optional = false) @notnull @size(min = 1, max = 4) @column(name = "c_codigo") private string ccodigo; @basic(optional = false) @notnull @size(min = 1, max = 4) @column(name = "c_cod_gsa") private string ccodgsa; and when execute query not returns anything:
sql: select u bapeconvcatezonas u u.bapeconvcatetopes.bapeconvcatetopespk.ccodconv=:convocatoria , u.bapeconvcatetopes.bapeconvcatetopespk.ccodcate=:categoria , u.bapecentrossanitarios null , u.binterinidad='s' order u.ccodgsa.ccodigo asc; bapeconvcatetopes entity: public class bapeconvcatetopes implements serializable {
@column(name = "n_anio_finalizacion") private short naniofinalizacion; @jointable(name = "bape_conv_cate_meri_sub", joincolumns = { @joincolumn(name = "c_cod_conv", referencedcolumnname = "c_cod_conv"), @joincolumn(name = "c_cod_cate", referencedcolumnname = "c_cod_cate")}, inversejoincolumns = { @joincolumn(name = "c_cod_area", referencedcolumnname = "c_cod_area"), @joincolumn(name = "c_cod_tipo", referencedcolumnname = "c_cod_tipo"), @joincolumn(name = "c_cod_subtipo", referencedcolumnname = "c_codigo")}) @manytomany @orderby("bapemeritossubtipospk.ccodarea asc") private list<bapemeritossubtipos> bapemeritossubtiposlist; @jointable(name = "bape_conv_cate_titu_expe", joincolumns = { @joincolumn(name = "c_cod_conv", referencedcolumnname = "c_cod_conv"), @joincolumn(name = "c_cod_cate", referencedcolumnname = "c_cod_cate")}, inversejoincolumns = { @joincolumn(name = "c_cod_titu", referencedcolumnname = "c_codigo")}) @manytomany private list<bapetitulaexper> bapetitulaexpercollection; @joincolumn(name = "c_cod_conv", referencedcolumnname = "c_codigo", insertable = false, updatable = false) @manytoone(optional = false) private bapeconvocatorias bapeconvocatorias; @joincolumn(name = "c_cod_cate", referencedcolumnname = "c_codigo", insertable = false, updatable = false) @manytoone(optional = false) private bapecategorias bapecategorias; private static final long serialversionuid = 1l; @embeddedid protected bapeconvcatetopespk bapeconvcatetopespk; @basic(optional = false) @notnull @size(min = 1, max = 1) @column(name = "b_activo") private string bactivo; @basic(optional = false) @notnull @size(min = 1, max = 1) @column(name = "b_notif_automatica") private string bnotifautomatica; // @max(value=?) @min(value=?)//if know range of decimal fields consider using these annotations enforce field validation @column(name = "tope") private bigdecimal tope; @onetomany(cascade = cascadetype.refresh, mappedby = "bapeconvcatetopes") @ordercolumn(name="c_cod_gsa") private list<bapeconvcatezonas> bapeconvcatezonaslist; and bapeconvcatetopespk:
@embeddable public class bapeconvcatetopespk implements serializable { @basic(optional = false) @notnull @column(name = "c_cod_conv") private long ccodconv; @basic(optional = false) @notnull @column(name = "c_cod_cate") private long ccodcate; the table has values meet criteria. based on last change has related relationship , complex id don't quit grasp be. edit: added little more information id change.
while id changes confound issue, jpql being used incorrect. " u.bapecentrossanitarios null" forcing inner join on tables. inner join filters out nulls, clause can never true, , no results returned.
the query should use left outer join on relationship. like:
"select u bapeconvcatezonas u left outer join u.bapecentrossanitarios bapecentrossanitarios u.bapeconvcatetopes.bapeconvcatetopespk.ccodconv=:convocatoria , u.bapeconvcatetopes.bapeconvcatetopespk.ccodcate=:categoria , bapecentrossanitarios null , u.binterinidad='s' order u.ccodgsa.ccodigo asc" will work, though there might many ways form similar query.
the reason might have worked first changes compound pk bypassing eclipselink optimization allows evaluate 'u.bapecentrossanitarios null' without peforming join - check pk value if null. changes, 1 or more of foreign keys may or may not null (they not controlled mapping), must join tables sure.
Comments
Post a Comment