tdc2016sp - jooq: sql orientado a objetos
TRANSCRIPT
jOOQSQL orientado a objetos
The Developers ConferenceSão Paulo - 2016
Gabriel Saraiva Desenvolvedor na
SQL ❤
String sql = "SELECT * FROM users WHERE email = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,"[email protected]");
SQL ❤ ?
String sql = "UPDATE products" +
"SET name = ?, ..., price = ?" +
"WHERE id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, "Café");
// 98 campos depois...
stmt.setLong(99, 123L);
(╯°□°)╯︵ ┻━┻
String sql = "SELECT avg(age), cityId " +
"FROM users " +
(filter ? "WHERE cityId IN (...) ":"") +
"GROUP BY cityId " +
(order ? "ORDER BY name DESC":"") +
...
;
SQL !!!
SQL:
“Stringish Query Langage” ?
SQL = Structured Query Language
O problema não é o SQL.
É como escrevemos SQL.
SQL ❤ !!!
String sql = select()
.from(USERS)
.where(
USERS.ID.in(ids)
)
.toString();
“The easiest way to write SQL in Java”
or in
Scala
jOOQ:
Uma DSL para escrever SQL
Setup: 1 minuto.
Setup: 1 minuto.
<dependency> <groupId>org.jooq</groupId> <artifactId>jooq</artifactId> <version>3.8.2</version></dependency><dependency> <groupId>org.jooq</groupId> <artifactId>jooq-meta</artifactId> <version>3.8.2</version></dependency><dependency> <groupId>org.jooq</groupId> <artifactId>jooq-codegen</artifactId> <version>3.8.2</version></dependency>
Setup: 1 minutoou mais!
Type Safe
select()
.from(USERS)
.where(
USERS.EMAIL.equal(1234)
)
SintaxeFluente
select()
.from(USERS)
.where(
USERS.ID.equal(1234),
USERS.AGE.equal(26)
)
SQL InjectionSafe
String name =
"Bob\"); DROP TABLE users; --";
select()
.from(USERS)
.where(
USERS.NAME.eq(name)
)
Free:p/ bancosOpen Source
$$$:p/ bancosEnterprise
mais informações disponíveis em: http://www.jooq.org/legal/licensing#databases
Modelodo Banco:(opcional)Automático!
<configuration> <jdbc> <driver>com.mysql.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306</url> <user>root</user> <password>password</password> </jdbc>
<generator> <database> <name>org.jooq.util.mysql.MySQLDatabase</name> <includes>.*</includes> <excludes/> <inputSchema>example_database</inputSchema> <unsignedTypes>false</unsignedTypes> </database> <target> <packageName>com.example.database</packageName> <directory>src/main/java</directory> </target> </generator></configuration>
Usando o jOOQ para escrever SQL
1 minuto de setup: Sem modelo do db.
String sql = DSL.using(SQLDialect.MYSQL)
.select(field("name"), field("phone"))
.from("users")
.where(
field("id").equal(1234)
)
.toString();
2 minutos de setup: Modelo db automático
String sql = select(USERS.NAME, USERS.PHONE)
.from(USERS)
.where(
USERS.ID.eq(1234L)
)
.toString();
Inserts sem ?,?,?,?,?,?,,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? será que faltou um ?
String sql = insertInto(USERS)
.set(USERS.NAME,"Gabriel Saraiva")
.set(USERS.PHONE,"16999666333")
.set(USERS.EMAIL,"[email protected]")
.toString();
Filtros
String sql = select()
.from(PRODUCTS)
.where(PRODUCTS.CATEGORY.eq("drinks")
.or(PRODUCTS.CATEGORY.eq("beers"))
.and(PRODUCTS.NAME.contains("coffee"))
)
.toString();
Lista de Filtros
List<Condition> filters = Arrays.asList(
PRODUCTS.CATEGORY.equal("drinks"),
PRODUCTS.PRICE.greaterThan(new BigDecimal(25))
);
String sql = select(PRODUCTS.NAME, PRODUCTS.PRICE)
.from(PRODUCTS)
.where(filters)
.orderBy(PRODUCTS.PRICE.desc())
.toString();
Joins!
Users u = USERS;
Orders o = ORDERS;
OrdersProducts op = ORDERS_PRODUCTS;
Products p = PRODUCTS;
String sql = select()
.from(u
.innerJoin(o).on(u.ID.eq(o.USER_ID))
.innerJoin(op).on(o.ID.eq(op.ORDER_ID))
.innerJoin(p).on(p.ID.eq(op.ORDER_ID))
)
.toString();
Group By!
String sql = DSL.
select(
u.NAME,
o.ID,
sum(op.PRODUCT_SOLD_PRICE)
)
.from(join)
.where(filters)
.groupBy(o.ID)
.toString();
Lendo dados do ResultSet
ResultSet rs = stmt.executeQuery();
rs.next();
return new Product(
rs.getLong(PRODUCTS.ID.getName()),
rs.getString(PRODUCTS.NAME.getName()),
rs.getBigDecimal(PRODUCTS.PRICE.getName()),
rs.getString(PRODUCTS.CATEGORY.getName())
);
Escrever SQL
pode ser
divertido!
Obrigado
Gabriel [email protected]
github.com/gabrsartwitter/gabrielSaraivaAgradecimentos ❤
Igor EscobarGiuliano CaliariDiego Leme