Codegenerated Path-Expressions with Databases (Mariadb, Mysql) that have unique FK-names

Bernd Huber

unread,

Jun 11, 2024, 4:40:12 AM6/11/24

to jOOQ User Group

Im using Mariadb, and the FKs in Mariadb need to be provided with a unique FK-name.

see:

- https://mariadb.com/kb/en/foreign-keys/

- Quote: "The symbol clause, if specified, is used in error messages and must be unique in the database."

Sadly (because of this) the jooq-codegen generates path-expression functions on the Tables that are exactly reflecting this FK-name (which is often a bit long, and can sometimes get very long).

For example:

PRODUCT.fk_product_creatorId().USERID,
PRODUCT.fk_product_editorId().USERID,
PRODUCT_LANG.fk_product_lang_productId().PRODUCTID

I know that this is a problem of Mariadb/Mysql and probably there can nothing be done from jooqs side, but i envy other databases, where the fk function name would be very short and concise. Ideally i would like to see:

PRODUCT.creator().USERID,
PRODUCT.editor().USERID,
PRODUCT_LANG.product().PRODUCTID

Is there maybe a trick with the Jooq-Codegen Settings, where one would be able (with Regex) to replace the Path-Expression Function-Names in the generated code with such shorter variants ?

For me the rule could be:
- Take the String after the last "_"
- Cut of any "Id" at the End of the String

But maybe i just need to accept that MySql/Mariadb are causing the problem here, and i need to accept for now the long path-expressions :D

Its not really urgent or important, just some small thing i wonder if it could be solved.

Greets,

Bernd

Lukas Eder

unread,

Jun 11, 2024, 10:20:42 AM6/11/24

to jooq...@googlegroups.com

Look into these sections:

- https://www.jooq.org/doc/latest/manual/code-generation/codegen-generatorstrategy/

- https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d951b8e8-65f3-4ac0-86e7-876b5a6514e8n%40googlegroups.com.

Bernd Huber

unread,

Jun 11, 2024, 3:07:25 PM6/11/24

to jOOQ User Group

thank you Lukas,

im currently using a custom GeneratorStrategy.

It seems when i use a custom GeneratorStrategy i can not additionally use the Matchers.

I read about it in following page: https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

Quote:

- "...Instead of specifying a strategy name, you can also specify a <matchers/> element as explained in the following subsections.."

So its an either or i guess? (either strategy name or matchers)

---

i have tried the matchers out and it was very easy to achieve the result i asked about. Really nice!

.withStrategy(new Strategy()
...
.withMatchers(new Matchers()
.withForeignKeys(
new MatchersForeignKeyType()
.withExpression("^(.*)_(.*?)(Id)?$")
.withPathMethodName(new MatcherRule()
.withExpression("$2")
)
.withPathMethodNameManyToMany(new MatcherRule()
.withExpression("$2")
)
.withPathMethodNameInverse(new MatcherRule()
.withExpression("$2")
)
)
)
)

---

but unfortunately i still need a Custom GeneratorStrategy for creating Getters/Setters correctly.

When i use the default Strategy (Strategy()) instead, then the Getters/Setters look somehow off to me.

For example:

i have following fields in the database:

- clientId

- userId

The code-generator would generate following Getters/Setters for them with the default-settings:

- setUserid / getUserid

- setClientid / getClientid

This seemed strange to me, and i fixed those with a CustomGenerator, so the "Id" stays as it is in the database

(only Uppercasing the first Letter of the fieldname and adding a leading "set" / "get")

- setUserId / getUserId

- setClientId / getClientId

In addtion to that i also have some fields in the database (liquibase) that are completely uppercase:
- CONTEXTS
- LABELS

The code-generator would generate following Getters/Setters for them with the default-settings:
- setContexts / getContexts
- setLabels / getLabels

I also would like to keep the fieldname behind the setter/getter exactly in uppercase like its in the database, only Uppercasing the first Letter of the fieldname
- setCONTEXTS / getCONTEXTS
- setLABELS / getLABELS

i also think somewhere i had problems with using Java-Reflection on the methods/fields and by changing it that way,
this helped me to get java-reflection work.

When i checked the MatcherStrategies i could not find a way to replicate this behavior with the Matchers.
I tried out following which came pretty close, if the "withTransform" would only apply to the Source (before applying the expression/regex on it)

.withFields(
new MatchersFieldType()
.withFieldIdentifier(new MatcherRule()
.withTransform(MatcherTransformType.AS_IS))
.withFieldMember(new MatcherRule()
.withTransform(MatcherTransformType.AS_IS))
.withFieldGetter(new MatcherRule()
.withExpression("$0")
.withTransform(MatcherTransformType.UPPER_FIRST_LETTER))
.withFieldSetter(new MatcherRule()
.withExpression("$0")
.withTransform(MatcherTransformType.UPPER_FIRST_LETTER))
)

---

still a pretty great API here!

i wonder why i need to customize the Getters/Setters so much though.
It seems to me that normally the defaults of jooq should be the best, but i needed to change those (as named for the reasons above)

My CustomGenerator looks like this:

public final class FkGeneratorStrategy extends DefaultGeneratorStrategy {
@Override
public String getJavaSetterName(Definition definition, Mode mode) {
return "set" + definition.getOutputName().substring(0, 1).toUpperCase() + definition.getOutputName().substring(1);
}
@Override
public String getJavaGetterName(Definition definition, Mode mode) {
return "get" + definition.getOutputName().substring(0, 1).toUpperCase() + definition.getOutputName().substring(1);
}
@Override
public String getJavaMethodName(Definition definition, Mode mode) {
return definition.getOutputName();
}
@Override
public String getJavaMemberName(Definition definition, Mode mode) {
return definition.getOutputName();
}
}

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

Bernd Huber

unread,

Jun 11, 2024, 3:11:05 PM6/11/24

to jOOQ User Group

sorry following correction to the second MatcherStrategy example.
I tried it like this:

.withFields(
new MatchersFieldType()
.withFieldIdentifier(new MatcherRule()
.withTransform(MatcherTransformType.AS_IS))
.withFieldMember(new MatcherRule()
.withTransform(MatcherTransformType.AS_IS))
.withFieldGetter(new MatcherRule()

.withExpression("get$0")
.withTransform(MatcherTransformType.UPPER_FIRST_LETTER))
.withFieldSetter(new MatcherRule()
.withExpression("set$0")
.withTransform(MatcherTransformType.UPPER_FIRST_LETTER))
)

but as i said, this would produce following results,
because the UPPER_FIRST_LETTER applies to the whole expression (which is to be expected)

GetuserId
SetuserId

Bernd Huber

unread,

Jun 12, 2024, 7:56:49 AM6/12/24

to jOOQ User Group

i got it to work now with the Custom GeneratorStrategy! :)

public final class FkGeneratorStrategy extends DefaultGeneratorStrategy {

private static Pattern FK_PATTERN = Pattern.compile("^(.*)_(.*?)(Id)?$");

...

@Override
public String getJavaMethodName(Definition definition, Mode mode) {

if (definition instanceof ForeignKeyDefinition
|| definition instanceof ManyToManyKeyDefinition
|| definition instanceof InverseForeignKeyDefinition) {
Matcher matcher = FK_PATTERN.matcher(definition.getOutputName());
if (matcher.matches()) {
return matcher.group(2);
} else {
return definition.getOutputName();
}
} else {
return definition.getOutputName();
}
}
...

Reply all

Reply to author

Forward