How To Configure Multiple DataSource Using SpringBoot And Jooq

[Debapriya Patra]

Hi Lukas,

I have an application running in production and we use postgres DB for storage. In prod we have read replica for the same DB and i wanted to setup multiple datasources using Jooq and SpringBoot So that I can re-direct the GET  APIs to go and read from read replica Datasource and writes will happen to the primary DB.

I ma not sure how Jooq will be configured in this case. Do you have any sample which i can refer to work on this ?

As of now we are using application.properties to configure the datasource.

server.port=8080

management.endpoints.web.exposure.include=info,health,env

# To check why is the healthcheck failing
management.endpoint.health.show-details=always

info.app.name=deck-service
info.app.description=Deck REST services
info.app.version=0.1

spring.mvc.message-codes-resolver.format=PREFIX_ERROR_CODE

spring.datasource.driver-class-name=org.postgresql.Driver
spring.jooq.sql-dialect=Postgres
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.pool-name=DeckServiceDB
spring.datasource.hikari.initial-size=5
spring.datasource.hikari.maximum-pool-size=40
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.idle-timeout=10000
spring.datasource.query-timeout=${TIMEOUT_FROM_AWS}
spring.datasource.url=${DB_URL_FROM_AWS}
spring.datasource.username=${USERNAME_FROM_AWS}
spring.datasource.password=${PASSWORD_FROM_AWS}

#Optional properties
#spring.datasource.hikari.maxLifetime=2000000
#spring.datasource.hikari.leak-detection-threshold=60000

# ref - http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf
# ref - http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-useconfigs.html
spring.datasource.prepStmtCacheSize=250
spring.datasource.prepStmtCacheSqlLimit=2048

spring.output.ansi.enabled=ALWAYS

# ref - https://docs.spring.io/spring-boot/docs/current/reference/html/production-ready-jmx.html
management.endpoints.jmx.exposure.exclude=*
management.endpoint.jolokia.enabled=false

#Writes LocalDateTime in ISO8601DateFormat if set to false. Default value set by Spring-boot is true
spring.jackson.serialization.WRITE_DATES_AS_TIMESTAMPS=true

#tracing related properties
method.tracing.enabled=false

Our Repository is like this:

package com.chegg.deck.service.readds.dao;

import com.chegg.deck.service.PostgresJSONBBinding;
import com.chegg.deck.service.dao.BaseRepository;
import com.chegg.deck.service.model.Card;
import com.chegg.deck.service.service.util.JooqUtil;
import com.chegg.deck_service.db.tables.records.CardRecord;
import com.fasterxml.jackson.databind.JsonNode;
import graphql.GraphQLException;
import lombok.extern.slf4j.Slf4j;
import org.jooq.*;
import org.simpleflatmapper.converter.ContextualConverter;
import org.simpleflatmapper.jdbc.JdbcMapper;
import org.simpleflatmapper.jdbc.JdbcMapperFactory;
import org.simpleflatmapper.map.property.ConverterProperty;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;

import static com.chegg.deck_service.db.Tables.*;

/**
 * CardRepository
 * <p>
 * Created User : dpatra
 * Created On : 2019-01-23
 * Updated Users : dpatra
 * Updated On : 2019-01-23
 */

@Slf4j
@Repository
@Transactional
public class CardRepository{

    static final Field[] CARD_DECK_FIELDS = new Field[] {CARD_DECK.ID, CARD.FOREIGN_ID, CARD_DECK.CARD_ID, CARD_DECK.DECK_ID, CARD.CARD_CONTENT, CARD.CREATED, CARD.UPDATED, CARD.ORIGINAL_CREATED, CARD.ORIGINAL_UPDATED,  CARD_DECK.POSITION};
    static final Field[] CARD_FIELDS = new Field[] {CARD.ID,CARD.FOREIGN_ID, CARD.CARD_CONTENT, CARD.CREATED, CARD.UPDATED, CARD.ORIGINAL_CREATED, CARD.ORIGINAL_UPDATED};

    public static final ContextualConverter<Object, JsonNode> toJsonNode = (in, context) -> PostgresJSONBBinding.parse("" + in);
    private static final JdbcMapper<Card> cardMapper = JdbcMapperFactory
            .newInstance()
            .addColumnProperty(CARD.CARD_CONTENT.getName(), ConverterProperty.of(toJsonNode))
            .newMapper(Card.class);

   @Autowired
   protected DSLContext dsl;

   @PostConstruct
   public void init(){
      dsl.configuration().settings().setQueryTimeout(querytimeout);
   }

   public DSLContext getDsl() {
       return this.dsl;
   }

   public void setDsl(DSLContext dsl) {
       this.dsl = dsl;
   }
    @Autowired
    private CardDeckRepository cardDeckRepository;


    /**
     * Get cards for a given deck by the given deckId.
     *
     * @param deckId
     * @param limit
     *         number of records to be fetched.
     * @param offset
     *         after which record data will be fetched.
     * @return cards list.
     */
    public List<Card> getCardsByDeckId(UUID deckId, int limit, Float offset) {
        SelectConditionStep<Record> conditionStep = formSelectCondition(deckId);

        //Add offset value if its present.
        if (null != offset && offset.floatValue() != 0) {
            conditionStep.and(CARD_DECK.POSITION.greaterThan(offset.floatValue()));
        }
        ResultSet rs = conditionStep.orderBy(CARD_DECK.DECK_ID, CARD_DECK.POSITION).limit(limit).fetchResultSet();
        return transformQueryResultIntoListOfCards(rs);
    }

    /**
     * Pull the cards for the given deckId & set of card_deck ids.
     *
     * @param deckId
     * @param ids
     * @return List<Card>
     */
    public List<Card> getCardsByDeckIdAndCardDeckIds(UUID deckId, List<UUID> ids) {
        ResultSet rs = dsl.select(JooqUtil.fieldsWithAliases(CARD_DECK_FIELDS, Card.getAliases()))
                .from(CARD_DECK)
                .join(CARD).on(CARD.ID.eq(CARD_DECK.CARD_ID))
                .join(DECK).on(DECK.ID.eq(CARD_DECK.DECK_ID))
                .where(CARD_DECK.ID.in(ids).and(CARD_DECK.DECK_ID.eq(deckId)))
                .orderBy(CARD_DECK.DECK_ID, CARD_DECK.POSITION)
                .fetchResultSet();
        return transformQueryResultIntoListOfCards(rs);
    }

    private Card transformQueryResultIntoCard(ResultSet rs){
        try {
            Iterator<Card> cards = cardMapper.iterator(rs);
            if (!cards.hasNext()) {
                return null;
            }

            Card found = cards.next();
            if (cards.hasNext()) {
                throw new GraphQLException("Multiple cards were found");
            }
            return found;
        }
        catch (Exception ex) {
            throw new GraphQLException(ex.getMessage());
        }
    }

    private List<Card> transformQueryResultIntoListOfCards(ResultSet rs){
        try{
            return cardMapper.stream(rs).collect(Collectors.toList());
        } catch(Exception ex){
            throw new GraphQLException(ex.getMessage());
        }
    }


}

Please let me know if I will need to read data from one datasource and write into another datasource, then how will I change my code and how to setup in SpringBoot application with Jooq.

Thanks,

Deba

[Lukas Eder]

Hi Deba,

Thanks for your message. There's nothing special about using jOOQ with Spring Boot, with respect to multiple data sources. You do the same thing as if you had used Spring Boot with JDBC/Hibernate, etc. I.e. you configure two data sources and inject the right one into each DSLContext. You'll obviously also inject two DSLContexts.

If you have a question about your specific setup, I would try asking your question on https://stackoverflow.com. There are people more involved with Spring Boot on there, who can probably spot any issue that you might have much quicker than I could.

Thanks,

Lukas

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1255e3b5-d41d-46b6-b919-6e3638032d8b%40googlegroups.com.