Java.Sql.Sqlexception: Field Doesn't Have a Default Value

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

The error is self explanatory. Your column supplier_id does not have a default value. So during insertion, mysql cannot figure out what to insert in the column supplier_id. You can do either of the three things :-

1. Add a default value to the column supplier_id Using -

ALTER TABLE `xxx` ALTER `supplier_id` SET DEFAULT NULL

2. Supply some value to the supplier_id column during insertion.


3. Add an auto increment to the column and add a primary key to it using the code :-

ALTER TABLE `xxx` CHANGE `supplier_id` `supplier_id` INT(10)AUTO_INCREMENT PRIMARY KEY;

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

Have you tried to assign a default value to it through SQL query?
If you already have a database GUI ( like MySQL workbench )you can just execute this additional query that will set the field to have a default value.

ALTER TABLE `checking_point` ALTER `cp_type_id` SET DEFAULT NULL

Or you can alter your sql query that you already have

create table checking_point(
    id int primary key NOT NULL auto_increment,
    name_str_id int,
    foreign key(name_str_id) references localization(id),
    description_str_id varchar(500),
    cp_type_id int DEFAULT NULL, <-HERE
    foreign key(cp_type_id) references dim_cp_type(id),
    cp_category_group_id int,
    foreign key(cp_category_group_id) references cp_category_group(id),
    repeat_type_id int,
    foreign key(repeat_type_id) references dim_repeat_type(id),
    cp_repeat_reminder_id int,
    is_template boolean,
    is_top_template boolean,
    db_create_dts date,
    db_create_source nvarchar(320),
    db_updated_dts date,
    db_update_source nvarchar(320)
);

With assigning the te default value, you will stop getting these types of exceptions for cp_type_id, same approach goes for any other field that it is complaining about.

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

Hibernate

Hibernate is the interface/link/glue/middleware between your application and the database. It's one of the many framework/libraries, out there, that is making ORM possible and easy. Keyword here is easy, as it makes our lives really simple!

The idea behind Hibernate's naming strategies is to map your POJO(class/model), in your case UserEntity to its respective physical(actual) DB table as seamless as possible(magical, if you will). So, if you name your logical field userId in your POJO, Hibernate needs to be able to map it to the respective physical table column name, example user_id.

Hibernate naming strategies

If you do not explicitly specify the column name(i.e @Column(name = "userid"), Hibernate determines a proper logical name defined by the ImplicitNamingStrategy. If you choose the default/jpa strategy, for basic attributes, it uses the name of the attributes as the logical name. Ideally, all you have to do is:

@Column
private String userId;

So userId is mapped logically to userId implicitly. It further resolves this proper logical name to a physical name, defined by the PhysicalNamingStrategy. By default, Hibernate uses the logical name as the physical name, but the real purpose of the PhysicalNamingStrategy is to say that physical column userId is actually abbreviated to user_id.

Spring Boot

The good news is that Spring Boot provides defaults for both strategies:

spring.jpa.hibernate.naming.physical-strategy defaults to
org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

spring.jpa.hibernate.naming.implicit-strategy defaults to
org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy

By default, Spring Boot configures the physical naming strategy with CamelCaseToUnderscoresNamingStrategy, this strategy will:

-Change camel-case to snake case(Good if your DB users table column name is user_id.

-Replace dots with underscores.

-Lower-case table names, but it is possible to override that flag if your schema requires it.

You could explicitly(it's Spring Boot's default) add it as a Hibernate property:

@Configuration
public class HibernateConfiguration {

    Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
    emf.setDataSource(dataSource);
    emf.setJpaProperties(hibernateProperties());

Or choose the strategy that best match your use-case. This and this are excellent to learn more about naming strategies.

Easy, long-winded, but clear

The easiest, although not recommended, unless you have no choice(Will you be doing this for all the attributes of all your @Entity classes?), would be to explicitly defined the actual table's column name, in your UserEntity class:

@Column(name = "userid")
private String userId;

How should you name your columns?

Fabricio mentions this: Better yet if you follow the convention of using underscore separated names for your db columns(i.e snake-case) and camel case names for your Java entity properties. This is also Spring conventions:

@Column
private String userId;

DEBUG

During development, you can turn on debugging to see what's really going on by adding this in your application.properties:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE
spring.jpa.properties.hibernate.format_sql=true

EDIT:

The main issue was that the column was set to @Column(nullable = false), but i will leave all the notes above as it's informative.

Java Entity java.sql.SQLException: Field 'id' doesn't have a default value

Remove the id field from the database

---

Related Topics