Hibernate saves/retrieves date minus day if application uses another timezone than MySQL

I have an application started on tomcat on MACHINE_A with timezone GMT+3.

I use remote mysql server started on MACHINE_B with timezone UTC.

We use spring-data-jpa for persistence.

As an example of the problem, I will show the repository:

public interface MyRepository extends JpaRepository<MyInstance, Long> {     Optional<MyInstance> findByDate(LocalDate localDate); } 

If I pass localDate for 2018-09-06, I get entities where the date is 2018-09-05(previous day)

In the logs I see:

2018-09-06 18:17:27.783 TRACE 13676 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [DATE] - [2018-09-06] 

I googled that question a lot and found a several articles with the same content(for example https://moelholm.com/2016/11/09/spring-boot-controlling-timezones-with-hibernate/)

So, I have the following application.yml:

spring:   datasource:     url: jdbc:mysql://localhost:3306/MYDB?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC     username: root     password: *****   jpa:     hibernate:       naming:         physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl     properties:       hibernate:         show_sql: true         use_sql_comments: true         format_sql: true         type: trace         jdbc:           time_zone: UTC 

But it doesn't help.

We use the following connector:

<dependency>     <groupId>mysql</groupId>     <artifactId>mysql-connector-java</artifactId>     <version>8.0.12</version> </dependency> 

How can I resolve my problem?

P.S.

I tried to run both applications with the same time zone. In this case everything works as expected.

P.S.2

I tried to use mysql driver 6.0.6 version but it doesn't change anything.

6 Answers

Answers 1

If you're using LocalDate in Java, you should use a DATE column in MySQL. This way the problem will be solved.

If you use LocalDateTime, try setting the property like this in Spring Boot:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC 

For a more detailed explanation, check out this article. You can find a test case in my High-Performance Java Persistence GitHub repository which works just fine.

Answers 2

Ideally, your both servers should be in same time zone and preferred one be in UTC time zone. And to show correct time to user in his timezone; you parse it in browser itself. And while retrieving data from DB; you use UTC time. This way you will not have issue while fetching data from DB

Answers 3

In MySQL...

TIMESTAMP internally stores UTC, but converts to/from the server's timezone based on two settings. Check those settings via SHOW VARIABLES LIKE '%zone%'; Properly configured, the reader may see a different time than the writer (based on tz settings).

DATE and DATETIME take whatever you give it. There is no tz conversion between the string in the client and what is stored in the table. Think of it a storing a picture of a clock. The reader will see the same time string that the writer wrote.

Answers 4

I faced similar issues while creating some integration tests for a spring-boot application using hibernate. The database I used here was postgreSQL.

As another answer correctly points out, you can set the hibernate.jdbc.time_zone=UTC property like discribed. Nevermind this didn't solve my issues, so I had to set the JVM default time zone with the help of the following in my spring-boot applications main class:

@PostConstruct public void init(){     TimeZone.setDefault(TimeZone.getTimeZone("UTC"));   // It will set UTC timezone     System.out.println("Spring boot application running in UTC timezone :"+new Date());   // It will print UTC timezone } 

This should also solve your problems. You can gather more informations here.

Reason

I guess your problem (retrieving date - 1 day) comes from your specific setup. If your application is running in UTC and requesting timestamps from a database in GMT+3 it resolves in a earlier date, because the applications context (JVM and Hibernate are responsible here) is 3 hours behind the database context in UTC. Simple example:

2018-12-02 00:00:00 - 3hours = 2018-12-01 21:00:00

As you are only looking to the dates: 2018-12-02 - 3hours = 2018-12-01

Answers 5

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

It's used when you are working TimeZoned Date, but from your logs it seems you are not passing TimeZone:

binding parameter [1] as [DATE] - [2018-09-06]

Try to remote property:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Answers 6

If you add the following parsing to your HQL query, it will return a date without any time zone format or time of day. This is a quick workaround to your issue.

select DATE_FORMAT(date,'%Y-%m-%d') from Entity 

Read More

Spring Data JPA Meta JpaMetamodelMappingContext Memory Consumption

My Spring Data JPA/Hibernate Application consumes over 2GB of memory at start without a single user hitting it. I am using Hazelcast as the second level cache but I had the same issue when I used ehCache as well so that is probably not the cause of the issue.

I ran a profile with a Heap Dump in Visual VM and I see where the bulk of the memory is being consumed by JpaMetamodelMappingContext and secondary a ton of Map objects. I just need help in deciphering what I am seeing and if this is actually a problem. I do have a hundred classes in the model so this may be normal but I have no point of reference. It just seems a bit excessive.

Once I get a load of 100 concurrent users, my memory consumption increases to 6-7 GB. That is quite normal for the amount of data I push around and cache, but I feel like if I could reduce the initial memory, I'd have a lot more room for growth.

1 Answers

Answers 1

I don't think you have a problem here. Instead, I think you are misinterpreting the data you are looking at.

Note that the heap space diagram displays two numbers: Heap size and Used heap

Heap size (orange) is the amount of memory available to the JVM for the heap. This means it is the amount that the JVM requested at some point from the OS.

Used heap is the part of the Heap size that is actually used. Ignoring the startup phase, it grows linear and then drops repeatedly over time. This is typical behavior of an idling application. Some part of the application generates a moderate amount of garbage (rising part of the curve) which from time to time gets collected.

The low points of that curve are the amount of memory you are actually really using. It seems to be about 250MB which doesn't sound very much to me, especially when you say that the total consumption of 6-7GB when actually working sounds reasonable to you.

Some other observations:

Both CPU load and heap grows fast/fluctuates a lot at start time. This is to be expected because the analysis of repositories and entities happen at that time.

JpaMetamodelMappingContext s retained size is about 23MB. Again, a good chunk of memory, but not that huge. This includes the stuff it references, which is almost exclusively metadata from the JPA implementation as you can easily see when you take a look at its source.

Read More

Injecting one of the two @PersistenceContext

Consider having two entity manager factories:

<bean id="writeEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">...</bean> <bean id="readOnlyEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">...</bean> 

Then I want to have two Beans to which I would inject the correct persistence context:

<bean id="readOnlyManager" class="..MyDatabaseManager"> <bean id="writeManager" class="..MyDatabaseManager"> 

The bean would look something like:

public class MyDatabaseManager {      private javax.persistence.EntityManager em;      public EntityManager(javax.persistence.EntityManager em) {         this.em = em;     }     ... } 

This obviously doesn't work, because EntityManager is not a bean and cannot be injected in this way:

No qualifying bean of type 'javax.persistence.EntityManager' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {} 

How can I qualify correct EntityManager in the bean? I used to use @PersistenceContext annotation, but this is not usable as I need to inject it.

How can I specify the PersistenceContext for such Bean?

UPDATE: My question is how to inject PersistenceContext with qualifier via XML, not via annotation.

2 Answers

Answers 1

Assuming that you are using spring in order to manage transactions what I would do is 2 different transaction managers and then in my services i would use the most appropriate transaction manager like this:

Configuration section

@Bean public LocalContainerEntityManagerFactoryBean writeEntityManagerFactory() {      LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();     //Your configuration here     return factory; }  @Bean(name={"writeTx"}) public PlatformTransactionManager writeTransactionManager() {      JpaTransactionManager txManager = new JpaTransactionManager();     txManager.setEntityManagerFactory(writeEntityManagerFactory().getObject());     return txManager; }  @Bean public LocalContainerEntityManagerFactoryBean readEntityManagerFactory() {      LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();     //Your configuration here     return factory; }  @Bean(name={"readTx"}) public PlatformTransactionManager readTransactionManager() {      JpaTransactionManager txManager = new JpaTransactionManager();     txManager.setEntityManagerFactory(readEntityManagerFactory().getObject());     return txManager; } 

Service layer

@Transactional(value="readTx") public List<Object> read(){     //Your read code here }  @Transactional(value="writeTx") public void write(){     //Your write code here } 

UPDATED ANSWER I misunderstood the question.

In your configuration class you can define:

@Bean     public LocalContainerEntityManagerFactoryBean writeEntityManagerFactory() {          LocalContainerEntityManagerFactoryBean em  = new LocalContainerEntityManagerFactoryBean();         em.setDataSource(dataSource());         em.setPackagesToScan(new String[] { "models" });         JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();         em.setJpaVendorAdapter(vendorAdapter);         em.setJpaProperties(hibProps());         em.setPersistenceUnitName("writer");         return em;     }     @Bean     public LocalContainerEntityManagerFactoryBean readEntityManagerFactory() {          LocalContainerEntityManagerFactoryBean em  = new LocalContainerEntityManagerFactoryBean();         em.setDataSource(dataSource());         em.setPackagesToScan(new String[] { "models" });         JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();         em.setJpaVendorAdapter(vendorAdapter);         em.setJpaProperties(hibProps());         em.setPersistenceUnitName("reader");         return em;     }    

Please see the PersistenceUnitName values

Then you can injecting them by doing:

@PersistenceContext(unitName="writer") private EntityManager emWriter;  @PersistenceContext(unitName="reader") private EntityManager emReader; 

I just tested it and all worked pretty good

Angelo

Answers 2

persistence.xml (2 persistence unit for 2 different entitymanager, based on your persistence provider here i ma using HibernatePersistence)

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">    <persistence-unit name="pu1">       <provider>org.hibernate.ejb.HibernatePersistence</provider>    </persistence-unit>     <persistence-unit name="pu2">       <provider>org.hibernate.ejb.HibernatePersistence</provider>    </persistence-unit>    </persistence> 

Make sure you are assigning the persistence-unit to entity manager using property persistenceUnitName

    <bean id="writeEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">             <property name="persistenceXmlLocation" value="classpath: of yout persistence xml" />             <property name="persistenceUnitName" value="pu1" />                .....other configuration of em..       </bean> 

same for other em.

Now, use constructor injection for MyDatabaseManager to inject EntityManager (using qualifier name ex. writeEntityManagerFactory )

<bean id="mdm" class="MyDatabaseManager">      <constructor-arg ref = "writeEntityManagerFactory"/> </bean> 

Read More

How to use “on update CURRENT_TIMESTAMP” in H2 database?

I want my entity to have a modification timestamp whenever it is updated. mysql supports this using the following definition:

@Entity public class MyTable {     @Column(columnDefinition = "TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP")     private LocalDateTime thetime; } 

Problem: in my JUnit tests I want to use an embedded inmemory H2 database. And H2 does not support on update CURRENT_TIMESTAMP.

Question: how can I keep the column definition (as I will be running mysql in all cases except in automated tests)? And how can I workaround that in my h2 testing?

1 Answers

Answers 1

The official statement from the H2 people is that it is not supported and the workaround is to create a trigger. You can read this here https://github.com/commandos59/h2database/issues/491

Whatever you put in the "columnDefinition" it is provider specific. And since you have already mapped your entity with this specific column definition you are not leaving yourself much space to manouver.

There are several things you can do. Some of the things are hacks.

1. Mix XML configuration for the tests. The XML configurationn of the Entities has higher priority than the annotations so you can actualy override the

   @Column(columnDefinition = "TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP") private LocalDateTime thetime

with H2 specific column definition.

2. Another alternative agnostic of the Database is to leave your time generation to the application server layer and hook it to the @PrePersist @PreUpdate listeners on the entity

3. If you insist to have your timestamp generated by the database you can do something similar to how the IDs are generated. Have some sort of dedicated object that reads the CURRENT_TIMESTAMP from the database and puts it the entty right before you persist, update.

Read More

Hibernate saves/retrieves date minus day if application uses another timezone than MySQL

I have application started on tomcat on MACHINE_A with timzone GMT+3

I use remote mysql server started on MASHINE_B with time zone UTC.

We use spring-data-jpa for pesistence.

As example of problem I will demonstrate repository:

    public interface MyRepository extends JpaRepository<MyInstance, Long> {         Optional<MyInstance> findByDate(LocalDate localDate); } 

if I pass localDate for 2018-09-06 I get entities where date is 2018-09-05(previous day)

In logs I see:

2018-09-06 18:17:27.783 TRACE 13676 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [DATE] - [2018-09-06] 

I googled that question a lot and found a several articles with the same content(for example https://moelholm.com/2016/11/09/spring-boot-controlling-timezones-with-hibernate/)

So I have following application.yml:

spring:   datasource:     url: jdbc:mysql://localhost:3306/MYDB?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC     username: root     password: *****   jpa:     hibernate:       naming:         physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl     properties:       hibernate:         show_sql: true         use_sql_comments: true         format_sql: true         type: trace         jdbc:           time_zone: UTC 

But it doesn't help anyway.

We use following connector:

<dependency>     <groupId>mysql</groupId>     <artifactId>mysql-connector-java</artifactId>     <version>8.0.12</version> </dependency> 

How can I resolve my problem?

P.S.

I tried to run both applications with the same timezone. At this case averything works as expected

4 Answers

Answers 1

If you're using LocalDate in Java, you should use a DATE column in MySQL. This way the problem will be solved.

If you use LocalDateTime, try setting the property like this in Spring Boot:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC 

For a more detailed explanation, check out this article. You can find a test case in my High-Performance Java Persistence GitHub repository which works just fine.

Answers 2

Ideally, your both servers should be in same time zone and preferred one be in UTC time zone. And to show correct time to user in his timezone; you parse it in browser itself. And while retrieving data from DB; you use UTC time. This way you will not have issue while fetching data from DB

Answers 3

In MySQL...

TIMESTAMP internally stores UTC, but converts to/from the server's timezone based on two settings. Check those settings via SHOW VARIABLES LIKE '%zone%'; Properly configured, the reader may see a different time than the writer (based on tz settings).

DATE and DATETIME take whatever you give it. There is no tz conversion between the string in the client and what is stored in the table. Think of it a storing a picture of a clock. The reader will see the same time string that the writer wrote.

Answers 4

I faced similar issues while creating some integration tests for a spring-boot application using hibernate. The database I used here was postgreSQL.

As another answer correctly points out, you can set the hibernate.jdbc.time_zone=UTC property like discribed. Nevermind this didn't solve my issues, so I had to set the JVM default time zone with the help of the following in my spring-boot applications main class:

@PostConstruct public void init(){     TimeZone.setDefault(TimeZone.getTimeZone("UTC"));   // It will set UTC timezone     System.out.println("Spring boot application running in UTC timezone :"+new Date());   // It will print UTC timezone } 

This should also solve your problems. You can gather more informations here.

Reason

I guess your problem (retrieving date - 1 day) comes from your specific setup. If your application is running in UTC and requesting timestamps from a database in GMT+3 it resolves in a earlier date, because the applications context (JVM and Hibernate are responsible here) is 3 hours behind the database context in UTC. Simple example:

2018-12-02 00:00:00 - 3hours = 2018-12-01 21:00:00

As you are only looking to the dates: 2018-12-02 - 3hours = 2018-12-01

Read More

hibernate second level cache with Redis -will it improve performance?

I am currently developing application using Spring MVC4 and hibernate 4 .I have implemented hibernate second level cache for performance improvement .If I use Redis which is in-memory data structure store, used as a database, cache etc, performance will increase but will it be a drastic change?

4 Answers

Answers 1

Drastic differences you may expect if you cache what is good to be cached and avoid caching data that should not be cached at all. Like beauty is in the eye of the beholder same is with the performance. Here are several aspects you should have in mind when using hibernate AS second level cache provider:

No Custom serialization - Memory intensive
If you use second level caching you would not be able to use fast serialization frameworks as Kryo and will have to stick to java serializeable which sucks.

On top of this for each entity type you will have a separate region and within each region you will have entry for each key of each entity. In terms of memory efficiency this is inefficient.

Lacks ability to store and distribute rich objects
Most of the modern caches also present computing grid functionality having your objects fragmented into many small pieces decrease your ability ability to execute distributed tasks with guaranteed data co-location. That depends a little bit on the Grid provider, but for many would be limitation.

Sub optimal performance
Depending on how much performance you need and what type of application you are having using hibernate second level cache might be a good or a bad choice. Good in terms that it is plug and play...."kind of..." bad because you will never squeeze the performance you would have gained. Also designing rich models mean more upfront work and more OOP.

Limited querying capabilities ON the Cache itself
That depends on the cache provider , but some of the provider really are not good doing JOINs with Where clause different than the ID. If you try to build and in memory index for a query on Hazelcast for example you will see what I mean.

Answers 2

Yes, if you use Redis, it will improve your performance.

No, it will not be a drastic change. :)

https://memorynotfound.com/spring-redis-application-configuration-example/

http://www.baeldung.com/spring-data-redis-tutorial

the above links will help you to find out the way of integration redis with your project.

Answers 3

Your question was already discussed here. Check this link: Application cache v.s. hibernate second level cache, which to use?

This was the most accepted answer, which I agree with:

It really depends on your application querying model and the traffic demands.

1. Using Redis/Hazelcast may yield the best performance since there won't be any round-trip to DB anymore, but you end up having a normalized data in DB and denormalized copy in you cache which will put pressure on your cache update policies. So you gain the best performance at the cost of implementing the cache update whenever the persisted data changes.
2. Using 2nd level cache is easier to setup but it only stores entities by id. There is also a query cache, storing ids returned by a given query. So the 2nd level cache is a two step process that you need to fine tune to get the best performance. When you execute projection queries the 2nd level object cache won't help you, since it only operates on entity load. The main advantage of 2nd level cache is that it's easier to keep it in sync whenever data changes, especially if all your data is persisted by hibernate.

So, if you need ultimate performance and you don't mind implementing your cache update logic that ensures a minimum eventual consistency window, then go with an external cache.

If you only need to cache entities (that usually don't change that frequently) and you mostly access those through Hibernate entity loading, then 2nd level cache can help you.

Hope it helps!

Answers 4

It depends on the movement.

If You have 1000 or more requests per second and You are low on RAM, then Yes, use redis nodes on other machine to take some usage. It will greatly improve your RAM and request speed.

But If it's otherwise then do not use it.

Remember that You can use this approach later when You will see what is the RAM and database Connection Pool usage.

Read More

Can not persist data model's field into database, but can retrieve it

I have a problem when trying to persist a data model class into a database. I have a class like this:

class DataModelClass{     //some more field etc.      @Column(name = "number1", nullable = true)     private Integer number1;      @Column(name = "number2", nullable = true)     private Integer number2;      public DataModelClass(){}      (...)      public Integer getNumber2() {         return number2;     }      public void setNumber2( Integer number2 ) {         this.number2= number2;     } } 

The second field was added after first one. When to persist object created with this class via:

em.persist(dataModelClass); 

A new row in database is created, but only with first field added. The second one is empty. When I am debugging the object dataModelClass has every field set with some integer value. When I am adding a value for number2 through pgAdmin, and then retrieving this row with java code via:

DataModelClass dmc = em.find(DataModelClass.class, 1); 

Than dmc.getNumber2() is not empty/null.

Anyone have any ideas what is wrong?

[Edit] Maybe it will help a little more, On data model (DataModelClass) class i got this annotation:

@Entity @Table(name = "custom_table",        uniqueConstraints=@UniqueConstraint(name="UK_example_foreign_id", columnNames={"example_foreign_id"}) ) @SequenceGenerator(name = DataModelClass.SEQ_NAME, sequenceName = DataModelClass.SEQ_NAME, allocationSize = 1) 

Obviously this field exist in my class

2 Answers

Answers 1

I would check if my database is updated as my entity class.

Answers 2

The problem was, that after persist there was another query which was updating the database with null value. So the answer was to change this value in update query. Thanks all.

Read More

How to set lock timeout in postgres - Hibernate

I'm trying to set a Lock for the row I'm working on until the next commit:

entityManager.createQuery("SELECT value from Table where id=:id")             .setParameter("id", "123")             .setLockMode(LockModeType.PESSIMISTIC_WRITE)             .setHint("javax.persistence.lock.timeout", 10000)             .getSingleResult(); 

What I thought should happen is that if two threads will try to write to the db at the same time, one thread will reach the update operation before the other, the second thread should wait 10 seconds and then throw PessimisticLockException.

But instead the thread hangs until the other thread finishes, regardless of the timeout set.

Look at this example :

database.createTransaction(transaction -> {     // Execute the first request to the db, and lock the table     requestAndLock(transaction);      // open another transaction, and execute the second request in     // a different transaction     database.createTransaction(secondTransaction -> {         requestAndLock(secondTransaction);     });      transaction.commit(); }); 

I expected that in the second request the transaction will wait until the timeout set and then throw the PessimisticLockException, but instead it deadlocks forever.

Hibernate generates my request to the db this way :

SELECT value from Table where id=123 FOR UPDATE 

In this answer I saw that Postgres allows only SELECT FOR UPDATE NO WAIT that sets the timeout to 0, but it isn't possible to set a timeout in that way.

Is there any other way that I can use with Hibernate / JPA? Maybe this way is somehow recommended?

3 Answers

Answers 1

I think you could try

SET LOCAL lock_timeout = '10s'; SELECT ....; 

I doubt Hibernate supports this out-of-box. You could try find a way to extend it, not sure if it worth it. Because I guess using locks on a postges database (which is mvcc) is not the smartest option.

You could also do NO WAIT and delay-retry several times from your code.

Answers 2

Hibernate supports a bunch of query hints. The one you're using sets the timeout for the query, not for the pessimistic lock. The query and the lock are independent of each other, and you need to use the hint shown below.

But before you do that, please be aware, that Hibernate doesn't handle the timeout itself. It only sends it to the database and it depends on the database, if and how it applies it.

To set a timeout for the pessimistic lock, you need to use the javax.persistence.lock.timeout hint instead. Here's an example:

entityManager.createQuery("SELECT value from Table where id=:id")         .setParameter("id", "123")         .setLockMode(LockModeType.PESSIMISTIC_WRITE)         .setHint("javax.persistence.lock.timeout", 10000)         .getSingleResult(); 

Answers 3

There is the lock_timeout parameter that does exactly what you want.

You can set it in postgresql.conf or with ALTER ROLE or ALTER DATABASE per user or per database.

Read More

Best JPA entity model for Hashtags

I need to design a JPA entity model for hashtags in my application similar to Instagram, twitter and Stack Overflow. My application. The following are the schema specific points that our application uses

• Hashtag should be belongs to a question
• Hashtag is user specific.
• Each user can tag their question with same hashtag

Currently, I have two JPA model for the above schema

Common tables applicable for both models

table: questions

columns: id, question, description, etc.,

table: users

columns: id, name, role, group, etc.,

Model 1

table: question_hash_tags

columns: id, question_id, user_id, hashtag_text

Model 2

table: hashtags

columns: id, hastag_text

table: user_hashtags

columns: user_id, hashtag_id, question_id

Model 1 will have each row even if hashtag is same between users.

Model 2 will have unique hashtag row and it is reffered across users using user_hashtags.

I am expecting a better and standard model beyond these two.

Note: Questions are searchable based on hashtags as well as users

2 Answers

Answers 1

We have three "things" here: Users, Questions, and Hashtags. I'd avoid model #1 in your example mainly because it's not flexible. What happens when the product owner later decides to add a description to tags? What happens if the product owner next week wants a user to only choose tags from an existing pool? While your domain requirements are incomplete and unclear, a more flexible solution allows for implementing future functionality on top of your model without major refactoring. With that said, I would definitely consider Hashtag to be its own independent entity.

In your second model, the tertiary relationship represented in the USER_HASHTAGS table seems to assume an optional many-to-many relationship between Users and Questions. If indeed your domain requires that many users can author the same question, I think model #2 would suite your needs. More likely, your requirement might explicitly restrict the ability for more than one user to author a single specific question. If so, such constraints should be accommodated in the model. Plus if a user decides to tag a question with 5 different tags, that one-to-many relationship between users and questions would be asserted 5 times in your user_hashtags table for each tag. A simple query to display the questions authored by a user would involve using DISTINCT which should immediately raise alarm bells about the design.

Assuming a one-to-many relationship between users and questions, I'd remove the USER_ID from your "USER_HASHTAGS" table and instead put the USER_ID into the QUESTIONS table as a foreign key. Then rename your USER_HASHTAGS table to QUESTION_HASHTAGS. That makes it both simple and performant because now you can simply query a single table to get the questions authored by a specific USER_ID without joining and adding who-knows-how-many duplicates the database would need to filter with DISTINCT. Also now supported is the possibility where a user chooses not to include a hashtag for their question (no blank foreign key).

NOTE:

There are many factors that influence the physical design of a database - not only the "select" access patterns, but potentially even the relative frequency of each. The ratio between database writes to reads, what can be updated, and how often in relation to other updates, etc. can also influence how you structure your tables in the end. So there is no "definitive" answer, just an answer based on a few assumptions and the limited information provided in your question.

Answers 2

Hashtags are already a kind of ID, so no need for a dedicated table for them. All you need is questions table:

create table questions (     id bigint not null constraint questions_pkey primary key,     user_id bigint constraint fk_questions_users references users,     question text not null; ) 

and questions_hashtags relation table with index by hashtag field:

create table questions_hashtags (     question_id bigint not null fk_questions_hashtags_questions references questions,     hashtag text not null,     constraint uk_questions_hashtags unique (question_id, hashtag) );  create index index_questions_hashtags_hashtag on questions_hashtags(hashtag); 

(here is a PostgreSQL dialect.)

These tables simple map to the single(!) entity (not considering User entity):

@Entity @Table(name = "questions") public class Question {     @Id     @GeneratedValue     private Long id;      @Column(nullable = false)     private String question;      @ManyToOne(optional = false)     private User user;      @CollectionTable(name = "questions_hashtags", joinColumns = @JoinColumn(name = "question_id"))     @Column(name = "hashtag")     @ElementCollection(fetch = FetchType.EAGER)     @BatchSize(size = 20)     private Set<String> hashtags = new HashSet<>();      public Question(User user, String question) {         this.user = user;         this.question = question;     }      private Set<String> extractHashtags() {       // extract hashtags from question to Set...     }      @PrePersist     @PreUpdate     private void populateHashtags() {         hashtags.clear();         hashtags.addAll(extractHashtags());     }      // other stuff } 

It's a very convenient model. To create and save question with hashtags you simply do something like this:

questionRepo.save(new Question(user, question)); 

To get all hashtags you can use this query method of your questionRepo:

@Query("select distinct h as hashtag from Question q join q.hashtags h") List<String> getAllHashtags(); 

To find all questions related to particular hashtag you can use the following query method:

@Query("select q from Question q join q.hashtags h where h = ?1") List<Question> getQuestionsByHashtag(String hashtag); 

To find questions by several hashtags you can use this method:

@Query("select q from Question q join q.hashtags h where h in (?1)") List<Question> getQuestionsByHashtag(Set<String> hashtags); 

And to find users related to the given hashtags you can use this method:

@Query("select distinct u from Question q join q.user u join q.hashtags h where h in (?1)") List<User> getUsersByHashtag(Set<String> hashtags); 

---

See my REST service example of hashtag usage sb-hashtag-usage-example:

1) POST /users - create new user

{     "name": "user1" } 

2) POST /questions - create new question

{     "question": "How implement best JPA #entity #model for Hashtags?",     "user": "/user/1" } 

3) GET /hashtags - get all hashtags

4) GET/questions/search/by_hashtag?hashtag=%23model - get questions by one hashtag

5) GET /questions/search/by_hashtags?hashtags=%23entity,%23model - get questions by several hashtags

6) GET /users/search/by_hashtags?hashtags=%23entity - get users by several hashtags

(Other methods like PATCH, DELETE also available.)

Read More

JpaRepository find entities by logged in user context

I am using Spring Boot and Hibernate and JPA repositories to search data. I want to filter search results by logged in user's context. E.G. Find method to return all entities that are owned by logged in user? I have many JPA methods for filtering and I don't want to edit all of them with an additional constraint. Is it possible to do it dynamically?

1 Answers

Answers 1

You can create hibernate filter and enableFilter for session like this

/**  * @author ali akbar azizkhani  */ @Entity @Table(name = "post") @Getter @Setter @NoArgsConstructor @AllArgsConstructor @Filter(name = "filter",condition = "created_By=:user") @FilterDef(name = "filter", defaultCondition = "deleted=0",parameters = {         @ParamDef(name = "user",type = "string") }) public class Post {      @Id     @GeneratedValue     Long id;      @Column(name = "title")     String title;      @Column(name = "deleted")     boolean deleted = false;      @OneToMany(mappedBy = "post")     Set<PostComment> commentList = new HashSet<>();      @Column(name = "createdBy")     String createdBy;  } 

and then enable filter using aspect

@Aspect @Component class EnableFilterAspect {      @AfterReturning(             pointcut = "bean(entityManagerFactory) && execution(* createEntityManager(..))",             returning = "retVal")     public void getSessionAfter(JoinPoint joinPoint, Object retVal) {         if (retVal != null && EntityManager.class.isInstance(retVal)) {             Session session = ((EntityManager) retVal).unwrap(Session.class);             session.enableFilter("filter").setParameter("user","admin");//get from security context holder          }     }  } 

Read More

Fetch multiple onetoMany relationships Hibernate JPA

I am using Hibernate JPA 1.0.

I have the following type of model and I consider manyToOne and oneToOne relationships "eagerly" fetched and oneToMany "lazily" fetched.

I want to fetch Entity A and all its associations where a.id=?

• A oneToMany B
  • B oneToOne C
    • C oneToMany D
  • B oneToOne E
    • E oneToMany D
  • B oneToOne F
    • F oneToMany D

Is it possible to load this entity in a single query? Or in a subset of queries baring in mind the "n+1 selects problem"!

So far my solution to loading all of A associations was to perform the following:

"Select DISTINCT a from A a JOIN FETCH a.bs WHERE a.id=:aID"

And then iterate using code in order to fetch all other associations.

Collection B bs = A.getBs();

         for (final B b : bs) {          b.getCs().getDs().size();          b.getEs().getDs().size();          b.getFs().getDs().size();          } 

Obviously there must be a better way of doing this.

3 Answers

Answers 1

You might be interested in reading this article. I have tested both

@Fetch(FetchMode.SUBSELECT) 

and also using a Set instead of List, in combination with fetch = FetchType.EAGER it works.

Answers 2

Use a FETCH JOIN. From the JPA 1.0 specification:

4.4.5.3 Fetch Joins

A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN is specified over an entity and its related entities.

The syntax for a fetch join is

fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression 

The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.

The following query returns a set of departments. As a side effect, the associated employees for those departments are also retrieved, even though they are not part of the explicit query result. The persistent fields or properties of the employees that are eagerly fetched are fully initialized. The initialization of the relationship properties of the employees that are retrieved is determined by the metadata for the Employee entity class.

SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1 

A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.

Of course, use it wisely, don't join too many tables or you will kill performances.

Answers 3

Looking for an answer drawing from credible and/or official sources.

How about JBoss ORM documentation?

https://docs.jboss.org/hibernate/orm/current/userguide/html_single/chapters/fetching/Fetching.html

There are a number of scopes for defining fetching:

static

Static definition of fetching strategies is done in the mappings. The statically-defined fetch strategies is used in the absence of any dynamically defined strategies

SELECT Performs a separate SQL select to load the data. This can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed). This is the strategy generally termed N+1.

JOIN Inherently an EAGER style of fetching. The data to be fetched is obtained through the use of an SQL outer join.

BATCH Performs a separate SQL select to load a number of related data items using an IN-restriction as part of the SQL WHERE-clause based on a batch size. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).

SUBSELECT Performs a separate SQL select to load associated data based on the SQL restriction used to load the owner. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).

---

dynamic (sometimes referred to as runtime)

Dynamic definition is really use-case centric. There are multiple ways to define dynamic fetching:

Fetch profiles defined in mappings, but can be enabled/disabled on the Session.

HQL/JPQL and both Hibernate and JPA Criteria queries have the ability to specify fetching, specific to said query.

Entity Graphs Starting in Hibernate 4.2 (JPA 2.1) this is also an option.

And to prove the answer above, here's an example:

FetchMode.SUBSELECT To demonstrate how FetchMode.SUBSELECT works, we are going to modify the FetchMode.SELECT mapping example to use FetchMode.SUBSELECT:

Example 17. FetchMode.SUBSELECT mapping example:

@OneToMany(mappedBy = "department", fetch = FetchType.LAZY) @Fetch(FetchMode.SUBSELECT) private List<Employee> employees = new ArrayList<>(); 

Now, we are going to fetch all Department entities that match a given
filtering criteria and then navigate their employees collections.

Hibernate is going to avoid the N+1 query issue by generating a single SQL statement to initialize all employees collections for all Department entities that were previously fetched. Instead of using passing all entity identifiers, Hibernate simply reruns the previous query that fetched the Department entities.

Example 18. FetchMode.SUBSELECT mapping example:

List<Department> departments = entityManager.createQuery(     "select d " +     "from Department d " +     "where d.name like :token", Department.class)     .setParameter( "token", "Department%" )     .getResultList();  log.infof( "Fetched %d Departments", departments.size());  for (Department department : departments ) {     assertEquals(3, department.getEmployees().size()); } 

-- Fetched 2 Departments

SELECT     d.id as id1_0_ FROM     Department d where     d.name like 'Department%'  -- Fetched 2 Departments  SELECT     e.department_id as departme3_1_1_,     e.id as id1_1_1_,     e.id as id1_1_0_,     e.department_id as departme3_1_0_,     e.username as username2_1_0_ FROM     Employee e WHERE     e.department_id in (         SELECT             fetchmodes0_.id         FROM             Department fetchmodes0_         WHERE             d.name like 'Department%'     ) 

Read More

Hibernate inconsistently generating duplicate primary keys

I'm using Spring and Hibernate (hibernate-core 3.3.1.GA), and as a result of a web call, the code does a transaction with several inserts. Sometimes, one of the inserts fails with Hibernate saying 'Duplicate entry ... for key 'PRIMARY'. I have not been able to identify any pattern on when this happens -- it may work for 4 - 5 requests, and then it fails, then works on retrying, and then may fail on the next request.

Below are the relevant parts of the code:

Controller

@RequestMapping(value = "/users", method = RequestMethod.POST) public @ResponseBody Map<Object, Object> save(<params>) throws IllegalArgumentException {     ...     try {             map = userHelper.save(<parameters>);     ...     } catch (Exception e) {         e.printStackTrace();     } } 

The exception is thrown in the above part.

UserHelper.save() method

@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class) public HashMap<String, Object> save(<parameters>) throws NumberParseException, IllegalArgumentException, HibernateException {     ....     userService.save(<parameters>);     return save; } 

UserService

HBDao dao;  @Autowired public UserService(org.hibernate.SessionFactory sessionFactory) {     dao = new HBDao(sessionFactory); } ... @Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class) public HashMap<String, Object> save(<parameters>) throws NumberParseException {     ...     User user;     // several lines to create User object     dao.save(user);     ...     lookupService.saveUserConfigurations(user, userType, loginById);     ...     return response; } 

HBDao

This class wraps hibernate sessions.

public HBDao(SessionFactory sf) {     this.sessionFactory = sf; }  private Session getSession() {     sessionFactory.getCurrentSession(); }  public void save(Object instance) {     try {         getSession().saveOrUpdate(instance);     } catch (RuntimeException re) {         throw re;     } } 

lookupService.saveUserConfigurations(user, userType, loginById) call results in the below methods in LookupRepository class to be executed:

LookupRepository

@Transactional(propagation = Propagation.SUPPORTS, rollbackFor = Exception.class) public LookupMapping save(LookupMapping configuration) {     dao.save(configuration);     return configuration; }  public Collection<LookupMapping> saveAll(Collection<LookupMapping> configurations) {     configurations.forEach(this::save);     return configurations; } 

LookupMapping

@Entity public class LookupMapping {     @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long configId;     ... } 

Hibernate Mapping for LookupMapping class

<hibernate-mapping package="com...configuration.domain">     <class name="LookupMapping" table="lookup_mapping" mutable="false">         <id column="id" name="configId" type="long">             <generator class="increment"/>         </id>         ...     </class> </hibernate-mapping> 

Hibernate config

<hibernate-configuration>     <session-factory name="sosFactory">         <!-- Database connection settings -->         ...          <property name="connection.pool_size">2</property>          <!-- SQL dialect -->         <property name="dialect">com. ... .CustomDialect</property>          <!-- Enable Hibernate's current session context -->         <property name="current_session_context_class">org.hibernate.context.ManagedSessionContext</property>          <!-- Disable the second-level cache -->         <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>          <!-- Echo all executed SQL to stdout -->         <property name="show_sql">true</property>         <property name="format_sql">true</property>          ... </session-factory> </hibernate-configuration> 

Below are the lines from the log:

2018-05-04 10:24:51.321 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 ERROR [http-nio-8080-exec-1] org.hibernate.util.JDBCExceptionReporter - Duplicate entry '340932' for key 'PRIMARY' 2018-05-04 10:24:51.321 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 WARN [http-nio-8080-exec-1] org.hibernate.util.JDBCExceptionReporter - SQL Error: 1062, SQLState: 23000 2018-05-04 10:24:51.322 7|13|60f566fa-4f85-11e8-ba9b-93dd5bbf4a00 ERROR [http-nio-8080-exec-1] org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update     at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167) ~[hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027) [hibernate-core-3.3.1.GA.jar:3.3.1.GA]     at com.arl.mg.helpers.UserHelper.save(UserHelper.java:329) [classes/:?] ... 

I'm working on a legacy codebase (so cannot upgrade Hibernate easily), and the code that I wrote are in LookupRepository class (and LookupService which is called in UserService).

The Duplicate entry error happens while persisting the LookupMapping objects. There are always two of this object being persisted, and when the error occurs, the duplicate ID is created same as the last entry. That is, if for the first request, IDs 999 and 1000 were inserted, and if the error occurs for the next request, the duplicate ID will be 1000 (and not 999).

Another, possibly important thing to note is that Hibernate shows this line:

org.hibernate.jdbc.ConnectionManager [] - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources! 

This is all the info that I have so far, and I hope I've covered the relevant code as well. Any help will be much appreciated. Do let me know if I have to give more info.

Thanks!

2 Answers

Answers 1

The problem was with the ID generation strategy defined in the Hibernate mapping file.

The strategy was set as increment, which seems to work only when there are no other processes inserting to the table. In my case, it seems that sometimes there were previously open sessions, and new requests ended up inserting to the table simultaneously.

The solution was to change the strategy to native, which uses the underlying database's strategy to generate ID.

<hibernate-mapping package="com...configuration.domain">     <class name="LookupMapping" table="lookup_mapping" mutable="false">         <id column="id" name="configId" type="long">             <generator class="native"/>         </id>         ...     </class> </hibernate-mapping> 

Answers 2

I agree with response by @shyam I would switch to some sequence generator.

But also have a look at this peace of code:

User user; // several lines to create User object dao.save(user); ... lookupService.saveUserConfigurations(user, userType, loginById); 

In this case you are sending user to saveUserConfigurations which is not managed, and within saveUserConfigurations you might calling merge method. This will cause additional insert statement. Consider refactoring your code to:

User user; // several lines to create User object // dao.save should return the stored value of user. user = dao.save(user); ... lookupService.saveUserConfigurations(user, userType, loginById); 

With such constructions you will be using stored entity (i.e. managed by current hibernate's session). and have a look at all your code and prevent usage of not managed entities once those have been stored.

Read More

Hibernate CompositeUserType that is comparible in JPA-QL (or HQL) query

I've created a custom type for Hibernate to store an OffsetDateTime's timestamp and offset (because the default JPA 2.2 / Hibernate 5.2 with java 8 support implementation loses the offset information):

public class OffsetDateTimeHibernateType implements CompositeUserType {      @Override     public Class returnedClass() {         return OffsetDateTime.class;     }      @Override     public String[] getPropertyNames() {         return new String[] {"dateTime", "zoneOffset"};     }      @Override     public Type[] getPropertyTypes() {         // Not sure if we should use LocalDateTimeType.INSTANCE instead of TIMESTAMP         return new Type[]{StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER};     }      @Override     public Object getPropertyValue(Object o, int propertyIndex) {         if (o == null) {             return null;         }         OffsetDateTime offsetDateTime = (OffsetDateTime) o;         switch (propertyIndex) {             case 0:                 return Timestamp.valueOf(offsetDateTime.toLocalDateTime());             case 1:                 return offsetDateTime.getOffset().getTotalSeconds();             default:                 throw new IllegalArgumentException("The propertyIndex (" + propertyIndex                         + ") must be 0 or 1.");         }     }      @Override     public OffsetDateTime nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner)             throws SQLException {         if (resultSet == null) {             return null;         }         Timestamp timestamp = (Timestamp) StandardBasicTypes.TIMESTAMP.nullSafeGet(resultSet, names[0], session, owner);         if (timestamp == null) {             throw new IllegalStateException("The timestamp (" + timestamp + ") for an "                     + OffsetDateTime.class.getSimpleName() + "cannot be null.");         }         LocalDateTime localDateTime = timestamp.toLocalDateTime();         Integer zoneOffsetSeconds = (Integer) StandardBasicTypes.INTEGER.nullSafeGet(resultSet, names[1], session, owner);         if (zoneOffsetSeconds == null) {             throw new IllegalStateException("The zoneOffsetSeconds (" + zoneOffsetSeconds + ") for an "                     + OffsetDateTime.class.getSimpleName() + "cannot be null.");         }         return OffsetDateTime.of(localDateTime, ZoneOffset.ofTotalSeconds(zoneOffsetSeconds));     }      @Override     public void nullSafeSet(PreparedStatement statement, Object value, int parameterIndex, SessionImplementor session)             throws SQLException {         if (value == null) {             statement.setNull(parameterIndex, StandardBasicTypes.TIMESTAMP.sqlType());             statement.setNull(parameterIndex, StandardBasicTypes.INTEGER.sqlType());             return;         }         OffsetDateTime offsetDateTime = (OffsetDateTime) value;         statement.setTimestamp(parameterIndex, Timestamp.valueOf(offsetDateTime.toLocalDateTime()));         statement.setInt(parameterIndex, offsetDateTime.getOffset().getTotalSeconds());     }      // ************************************************************************     // Mutable related methods     // ************************************************************************      @Override     public boolean isMutable() {         return false;     }      @Override     public Object deepCopy(Object value) {         return value; // OffsetDateTime is immutable     }      @Override     public Object replace(Object original, Object target, SessionImplementor session, Object owner) {         return original; // OffsetDateTime is immutable     }      @Override     public void setPropertyValue(Object component, int property, Object value) {         throw new UnsupportedOperationException("A OffsetDateTime is immutable.");     }      // ************************************************************************     // Other methods     // ************************************************************************      @Override     public boolean equals(Object a, Object b) {         if (a == b) {             return true;         } else if (a == null || b == null) {             return false;         }         return a.equals(b);     }      @Override     public int hashCode(Object o) {         if (o == null) {             return 0;         }         return o.hashCode();     }      @Override     public Serializable disassemble(Object value, SessionImplementor session) {         return (Serializable) value;     }      @Override     public Object assemble(Serializable cached, SessionImplementor session, Object owner) {         return cached;     }  } 

Now, I want to be able to compare it, so this JPA-QL query works:

       @NamedQuery(name = "Shift.myQuery",                    query = "select sa from Shift sa" +                            " where sa.endDateTime >= :startDateTime" +                            " and sa.startDateTime < :endDateTime") 

on this model:

@Entity public class Shift {      @Type(type = "...OffsetDateTimeHibernateType")     @Columns(columns = {@Column(name = "startDateTime"), @Column(name="startDateTimeOffset")})     private OffsetDateTime startDateTime;     @Type(type = "...OffsetDateTimeHibernateType")     @Columns(columns = {@Column(name = "endDateTime"), @Column(name="endDateTimeOffset")})     private OffsetDateTime endDateTime;      ... 

}

But that fails with:

HHH000177: Error in named query: Shift.myQuery: org.hibernate.hql.internal.ast.QuerySyntaxException: >= operator not supported on composite types. [select sa from org.optaplanner.openshift.employeerostering.shared.shift.Shift sa where sa.endDateTime >= :startDateTime and sa.startDateTime < :endDateTime]     at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79)     at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)     at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218)     at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)     at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)     at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)     at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)     at org.hibernate.internal.NamedQueryRepository.checkNamedQueries(NamedQueryRepository.java:155)     at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:796)     at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:492)     at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:422)     at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:880) 

How can I make my CustomUserType comparable?

1 Answers

Answers 1

Hibernate has no way of knowing how to compare your custom type with multiple columns. You know how the columns relate to each other, but Hibernate doesn't. Without having tested it (can do if I get time later) I think you can rewrite the query to use the property names of the parts, for example:

select sa from Shift sa   where sa.endDateTime.dateTime >= :startDateTimeDateTimePart   and sa.startDateTime.dateTime < :endDateTimeDateTimePart 

To make it work with the offset you would need to normalize the value you compare, i.e. add the number of hours represented by the offset to the date. You can do that with a custom function for your database, see 4.6.17.3 in JPA 2.2 (https://jcp.org/aboutJava/communityprocess/mrel/jsr338/index.html). Of course you could also define a custom compare function in the database that takes both parts as input parameters and call it with the function, but personally I would try to stick to the pre-defined functions as much as possible. Adding hours to a timestamp should be covered no matter what database you are using.

Read More

Java request taking 40-50MB memory(Spring JPA Hibernate)

I am using spring boot with JPA Hibernate. I am monitoring the service for Heap and found that each of my request is taking around 40-50 MB.

So the memory gets increased, after few requests GC runs, it frees the memory and this goes on forever.

So my first question is that is this memory leak?

Also I am trying to find what is causing this. So, I have used Runtime.getRuntime() freeMemory and totalMemory() to identify that around 15MB is getting used when getting one db call and populating projection with it

public interface RecommendationProjection {     public String getType();     public boolean getIsOK();     public int getId();     public int getTagCount();     public double getQuality() ;     public LocalDateTime getLastActivity();  } 

and hibernate returns 567 records, so basically what I am getting from DB is list of 567 above projection, But what i dont understand that how could this object take such high memory? Is hibernate causing this?

When using projection, hibernate queries for specific field or fetches all fields from database?

Then I am mapping this domain to DTO, which again uses 15-20MB memory? this is my DTO

public class RecommendationInfoDTO {     private String type;     private boolean isOK;     private int id;     private int tagCount;     private double quality ;     @JsonFormat(shape=JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss", timezone="IST")     private LocalDateTime lastActivity;       .. getters and setters } 

FYI : For monitoring I am using VisualVM. Can anybody tell me what is possible issue?

I have also analyzed heap dump but could not get anything?

This is my heap dump diff.

I am firing 6 hibernate queries in a request and 3 simple plain mysql queries(using jdbc call)

Issue is just 1 hibernate call. I think some thing is wrong with my hibernate ? Is there any way I could do request based profiling?

Gc / Memory Graph

Heap dump sorted on size

2 Answers

Answers 1

Below is my view:

So the memory gets increased, after few requests GC runs, it frees the memory and this goes on forever.

So my first question is that is this memory leak?

Not necessarily a memory leak. But you need to run and hit the application for a longer period of time and see how the memory is getting released during GC cycles. As long as the memory usage kind of follows a sawtooth pattern it is a one indicator that the GC is able to reclaim the garbage and memory is utilized efficiently.

When using projection, hibernate queries for specific field or fetches all fields from database?

No it fetches only the specified columns in case of projection.

Then I am mapping this domain to DTO, which again uses 15-20MB memory?

It is not only your DTOs, but hibernate and on top of spring-data-jpa would be creating their own objects internally to fulfill the query and those objects might be awaiting GC. As long as they are getting reclaimed after GC cycle and the memory usage is not increasing constantly after each GC, it is a healthy sign.

But more than the memory used by each request, you may want to look at the bigger picture and some of the items (not an exhaustive/complete list) could be:

1. See how the memory usage is over a period of time for the normal load and peak load scenarios.
2. How frequently are minor and major GC happening and how it impacting the application?
3. Is the application spending too much of time in GC itself"?
4. Tune the GC based on the application behavior. For ex: is the app creating too many short lived objects for serving requests etc.
5. Given the heap/GC configuration, is the application able to meet your application response time and throughput as expected?

And finally you may want to go through java8 GC tuning guide to understand the GC and tuning it.

Answers 2

This looks to me to be normal behavior.

So my first question is that is this memory leak?

No. Memory leaks require for the memory to remain allocated beyond its useful life. Since your GC is clearing the total memory space consumed by the query, you are not leaking, you're simply using memory.

But what i dont understand that how could this object take such high memory?

The object isn't taking much memory, it's the 567 instances of the object per query that are taking the memory.

Lets take a look to explain why:

Each instance of your projection contains

• a String of unknown length (Strings are not primitives, so there are a significant number of metadata attributes one top of the pure character count, but lets just say 1 byte)
• a boolean which allocates 1 byte
• 2 int of a byte each
• a double of 2 bytes
• and LocalDateTime which is made of several fields (so lets be optimistic and say at 2 bytes)

So each instance is at least 8 bytes. 567 * 8 = 4536 bytes minimum per query.

You're firing 6 queries against this dataset 4536 * 6 = 27216 bytes per method call

Some of this is overhead in hibernate which gets reused between calls, so you won't quite see the full theoretical footprint.

This is relatively close to what you're observing, so I don't think anything is misbehaving.

If you're expecting a smaller footprint, reevaluate your approach to reuse as much data as possible to reduce the number of queries you have to make.

Read More

Mapping MySQL JSON column to hibernate value type

What is the best practice for working with JSON column within Java code? Specifically I am interested in being able to save & query JSON columns in a MySQL DB using hibernate..

@Entity public class MyEntity {      private String myField; // this field is a json column      public MyEntity() {     } } 

3 Answers

Answers 1

The mapping between JSON and Java entities JSON.simple maps entities from the left side to the right side while decoding or parsing, and maps entities from the right to the left while encoding.

JSON    Java string  java.lang.String number  java.lang.Number true|false  java.lang.Boolean null    null array   java.util.List object  java.util.Map 

On decoding, the default concrete class of java.util.List is org.json.simple.JSONArray and the default concrete class of java.util.Map is org.json.simple.JSONObject.

Encoding JSON in Java Following is a simple example to encode a JSON object using Java JSONObject which is a subclass of java.util.HashMap. No ordering is provided. If you need the strict ordering of elements, use JSONValue.toJSONString ( map ) method with ordered map implementation such as java.util.LinkedHashMap.

import org.json.simple.JSONObject;  class JsonEncodeDemo {     public static void main(String[] args){       JSONObject obj = new JSONObject();        obj.put("name", "foo");       obj.put("num", new Integer(100));       obj.put("balance", new Double(1000.21));       obj.put("is_vip", new Boolean(true));        System.out.print(obj);    } } 

On compiling and executing the above program the following result will be generated −

{"balance": 1000.21, "num":100, "is_vip":true, "name":"foo"} 

Following is another example that shows a JSON object streaming using Java JSONObject −

import org.json.simple.JSONObject;  class JsonEncodeDemo {     public static void main(String[] args){        JSONObject obj = new JSONObject();        obj.put("name","foo");       obj.put("num",new Integer(100));       obj.put("balance",new Double(1000.21));       obj.put("is_vip",new Boolean(true));        StringWriter out = new StringWriter();       obj.writeJSONString(out);        String jsonText = out.toString();       System.out.print(jsonText);    } } 

On compiling and executing the above program, the following result is generated −

{"balance": 1000.21, "num":100, "is_vip":true, "name":"foo"} 

Decoding JSON in Java The following example makes use of JSONObject and JSONArray where JSONObject is a java.util.Map and JSONArray is a java.util.List, so you can access them with standard operations of Map or List.

import org.json.simple.JSONObject; import org.json.simple.JSONArray; import org.json.simple.parser.ParseException; import org.json.simple.parser.JSONParser;  class JsonDecodeDemo {     public static void main(String[] args){        JSONParser parser = new JSONParser();       String s = "[0,{\"1\":{\"2\":{\"3\":{\"4\":[5,{\"6\":7}]}}}}]";        try{          Object obj = parser.parse(s);          JSONArray array = (JSONArray)obj;           System.out.println("The 2nd element of array");          System.out.println(array.get(1));          System.out.println();           JSONObject obj2 = (JSONObject)array.get(1);          System.out.println("Field \"1\"");          System.out.println(obj2.get("1"));               s = "{}";          obj = parser.parse(s);          System.out.println(obj);           s = "[5,]";          obj = parser.parse(s);          System.out.println(obj);           s = "[5,,2]";          obj = parser.parse(s);          System.out.println(obj);       }catch(ParseException pe){           System.out.println("position: " + pe.getPosition());          System.out.println(pe);       }    } } 

On compiling and executing the above program, the following result will be generated −

The 2nd element of array

{"1":{"2":{"3":{"4":[5,{"6":7}]}}}} 

Field "1"

{"2":{"3":{"4":[5,{"6":7}]}}} {} [5] [5,2] 

Answers 2

Use SpringMVC because it implicilty includes jackson and will save your time.

@Autowired MyEntityService myEntityService;  @RequestMapping("/myentity") public @ResponseBody MyEntity getEntity(@RequestParam(value = "myField") String myField) {      return myEntityService.getMyEntity(myField); } 

Reference documentation https://www.javadevjournal.com/spring/building-restful-web-services/

Answers 3

There is good library to deal with json types in Hibernate: https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/ scroll down to MySQL section to get right example.

And some samples of usage MySQL's JSON API inside query: How to search JSON data in mysql?

Read More

Can I add 'ON DELETE CASCADE' to tables managed by Hibernate?

I have some tables managed by Hibernate with various foreign key constraints. Cascade on delete is currently managed by Hibernate alone. For playing around with test data I often create and remove some rows by hand. It would help me a lot if I could add ON DELETE CASCADE to the foreign key constraints but I don't know if Hibernate trips over this because the database removes stuff before Hibernate does.

---

A lot of people seem to concentrate on DDL. My intention is not to instruct Hibernate to create DDL with SQL DELETE CASCADES. I just want to know if it does any harm if I specify an ON DELETE CASCADE in the database in addition to having JPA's cascade = CascadeType.REMOVE on the reference annotation, e.g., @ManyToOne.

6 Answers

Answers 1

You can use CascadeType.DELETE, however this annotation only applies to the objects in the EntityManager, not the database. You want to be sure that ON DELETE CASCADE is added to the database constraint. To verify, you can configure JPA to generate a ddl file. Take a look at the ddl file, you'll notice that ON DELETE CASCADE is not part of the constraint. Add ON DELETE CASCADE to actual SQL in the ddl file, then update your database schema from the ddl. This will fix your problem .

This link shows how to use ON DELETE CASCADE on for CONSTRAINT in MySQL. You do this on the constraint. You can also do it in a CREATE TABLE or ALTER TABLE statement. It's likely that JPA creates the constraint in an ALTER TABLE statement. Simply add ON DELETE CASCADE to that statement.

Note that some JPA implementors do provide a means for this functionality.

Hibernate does supply this functionality using the @OnDelete annotation, thus it is preferred to use this or simply update the ddl file if you would like to stick with standard JPA functionality.

Answers 2

You can use the native database functionality to delete the child records upon deletion of parent record.

Be aware of bi-directional relationships and to be sure, ensure you just specify insert and update in cascade (to be on safer side).

Answers 3

You mention for testing purposes. I'm guessing, execute some test, delete data, replay test...

When using second-level caching or query cache, the cache will and up being stale if you directly remove the data from the database. This might result in unexpected test results.

So yes, this will conflict with Hibernate if you use second-level / query caching as the entity's will not get evicted from cache. Make sure all caches get cleared after you directly deleted any data. See this question on how to clear cache.

The official Hibernate docs also mention this:

Be aware that caches are not aware of changes made to the persistent store by other applications. They can, however, be configured to regularly expire cached data.

Answers 4

I see two potential issues:

1. If an entity that represents the table to which you cascade operations directly in the database is versioned, then it would not work because when Hibernate tries to delete records on its own, the version check would fail (Hibernate would assume concurrent thread already updated or deleted the corresponding records).
2. If there are use cases in which your business logic re-persists such entity instances after removal has been cascaded to them from the parent (for example, you are deleting old parent and migrating associated children to a new one, although for better clarity I would not cascade removal at all if such a use case exists for an association, but it's up to you as it is allowed by the JPA spec), then Hibernate would just un-schedule the deletion of children and delete only the parent, so you would still end up with deleted children if you cascade deletion in the database.

Probably there are some other situations that could be problematic in some scenarios, so I would recommend not to do it.

Answers 5

Use orphanRemoval = true clause in your @OneToMany relation. Then when the main entity (ParameterGroup) gets deleted, every related record (Parameter) will get deleted first. Just delete ParameterGroup entity via entityManager. Also remember to set cascade clause as CascadeType.ALL (support all cascade operations) or CascadeType.REMOVE (support only cascade deletion).

@Entity @Table(name = "PARAMETER_GROUP") public class ParameterGroup {      @Id     private Long id;      @OneToMany(mappedBy = "parameterGroup", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)     private List<Parameter> parameters = new LinkedList<>();  }  @Entity @Table(name = "PARAMETER") public class Parameter {      @Id     private Long id;      @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.REFRESH)     @JoinColumn(name = "PARAMETER_GROUP_ID")     private ParameterGroup parameterGroup;  } 

From documentation:

public abstract boolean orphanRemoval (Optional)

Whether to apply the remove operation to entities that have been removed from the relationship and to cascade the remove operation to those entities.

Answers 6

Don't use cascade = CascadeType.REMOVE Documentation here

Because of your db may be destroyed. You can use formal order. Delete sub stable and then remove master table

Read More

In Hibernate HQL, how do I left join fetch a subclass' linked entities when multiple subclasses have a property with the same name?

Summary

In Hibernate, I am trying to LEFT JOIN FETCH properties from subclasses of a main class, where multiple subclasses have properties with the same name. Hibernate, though, is only fetching the first subclass' linked entities, and not the others.

Background

I am modelling genomic features (genes, transcripts and exons) and genetic variants in a Hibernate-based system. Genes, Transcripts and Exons are all subclasses of GenomicFeature, and Variants can each have zero-to-many GenomicFeatures. Genes, in turn, have zero-to-many transcripts -- as do Exons -- and Transcripts have zero-to-many Genes and Exons. Each of these relationships are fetched lazily. Sometimes, though, I want to fetch a variant and all of its genomic features, as well as all of the genomic features linked from the immediate genomic features. Eg, I want to fetch a specific variant, the Genes/Transcripts/Exons associated with the variant, all the Transcripts of the genes, all the Genes&Exons of the Transcripts, and all the Transcripts of the Exons.

The problem

When I do a query for the above, it works except that the Transcripts for the Genes are not fetched, only the Exon's Transcripts are fetched. I assume this is because the property -- gene.transcripts and exon.transcripts -- shares the same name.

What I've tried

The main query, in a PagingAndSortingRepository, is below

    @Query("SELECT v FROM Variant v"         + " LEFT JOIN FETCH v.variantGenomicFeatures AS vgf"         + " LEFT JOIN FETCH vgf.genomicFeature AS gf LEFT JOIN FETCH gf.genes LEFT JOIN FETCH gf.exons LEFT JOIN FETCH gf.transcripts"         + " WHERE"         + "     v.id = (:id)") public Variant findOneByIdAndGenomicFeaturesEagerly(@Param("id") Integer id); 

I've tried joining genomicFeature twice, once for Genes and once for Transcripts&Exons, but this doesn't work.

I've tried only selecting Genes (WHERE TYPE(gf) = Gene), but looking at the query it generates, it still joins only the Exon's Transcripts and then returns the Genes.

2 Answers

Answers 1

I figured it out. It just works. I had a syntax error in my HQL.

When a discriminated subclass has properties/column that are not in the root class all properties of all distriminator subclasses are accessible in the HQL when using the root class in the FROM statement.

Answers 2

I didn't try myself but maybe with alias on fetch clause can help Hibernate to distinguish field ?

LEFT JOIN FETCH gf.genes as g  LEFT JOIN FETCH gf.exons as e  LEFT JOIN FETCH gf.transcripts as t 

Read More

Hibernate Batch process : Stateless Session VS Regular Session Native Query

As I understand so far stateless sessions are preferred when using batch processes since It will just detach objects doing the process So that persistent context and cache would be free so batch processes are the idle case for that, It is commonly known as plain JDBC Query Engine with queries translated to SQL queries immediately. Referenced from : https://stackoverflow.com/a/14174403/1460591

On the other hand, I learned that native queries do the same the one difference I see is that the stateless session can map the result to an entity, Native queries don't do that until mapper is explicitly provided.

So is there another difference and from a performance point of view which is better when doing batch processes?

3 Answers

Answers 1

If by batch processing you mean modifying entities in the database server via the SQL query itself (e.g. UPDATE things SET daily_quota=15) then the native SQL is faster. However, in this case, you aren't loading any entities so this doesn't really seem to jive with your question.

If by batch processing you mean modifying entities in your program (e.g. load all Thing instances, modify the dailyQuota attribute to 15 and write an update then you will want a stateless session.

Using a native query to retrieve the objects doesn't give you any mechanism to modify the object. You still need to merge it back to the persistence context and flush those changes. Once you do this (assuming you don't have a stateless session) then it will use the classic change-detecting & cache-keeping flush mechanism.

A stateless session on the other hand gives you a way to modify entities in your program without forcing the ORM layer to go through the slow change detection process.

Answers 2

As always, if it comes to performance, the best thing to do is to measure. Noone can tell which one will be better in your setup, we don't know which DB you are using, which Hibernate version, which OS, etc. Depends on a lot of things.

However, if it comes to performance, the best thing you can do is using native queries. If you can simply put your update logic into a query, do it and execute the query. In this case the DB will handle everything which is the best in terms of performance.

If it's a requirement for you to work with entities, go with stateless session because as you mentioned it gives you the possibility to map the results of a query to entities. Although, there are other differences between going stateless and native queries:

With stateless session you will lose:

• First level caching
• Second level caching
• Any interceptor mechanism, as it's bypassing everything
• Automatic dirty checking
• Cascading

Of course losing these also means performance, but if any of these are important for you, it's better to avoid then.

In case you want to go with a regular session, you can use JDBC batching as well which greatly can improve the performance, but make sure you regularly flush and clear your persistence context to avoid growing your cache.

Answers 3

Hello before crashing into the Hibernate Stateless session I would seriously ask myself why do I need it. First you already got the idea and the features you will loose when using Stateless session.

Even though the hibernate session is memory consuming you can still get decent performance in most cases speed. If you can not do that probably you have other problems in your code.

I would like to emphasize that the stateless session does not cascade the persist, merge and so on.. operations. This will remove a significant element of the authomatization you would normaly recieve through hibernate. This may affect your code in long run in terms of maintanability and the ease of change.

Since no code is completly static in terms of development, new features are coming sometimes something needs to be changes and so on.... very often a simple at a first glance batch may turn into something pretty big. In which case the amount of features you have at hand really matters.

I would argue that if you realy are in desparate need of performance then you should have a clear separation of the persistence from tghe business layer in terms of repositorys or something else. And if nessesary you just write some pure SQL based repositories or something that is as close as possible to native sql for example JOOQL.

During my career I have seen some pretty complex batches written with normal hibernate session running quite OK when the SLA is taken in mind. I have a resent example of a batch running over 2000 distributed transactions per second over 16 cpus writen with Hibernate with normal session.

The very few cases where I need massive performance I would just use JOQL because it is the closest thing to SQL which maintains ORM relations.

Read More

Mysql lock timeout when inserting concurrently

I am trying to insert concurrently (heavy inserts by 8 threads) into sql throught hibernate. My pojo consists of two tables, one table references the other through foreign key constraint. I am trying to save a lot of instances of my pojo to the db concurrently. Sometimes the insert is failing and rolling back because of lock wait timeout.

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction.

Suppose there are table A(table edges in the screenshot) and table B. Table B has a foreign key constraint which references it's id to primary key of table A. What I could infer from the locks table is an S lock is being held on table A's record by trx 114888 while try to insert in table B(id corresponding to table B) and 11493 is waiting to acquire X lock to insert new record in table A. Table A has index on some of its columns.

What is meant by supremum pseudo-record here? Is it a gap lock? If so then why is the record type as 'RECORD'? Is there a way around this so as to avoid this gap lock or whatever lock it is?

These are the screenshots of the lock tables. INNODB_LOCKS table screnshot

Some innodb status logs

---TRANSACTION 114893, ACTIVE 2611 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 48609 lock struct(s), heap size 4726992, 585460 row lock(s), undo log entries 1132742 MySQL thread id 12620, OS thread handle 123145553027072, query id 38123782 localhost 127.0.0.1 root update insert into edges (---some values--) Trx read view will not see trx with id >= 114862, sees < 114817 ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 408 page no 135298 n bits 240 index PRIMARY of table `**database**.edges` trx id 114893 lock_mode X insert intention waiting Record lock, heap no 1 

1 Answers

Answers 1

You are using the repeatable read isolation level. In the repeatable read isolation level so called gap locks are used and are held for the duration of the transaction (you can read more about gap locks in the documentation). If you switch the isolation level from repeatable read to read committed, the problem will go away. You can set the isolation level with

set transaction isolation level read committed 

You should check the documentation of the command. The isolation level can be set at a session level or global level.

Read More