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