Software Engineering

Dynamic Query on Save or Update Spring JPA

Save or update the table on database is a common activity in software engineering. Doing insert and update query is a simple query, yes I agree. For the first time it’s easy to use, But as time goes by we will find it a little odd. In this article we will find it for the details, and what’s the impact with your data in you database.

By default when we call save(...) function in your Repository. It will do a insert or update query depend on your data you want to save. If the data has it’s Primary Key, it will do a update query, otherwise Spring will find it as a new data because it has no Primary Key reference so Spring will do a insert query. But did you know what’s exactly the query that Spring execute?

First thing we need a Spring application, follow this article to create it. Then, assuming we have below table on our database

create table user(
	id int primary key auto_increment,
	name varchar(256) default null,
	address varchar(256) default null,
	last_login datetime default current_timestamp
);

Just a simple table that contains ID as the primary key, name, address, and last_login which has current_timestamp as its default value. We all agree that default identifier in the query says that if the field is not contained in the query it will insert the default value instead. So if we do a query

insert into user (name) values ("Spring Boot");

The result will be like this, last login will automatically inserted with current timestamp.

IDNameAddressLast Login
1Spring BootNULL2021-02-13 09:33:48

So, Where’s The Odd Thing?

Alright, let’s start with create an entity to represent user table above.

@Entity
@Table(name = "user")
data class UserEntity(

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null,

    @Column(name = "name")
    var name: String? = null,

    @Column(name = "address")
    var address: String? = null,

    @Column(name = "last_login")
    var lastLogin: Date? = null
)

A common entity code with @table annotation contains the table fields in our database. Don’t forget to create a Repository for this entity.

@Repository
interface UserRepository : JpaRepository<UserEntity, Long>

Then, call it like this

fun add() {
        val data = UserEntity(
            name = "Rio Swarawan",
            address = "Yogyakarta"
        )
        userRepository.save(data)     
    }

Do we have the same expectation of this result? I tried to execute this code from my brain, when I call add() function it will do:

  • Create an entity variable that contains name and address, put it into a variable named data.
  • Then repository will save the data.
  • Because it has no ID, it means an insert query.
  • Because it has no lastLogin value, it means last_login will store a current_timestamp as default.

Unfortunately, our expectation will not happened accordingly. Which point? Yes, the last point will not happen. How it could be?

Here’s the Odd Thing!!!

Try to add this configuration in our application.properties

spring.jpa.show-sql=true

With this line, Spring will print all the query happened in our application. Then, call the add() once again. What is it say? Yes…

insert into user (address, last_login, name) values (?, ?, ?)

Feel the odd thing??? Yes….

  1. Why is last_login called?
    Because we have last_login field in our entity. Spring tried to call all the field in the entity, regardless of the value. It means, if we put any value, it will store to the field. Otherwise, it will store as NULL. Yes.. NULL

    Then, in MySQL query there’s a different between NOT CALL THE FIELD and CALL WITH NULL VALUE. Both has difference result, the first one, MySQL will put the default value if any. Second, MySQL think that NULL is the value, so it will put as NULL.
  2. So, what’s the value of the last_login?
    In our data variable, we did not call lastLogin, so it will store as NULL. Then read point 1 again.

SOLUTION: Don’t Put Into Entity

Sure, with this action Spring do not recognize that the field is exist. So it will not be called in any query. But still, depend on our requirement of the business flow. Because sometimes we need to put new or different value even the field already has default value.

SOLUTION: Put a Dynamic Annotation

This is an interest one. We can do a dynamic query on insert and update query. This means that we can ignore the NULL value field into our query. Long story short, we only put NON-NULL value into our query. Just put @DynamicInsert and/or @DynamicUpdate on entity class level. So, we can update our Entity to be like this

@Entity
@Table(name = "user")
@DynamicInsert
@DynamicUpdate
data class UserEntity(

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null,

    @Column(name = "name")
    var name: String? = null,

    @Column(name = "address")
    var address: String? = null,

    @Column(name = "last_login")
    var lastLogin: Date? = null

)

After adding those annotations, I will tried to call our add() function again, and the query printed on console below

insert into user (address, name) values (?, ?)

This is what we need, only inserting into 2 fields instead.

Wisdom (wanna be) Note:
Working with Spring Framework is like adventure into magical world, magical things can happen with only one or two annotations. Sometimes we find the another odd things, just believe that Spring can do that things and keep searching for the solution.