Skip to main content

JHipster, JPA, and MySQL database character set

After I generated my entities and got all the basic functionality of a JHipster application running, I started working on a multi-table join query that I want to use for reporting.  I don't want to query individual records and then aggregate my query results into a report using java; this is inefficient, and a single query can do all of the work for me, keeping the database doing what it does best.  So I wrote the query, and I then had to figure out how best to use this query in the JHipster framework.

The best option seems to be using JPA's native SQL API and mapping results to a custom data object.  I can inject an EntityManager, and that allows me to call createNativeQuery().  I can then call setParameter() for each parameter needed by referencing my parameters by name like this:

Query
select foo
from bar
where some_column = :columnParam

API
nativeQuery.setParameter("columnParam", <valueToUse>)

So far, so good.  When I make this call at runtime, however, I get this error:

org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet
...
...
Caused by: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

Some googling suggests that this is an old/known problem for MySQL, though it is new to me.  The problem is that the code and configuration generated by JHipster runs spring boot such that it assumes a collation / character set that supports utf8mb4, which is a more complete utf8 set that can represent emojis, etc.  While I have no need for this, I don't want to walk down a backwards road in the learning process.

Fortunately, after searching around a lot, I came to a fairly simple answer: re-create the database (JHipster helped with this by configuring and using liquibase to generate database contents on startup -- hurray!) using the desired collation and character set for the database in MySQL.  For me, that ended up being:

Character set: utf8mb4 -- UTF-8 Unicode
Collation: utf8mb4_general_ci

After I made this change, the query started working successfully.
Labels:

Comments

Post a Comment

Popular posts from this blog

Spring Security - Authority vs Role

I have spent a lot of time recently trying to understand the difference between Authority and Role in Spring Security.  This is a brief review of what I found. When creating a UserDetailsService or overriding configure(AuthenticationManagerBuilder auth) in the security config class that extends WebSecurityConfigurerAdapter, I basically get complete control over what I populate inside of the UserDetails that is used/returned.  This is important because the UserDetails interface really only cares about how to return one thing: Collection<? extends GrantedAuthority> getAuthorities(); A GrantedAuthority just seems like a glorified String wrapper that names some thing.  The question is... what is that thing? This is where the subtle difference between Authority and Role comes into play. I think that Role is an older thought/construct that automatically gets plugged into Authority if we just create a user with a Role.  But completely forget about the code a...
Post a Comment
Read more

SQL, Booleans, JPA, and Hibernate

For a long time, SQL and Booleans have not gotten along.  Standards for SQL never really addressed the need for boolean data -- it was assumed that some other data type could easily just step in and address this need.  The result was a lot of different data models for boolean values.  Here are some examples. TRUE or FALSE T or F Y or N 1 or 0 <any value> vs NULL The internet shows the debate has gone on , even as SQL standards have changed .  Coming from a professional background with Oracle, I struggled with this across my teams because everyone had a different opinion, which led to a lot of time wasted due to debate. This said, I appreciate working with native queries in hibernate's JPA implementation against MySQL.  MySQL supports a BIT data type I recently discussed .  When we represent data in MySQL with BIT and restrict the length to just 1 (ie 1 bit), Hibernate JPA magically knows to query and return this data as a Boolean in the ...
Post a Comment
Read more

ANSI-92 SQL Syntax - aka "JOIN" me in software progress!

 I've run into several projects lately that don't use and/or refuse to use the JOIN keyword/syntax.  I've had conversations in these projects to help people understand that, but I wonder why this is not more common across SQL database projects.  This standard is not new, and it more easily defines the join section vs the WHERE/filter section, so it has good benefits.  So far, the only reason I have heard to not use it is "we just don't tend to do that here."  This makes me sad. I hope to continue evangelizing small changes like this in my projects, be it JOIN, use of functional programming, listening to IDE feedback (great static analysis checking... available before you ever even push!), etc.  There are so many small things that help make software easier for everyone, I think.
Post a Comment
Read more