(Hibernate) java.sql.SQLException: Field 'xxxx' doesn't have a default value

Question

Hibernate is throwing the following exception:

Caused by: java.sql.SQLException: Field 'catVerb_id' doesn't have a default value

People say that the problem is with my PKs that haven't the AUTO_INCREMENT statement, however you can see that I've done this in my database and the issue continues. So, I brought my classes and my database implementations.

I think my problem is with the testing class... Somebody could show me how do I test this?

(Yes, some words are in Portuguese, but you can understand).

CategoriaVerbete

@Entity
@Table(name="verbete_categoria")
public class CategoriaVerbete implements Serializable{
    private long id;
    private String nome;
    private String descricao;
    private int serie;
    private Set<Verbete> verbetes = new HashSet<Verbete>();
    private Set<SignificadosVerbete> significados = new HashSet<SignificadosVerbete>();

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="catVerb_id")
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }

    ...

    @OneToMany(cascade = {CascadeType.ALL})
    @JoinColumn(name="catVerb_id", nullable = true) 
    public Set<Verbete> getVerbetes() {
        return verbetes;
    }
    public void setVerbetes(Set<Verbete> verbetes) {
        this.verbetes = verbetes;
    }

    @OneToMany(cascade = {CascadeType.ALL})
    @JoinColumn(name="catVerb_id", nullable = true)
    public Set<SignificadosVerbete> getSignificados() {
        return significados;
    }
    public void setSignificados(Set<SignificadosVerbete> significados) {
        this.significados = significados;
    }

}

Verbete

@Entity
@Table(name="verbete")
public class Verbete implements Serializable{
   ...

    @ManyToOne
    @JoinColumn(name="catVerb_id", insertable = false, updatable = false)
    public CategoriaVerbete getCategoria() {
        return categoria;
    }
    public void setCategoria(CategoriaVerbete categoria) {
        this.categoria = categoria;
    }

    ...
}

SignificadosVerbete

@Entity
@Table(name="verbete_significados")
public class SignificadosVerbete {
    ... 

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="catVerb_id", insertable = false, updatable = false)
    public CategoriaVerbete getCategoria() {
        return categoria;
    }
    public void setCategoria(CategoriaVerbete categoria) {
        this.categoria = categoria;
    }

   ...

}

In the database...

CREATE TABLE verbete_categoria (
catVerb_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
catVerb_nome VARCHAR(100) UNIQUE,
catVerb_descricao MEDIUMTEXT,
catVerb_serie INT NOT NULL
);

Any help will be appreciated, thank you.

UPDATE - PROBLEM SOLVED

Well, I was expecting a triumphant soundtrack, but it's ok...

I just followed what this link says:

https://www.ibm.com/developerworks/community/blogs/fd26864d-cb41-49cf-b719-d89c6b072893/entry/como_criar_relacionamento_onetomany_com_hibernate?lang=en

I read that what is in that link isn't recommended by the Hibernate documentation, but I tried to follow the recomendations and passed one week treating errors. So, I hope this helps other people.

And thanks for all the answers.

Answer 1

I believe it is the SignificadosVerbete insertion that is causing the cannot be null issue (I would strongly suggest you to turn on sql trace to see which query is causing problems).

It is a common problem for bi-directional one-to-many relationships. What you have is something like:

class Foo {
    @Id
    @Column("foo_id")
    Long id;

    @OneToMany(cascade=ALL)
    @JoinColumn("foo_id")
    Set<Bar> bars;
}

class Bar {
    @Id
    @Column("bar_id")
    Long id;

    @ManyToOne
    @JoinColumn("foo_id", insertable=false, updatable=false)
    Foo foo;
}

This kind of mapping will cause the relationship to be owned by Foo, and this will create multiple insert+update statements: When you are inserting a Foo, with 2 Bars, what will happen is that it will first insert that Foo, and 2 Bar without the FK foo_id. Then two updates will be issued for each Bar to update the correct foo_id in the Bar table.

Normally we do not map bi-directional OneToMany mappings this way, we do this instead:

class Foo {
    @Id
    @Column("foo_id")
    Long id;

    @OneToMany(mappedBy="foo", cascade=ALL)
    Set<Bar> bars;
}

class Bar {
    @Id
    @Column("bar_id")
    Long id;

    @ManyToOne
    @JoinColumn("foo_id")
    Foo foo;
}

By doing so, the relationship is owned by Bar, and when you are inserting as I described , you will be inserting 1 Foo, and 2 Bars that already have the foo_id as part of insert.

That's how we normally do bi-directional relationships in Hibernate.

Answer 2

I agree with the link provided. It worked following the link. As I understood, removing the below part did work for me.

Before:

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="catVerb_id", insertable = false, updatable = false)

After:

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="catVerb_id")

Answer 3

java.sql.SQLException: Field 'xxxx' doesn't have a default value

( aka. indirect Fetch.LAZY load issue )

I ran into this problem today.

Turned out to be a couple of issues in my situation.

The first thing I found that had to be fixed was that the List in question needed to be LAZY LOADED. And by LAZY LOADED I mean it has to be accessed from the database when the database "is in session". Now there is a little trick I found you have to do when you are dealing with Fetch.LAZY, such that you need to (seemingly needlessly) call a method on the referenced object when you load it into the container. It's not enough to just return the reference, even inside the session object.

For example, instead of just saying this:

public List<Apple> getApples(Kid kid) throws KidNotFoundException 
{
    log.info("getApples (service) called"); 
    kid = get(kid);
    List<Apple> list = kid.getApples();
    return list;
}

You have to add one more seemingly insignificant step. To trigger JPA's Lazy Load mechanism you need to call a method on the referenced object itself (and it only works when inside a JPA Transaction, whether direct or implied via a @Stateless tag). Till you do, JPA will be doing it's best not to load unnecessary objects into memory ( aka. the whole idea behind Fetch.LAZY ).

So in this case I call a method on the retrieved ( Fetch.LAZY designation ) object 'list' :

 list.size();

Any method on the object can be called to trigger JPA to load the object (or objects), in this case I just called the list.size() method was just so convenient, it doesn't look out of place as much as just calling a seemingly needless kid.getName(); from a professional standpoint of view. How I wish a good tutorial on Fetch.LAZY could have pointed all this out to me. Fodder for another book I guess ...

This forces JPA to actually load the referenced object from the database.

public List<Apple> getApples(Kid kid) throws KidNotFoundException 
{
    log.info("getApples (service) called"); 
    kid = get(kid);
    List<Apple> list = kid.getApples();
    list.size();
    return list;
}

That was the first issue generating the 'java.sql.SQLException: Field 'xxxx' doesn't have a default value' message in my case.

The second issue had to do with a improperly configured @ManyToMany relationship between the two tables. Today, it took about five minutes to set up what I thought was a many-to-many relationship from a previously created prototype I had. It took about six hours of hair pulling to get the kinks worked out. In any event, this is what I ended up doing to resolve my situation.

/**
 * "Buckets"
 * an Apple can be give to 0-to-n Kids ... 
 */

@ManyToMany(cascade=CascadeType.PERSIST)
@JoinTable(name="Buckets",
    joinColumns=@JoinColumn(name="Apple_id"),
    inverseJoinColumns=@JoinColumn(name="Kid_id"))
private List<Kid> kids;

public List<Kid> getKids() {
    return kids;
}

public void setKids(List<Kid> kids) {
    this.kids = kids;
}

. . .

/**
 * a Kid can have 0-n Apples
 */

@ManyToMany(mappedBy="kids", cascade=CascadeType.PERSIST)
private List<Apple> apples;

That and the fact that my original @ManyToMany setup was referencing the wrong table contributed to this error in my case. But the simple fixing of the LAZY LOAD situation resolved the 'java.sql.SQLException: Field 'xxxx' doesn't have a default value' problem immediately.

Perry

Ps. After all day, I did learn some neat tricks hidden beneath JPA's rough exterior ( aka. surfing the web and reading docs on how to use @ManyToMany relationships efficiently ). Such that the above configuration will create a third database table dynamically that looks as good as though you created it by hand. It also enforces unique relationship between Kids and Apples as far as a one-to-one relationship is concerned. As well as create only one extra table in the database, instead of needing two identical tables in the database. In effect, in my case I had 2 tables before this @ManyToMany construct:

Kids Apples

As a result of the @ManyTo@Many construct listed just as above I now have 3:

Kids Apples Buckets

Inside the Buckets table there are only two fields, Apple_id and Kid_id and what it important is that when I add an entry to both:

kids.add(apple);
apple.add(kids);

There is only ONE record in the Buckets table and not two! Which is very important if you are trying to use the List<> in place of a Set<>, ( aka. unique ).