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

JHipster, Liquibase, MySQL, and initializing data, including booleans!

When generating a data model from JHipster JDL, we will often declare entities with Boolean fields.  I have so far abandoned H2 as a database because of liquibase issues, and both my dev and production databases will be MySQL.  This is relevant to the Boolean field desire there is a long history in software development of how to store Boolean data types in a SQL database whose standards classically do not support Boolean. In the current JHipster/Liquibase incarnation, tables in MySQL are generated for us, which is really nice.  The Boolean data types are stored as BIT  (1).  This is not a problem so far -- most developers seem to agree now that as a best practice, we should store values in databases as false = 0 and true = 1, and a BIT(1) is a great, simple way to do that. An issue arises when we try to use liquibase to set/update our database to the desired starting state.  For my project, I've chosen gradle instead of maven as a build tool, and gradle has a plugin for liquiba
Post a Comment
Read more

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 and classes for a mi
Post a Comment
Read more

Gmail internal application, Two-Legged OAuth2, Server to Server authentication, and Google API versions

I am working on a little tool at home in my free time to put some skills into practice.  The general idea (nothing novel) is this: I have some financial alerts sent to a new email address I have spun up on my domain.  I am creating an AWS Lambda that will wake up on an hourly schedule, read those emails, and publish SNS messages with parsed financial transaction information.  I then will have an SQS queue listen to the SNS message topic that is consumed by a Step Function.  The Step Function will: store the financial transaction information into a database send an SMS to me if the transaction is above a certain threshold. I could later extend this to do some aggregation reporting, etc if I wanted, too.  This will only work for my own financial transactions, and the data being gathered/stored will be sufficiently vague, so I am not really concerned about financial security for this project. The biggest hurdle I have run into so far is connecting to Gmail securely.  I was ab
Post a Comment
Read more