When using use the MariaDB Connector/J with UTF-8 data, some characters are not properly rendered, and the collation from the database is not respected. The problem was observed when trying to use latin1_spanish_ci collation in driver version 2.5.4 (mariadb-java-client-2.5.4.jar) - see MariaDB issue CONJ-417 Support characterEncoding and collation in jdbc and Setting Character Sets and Collations in the MariaDB knowledge base for more information.
The short answer is to not set connection collation at all and instead upgrade drivers to the most recent version.
If you do need to set a collation sequence, you should set an utf8mb4 collation (such as utf8mb4_spanish_ci ).
Please note that drivers before version 2.6.1 may use 'utf8' (not 'utf8mb4') if server charset defaults to 'utf8'. In this special case, this may result in issues using 4 bytes utf8 encoded characters, like most of the smileys for example (thanks Diego :-).
Still, using an older driver, it may be possible to resolve this by setting the following session variables in the connection string (thanks Martie :-)
Database Connection > Properties > Driver Properties > SessionVariables:
character_set_client=utf8,
character_set_results=utf8,
character_set_connection=utf8,
collation_connection=utf8mb4_spanish_ci
Workaround: MySQL Driver
If this doesn't work, you may be able to use the MySQL jdbc driver, not the MariaDB driver (even though the database itself is MariaDB), and set the following driver properties:
- characterEncoding utf-8
- connectionCollation latin1_spanish_ci
Disclaimer:
Depending on the version of the database and driver, you may experience other problems when running MariaDB using a MySQL driver; we recommend using the MariaDB driver when connecting to a MariaDB server.