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 On Fri, Apr 24, 2020 at 4:59 PM Debapriya Patra <debapriya.pa...@gmail.com> wrote: > 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 > > > -- > 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+unsubscr...@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 > <https://groups.google.com/d/msgid/jooq-user/1255e3b5-d41d-46b6-b919-6e3638032d8b%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO66Xg_tj%3DOxQu_dY_rqg91JhdNzd0M3GS3PPWmjXym3aw%40mail.gmail.com.
