JOOQ - Update newly added columns-CodePudding

I am using JOOQ and a Postgresql Database and I'm trying to add new columns to a table and then update those columns with values. Those values correspond to sentiment of a tweet.

My attempt so far (the one without compilation errors :) is:

final String SENTIMENT_NEGATIVE = "sentiment_negative";
final String SENTIMENT_NEUTRAL  = "sentiment_neutral";
final String SENTIMENT_POSITIVE = "sentiment_positive";
final String SENTIMENT_COMPOUND = "sentiment_compound";

context.alterTable(Tweets.TWEETS).addColumnIfNotExists(SENTIMENT_NEGATIVE, DOUBLE).execute();
context.alterTable(Tweets.TWEETS).addColumnIfNotExists(SENTIMENT_NEUTRAL , DOUBLE).execute();
context.alterTable(Tweets.TWEETS).addColumnIfNotExists(SENTIMENT_POSITIVE, DOUBLE).execute();
context.alterTable(Tweets.TWEETS).addColumnIfNotExists(SENTIMENT_COMPOUND, DOUBLE).execute();

// example usage
for (Record record: tweetsResult) {
    String tweet = record.get(Tweets.TWEETS.CONTENT);
    SentimentAnalyzer sentimentAnalyzer = new SentimentAnalyzer(tweet);
    sentimentAnalyzer.analyze();
    log.debug(sentimentAnalyzer.getPolarity()   " | "   tweet);

    context.update(Tweets.TWEETS)
        .set(
            row(SENTIMENT_NEGATIVE,
               SENTIMENT_NEUTRAL,
               SENTIMENT_POSITIVE,
               SENTIMENT_COMPOUND),
            row(getPolarity(sentimentAnalyzer, SENTIMENT_NEGATIVE),
                getPolarity(sentimentAnalyzer, SENTIMENT_NEUTRAL),
                getPolarity(sentimentAnalyzer, SENTIMENT_POSITIVE),
                getPolarity(sentimentAnalyzer, SENTIMENT_COMPOUND))
            )
        .where(Tweets.TWEETS.ID.eq(record.get(Tweets.TWEETS.ID)))
        .execute();
}

but I get an error:

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [update "public"."tweets" set (?, ?, ?, ?) = row (?, ?, ?, ?) where "public"."tweets"."id" = ?]; ERROR: syntax error at or near "$1"

I know that I can regenerate my source code according to the changes in db schema, but I wonder if it is possible to do it using my approach.

CodePudding user response：

Ideally, you'll implement a cleaner separation of your querying logic and your migration logic. Database change management tools like Flyway or Liquibase will help you maintain your schema, and apply increments whenever your application starts.

Once you use database change management software, you can use it as well during your development, e.g. by applying your increments all the time to your development database within testcontainers, from which you then re-generate your code, see

That way, your generated code is always up to date, and you don't have to resort to using temporary type-unsafe String representations of your latest columns.