I have tried nearly every combination in .hbm.xml and cannot get nullable one-to-one AKA many-to-one to work

[asdf_username]

PLEASE HELP I have tried for days now to get this simple nullable relationship to work in hibernate to no avail.

In fact, I spent 3 hours simply preparing this question to be as simple as possible without a bunch of db columns that aren’t relevant to the problem, by commenting out countless lines of code, dropping the db and recreating the db with fewer columns in these tables, removing an update trigger that was on this table, etc. (tested after and still got the same errors.)

The relationship is as follows.

-A company can have a PIA agreement, but is not required to have one.
→ Based on this, I have concluded I need to use ‘many-to-one’ in Company.hbm.xml, because although a company can only have a max of one pia agreement, it seems you can’t “null” the realtionship if you make it one-to-one, so I’m forced to save a company with a pia if I use one-to-one. Even when using “many-to-one” I am facing issues when updating the Company regardless of if a Pia has been set on the object or not.

After exhausting what I could find on the internet myself I have been going back and forth with ChatGPT for almost 10 hours now, and have made 50+ build attempts to try to find something that works, with no luck.

I have a feeling it’s something wrong in either of the two .hbm.xml files for these tables.

Create Company table mysql code:

CREATE TABLE company (
  id SMALLINT AUTO_INCREMENT,
  name varchar(60) default NULL,
  PRIMARY KEY  (id)
) Engine=InnoDB;
COMMIT;

Create Pia table mysql code:

CREATE TABLE pia (
  company_id smallint,
  agreement_number varchar(50),
  PRIMARY KEY (company_id),
  FOREIGN KEY (company_id) REFERENCES company (id)
) Engine=InnoDB;
COMMIT;

As of right now I have these entries in the two tables (scroll down for image, was only allowed to attach 1 image)

So Company has 1 entry, but Pia has none.

Currently, when I try to update the company with the same info that’s already in there now, this happens (see request body in image, “#1” in red) :

WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions()] (default task-19) SQL Error: 1048, SQLState: 23000
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions()] (default task-19) (conn=81) Column 'id' cannot be null
ERROR [org.hibernate.internal.ExceptionMapperStandardImpl.mapManagedFlushFailure()] (default task-19) HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]

(see null pointer exception too, but in a different log)

And when I update the company to now have a PIA agreement, this happens (see request body in image, “#2” in red):

  java.lang.NullPointerException

**In both cases, **
it fails on the “template.saveOrUpdate(company);” line below.
I believe I can call saveOrUpdate() to handle more scenarios than just .save() and .update() according to chatgpt, such as if the company already exists but the pia agreement needs to be inserted.

HCompanyDAOImpl.java

@Component
public class HCompanyDAOImpl extends HBaseDAOImpl implements CompanyDAO {

    public HCompanyDAOImpl(HibernateTemplate template) {
        this.template = template;
    }
    
    @Transactional
    @Override
    public int updateCompany(Company company) {
        int result = 0;
        try {
            template.saveOrUpdate(company);
            result = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    ... other methods 
}

HBaseDAOImpl.java

 public abstract class HBaseDAOImpl {
     protected HibernateTemplate template;
 }

CompanyRestController.java

@RequestMapping(value = "/updateCompanyData", method = RequestMethod.POST)
public void updateCompanyData(@RequestBody Company company) {
    companyDelegate.updateCompanyData(company);
}

CompanyDelegate.java

public void updateCompanyData(Company company) {
    Company co = companyDAO.findCompanyByCompanyId(company.getId());
    companyDAO.updateCompany(company);
}

Company.hbm.xml

<class name="com.something.something.something.Company" table="COMPANY">
    <id name="id" column="ID">
        <generator class="increment"/>
    </id>

    <property name="name" column="NAME"/>

    <many-to-one name="pia" class="com.something.something.something.Pia" outer-join="true" not-found="ignore" not-null="false" cascade="save-update">
        <column name="id" />
    </many-to-one>
</class>

Pia.hbm.xml

<class name="com.something.something.something.Pia" table="PIA">
    <id name="companyId" column="company_id">
        <generator class="foreign">
            <param name="property">company</param>
        </generator>
    </id>

    <property name="agreementNumber" column="AGREEMENT_NUMBER"/>
</class>

Company.java (POJO)

public class Company {

    private Integer id;
    private String name;
    private Pia pia = null;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public void setPia(Pia pia) { this.pia = pia; }
    public Pia getPia() {
        return pia;
    }


    @Override
    public boolean equals(Object o) {
        if (this == o)
            return true;
        if (o == null || getClass() != o.getClass())
            return false;

        Company company = (Company) o;

        if (id != company.id)
            return false;
        if (name != null ? !name.equals(company.name) : company.name != null)
            return false;
        if (pia != null ? !pia.equals(company.pia) : company.pia != null)
            return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = id;
        result = 31 * result + (name != null ? name.hashCode() : 0);
        result = 31 * result + (pia != null ? pia.hashCode() : 0);
        return result;
    }
}

Pia.java (POJO)

public class Pia {

    private Integer companyId;
    private String agreementNumber;

    public Integer getCompanyId() {
        return companyId;
    }
    public void setCompanyId(Integer companyId) {
        this.companyId = companyId;
    }

    public String getAgreementNumber() {
        return agreementNumber;
    }
    public void setAgreementNumber(String agreementNumber) {
        this.agreementNumber = agreementNumber;
    }

}

Any help or pointers as to what could be possibly going wrong would be greatly appreciated as I am officially out of ideas

[beikov]

I can’t really help you a lot with the hbm.xml parts, but in general, you have two options for modelling this.

1. Put the FK on Company and allow lazy loading
2. Put the FK on Pia and force eager loading

For 1), the model would look roughly like this:

@Entity
public class Company {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    @OneToOne(fetch = LAZY)
    @JoinColumn(name = "pia_id")
    private Pia pia;
}
@Entity
public class Pia {
    @Id
    @GeneratedValue
    private Integer id;
    private String agreementNumber;
}

which results in the table model:

CREATE TABLE company (
  id SMALLINT AUTO_INCREMENT,
  name varchar(60) default NULL,
  pia_id SMALLINT
  PRIMARY KEY  (id),
  FOREIGN KEY (pia_id) REFERENCES pia (id)
) Engine=InnoDB;

CREATE TABLE pia (
  id smallint,
  agreement_number varchar(50),
  PRIMARY KEY (id)
) Engine=InnoDB;

For 2) you can either model it in the same class (but different table) by using @SecondaryTable, or using the DDL you were suggesting:

@Entity
public class Company {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    @OneToOne(mappedBy = "company")
    private Pia pia;
}
@Entity
public class Pia {
    @Id
    @OneToOne(fetch = LAZY)
    private Company company;
    private String agreementNumber;
}

[asdf_username]

Sadly since our product was configured with Hibernate using XML mappings instead of the @ annotations, I’m not sure how much this can help me. But switching to annotations has been something I’ve been considering for a while now, but for the amount of work it would be I would want the benefits to be worth it.

Do IDE’s like Intellij (which I use) offer more help figuring out what needs to go where than XML, because for the XML mapping I get 0 help from my IDE (and little to no help from the hibernate documentation) so I’m basically just rebuilding hundreds of times hoping that I’m getting the right combination… concepts like “.flush()”, “eager” fetching, “detached” entities, “sessions”, “transactions”, “lazy” still making no sense after spending days trying to understand them, I’m just trying to select update and delete from the dang db… This has been more complicated than trying to understand my insurance policy. I want to know what all these things mean, and why they exist, but I feel like I am not able to find this information written in even a relatively simple manner given the resources that are out there. And even places like stack-overflow are not able to explain things in a simple manner. It’s as if people understand hibernate completely or they don’t understand it at all, and the people who understand it completely are not explaining it in simple terms, which is making me question whether they really understand it at all.

Does the foreign key need to be declared in the company table instead of the pia table?
According to the mysql documentation the FK needs to be listed in the table that is the child, and in my example the child is the Pia table

Also, it seems I shouldn’t fetch the children pia agreement “lazy”, but rather “eager” (I think?), because I would like the child pia to be included automatically in the select statement results if a pia does exist for a given company

[beikov]

Learning new concepts isn’t easy, but there are plenty of books on JPA and Hibernate that explain the overall idea and go into details of various mappings. I figured that for me, the fact that I want to get something concrete done always stood in the way in the past. It’s hard that when you have a way of thinking about a problem to let yourself go and just follow tutorials to learn something new, but I guess that’s the hard part about learning, you have to rewire your brain

I can try to help you a bit, but since an ORM is an abstraction built on top of JDBC/SQL, you usually also have to understand SQL and modelling concepts to fully grasp how things work.

The JPA annotation model follows the language of the concepts, so it might be easier for you to just use that in order to understand how certain configurations affect the underlying SQL.

In short, an entity has a lifecycle. It can be in the states “detached”, “new”, “managed” and “removed”. “new” means that the object has no identifier assigned yet. “detached” means that it has an identifier assigned, but isn’t known to the “persistence context”. The “persistence context” is managed by the Hibernate Session (in JPA this is called EntityManager) object and is essentially a mapping of "entity primary key identity" => "entity object". This means that there can only be a single managed object in a “persistence context” that maps to a row in a table. This is important, because other SQL abstractions for CRUD sometimes suffer lost updates because they lack this, though the concept also has some downsides.
A “managed” entity is something that is registered in the “persistence context” and “removed” means that a remove operation was called for that object, meaning that the row was logically deleted.
The job of Hibernate/JPA is to synchronize the “persistence context” with the database in an efficient fashion. Synchronize already implies that the “persistence context” might contain data that the database does not yet know about, and it is the “flushing” that forces these data changes to be sent over to the database via SQL statements. Since SQL statements are all bound to a transaction (explicit or implicit for a single statement), it is important to understand that flushing is usually bound to happen within a transaction, but can also happen multiple times within a transaction.
In order to add a new object to the database, one calls EntityManager#persist on a “new” entity, which will make that object “managed”. To update an object, call EntityManager#merge on a “detached” entity, or just change the state of an already “managed” entity object. The “managed” entity representing the current state can be fetched by calling EntityManager#find. After you changed the state of the entity object, Hibernate can at some later point determine that the entity needs flushing, and usually flushing automatically happens right before commit.

“lazy” just means that an entity object is not directly read from the database, but instead a proxy object is created, which is initialized on first access of any data other than the primary key. Imagine loading a Child entity which has a foreign key to the parent table, modeled as a many-to-one association to the Parent entity. marking the association as “eager” means that whenever you load a Child object, it has to immediately also load the Parent object for that association. This can happen through a join in the SQL select for the Child or through a separate SQL select that reads the row from the parent table by primary key. Since “eager” loading incurs an overhead, especially if you load a lot of Child entities, it is generally better to specify “lazy” loading. You can usually control the eagerness separately by specifying an “entity graph” as what JPA calls “load graph” or using “join fetch” in the JPQL/HQL query. Another alternative is to get rid of lazy loading altogether by using a DTO approach where you specify exactly the data that you require instead of loading the whole entity with all of it’s associations to ignore parts of it later in the process.

Does the foreign key need to be declared in the company table instead of the pia table?

That is something you need to decide for your model. Another option could be to not have a separate table at all and just extend the company table by the new column. There is always a tradeoff.

According to the mysql documentation the FK needs to be listed in the table that is the child, and in my example the child is the Pia table

This is no classical parent-child relationship which would be modeled as many-to-one. You are modelling a one-to-one association. You can model it either way, but usually it is good to ask yourself the question if one can exist without the other. If “pia” can only exist for a particular “company”, then people usually say that “pia” is compositionally dependent on “company”, meaning that “pia” has no identity. Modelling that can be done by either moving the data from “pia” to the “company” table or adding a foreign key column to the “pia” table that refers to the “company” table (like you did).

On the Java side you can model this as a single class, by using @SecondaryTable and specifying @Column(name = agreement_number, table = "pia"), or like I suggested:

@Entity
public class Company {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    @OneToOne(mappedBy = "company")
    private Pia pia;
}
@Entity
public class Pia {
    @Id
    @OneToOne(fetch = LAZY)
    private Company company;
    private String agreementNumber;
}

Also, it seems I shouldn’t fetch the children pia agreement “lazy”, but rather “eager” (I think?), because I would like the child pia to be included automatically in the select statement results if a pia does exist for a given company

The fetch config of Pia#company only makes sense if you load a Pia object in isolation without the Company being part of the “persistence context”. If you load Pia in isolation, “eager” would always force a join to the company table in the SQL select, whereas “lazy” would only fetch the foreign key column and only trigger a separate SQL select statement if the “persistence context” does not contain the Company already, and also only if you access data other than the primary key of the Company.