MariaDB issues

I ran into some issues, and I thought I'd document them here.

Not able to detect platform for vendor name [MariaDB1010.3.17-MariaDB]. Defaulting to [org.eclipse.persistence.platform.database.DatabasePlatform]. The database dialect used may not match with the database you are using. Please explicitly provide a platform using property "eclipselink.target-database".]]

So changed my persistence.xml and added:

<property name="eclipselink.target-database" value="MySQL4"/>

Also:

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=16) Table 'mmud.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
       bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
       at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

As the vendor name was not detected, eclipselink switched to a default implementation. The default implementation requires SEQUENCES for the IDENTITY definition.

It turns out the MariaDB implementation of Sequences is not compatible with the standard SQL way of creating sequences.

So I had to add the following property to the JDBC Connection pool:

useMysqlMetadata = true

References

JIRA MariaDB : since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns

https://jira.mariadb.org/browse/CONJ-702

Java Persistence API (JPA) Extensions Reference for EclipseLink, Release 2.4 : target-database

https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_target_database.htm

Eclipse JIRA : Bug 462196 - Add support for MariaDB

https://bugs.eclipse.org/bugs/show_bug.cgi?id=462196

Using SQL to generate JSON output

I recently read [1], and it had a very interesting notion.

The idea is to let the database generate JSON, and provide it straight into your client.

So I decided to find out if MariaDB had some support for this as well.

It does².

So, it basically was nothing more then calling a NativeQuery on the EntityManager, and returning a concatted resultset with a '['prefix and a ']'postfix and a comma-delimiter and away we go.

The native query looked like the following:

It worked flawlessly!

Caveat: of course, this is only in the case where your middleware (as in this example) really doesn't need to do anything with the result.

I mean, you are going to have to do all checks in the database query.

I think this example shows its strength when you just really want to read a lot of data, and do not need to process it.

References

[1] Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

https://blog.jooq.org/2019/11/13/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/

[2] MariaDB - starting with 10.2.3 - JSON Functions

https://mariadb.com/kb/en/library/json-functions/

MariaDB - JSON with MariaDB Platform: What Is JSON and Why Use It – With Examples

https://mariadb.com/resources/blog/json-with-mariadb-10-2/

MariaDB - Relational and Semi-structured Data

https://mariadb.com/database-topics/semi-structured-data/

MariaDB - Function Differences Between MariaDB 10.4 and MySQL 8.0

https://mariadb.com/kb/en/library/function-differences-between-mariadb-104-and-mysql-80/#present-in-mysql-only

Do not use Optional in method parameters

The Java language authors have been quite frank that Optional was intended for use only as a return type, as a way to convey that a method may or may not return a value.

At work there are some cases where providing an Optional as a method parameter is a good idea, because QueryDSL has been configured to automatically return an empty resultset if the optional is not present.

But an Optional as a method parameter seems to be a disputed design choice¹.

A lot of Java tooling complains about it (SonarLint, Findbugs, etc).

A lot of people mention that method overloading might be a better and clearer choice.

For example:

I took a stab at it:

Than I had to go and throw up, this is ugly as sin!

The best answer I found on StackOverflow³ without Java 9 so far:

Now this seems very interesting, but they needed to invent something better. And they did. In Java 9.

Optional.ifPresentOrElse​

Perhaps this is why in Java 9² there is a new method in the Optional class called ifPresentOrElse​.

References

[1] StackOverflow - Should Java 8 getters return optional type?

https://stackoverflow.com/questions/26327957/should-java-8-getters-return-optional-type

[2] JavaDoc - Optional (Java SE 9 & JDK 9)

https://docs.oracle.com/javase/9/docs/api/java/util/Optional.html

[3] StackOverflow - Functional style of Java 8's Optional.ifPresent and if-not-Present?

https://stackoverflow.com/questions/23773024/functional-style-of-java-8s-optional-ifpresent-and-if-not-present

IntelliJ Idea Blog - Java 8 Top Tips

https://blog.jetbrains.com/idea/2016/07/java-8-top-tips/

SonarSource rules - "Optional" should not be used for parameters

https://rules.sonarsource.com/java/RSPEC-3553

DZone - Optional Method Parameters

https://dzone.com/articles/optional-method-parameters

The Pitfalls of Boolean Trap

A colleague is looking to update all inspections in IntelliJ at work, which is commendable.

He referred a link, see [1], to me regarding the use of Booleans as method parameters ("switches"), which is a pet peeve of another colleague of mine.

I thought I'd just mention it here, for who's interested.

References

[1] The Pitfalls of Boolean Trap

https://ariya.io/2011/08/hall-of-api-shame-boolean-trap

XMLGregorianCalendar off by a few days on dates before 1582

Oh, darn. Just got bit by this thing.

The customer had stored documents for which it did not know the date with date 01-01-0007.

When importing it via XML, it turned out wrong.

The reference [1] below explains why.

References

[1] StackOverflow - Date change when converting from XMLGregorianCalendar to Calendar

https://stackoverflow.com/questions/16321193/date-change-when-converting-from-xmlgregoriancalendar-to-calendar

Wikipedia - Proleptic Gregorian Calendar

https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar