1 Reply Latest reply: May 21, 2013 1:49 PM by cdelahun RSS

    JPA 2.0:Columns inserted as nulls while the table have multiple foreign key

    1009712
      I have issues with multple foriegn key in third table i.e.,ROLE_MENUS_TBL while insert/update on menu_id,rol_id columns through jpa.These columns was inserted/updated like Menu id added value and role_id is added a null value. in the database table.

      I added both entities i..e,menutbl,roletble into rolemenutbl entity and check the below code snippet

      I added both entities i..e,menutbl,roletble into rolemenutbl entity and check the below code snippet.

      Code :-


      ....
      Rolemenustbl roleMenusTbl=new Rolemenustbl();
      menuTbl.setMenuTbl(selectedMenuItem);
      roleTbl.setRoleTbl(selectedRoleItem);
      getService().merge(roleMenusTbl);


      The above code was setting the values into selectedMenuItem,selectedRoleItem entities.But why null was inserted into role_id and i checked with hibernate generated sql query was not add role_id column in insert query.i mentioned below entites and sql script


      @Entity
      @Table(name = "MENU_TBL")
      public class MenuTbl {
      private Long menuid;
      private String menucd;
      private String menunm;
      private String menuurl;
      private List<Rolemenustbl> roleMenuList;

      @LazyCollection(LazyCollectionOption.FALSE)
      @OneToMany(mappedBy="menuTbl")
      public List<Rolemenustbl> getRoleMenuList() {
      return roleMenuList;
      }
      public void setRoleMenuList(List<Rolemenustbl> roleMenuList) {
      this.roleMenuList = roleMenuList;
      }
      @Override
      @Column(name = "menu_id", precision = 19)
      @GeneratedValue
      @Id
      public Long getMenuid() {
      return menuid;
      }
      public void setMenuid(Long menuid) {
      this.menuid = menuid;
      }
      //remaing attribute setter and getters
      }



      @Entity
      @Table(name = "ROLE_TBL")
      public class RoleTbl {
      private Long roleid;
      private String roleNm,roleDesc;
      private List<Rolemenustbl> roleMenuList;

      @LazyCollection(LazyCollectionOption.FALSE)
      @OneToMany(mappedBy="roleTbl")

      public List<Rolemenustbl> getRoleMenuList() {
      return roleMenuList;
      }
      public void setRoleMenuList(List<Rolemenustbl> roleMenuList) {
      this.roleMenuList = roleMenuList;
      }

      @Override
      @Column(name = "role_id", precision = 19)
      @GeneratedValue
      @Id
      public Long getRoleid() {
      return roleid;
      }
      public void setRoleid(Long roleid) {
      this.roleid = roleid;
      }
      //remaing attribute setter and getters

      }



      @Entity
      @Table(name = "ROLE_MENUS_TBL")
      public class Rolemenustbl {
      private Long rolemenuTblid;
      private String createdBy,updatedBy;
      private Date createddate,updateddate;
      private MenuTbl menuTbl;
      private RoleTbl roleTbl;

      @JoinColumn(name = "role_id", nullable = false)
      @ManyToOne
      public RoleTbl getRoleTbl() {
      return roleTbl;
      }
      public void setRoleTbl(RoleTbl roleTbl) {
      this.roleTbl = roleTbl;
      }
      @NotNull
      @JoinColumn(name = "menu_id", nullable = false)
      @ManyToOne
      public MenuTbl getMenuTbl() {
      return menuTbl;
      }
      public void setMenuTbl(MenuTbl menuTbl) {
      this.menuTbl = menuTbl;
      }

      @Override
      @Column(name = "ROLE_MENUS_TBL_id", precision = 19)
      @GeneratedValue
      @Id
      public Long getRolemenuTblid() {
      return rolemenuTblid;
      }
      public void setRolemenuTblid(Long rolemenuTblid) {
      this.rolemenuTblid = rolemenuTblid;
      }
      //remaing attribute setter and getters


      }


      and sql script :-

      CREATE TABLE `MENU_TBL` (
      BIGINT(20) NOT NULL AUTO_INCREMENT,
      `menu_cd` VARCHAR(60) NOT NULL,
      `menu_nm` VARCHAR(240) NOT NULL,
      `menu_url` LONGTEXT NOT NULL,
      PRIMARY KEY (`menu_def_id`),
      INDEX `MENU_TBL_fk_idx` (`menu_def_id`),
      )

      CREATE TABLE `ROLE_TBL` (
      `ROLE_ID` INT(11) NOT NULL AUTO_INCREMENT,
      `ROLE_NAME` VARCHAR(45) NULL DEFAULT NULL,
      `ROLE_DSC` VARCHAR(450) NULL DEFAULT NULL,
      PRIMARY KEY (`ROLE_ID`)
      )

      CREATE TABLE `ROLE_MENUS_TBL` (
      `ROLE_MENUS_TBL_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
      `menu_id` BIGINT(20) NOT NULL,
      `role_id` INT(11) NULL DEFAULT NULL,
      `create_by` VARCHAR(45) NOT NULL,
      `create_date` DATETIME NOT NULL,
      `update_by` VARCHAR(45) NULL DEFAULT NULL,
      `update_date` DATETIME NULL DEFAULT NULL,
      PRIMARY KEY (`ROLE_MENUS_TBL_id`),
      INDEX `ROLE_MENUS_TBL_idx` (`menu_id`),
      INDEX `ROLE_MENUS_TBL_fk1_idx` (`role_id`),
      CONSTRAINT `ROLE_MENUS_TBL_fk1` FOREIGN KEY (`role_id`) REFERENCES `ROLE_TBL` (`ROLE_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT `ROLE_MENUS_TBL_fk` FOREIGN KEY (`menu_id`) REFERENCES `MENU_TBL` (`MENU_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
      )

      Why the role_id is not added in the hibernate sql query while calling merge/persist data?. Is there any issue with associations while using multiple foreign keys?
        • 1. Re: JPA 2.0:Columns inserted as nulls while the table have multiple foreign key
          cdelahun
          First, you are using a different JPA provider - you might want to post your product questions on a forum for your product as this is a TopLink/JPA forum.

          Second, your code:
          Rolemenustbl roleMenusTbl=new Rolemenustbl();
          menuTbl.setMenuTbl(selectedMenuItem);
          roleTbl.setRoleTbl(selectedRoleItem);
          getService().merge(roleMenusTbl);
          is hard to follow, but looking at it, you have not set anything in the roleMenusTbl that you are calling merge on. So everything should be null. You are in essence calling:
          Rolemenustbl roleMenusTbl=new Rolemenustbl();
          getService().merge(roleMenusTbl);
          Unless you call roleMenusTbl.setRoleTbl(roleTbl), "ROLE_MENUS_TBL.role_id" is expected to be null after the flush/commit.