Multitenant configuration: StaleObjectStateException on Transaction (hibernate + spring-data-jpa)

i'm trying to setup a configuration for manage a multi-tenant enviroment with spring-boot, spring-data-jpa, hibernate and mysql (same schema, every tenant table has a tenant_code column). For dependencies the parent maven project is&nbsp;<strong>spring-boot-starter-parent</strong>&nbsp;(2.1.2.RELEASE).

i'm trying to setup a configuration for manage a multi-tenant enviroment with spring-boot, spring-data-jpa, hibernate and mysql (same schema, every tenant table has a tenant_code column). For dependencies the parent maven project is spring-boot-starter-parent (2.1.2.RELEASE).

On save entity hibernate throws this exception: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) :

The highlights steps are:

  1. Intercept "tenant code" with custom spring HandlerInterceptorAdapter (i read oauth2 token and extract custom attribute "tenant code");
  2. Save "tenant code" in a @RequestScope bean; 

  3. On Save
  4. Define an custom hibernate EmptyInterceptor to intercept onSave action and previously set the "tenant code" (taken on a @RequestScope bean) 

  5. On Read
  6. In abstract Entity (extended by all my entities) i define @Filter and set it via AOP

If in interceptor i override onSave method i get this exception:

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [com.test.module.api.domain.entity.User#22]
    at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:2522) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3355) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3229) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3630) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:146) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:532) ~[spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.4.RELEASE.jar:5.1.4.RELEASE]
    at com.test.module.api.service.UserServiceImpl$$EnhancerBySpringCGLIB$$baf4f87e.updateUser(<generated>) ~[classes/:na]
    at com.test.module.api.web.UserController.updateUser(UserController.java:38) ~[classes/:na]


Here the code: 

Spring Interceptor from Request Header:

    @Component
    public class TokenInterceptor extends HandlerInterceptorAdapter {
        public final String TENANT_CODE = "tenant_code";
    @Autowired
    private CurrentUser currentUser;

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {
        String tenantCode = ... extract tenant code from request...;
        currentUser.setTenantCode(tenantCode);        
        return true;
    }
}


Class to setup inteceptor in hibernate:

    @Component
public class TenantHibernateInterceptorCustomizer implements HibernatePropertiesCustomizer {

    @Autowired
    private TenantJpaInterceptor jpaInterceptor;

    @Override
    public void customize(Map&lt;String, Object&gt; hibernateProperties) {
        hibernateProperties.put("hibernate.session_factory.interceptor", jpaInterceptor);
    }

}


Hibernate custom interceptor:

    @Component
public class TenantJpaInterceptor extends EmptyInterceptor {
//Comment to test if problem is about spring context
//@Autowired
//private CurrentUser currentUser;

    @Override
    public boolean onSave(Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) {
        if (entity instanceof TenantAuditable) {
            //((TenantAuditable&lt;?&gt;) entity).setTenandCode(currentUser.getTenantCode());
            //if i comment this line everythings works (without tenant code)
            ((TenantAuditable&lt;?&gt;) entity).setTenandCode("TEST");            
        }
        return false;
    }
}


Service layer:

    @Transactional
public UpsertResponse<UserDto> updateUser(UserDto userDto) {
UpsertResponse<UserDto> validationResult = CommonValidators.validateUserDto(userDto);
if(!validationResult.isValidated()) {
return validationResult;

    SpUser user = userRepo.findById(userDto.getUserId()).orElse(new SpUser());
    userMapper.mapUserDtoToEntity(userDto, user);
    try {
        SpUser savedUser = userRepo.save(user);
        validationResult.setEntity(userMapper.mapUserDtoFromEntity(savedUser));
        validationResult.setValidated(true);
    }catch(Exception e) {
        e.printStackTrace();
        validationResult.setValidated(false);
        validationResult.setGlobalMessage(e.getLocalizedMessage());

    }
    return validationResult;
}

I expected a insert/update sql query with filled tenant_code field.


Here complete log

2019-01-22 01:10:57.521  INFO 14304 --- [nio-8090-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2019-01-22 01:10:57.523 INFO 14304 --- [nio-8090-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2019-01-22 01:10:57.550 INFO 14304 --- [nio-8090-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 27 ms
Hibernate:
select
spuser0_.user_id as user_id1_0_0_,
spuser0_.created_by as created_2_0_0_,
spuser0_.created_date as created_3_0_0_,
spuser0_.last_modified_by as last_mod4_0_0_,
spuser0_.last_modified_date as last_mod5_0_0_,
spuser0_.tenant_code as tenant_c6_0_0_,
spuser0_.buyer_id as buyer_id7_0_0_,
spuser0_.email as email8_0_0_,
spuser0_.family_name as family_n9_0_0_,
spuser0_.given_name as given_n10_0_0_,
spuser0_.middle_name as middle_11_0_0_,
spuser0_.user_name as user_na12_0_0_
from
sp_user spuser0_
where
spuser0_.user_id=?
2019-01-22 01:11:10.689 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [2]
Hibernate:
select
spuser0_.user_id as user_id1_0_0_,
spuser0_.created_by as created_2_0_0_,
spuser0_.created_date as created_3_0_0_,
spuser0_.last_modified_by as last_mod4_0_0_,
spuser0_.last_modified_date as last_mod5_0_0_,
spuser0_.tenant_code as tenant_c6_0_0_,
spuser0_.buyer_id as buyer_id7_0_0_,
spuser0_.email as email8_0_0_,
spuser0_.family_name as family_n9_0_0_,
spuser0_.given_name as given_n10_0_0_,
spuser0_.middle_name as middle_11_0_0_,
spuser0_.user_name as user_na12_0_0_
from
sp_user spuser0_
where
spuser0_.user_id=?
2019-01-22 01:11:14.414 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [2]
Hibernate:
insert
into
sp_user
(created_by, created_date, last_modified_by, last_modified_date, tenant_code, buyer_id, email, family_name, given_name, middle_name, user_name)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2019-01-22 01:11:23.137 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [3bf38f12-5d17-47e4-9504-ad0705289d10]
2019-01-22 01:11:23.138 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [Tue Jan 22 01:11:14 CET 2019]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] - [3bf38f12-5d17-47e4-9504-ad0705289d10]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [TIMESTAMP] - [Tue Jan 22 01:11:14 CET 2019]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [5] as [VARCHAR] - [null]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [6] as [VARCHAR] - [1]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARCHAR] - [[email protected]]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [8] as [VARCHAR] - [Muscas]
2019-01-22 01:11:23.140 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [9] as [VARCHAR] - [Gabrielex]
2019-01-22 01:11:23.141 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [10] as [VARCHAR] - [Giuseppe Pippo]
2019-01-22 01:11:23.141 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [11] as [VARCHAR] - [gabriele.muscas]
Hibernate:
update
sp_user
set
created_by=?,
created_date=?,
last_modified_by=?,
last_modified_date=?,
tenant_code=?,
buyer_id=?,
email=?,
family_name=?,
given_name=?,
middle_name=?,
user_name=?
where
user_id=?
2019-01-22 01:11:24.912 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [3bf38f12-5d17-47e4-9504-ad0705289d10]
2019-01-22 01:11:24.912 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [Tue Jan 22 01:11:14 CET 2019]
2019-01-22 01:11:24.913 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] - [3bf38f12-5d17-47e4-9504-ad0705289d10]
2019-01-22 01:11:24.913 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [TIMESTAMP] - [Tue Jan 22 01:11:24 CET 2019]
2019-01-22 01:11:24.913 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [5] as [VARCHAR] - [PIPPO]
2019-01-22 01:11:24.913 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [6] as [VARCHAR] - [1]
2019-01-22 01:11:24.914 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [7] as [VARCHAR] - [[email protected]]
2019-01-22 01:11:24.914 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [8] as [VARCHAR] - [Muscas]
2019-01-22 01:11:24.914 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [9] as [VARCHAR] - [Gabrielex]
2019-01-22 01:11:24.915 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [10] as [VARCHAR] - [Giuseppe Pippo]
2019-01-22 01:11:24.915 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [11] as [VARCHAR] - [gabriele.muscas]
2019-01-22 01:11:24.915 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [12] as [BIGINT] - [23]
2019-01-22 01:11:24.918 ERROR 14304 --- [nio-8090-exec-2] o.h.i.ExceptionMapperStandardImpl : HHH000346: Error during managed flush [Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [com.supplhi.buyer.api.domain.entity.SpUser#23]]
Hibernate:
select
spuser0_.user_id as user_id1_0_0_,
spuser0_.created_by as created_2_0_0_,
spuser0_.created_date as created_3_0_0_,
spuser0_.last_modified_by as last_mod4_0_0_,
spuser0_.last_modified_date as last_mod5_0_0_,
spuser0_.tenant_code as tenant_c6_0_0_,
spuser0_.buyer_id as buyer_id7_0_0_,
spuser0_.email as email8_0_0_,
spuser0_.family_name as family_n9_0_0_,
spuser0_.given_name as given_n10_0_0_,
spuser0_.middle_name as middle_11_0_0_,
spuser0_.user_name as user_na12_0_0_
from
sp_user spuser0_
where
spuser0_.user_id=?
2019-01-22 01:11:24.928 TRACE 14304 --- [nio-8090-exec-2] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] - [23]
2019-01-22 01:11:24.940 ERROR 14304 --- [nio-8090-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.ObjectOptimisticLockingFailureException: Object of class [com.supplhi.buyer.api.domain.entity.SpUser] with identifier [23]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [com.supplhi.buyer.api.domain.entity.SpUser#23]] with root cause

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [com.supplhi.buyer.api.domain.entity.SpUser#23]
at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:2522) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3355) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3229) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3630) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:146) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:532) ~[spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.4.RELEASE.jar:5.1.4.RELEASE]
at com.supplhi.buyer.api.service.UserServiceImpl$$EnhancerBySpringCGLIB$$baf4f87e.updateUser(<generated>) ~[classes/:na]
at com.supplhi.buyer.api.web.UserController.updateUser(UserController.java:38) ~[classes/:na]

A little help, s'll vous plaît. Thanks

Understand to Spring Boot: MySQL and JPA, Hibernate

Understand to Spring Boot: MySQL and JPA, Hibernate

Spring Boot uses Hibernate as the default JPA implementation. The property spring.jpa.hibernate.ddl-auto is used for database initialization. I've used the value “update” for this property.

Before we start, this post is mostly aimed at Java developers, specifically Java developers who use the Spring umbrella of frameworks for development. If you are not a Java developer, you can still read on, chances are you haven't found the love of your life - in terms of languages - yet.

Also, I apologise in advance for the markup mishaps that will pop up here and there in various code sections. You could check out the original blog post here Original Post if you have trouble understanding/copy pasting the code.

First off, what really is Spring?

If you are a java developer, you've probably stumbled upon the term 'Spring' at some point in your journey, and possibly fell face first at either its learning curve, or its complexity. One thing most developers fail to understand - I also struggled with this at first - is that Spring represents a collection of frameworks that are tailored to meet specific development needs. As an example, if you're a java web developer, Spring provides the Web Servlet Framework for web development where Spring MVC (Included in this framework) is built on top of the Servlet API. Therefore, you need not learn all the frameworks that Spring provides, but rather the frameworks that fit your specific use case. Yeah, that's a shortcut, and yes, you're welcome.

If you've never heard about Spring before, Spring is an Inversion of Control and Dependency injection framework. This are fairly big terms but this comprehensive post will help you understand the meanings of these two concepts: IoC and Dependency Injection

Now onto Spring Boot

If you have used Spring MVC before, you've definitely have had to wrestle with Spring MVC's pre-configurations like Setting up the Dispatcher Servlet etc. etc. before you were able to get the framework up and running. This is where Spring Boot comes in. Spring Boot is an auto-configuration tool for setting up your Spring-powered applications. You can now put away those boxing gloves cause you might not need to wrestle with Spring Boot.

To help you understand Spring Boot further, and shine a light on why you should be using it if you already aren't, we'll build a simple Netflix API that allows client devices to register themselves, suggest movies and query movies.

Let us begin

Step 1: Setting up Spring Boot on your application.

Spring offers a project initializer, Spring Initialzr that allows you to select your project specifications and download an already configured Spring Boot project as a zip file or a maven build file. You could skip to step 2 if you have done this.

If you're a more of a hands on type of person who enjoys understanding what's happening under the hood, you can continue with this step.

Folder Structure.

Create a new Java project with you favourite IDE and configure your folder structure to mimic the following design:

└── src
    └── main
        └── controllers
        └── models
        └── repositories
        └── resources
            └── templates
                └── error.html
            └── application.properties
        └── Application.java

contollers - This folder will contain the controllers we define for this project

repositories - This folder will contain the repositories we'll define for our models that will be used to fetch data from the database.

resources - this folder will contain our project resources. The templatesfolder contains our template files that will be rendered by Spring. You can include other folders like static which will be used to server static content like javascript and css files.

Maven dependencies

Spring Boot allows us to include in our pom.xml file all the Spring dependencies that we'll use in our project. Copy paste the following dependencies, together with the Spring Boot Maven Plugin to your pom.xml.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
&lt;groupId&gt;org.springframework&lt;/groupId&gt;
&lt;artifactId&gt;gs-spring-boot&lt;/artifactId&gt;
&lt;version&gt;0.1.0&lt;/version&gt;

&lt;parent&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;
    &lt;version&gt;2.1.6.RELEASE&lt;/version&gt;
&lt;/parent&gt;

&lt;dependencies&gt;
    &lt;!--Spring dependencies--&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-starter-thymeleaf&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
    &lt;artifactId&gt;spring-boot-devtools&lt;/artifactId&gt;
    &lt;optional&gt;true&lt;/optional&gt;
&lt;/dependency&gt;

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
</dependency>
<!--Spring JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>

&lt;properties&gt;
    &lt;java.version&gt;1.8&lt;/java.version&gt;
&lt;/properties&gt;


&lt;build&gt;
    &lt;plugins&gt;
        &lt;plugin&gt;
            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
            &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;
        &lt;/plugin&gt;
    &lt;/plugins&gt;
&lt;/build&gt;

</project>

Our dependencies overview:

1. spring-boot-starter-web - When building web applications using java, we often need other external dependencies that we include in our pom.xml like tomcat and Spring MVC. What spring-boot-starter-web does is add all these dependencies through one single dependency.

2. spring-boot-starter-thymeleaf - If you've never used thymeleaf before, thymeleaf is a templating engine for processing and creating HTML, XML, JavaScript, CSS, and text whose template files retain the .html extension and therefore a better alternative to JSPs (Java Server Pages). What this basically means is that you can run thymeleaf template files like normal web pages without a backend server for template processing as in the case of JSPs.

3. spring-boot-devtools - These tools grease your gears of development therefore making the overall development process more bearable. To learn more about what these tools offer, you can check out this link: spring-boot-devtools

4. mysql-connector-java - These is the MySQL JDBC implementation that we'll use to make connections to our MySQL database.

5. spring-boot-starter-data-jpa - Most if not all web applications need some form of persistence, which in java cases, is often JPA (Java Persistence API). If spring-boot-data-jpa is in the classpath, Spring boot will automatically configure our data-source through reading our database configuration from the application.properties file that we will configure next.

Note that we've set our java version to 1.8 since JDK 11 does not offer a lot of things out of the box and therefore you may run into errors like: springboot: org.hibernate.MappingException: Could not get constructor for org.hibernate.persister.entity.SingleTableEntityPersister

Application.properties file

Spring boot automatically reads configuration settings from this file and configures our spring boot environment accordingly. We'll configure our database here and also at the same time disable Spring boot's whitelabel error page which we'll replace with our own custom error page. You can copy paste all this into your own application.properties file if you do not intended to make any changes.

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/netflix?useSSL=false
spring.datasource.username = netflix
spring.datasource.password = netflix

Hibernate Properties The SQL dialect makes Hibernate generate better SQL for the chosen database

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

Hibernate ddl auto (create, create-drop, validate, update)

spring.jpa.hibernate.ddl-auto = update

#Disabling the whitelabel error page
server.error.whitelabel.enabled=false

In the above application.properties file, We've configured our database, username and password to netflix . You can configure this if you want to. Spring JPA automatically uses the Hibernate implementation of JPA. 

We've set spring.jpa.hibernate.ddl-auto to update which will ensure that any changes we make to our models will be reflected in our Database, which also includes creating a new model. Please note that this option is only suitable for development environments rather than production environments. For more information, you can check this link: Database Initialization.

We've also set server.error.whitelabel.enabled to false to disable Spring boot's whitelabel error pages which we'll replace with our own custom error page.

Configuring our Application.java file

This file will contain the main method which we'll use to ignite our Spring Application with. Copy paste the following to your Application.java file:

package main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableJpaRepositories(basePackages="main.repositories")
@EnableTransactionManagement
@EnableJpaAuditing
@EntityScan(basePackages={"main.entities","main.models"})
public class Application {

public static void main(String[] args) {
    SpringApplication.run(Application.class, args);
}

}

@SpringBootApplication is a combination of the following more specific spring annotations -

1. @Configuration : Any class annotated with @Configuration annotation is bootstrapped by Spring and is also considered as a source of other bean definitions.

2. @EnableAutoConfiguration : This annotation tells Spring to automatically configure your application based on the dependencies that you have added in the pom.xml file. For example, If spring-data-jpa or spring-jdbc is in the classpath, then it automatically tries to configure a DataSource by reading the database properties from application.properties file.

3. @ComponentScan : It tells Spring to scan and bootstrap other components defined in the current package (main) and all the sub-packages.

@EnableJpaAuditing is used to support the automatic filling of fields that we'll annotate with @CreatedDate.

@EnableJpaRepositories tells Spring where to find our defined Repositories, since we'll not be using the @Repository annotation.


Step 2. Coding our Controllers.

We'll create only 3 Contollers namely: CustomErrorController that we'll use to format and serve our custom error page, MoviesController that will perform movie related functions and UsersContoller that will perform user related functions.

CustomErrorController

In this controller, we'll register a route error that will be mapped to our renderErrorPage method. Therefore all requests made through the errorroute will be recieved by our method.

Note that here we'll use the @Controller annotation since we'd like to return a view rather than plain text and therefore our method returning a string will return the name of the view. To return plain text rather than views, use the @RestController annotation.

We will also format our error messages to make them more user friendly when we display them on our error page.

We've also implemented the ErrorController interface and overridden the getErrorPath() method which will automatically be invoked when Spring encounters an error.

@Controller
public class CustomErrorController implements ErrorController {

@RequestMapping(value = "error",produces = "application/json;charset=UTF-8")
public String renderErrorPage(HttpServletRequest request, Model model) {
     String errorMsg = "";
    Object status = request.getAttribute(RequestDispatcher.ERROR_STATUS_CODE);
    int httpErrorCode = 404;
    if(status != null){
        httpErrorCode = Integer.valueOf(status.toString());
    }
    switch (httpErrorCode) {
        case 400: {
            errorMsg = "Http Error Code: 400. Bad Request";
            break;
        }
        case 401: {
            errorMsg = "Http Error Code: 401. Unauthorized";
            break;
        }
        case 404: {
            errorMsg = "Http Error Code: 404. Resource not found";
            break;
        }
        case 500: {
            errorMsg = "Http Error Code: 500. Internal Server Error";
            break;
        }
    }
    model.addAttribute("error",errorMsg);
    return "error";
}

@Override
public String getErrorPath() {
    return "/error";
}

}

Movies Controller

As we have stated earlier, this Controller will store functionalities related to our movies.

Since we are creating an api, we'll map api requests to url patterns that start with /api. Therefore, we add a @RequestMapping annotation on top of the class, rather than the method so that every request url we map on our methods will be appended to /api.

@RestController
@RequestMapping(value = "/api",produces = "application/json;charset=UTF-8") //All our api request URLs will start with /api and will return Json
public class MoviesController {

private MoviesRepository moviesRepository;
private CategoriesRepository categoriesRepository;
private UserRepository userRepository;

@Autowired
public MoviesController(MoviesRepository moviesRepository, CategoriesRepository categoriesRepository, UserRepository userRepository){
    this.moviesRepository = moviesRepository;
    this.categoriesRepository = categoriesRepository;
    this.userRepository = userRepository;
}

//Suggest A movie
@GetMapping(value = "/suggestMovie")
public String suggestMovie(@RequestParam(name = "category_id") Long categoryId,@RequestParam(name = "name")String name
,@RequestParam(name = "suggested_by")Long suggestedBy){
    //Movies added through this API route are automatically marked as suggested.
    String movieType = Movies.MovieType.SUGGESTED.getMovieType();
    Movies movies = new Movies();

    //Provided category id should be in our categories table.
    if(categoriesRepository.findById(categoryId).isPresent()){

        if(userRepository.findById(suggestedBy).isPresent()){
            movies.setCategoryId(categoryId);
            movies.setName(name);
            movies.setType(movieType);
            movies.setSuggestedBy(suggestedBy);
            return moviesRepository.save(movies).toString();
        } else {
            return "{'error':'The specified user id does not exist.'}";
        }

    } else {
        return "{'error':'The specified category id does not exist.'}";
    }



}

//delete a suggested movie
@GetMapping(value = "/deleteMovie")
public String deleteMovie(@RequestParam(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId) {
    if(userRepository.findById(userId).isPresent()){
        Optional&lt;Movies&gt; movies = moviesRepository.findById(movieId);
        if(movies.isPresent()){
            List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
            if(movie.size()&gt;0){
                moviesRepository.delete(movie.get(0));
                return movie.toString();
            } else {
                return generateErrorResponse("The user specified cannot delete this movie");
            }


        } else {
            return  generateErrorResponse("Specified movie id does not exist");
        }

    } else {
        return generateErrorResponse("Specified user id does not exist");
    }
}

//update a suggested movie. Supports only updating of the movie name or category.
@GetMapping(value = "/updateMovie/{movie_id}")
public String updateMovie(@PathVariable(name = "movie_id") Long movieId,@RequestParam(name = "user_id")Long userId,
                          @RequestParam(name = "movie_name",required = false)String movieName, @RequestParam(name = "movie_category",required = false) Long movieCategory) {
    List&lt;Movies&gt; movie = moviesRepository.findBySuggestedByEqualsAndIdEquals(userId,movieId);
    if(!(movie.size()&gt;0)){
        return generateErrorResponse("The user specified cannot update this movie");
    }

    if(moviesRepository.findById(movieId).isPresent()){
        Movies movies = moviesRepository.findById(movieId).get();
        if(movieName != null &amp;&amp; !movieName.isEmpty()){
            movies.setName(movieName);
        }
        if(movieCategory != null &amp;&amp; categoriesRepository.findById(movieCategory).isPresent()){
            movies.setCategoryId(movieCategory);
        }

        return moviesRepository.save(movies).toString();
    } else {
        return generateErrorResponse("The specified movie id does not exist");
    }
}

//query available movies
@GetMapping(value = "/queryMovies/{categoryId}")
public String queryMovies(@PathVariable Long categoryId,@RequestParam(name = "type") String type){
    JsonObjectBuilder jsonResponse = Json.createObjectBuilder();
    JsonObjectBuilder temp = Json.createObjectBuilder();
    int count = 0;
    for(Movies movie:moviesRepository.findAllByCategoryIdEqualsAndTypeEquals(categoryId,type)){
        temp.add("id",movie.getId());
        temp.add("name",movie.getName());
        temp.add("type",movie.getType());
        temp.add("category_id",movie.getCategoryId());
        temp.add("created_at",movie.getCreatedAt().toString());
        jsonResponse.add(count + "",temp);
        temp = Json.createObjectBuilder();
        count++;
    }

    return jsonResponse.build().toString();
}

private String generateErrorResponse(String message){
    return "{\"error\":\"" + message + "\"";
}

//add categories
@GetMapping(value = "/addCategories")
public String addCategories(@RequestParam(name = "name") String name){
    Categories categories = new Categories();
    categories.setName(name);

    return categoriesRepository.save(categories).toString();
}

}

In this Class, you may have noticed annotations that you might have not seen before. Let's go through them quickly:

1. @Autowired - As the annotation itself suggests, this annotation automatically injects an implementation of the movies, users and categories repository interface which we assign the the field variables we have declared. As we mentioned earlier, you need a repository to be able to access database contents, which explains these three repositories. I'll explain this further when we reach the repositories section.

2. @GetMapping - This annotation is the same as @RequestMapping except that it only maps get requests to the specified url.

3. @RequestParam - This annotation automatically injects the specified query parameter name to this variable.

4. @PathVariable` - This annotation automatically injects the path value - enclosed in curly braces - to this variable.

Users Controller

This controller will contain functionalities related to users. In this case, we'll define only a single method that will be responsible for creating a user.

@RestController

@RequestMapping(value = "/api",produces = "application/json;charset=UTF-8") //All our api request URLs will start with /api and return Json

public class UsersController {

private UserRepository userRepository;

@Autowired
public UsersController(UserRepository userRepository){
this.userRepository = userRepository;
}

@GetMapping(path = "/addUser")
public String addUser(@RequestParam(name = "id")Long id, @RequestParam(name="name") String name) {
Users users = new Users();
users.setId(id);
users.setName(name);

users = userRepository.save(users);
return users.toString();

}
}

Our user IDs in this case will not be auto-generated but instead, we'll provide users with an option to define their own IDs.

Step 3. Defining our Repositories

Repositories will be used by our models to query data from the Database. spring-jpa comes with a JpaRepository interface that defines all CRUD operations that we can perform on an Entity. We'll use the CrudRepositoryimplementation of JpaRespository as it offers many CRUD operations out of the box through methods like findAll(), save() etc. At the same time, CrudRepository automatically generated for us dynamic queries based on method names as we'll see in the following example.

We'll define three repositories for our three entities: CategoriesRepository , MoviesRepository and UsersRepository, which will all be interfaces extending CrudRepository.

CategoriesRepository

public interface CategoriesRepository extends

CrudRepository<Categories,Long> {

}

MoviesRepository

public interface MoviesRepository extends CrudRepository {

List<Movies> findAllByCategoryIdEqualsAndTypeEquals(Long categoryId,String type);

List<Movies> findBySuggestedByEqualsAndIdEquals(Long suggestedBy,Long movieId);

In this repository, notice the abstract methods we have defined. Extending CrudRepository will automatically compel Spring to create an implementation of these methods automatically at run-time just from the definition of the method name. To add Custom methods, we can add them in the following ways:

  1. We can start our query method names with find...Byread...Byquery...Bycount...By, and get...By. Before By we can add expression such as Distinct . After By we need to add property names of our entity.
  2. To get data on the basis of more than one property we can concatenate property names using And and Or while creating method names.
  3. If we want to use completely custom name for our method, we can use @Query annotation to write query.

UsersRepository

@Repository

public interface UserRepository extends CrudRepository {

Final Step: Defining our models.

The models (Entities) that we define will be used to store our table structures as will be defined in the database. We will therefore have three models for our three tables: Categories , Movies and Users.

Categories Model

@Entity

@Table(name = "categories")

public class Categories {

@Id
@GeneratedValue
private Long id;

@NotBlank
private String name;

public Long getId() {
return id;
}

public String getName() {
return name;
}

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

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

return builder.build().toString();

}

}

`

An entity is a plain old Java object (POJO) class that is mapped to the database and configured for usage through JPA using annotations and/or XML.

Note that we've included a @Table annotation to explicitly define the name of our table. The @Id annotation automatically declares the created field as a primary key for our table in our database. At the same time, the @GeneratedValue annotation will automatically generate a value and store it in the database during saving of a record, pretty much like an auto-increment field. The @NotBlank annotation will automatically validate values that will be inserted into the name variable we defined and ensure that this field is not blank.

We've also defined our own toString method (overriding the superclass's toString method) that will convert our model to a Json string that we return as a response in our controllers.

Movies Model

@Entity

@Table(name = "movies")

@EntityListeners(AuditingEntityListener.class)

@JsonIgnoreProperties(value = {"createdAt"},allowGetters = true)

public class Movies implements Serializable {

@Id
@GeneratedValue
private Long id;

private Long categoryId;

@NotBlank
private String type;

@NotBlank
private String name;

private Long suggestedBy;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
createdAt = new Date();
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public Long getCategoryId() {
return categoryId;
}

public void setCategoryId(Long categoryId) {
this.categoryId = categoryId;
}

public String getType() {
return type;
}

public void setType(String type) {
this.type = type;
}

public String getName() {
return name;
}

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

public Date getCreatedAt() {
return createdAt;
}

@Override
public String toString() {
JsonObjectBuilder builder = Json.createObjectBuilder();

//serialize to Json only if the data was persisted.
if(!Objects.isNull(id)){
    builder.add("id",id);
}
if(!Objects.isNull(name)){
    builder.add("name",name);
}

if(!Objects.isNull(categoryId)){
    builder.add("category_id",categoryId);
}

if(!Objects.isNull(createdAt)) {
    builder.add("created_at",createdAt.toString());
}
return builder.build().toString();

}

public Long getSuggestedBy() {
return suggestedBy;
}

public void setSuggestedBy(Long suggestedBy) {
this.suggestedBy = suggestedBy;
}

public enum MovieType{
SUGGESTED("suggested"),ORIGINAL("original");

private String movieType;

 MovieType(String movieType){
    this.movieType = movieType;
}

public String getMovieType() {
    return movieType;
}

}

In this model, note the annotations below:

1. @EntityListeners(AuditingEntityListener.class) - This will attach an entity listener to our model class that will automatically fill the fields we've annotated with @CreatedAt.

2. `@PrePersist - This annotation will ensure that the automatically generated value for the createdAt field is stored in this field whenever we'll need access. For more information on Database Auditing you can check this link: Database Auditing

Users Model

@Entity
@Table(name = "users")
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {"createdAt"},
allowGetters = true)
public class Users implements Serializable {
private static final long serialVersionUID = 2L;

@Column(updatable = false)
@Id
private Long id;

@NotBlank(message = "The field 'name' is mandatory.")
private String name;

@Column(nullable = false, updatable = false)
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
private Date createdAt; //Stores the date at which a user was created.

@PrePersist
public void prePersist(){
    createdAt = new Date();
}

public void setId(long id) {
    this.id = id;
}

public long getId() {
    return id;
}

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

public String getName() {
    return name;
}



@Override
public String toString() {
    JsonObjectBuilder builder = Json.createObjectBuilder();

    //serialize to Json only if the data was persisted.
    if(!Objects.isNull(id)){
        builder.add("id",id);
    }
    if(!Objects.isNull(name)){
        builder.add("name",name);
    }

    if(!Objects.isNull(createdAt)) {
        builder.add("created_at",createdAt.toString());
    }
    return builder.build().toString();
}


public Date getCreatedAt() {
    return createdAt;
}

}

The Custom Error Page Template

In the templates folder we defined, create a html page and name it error.html and copy paste the following code into it:

<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>Error</title>
</head>
<body>
    <div>Web Application. Error : th:text="${error}"</div>
</body>
</html>

thymeleaf will automatically parse this html page and render our error message by replacing the th:text attribute.

Finally

Run your Application.java's main method and test out your netflix api on your browser by navigation to localhost:8080/. You should be able to see your json responses on your browser. Alternatively, you can check out my git repository for the source code and a client you can test your code with: github repo

Conclusion

You've successfully made a netflix api using Spring boot, mysql and JPA. Congrats!

Thanks For Visiting, Keep Visiting.

Spring Boot + JPA + Hibernate + Oracle

Spring Boot + JPA + Hibernate + Oracle

In this tutorial, we will learn how to create a Spring Boot application that communicates with an Oracle data source through Hibernate.

In this tutorial, we will learn how to create a Spring Boot application that communicates with an Oracle data source through Hibernate.

Prerequisites
  • Eclipse IDE (neon release)
  • Maven 4
  • Java 1.8
Create a Maven Project

Open Eclipse, then create a new Maven project and name it SpringBootHibernate.

At the end of this tutorial, we’ll get the following project structure:

pom.xml

Configure Spring Boot inside your pom.xml by adding the following parent dependency:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.10.RELEASE</version>
</parent>

Then add a spring-boot-starter dependency in order to run the application as a standalone JAR application:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

Now in order to make use of Spring Data JPA and Hibernate, we need to just add spring-boot-starter-data-jpa as a dependency:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

As soon as we include Spring Boot Starter JPA in our project, we get the following features from a wide variety of dependencies:

  • Eclipse IDE (neon release)
  • Maven 4
  • Java 1.8

This is the whole pom.xml for reference:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.programmer.gate</groupId>
  <artifactId>SpringBootHibernate</artifactId>
  <packaging>jar</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringBootHibernate</name>

  <properties>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.10.RELEASE</version>
    </parent>

  <dependencies>

      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

  </dependencies>

  <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
  </build>

</project>

Add the Oracle Driver to the Classpath

In this tutorial, we’re going to override the default in-memory database provided by Spring Boot and use our own Oracle database.

For this purpose, we add “oracle-ojdbc6-11.2.0.3.jar” under WEB-INF/lib and define it in our classpath.

application.properties

Configure the Oracle data source and Hibernate in application.properties:

# create and drop tables and sequences, loads import.sql
spring.jpa.hibernate.ddl-auto=create-drop

# Oracle settings
spring.datasource.url=jdbc:oracle:thin:@localhost:1522:orcl
spring.datasource.username=HIBERNATE_TEST
spring.datasource.password=HIBERNATE_TEST
spring.datasource.driver.class=oracle.jdbc.driver.OracleDriver

# logging
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=debug

Entities

Our entities represent a player and a team with a one-to-many relationship. Each team could have many players, whereas a player could only play with a single team at a time.

So we create our entities under the com.programmer.gate.model package:

Player.java:

package com.programmer.gate.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;

@Entity
public class Player {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "player_Sequence")
    @SequenceGenerator(name = "player_Sequence", sequenceName = "PLAYER_SEQ")
    private Long id;

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

    @Column(name = "num")
    private int num;

    @Column(name = "position")
    private String position;

    @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "team_id", nullable = false)
        private Team team;

    public Player() {
    }
        // getters/setters
}

Team.java:

package com.programmer.gate.model;

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;

@Entity
public class Team {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "team_Sequence")
    @SequenceGenerator(name = "team_Sequence", sequenceName = "TEAM_SEQ")
    private Long id;

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

    @OneToMany(cascade = CascadeType.ALL,
            fetch = FetchType.EAGER,
            mappedBy = "team")
    private List<Player> players;

    public Team() {
    }

        // getters/setters
}

Since we set spring.jpa.hibernate.ddl-auto=create-drop inside application.properties, our application will automatically create Player and Team entities in our database, along with their sequences and constraints.

Our application would also look for import.sql in the classpath and execute it, if found.

In our example, we define import.sql under src/main/resources in order to fill our tables with static data:

insert into Team (id,name) values(1,'Barcelona');

insert into Player (id, team_id, name, num, position) values(1,1,'Lionel Messi', 10, 'Forward');
insert into Player (id, team_id, name, num, position) values(2,1,'Andreas Inniesta', 8, 'Midfielder');
insert into Player (id, team_id, name, num, position) values(3,1,'Pique', 3, 'Defender');

Repositories

We define our repositories’ interfaces under com.programmer.gate.repository. Each repository extends Spring CrudRepository, which provides a default implementation for the basic find, save,and delete methods — so we don’t care about defining implementation classes for them.

PlayerRepository:

package com.programmer.gate.repository;

import java.util.List;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.programmer.gate.model.Player;

@Repository
public interface PlayerRepository extends CrudRepository<Player, Long> {

    List<Player> findByTeamId(long teamId);
}

TeamRepository:

package com.programmer.gate.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.programmer.gate.model.Team;

@Repository
public interface TeamRepository extends CrudRepository<Team, Long> {

    Team findByPlayers(long playerId);
}

Service

Now we define our service class, which holds the business logic of our application. Our service exposes two methods: getAllTeamPlayers() and addBarcelonaPlayer() ( just rename it to your favorite club if you don’t like Barcelona!). Our service layer communicates directly with the repository layer.

SoccerService.java:

package com.programmer.gate.service;

import java.util.List;

public interface SoccerService {
    public List<String> getAllTeamPlayers(long teamId);
    public void addBarcelonaPlayer(String name, String position, int number);
}

SoccerServiceImpl:

package com.programmer.gate.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.programmer.gate.model.Player;
import com.programmer.gate.model.Team;
import com.programmer.gate.repository.PlayerRepository;
import com.programmer.gate.repository.TeamRepository;

@Service
public class SoccerServiceImpl implements SoccerService {

    @Autowired
    private PlayerRepository playerRepository;
    @Autowired
    private TeamRepository teamRepository;

    public List<String> getAllTeamPlayers(long teamId) {
        List<String> result = new ArrayList<String>();
        List<Player> players = playerRepository.findByTeamId(teamId);
        for (Player player : players) {
            result.add(player.getName());
        }

        return result;
    }

    public void addBarcelonaPlayer(String name, String position, int number) {

        Team barcelona = teamRepository.findOne(1l);

        Player newPlayer = new Player();
        newPlayer.setName(name);
        newPlayer.setPosition(position);
        newPlayer.setNum(number);
        newPlayer.setTeam(barcelona);
        playerRepository.save(newPlayer);
    }
}

Application.java

The final step is to create the Spring Boot initializer. This is the entry point of our application. We define Application.javaunder com.programmer.gate:

package com.programmer.gate;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.programmer.gate.service.SoccerService;

@SpringBootApplication
public class Application implements CommandLineRunner{

    @Autowired
    SoccerService soccerService;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String... arg0) throws Exception {

        soccerService.addBarcelonaPlayer("Xavi Hernandez", "Midfielder", 6);

        List<String> players = soccerService.getAllTeamPlayers(1);
        for(String player : players)
        {
            System.out.println("Introducing Barca player => " + player);
        }
    }
}

P.S.: It’s worth mentioning that the Spring Boot application automatically reads and creates entities, repositories, and services defined in the same package or in a sub-package relative to where you have your initializer class. So if we define Application.java under a different package, then we need to explicitly specify the package of the model, repository, and service.

Output:

When running the application as a standard Java app, we get the following output in the console:

2018-04-13 14:54:47 DEBUG org.hibernate.SQL - create sequence player_seq start with 1 increment by 1
2018-04-13 14:54:47 DEBUG org.hibernate.SQL - create sequence team_seq start with 1 increment by 1
2018-04-13 14:54:47 DEBUG org.hibernate.SQL - create table player (id number(19,0) not null, name varchar2(255 char), num number(10,0), position varchar2(255 char), team_id number(19,0) not null, primary key (id))
2018-04-13 14:54:47 DEBUG org.hibernate.SQL - create table team (id number(19,0) not null, name varchar2(255 char), primary key (id))
2018-04-13 14:54:47 DEBUG org.hibernate.SQL - alter table player add constraint FKdvd6ljes11r44igawmpm1mc5s foreign key (team_id) references team
2018-04-13 14:54:47 INFO  o.h.tool.hbm2ddl.SchemaExport - HHH000476: Executing import script '/import.sql'
2018-04-13 14:54:47 INFO  o.h.tool.hbm2ddl.SchemaExport - HHH000230: Schema export complete
2018-04-13 14:54:47 INFO  o.s.o.j.LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'default'
2018-04-13 14:54:48 INFO  o.s.j.e.a.AnnotationMBeanExporter - Registering beans for JMX exposure on startup
2018-04-13 14:54:48 DEBUG org.hibernate.SQL - select team0_.id as id1_1_0_, team0_.name as name2_1_0_, players1_.team_id as team_id5_0_1_, players1_.id as id1_0_1_, players1_.id as id1_0_2_, players1_.name as name2_0_2_, players1_.num as num3_0_2_, players1_.position as position4_0_2_, players1_.team_id as team_id5_0_2_ from team team0_, player players1_ where team0_.id=players1_.team_id(+) and team0_.id=?
2018-04-13 14:54:48 DEBUG org.hibernate.SQL - select player_seq.nextval from dual
2018-04-13 14:54:48 DEBUG org.hibernate.SQL - insert into player (name, num, position, team_id, id) values (?, ?, ?, ?, ?)
2018-04-13 14:54:48 INFO  o.h.h.i.QueryTranslatorFactoryInitiator - HHH000397: Using ASTQueryTranslatorFactory
2018-04-13 14:54:48 DEBUG org.hibernate.SQL - select player0_.id as id1_0_, player0_.name as name2_0_, player0_.num as num3_0_, player0_.position as position4_0_, player0_.team_id as team_id5_0_ from player player0_, team team1_ where player0_.team_id=team1_.id(+) and team1_.id=?

Introducing Barca player => Lionel Messi
Introducing Barca player => Andreas Inniesta
Introducing Barca player => Pique
Introducing Barca player => Xavi Hernandez

2018-04-13 14:54:49 INFO  com.programmer.gate.Application - Started Application in 4.213 seconds (JVM running for 4.555)

Source code

You can download the source code from this repository: spring-boot-jpa-hibernate.

Java Spring - Limiting Query Result with Spring Data JPA

Java Spring - Limiting Query Result with Spring Data JPA

Java Spring - Limiting Query Result with Spring Data JPA

Sometimes we may need to limit the result returned by query. Adding LIMIT clause is the solution if we use SQL query. Unfortunately, it’s not supported by Spring Data JPA’s @Query annotation. It will throw error if you add LIMIT to the query. If you are using Spring or Spring Boot, here are the simple solutions to limit the query result.

Using Pageable

Maybe you’re already familiar with Pageable. It’s usually used for pagination. But, even if you only need to limit the query without using pagination, it can be useful too. In the repository, where you define the method, just add Pageable as the last parameter.

ItemRepository.java

  package com.woolha.example.querylimit.repository;

  import com.woolha.example.querylimit.model.Item;
  import org.springframework.data.domain.Page;
  import org.springframework.data.domain.Pageable;
  import org.springframework.data.repository.PagingAndSortingRepository;

  public interface ItemRepository extends PagingAndSortingRepository<Item, UUID> {
      Page<Item> findAllByTypeAndIsActive(String type, Boolean isActive, Pageable pageable);
  }

That means we have to pass an instance of Pageable as the third argument. Below is the example of limiting query result to 20 (the second argument). The first argument is the offset, while the third argment allows us to define the ORDER BY clause. To get the result as List<Item>, use .getContent().

ItemServiceImpl.java

  Pageable pageable = PageRequest.of(0, 20, Sort.by(Sort.Direction.DESC, "updatedAt"));

  return this.itemRepository.findAllByTypeAndIsActive(user, true, pageable).getContent();

Using EntityManager

Another way is using EntityManager. Use the createQuery method to define the query, set all parameters and define the limit with setMaxResults.

  package com.woolha.example.querylimit.repository;

  import com.woolha.example.querylimit.model.Item;
  import org.springframework.stereotype.Repository;

  import javax.persistence.EntityManager;
  import javax.persistence.PersistenceContext;
  import java.util.List;

  @Repository
  public class ItemRepositoryImpl {

      @PersistenceContext
      private EntityManager entityManager;

      public List<Item> findAllByTypeAndIsActive(String type,
                                                 Boolean isActive,
                                                 int limit) {
          return entityManager.createQuery("SELECT i FROM Item i"
                          + " WHERE (i.type IS :type)"
                          + " AND (i.isActive = :isActive)",
                  Item.class)
                  .setParameter("type", type)
                  .setParameter("isActive", isActive)
                  .setMaxResults(limit).getResultList();
      }
  }


Thanks for reading ❤