Best Way to Write SQL in Java

Copyright (c) 2009-2015 by Data Geekery GmbH. Slides licensed under CC BY SA 3.0
Get Back in Control of your SQL
SQL and Java could work
together so much better if
we only let them.

Intro SQL and Java jOOQ Examples
Me – @lukaseder
SQL is a device whose mystery
is only exceeded by its power!
- Founder and CEO at Data Geekery
- Oracle Java Champion
- JUG.ch Board Member

Let’s talk about
SQL

SQL is Powerful!
My Reaction when I
forget the WHERE
clause on my DELETE
statement…
With autocommit
active

SQL and Java – in theory
Java SQL
In this metaphor, electricity is the data (SQL) that
flows into your appliance / application (Java)
one jack one plug

SQL and Java – in practice
Java SQL
Images from: http://en.wikipedia.org/wiki/AC_power_plugs_and_sockets. License: public domain
one jack lots of plugs

JDBC
PreparedStatement stmt = connection.prepareStatement(
"SELECT text FROM products WHERE cust_id = ? AND value < ?");
stmt.setInt(1, custID);
stmt.setBigDecimal(2, BigDecimal.ZERO);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("TEXT"));
}

JDBC – the naked truth
01: PreparedStatement stmt = connection.prepareStatement(
02: "SELECT p.text txt" +
03: (isAccount ? ", NVL(a.type, ?) " : "") +
04: "FROM products p " +
05: (isAccount ? " INNER JOIN accounts a USING (prod_id) " : "") +
06: " WHERE p.cust_id = ? AND p.value < ?" +
07: (isAccount ? " AND a.type LIKE '%" + type + "%'" : ""));
08: stmt.setInt(1, defaultType);
09: stmt.setInt(2, custID);
10: stmt.setBigDecimal(3, BigDecimal.ZERO);
11: ResultSet rs = stmt.executeQuery();
12:
13: while (rs.next()) {
14: Clob clob = rs.getClob("TEXT");
15: System.out.println(clob.getSubString(1, (int) clob.length()));
16: }
17:
18: rs.close();
19: stmt.close();

JDBC – the naked truth
01: PreparedStatement stmt = connection.prepareStatement( //
02: "SELECT p.text txt" + //
03: (isAccount ? ", NVL(a.type, ?) " : "") + //
04: "FROM products p " + // Syntax error when isAccount == false
05: (isAccount ? " INNER JOIN accounts a USING (prod_id) " : "") + //
06: " WHERE p.cust_id = ? AND p.value < ?" + //
07: (isAccount ? " AND a.type LIKE '%" + type + "%'" : "")); // Syntax error and SQL injection possible
08: stmt.setInt(1, defaultType); // Wrong bind index
09: stmt.setInt(2, custID); //
10: stmt.setBigDecimal(3, BigDecimal.ZERO); //
11: ResultSet rs = stmt.executeQuery(); //
12:
13: while (rs.next()) { //
14: Clob clob = rs.getClob("TEXT"); // Wrong column name
15: System.out.println(clob.getSubString(1, (int) clob.length())); // ojdbc6: clob.free() should be called
16: } //
17:
18: rs.close(); // close() not really in finally block
19: stmt.close(); //

What JDBC means for developers
Images from Flickr. To the left by: Matthew Straubmuller, Greg Grossmeier. License: CC BY SA 2.0. Electric Engineers to the right copyright by Marco Sarli, all rights reserved.
With JDBC, your developers have to do a lot of
manual, error-prone (dangerous) and inefficient work

EJB 2.0 EntityBeans
public interface CustomerRequest extends EJBObject {
BigInteger getId();
String getText();
void setText(String text);
@Override
void remove();
}
public interface CustomerRequestHome extends EJBHome {
CustomerRequest create(BigInteger id);
CustomerRequest find(BigInteger id);
}

EJB 2.0 – the naked truth
<weblogic-enterprise-bean>
<ejb-name>com.example.CustomerRequestHome</ejb-name>
<entity-descriptor>
<pool>
<max-beans-in-free-pool>100</max-beans-in-free-pool>
</pool>
<entity-cache>
<max-beans-in-cache>500</max-beans-in-cache>
<idle-timeout-seconds>10</idle-timeout-seconds>
<concurrency-strategy>Database</concurrency-strategy>
</entity-cache>
<persistence>
<delay-updates-until-end-of-tx>True</delay-updates-until-end-of-tx>
</persistence>
<entity-clustering>
<home-is-clusterable>False</home-is-clusterable>
<home-load-algorithm>round-robin</home-load-algorithm>
</entity-clustering>
</entity-descriptor>
<transaction-descriptor/>
<enable-call-by-reference>True</enable-call-by-reference>
<jndi-name>com.example.CustomerRequestHome</jndi-name>
</weblogic-enterprise-bean>

<pool>
<max-beans-in-free-pool>100</max-beans-in-free-pool>
</pool>
<entity-cache>
<max-beans-in-cache>500</max-beans-in-cache>
<idle-timeout-seconds>10</idle-timeout-seconds>
<concurrency-strategy>Database</concurrency-strategy>
</entity-cache>
<persistence>
<delay-updates-until-end-of-tx>True</delay-updates-…>
</persistence>
o_O

EJB 2.0

JPA and EJB 3.0
EntityManager em = factory.createEntityManager();
em.getTransaction().begin();
em.persist(new Event("Conference", new Date());
em.persist(new Event("After Party", new Date());
List result = em.createQuery("from Event").getResultList();
for (Event event : (List<Event>) result) {
System.out.println("Event : " + event.getTitle());
}
em.getTransaction().commit();
em.close();

@Entity @Table(name = "EVENTS")
public class Event {
private Long id;
private String title;
private Date date;
@Id @GeneratedValue(generator = "increment")
@GenericGenerator(name = "increment", strategy = "increment")
public Long getId() { /* … */ }
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "EVENT_DATE")
public Date getDate() { /* … */ }

EJB 3.0 – Yep, annotations!
@OneToMany(mappedBy = "destCustomerId")
@ManyToMany
@Fetch(FetchMode.SUBSELECT)
@JoinTable(
name = "customer_dealer_map",
joinColumns = {
@JoinColumn(name = "customer_id", referencedColumnName = "id")
},
inverseJoinColumns = {
@JoinColumn(name = "dealer_id", referencedColumnName = "id")
}
)
private Collection dealers;
Found at http://stackoverflow.com/q/17491912/521799

JPA 3.0 Preview – Annotatiomania™
@SeveralAndThenNothing @MaybeThisDoesSomething
@TweakThisWithThat(
tweak = {
@TweakID(name = "id", preferredValue = 1839),
@TweakID(name = "test", preferredValue = 839),
@TweakID(name = "test.old", preferredValue = 34),
},
inCaseOf = {
@ConditionalXMLFiltering(run = 5),
}
)
@OneToMany @OneToManyMore @AnyOne @AnyBody @DoesThisEvenMeanAnything @DoesAnyoneEvenReadThis
@ManyToMany @Many @AnnotationsTotallyRock @DeclarativeProgrammingRules @NoMoreExplicitAlgorithms
@Fetch @FetchMany @FetchWithDiscriminator(name = "no_name")
@JoinTable(joinColumns = {
@JoinColumn(name = "customer_id", referencedColumnName = "id")
})
@PrefetchJoinWithDiscriminator @JustTrollingYouKnow @LOL
@IfJoiningAvoidHashJoins @ButUseHashJoinsWhenMoreThan(records = 1000)
@XmlDataTransformable @SpringPrefechAdapter
private Collection employees;
Might not be true

What’s next?

Ask the industry experts
www.annotatiomania.com

Albert Einstein
I don’t know with
what annotations
JPA III will ship,
but JPA IV will
ship with sticks
and stones

JPA 4.0 Preview
var employees;

What JPA means for developers…
Images from Wikimedia. License: public domain. High voltage power lines by Simon Koopmann. License: CC-BY SA 3.0
With JPA, your developers use a huge framework with
lots of complexity that can get hard to manage

… when developers actually wanted this
Java SQL
one jack one plug

Note, we’re talking about SQL. Not Persistence…

Note, we’re talking about SQL. Not Persistence…
FYI: Gavin King: Creator of Hibernate!

NoSQL?
…
… so, should we maybe abandon SQL?

Big Data? What is it?

Source: www.itproportal.com

Seriously, MongoDB?

NoSQL? Who said it?
Our service ran at 99.99 percent
uptime in the first quarter of 2009,
runs more than 200 million
transactions a day, and has subsecond
response time; and we are constantly
making advances to deliver it even
faster.

NoSQL? Marc Benioff – salesforce.com
Our service ran at 99.99 percent
uptime in the first quarter of 2009,
runs more than 200 million
transactions a day, and has subsecond
response time; and we are constantly
making advances to deliver it even
faster.

NoSQL? Marc Benioff – salesforce.com
He’s talking about
salesforce.com’s
Oracle database.
He “invented” the
cloud

NoSQL? Who said it?
• 300 TB of data files for production
DBs in total
• LHC logging database ~140TB,
expected growth up to ~70 TB /
year
• 13 Production experiments'
database ~120 TB in total

NoSQL? Collecting LHC data with Oracle Exadata
• 300 TB of data files for production
DBs in total
• LHC logging database ~140TB,
expected growth up to ~70 TB /
year
• 13 Production experiments'
database ~120 TB in total

NoSQL? Stack Overflow on 1 SQL Server Instance
http://stackexchange.com/performance

NoSQL for Big Data?
- You’re giving up on ACID
- You’re giving up on type safety
- You’re giving up on standards
- You’re giving up on tooling
- You’re giving up on relational algebra
- You haven’t asked operations
- You don’t actually have «Big Data»

Seen at the O’Reilly Strata Conf:
History of NoSQL by Mark Madsen. Picture published by Edd Wilder-James
NoSQL? No, SQL!

So, let’s talk about
SQL

NoSQL? No, SQL!
So, let’s talk about
SQL

Let’s calculate a running total
| ID | VALUE_DATE | AMOUNT |
|------|------------|--------|
| 9997 | 2014-03-18 | 99.17 |
| 9981 | 2014-03-16 | 71.44 |
| 9979 | 2014-03-16 | -94.60 |
| 9977 | 2014-03-16 | -6.96 |
| 9971 | 2014-03-15 | -65.95 |

| ID | VALUE_DATE | AMOUNT | BALANCE |
|------|------------|--------|------------|
| 9997 | 2014-03-18 | 99.17 | 19985.81 |
| 9981 | 2014-03-16 | 71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 | -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

|------|------------|--------|------------|
| 9997 | 2014-03-18 | +99.17 =19985.81 |
| 9981 | 2014-03-16 | 71.44 | +19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 | -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

|------|------------|--------|------------|
| 9997 | 2014-03-18 | 99.17 | 19985.81 |
| 9981 | 2014-03-16 | +71.44 =19886.64 |
| 9979 | 2014-03-16 | -94.60 | +19815.20 |
| 9977 | 2014-03-16 | -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

|------|------------|--------|------------|
| 9997 | 2014-03-18 | 99.17 | 19985.81 |
| 9981 | 2014-03-16 | +71.44 =19886.64 | n
| 9979 | 2014-03-16 | -94.60 | +19815.20 | n+1
| 9977 | 2014-03-16 | -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |BALANCE(ROWn) = BALANCE(ROWn+1) + AMOUNT(ROWn)
BALANCE(ROWn+1) = BALANCE(ROWn) – AMOUNT(ROWn)

SELECT
t.*,
t.current_balance - NVL(
SUM(t.amount) OVER (
PARTITION BY t.account_id
ORDER BY t.value_date DESC,
t.id DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
),
0) AS balance
FROM v_transactions t
WHERE t.account_id = 1
t.id DESC

SUM(t.amount) OVER (
PARTITION BY t.account_id
t.id DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
)

|------|------------|--------|------------|
| 9997 | 2014-03-18 -(99.17)| +19985.81 |
| 9981 | 2014-03-16 -(71.44)| 19886.64 |
| 9979 | 2014-03-16 -(-94.60)| 19815.20 |
| 9977 | 2014-03-16 | -6.96 | =19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

We can help
www.jooq.org/training
SQL Wizardry?

More SQL Calculations
| TEXT | VOTES | RANK | PERCENT |
|-------------|-------|------------|---------|
| jOOQ | 1383 | 1 | 32 % |
| Hibernate | 1029 | 2 | 23 % |
| EclipseLink | 881 | 3 | 20 % |
| JDBC | 533 | 4 | 12 % |
| Spring JDBC | 451 | 5 | 10 % |
Data may not be accurate…

More SQL Calculations
SELECT p.text,
p.votes,
DENSE_RANK() OVER (ORDER BY p.votes DESC) AS "rank",
LPAD(
(p.votes * 100 / SUM(p.votes) OVER ()) || ' %',
4, ' '
) AS "percent"
FROM poll_options p
WHERE p.poll_id = 12
ORDER BY p.votes DESC

The same with jOOQ
select (p.TEXT,
p.VOTES,
denseRank().over().orderBy(p.VOTES.desc()).as("rank"),
lpad(
p.VOTES.mul(100).div(sum(p.VOTES).over()).concat(" %"),
4, " "
).as("percent"))
.from (POLL_OPTIONS.as("p"))
.where (p.POLL_ID.eq(12))
.orderBy(p.VOTES.desc());

The same with jOOQ in Scala (!)
select (p.TEXT,
p.VOTES,
denseRank() over() orderBy(p.VOTES desc) as "rank",
lpad(
(p.VOTES * 100) / (sum(p.VOTES) over()) || " %",
4, " "
) as "percent")
from (POLL_OPTIONS as "p")
where (p.POLL_ID === 12)
orderBy (p.VOTES desc)

What jOOQ means for developers
Java SQL
one jack all plugs
jOOQ
one adaptor
With jOOQ, Java plugs into SQL intuitively, letting
your developers focus on business-logic again.
Images from Wikimedia. License: public domain. Travel converter by Cephira. License: CC-BY SA 3.0

What jOOQ means for developers
Java SQL
one jack all plugs
jOOQ
one adaptor
Images from Wikimedia. License: public domain. Travel converter by Cephira. License: CC-BY SA 3.0

Examples

Examples – Code generation

Examples – Use the generated code

Examples – Auto completion on columns

Examples – Type checking

Examples – Type checking with Java 8

Examples – Type checking – Opt out if you want

Examples – Standardisation – Oracle

Examples – Standardisation – SQL Server

Examples – Standardisation – SQLite

Examples – Standardisation

Examples – Standardisation – PostgreSQL

Examples – Standardisation – Oracle 11g

Examples – Standardisation – DB2

Examples – Syntax checking

Examples – Active Records

Examples – But of course, DML too

Examples – Stored Procedures

Marc Benioff from salesforce
All companies
benefit when they
can afford to focus
on innovation
rather than
infrastructure

A vision of a better SQL application
- Database first
- Performance
- Type safe JDBC
- Code Generation
- Active Records
- Stored Procedures
- SQL Transformation
- SQL Standardisation

«jOOQ» 10% discount code
A book recommendation
Markus Winand from
Use-The-Index-Luke.com
ROI north of 83’174%
Achieve proper indexing and
performance in popular RDBMS

«jOOQ» 20% discount code
A book recommendation (in the making)
Vlad Mihalcea from
vladmihalcea.com
JDBC, JPA, jOOQ
Performance, Scaling
https://leanpub.com/high-performance-java-persistence/c/jOOQ

And a shameless tool recommendation
Open source databases:
- Free / Apache license
Commercial databases:
- Commercial license

1. Everything is a table – Compare it to
Java 8
TABLE : Stream<Tuple<..>>
SELECT : map()
DISTINCT : distinct()
JOIN : flatMap()
WHERE / HAVING : filter()
GROUP BY : collect()
ORDER BY : sorted()
UNION ALL : concat()
See:
http://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/

1. Everything is a table – Compare it to
Java 8
Better Streams:
https://github.com/jOOQ/jOOL

Seq.seq(persons)
.collect(
count(),
max(Person::getAge),
min(Person::getHeight),
avg(Person::getWeight)
);
// (3, Optional[35],
// Optional[1.69], Optional[70.0])

That’s it folks
More free Java / SQL knowledge on:
• Blog: http://blog.jooq.org
• Twitter: @JavaOOQ / @lukaseder
• Newsletter: http://www.jooq.org

Best Way to Write SQL in Java

More Related Content

What's hot

Viewers also liked

Similar to Best Way to Write SQL in Java

More from Gerger

Recently uploaded

Best Way to Write SQL in Java