https://github.com/lesfurets
@BeastieFurets
https://github.com/dubreuia
@dubreuia
"Hibernate shares JPA’s vision of ORM. jOOQ shares SQL’s vision of powerful querying, so for the sake of the argument, let’s use Hibernate / JPA / ORM interchangeably, much like jOOQ / JDBC / SQL."
Hibernate have functionality that jOOQ will never have: optimistic locking for complex writes, cache, etc.
public FicheAuto selectRecentFiche(Integer statut) throws Exception {
return DSL.using(connection)
.select(...)
.from(FICHES_AUTO)
.where(statut == null ? DSL.trueCondition()
: FICHES_AUTO.STATUT.gt(statut))
.orderBy(FICHES_AUTO.STATUT.desc())
.limit(1)
.stream()
.map(this::mapFicheAuto)
.findFirst()
.orElseThrow(() -> new Exception("No fiche for " + statut));
}
try {
preparedStatement = connection.prepareStatement("select departement, manager,
netprofit, operatingexpense,year,turnover from result");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
Result result = new Result();
result.setDepartement(resultSet.getString("departement"));
result.setManager(resultSet.getString("manager"));
result.setNetProfit(resultSet.getDouble("netprofit"));
result.setOperatingExpense(resultSet.getDouble("operatingexpense"));
result.setYear(resultSet.getInt("year"));
results.add(result);
}
return results;
} catch (SQLException e) {
...
Let's try to simplify the mapping with Spring JDBC Templates.
@Autowired
public InsuranceParamRepository(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Provider> findProvidersBy(String agencyId) {
return jdbcTemplate.query(FIND_PROVIDER_BY_AGENCY_ID,
new Object[] { agencyId }, (resultSet, i) ->
new Provider(resultSet.getString("CODE"),
resultSet.getString("NAME"),
resultSet.getString("COUNTRY_CODE")));
}
@Entity
@Table(name = "marques")
public class Marque {
@Id
private String code;
private String name;
private String logoUrl;
@Transient
private byte[] logo;
@Transient
private String logoName;
@OneToMany(mappedBy = "marque", fetch = FetchType.EAGER)
@Fetch(FetchMode.SELECT)
private List<Brochure> brochures;
public class MarqueDao {
@PersistenceContext
private EntityManager entityManager;
public Marque findByCode(String code) {
try {
Query query = entityManager
.createQuery("from Marque to where to.code=:code")
.setParameter("code", code);
return (Marque) query.getSingleResult();
} catch (Exception e) {
throw new ResourceNotFoundException(e);
}
}
}
public FicheAuto selectRecentFiche(int statut) throws Exception {
return DSL.using(connection)
.select(...)
.from(FICHES_AUTO)
.where(FICHES_AUTO.STATUT.gt(statut))
.orderBy(FICHES_AUTO.STATUT.desc())
.stream()
.map(record -> new FicheAuto(
FICHES_AUTO.ID.get(record),
FICHES_AUTO.OFFRE_UID.get(record),
FICHES_AUTO.CREATION_DATE.get(record),
FICHES_AUTO.CODE_POSTAL.get(record),
FICHES_AUTO.EMAIL.get(record),
FICHES_AUTO.CODE_SRA.get(record),
FICHES_AUTO.STATUT.get(record)))
.findFirst()
.orElseThrow(() -> new Exception("No fiche for " + statut));
}
public FicheAuto selectRecentFiche(int statut) throws Exception {
return DSL
.using(connection)
.select(...)
.from(FICHES_AUTO)
.where(FICHES_AUTO.STATUT.gt(statut))
.orderBy(FICHES_AUTO.STATUT.desc())
.stream()
.map(this::mapFicheAuto)
.findFirst()
.orElseThrow(() -> new Exception("No fiche for " + statut));
}
private FicheAuto mapFicheAuto(Record7 record) {
return new FicheAuto(
FICHES_AUTO.ID.get(record),
FICHES_AUTO.OFFRE_UID.get(record),
FICHES_AUTO.CREATION_DATE.get(record),
FICHES_AUTO.CODE_POSTAL.get(record),
FICHES_AUTO.EMAIL.get(record),
FICHES_AUTO.CODE_SRA.get(record),
FICHES_AUTO.STATUT.get(record));
}
Date creationDate = resultSet.getDate("creation_date");
LocalDateTime date = Instant.ofEpochMilli(creationDate.getTime())
.atZone(ZoneId.systemDefault())
.toLocalDateTime();
public List<Tuple2<LocalDateTime, String>> findPlate(Connection connection)
throws SQLException {
return DSL
.using(connection)
.fetch(FIND_PLATE_AND_CREATION_DATE)
.stream()
.map(r -> {
String plate = r.getValue(PLATE, String.class);
LocalDateTime date = r.getValue(CREATION_DATE, LocalDateTime.class);
return new Tuple2<>(date, plate);
}).collect(toList());
}
Tuple2 ? Wait ? What ?
public List<Tuple2<LocalDateTime, String>> findPlate(Connection connection)
throws SQLException {
return DSL
.using(connection)
.fetch(FIND_PLATE_AND_CREATION_DATE)
.stream()
.map(r -> {
String plate = r.getValue(PLATE, String.class);
LocalDateTime date = r.getValue(CREATION_DATE, LocalDateTime.class);
return new Tuple2<>(date, plate);
}).collect(toList());
}
Function1 to Function16Tuple1 to Tuple16Seqsupplier and consumerTuple1 to Tuple16
Tuple2<LocalDate, String> tuple = Tuple.tuple(now(), "Yolo");
Seq.of(1, 2, 3).concat(Seq.of(4, 5, 6)); // Seq.of(1, 2, 3, 4, 5, 6);
return abScenarios
.stream()
.map(abScenario -> {
try {
return uuidPairsWithAbTesting(...);
} catch (SQLException e) {
e.printStackTrace();
}
})
.collect(toList());
return abScenarios
.stream()
.map(Unchecked.function(abScenario -> uuidPairsWithAbTesting(...)))
.collect(toList());
Tuple to replace POJOSFunction16
<configuration>
<jdbc>
<driver>${db.driver}</driver>
<url>${db.url}</url>
<user>${db.username}</user>
<password>${db.password}</password>
</jdbc>
<generator>
<database>
<name>org.jooq.util.h2.H2Database</name>
<includes>.*</includes>
...
</database>
<generate>
<instanceFields>true</instanceFields>
<javaTimeTypes>true</javaTimeTypes>
<deprecated>false</deprecated>
<pojos>false</pojos>
<records>false</records>
</generate>
<target>
<packageName>com.lesfurets.db</packageName>
<directory>target/generated-sources</directory>
</target>
</generator>
</configuration>
java.time instead of java.sql for time typesFicheAuto we already haveFicheAutoRecords, we are using Recordn directly
DSL.insert().returning() (equivalent of JDBC Statement.RETURN_GENERATED_KEYS) do not work unless you use code generationDSL.fetchOne returns an empty Record (not null), using DSL.stream().findFirst() is better, since you get back an OptionalSometimes jOOQ does not support something you need (e.g. insert returning for batch inserts), but falling back to JDBC is easy by using the Query object
List<String> queries = leads.stream()
.map(lead -> insertLead(conn, refTimeByHour, insert, lead))
.map(query -> query.getSQL(INLINED))
.collect(toList());
Then use the SQL in a normal JDBC statement
"NoSQL databases will not profit from jOOQ directly any time soon – unless they implement actual SQL"
No unified language between NoSQL databases