Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get some hands-on insight on what's behind developing jOOQ.
A very nice little rant on Hibernate:
http://jeffkemponoracle.com/2011/11/25/3-reasons-to-hate-hibernate
While I don’t agree 100% (e.g. CRUD/OLTP really is different from OLAP, and Hibernate is a strong CRUD tool), I certainly share most of his feelings.
CREATE TABLE documents (
id NUMBER(7) NOT NULL,
CONSTRAINT docu_id PRIMARY KEY (id)
);
CREATE TABLE persons (
id NUMBER(7) NOT NULL,
CONSTRAINT pers_id PRIMARY KEY (id)
);
CREATE TABLE document_person (
docu_id NUMBER(7) NOT NULL,
pers_id NUMBER(7) NOT NULL,
flag NUMBER(1) NULL,
CONSTRAINT docu_pers_pk PRIMARY KEY (docu_id, pers_id),
CONSTRAINT docu_pers_fk_docu
FOREIGN KEY (docu_id) REFERENCES documents(id),
CONSTRAINT docu_pers_fk_pers
FOREIGN KEY (pers_id) REFERENCES persons(id)
);
-- The target table
MERGE INTO document_person dst
-- The data source. In this case, just a dummy record
USING (
SELECT :docu_id as docu_id,
:pers_id as pers_id,
:flag as flag
FROM DUAL
) src
-- The merge condition (if true, then update, else insert)
ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)
-- The update action
WHEN MATCHED THEN UPDATE SET
dst.flag = src.flag
-- The insert action
WHEN NOT MATCHED THEN INSERT (
dst.docu_id,
dst.pers_id,
dst.flag
)
VALUES (
src.docu_id,
src.pers_id,
src.flag
)
MERGE INTO document_person dst
-- The data source is now all "documents" (or just :docu_id) left outer
-- joined with the "document_person" mapping
USING (
SELECT d.id as docu_id,
:pers_id as pers_id,
:flag as flag
FROM documents d
LEFT OUTER JOIN document_person d_p
ON d.id = d_p.docu_id AND d_p.pers_id = :pers_id
-- If :docu_id is set, select only that document
WHERE (:docu_id IS NOT NULL AND d.id = :docu_id)
-- Otherwise, select all documents
OR (:docu_id IS NULL)
) src
-- If the mapping already exists, update. Else, insert
ON (dst.docu_id = src.docu_id AND dst.pers_id = src.pers_id)
-- The rest stays the same
WHEN MATCHED THEN UPDATE SET
dst.flag = src.flag
WHEN NOT MATCHED THEN INSERT (
dst.docu_id,
dst.pers_id,
dst.flag
)
VALUES (
src.docu_id,
src.pers_id,
src.flag
)
select 'Lukas' as employee,
'SoftSkills' as company,
80000 as salary,
2007 as year
from dual
union all select 'Lukas', 'SoftSkills', 80000, 2008 from dual
union all select 'Lukas', 'SmartSoft', 90000, 2009 from dual
union all select 'Lukas', 'SmartSoft', 95000, 2010 from dual
union all select 'Lukas', 'jOOQ', 200000, 2011 from dual
union all select 'Lukas', 'jOOQ', 250000, 2012 from dual
union all select 'Tom', 'SoftSkills', 89000, 2007 from dual
union all select 'Tom', 'SoftSkills', 90000, 2008 from dual
union all select 'Tom', 'SoftSkills', 91000, 2009 from dual
union all select 'Tom', 'SmartSoft', 92000, 2010 from dual
union all select 'Tom', 'SmartSoft', 93000, 2011 from dual
union all select 'Tom', 'SmartSoft', 94000, 2012 from dual
with data as ([above select])
select employee, avg(salary)
from data
group by employee
+--------+-----------+ |EMPLOYEE|AVG(SALARY)| +--------+-----------+ |Lukas | 132500| |Tom | 91500| +--------+-----------+So it’s probably interesting to find out what they have earned on average in which company:
with data as (...)
select company, employee, avg(salary)
from data
group by company, employee
order by company, employee
+----------+--------+-----------+ |COMPANY |EMPLOYEE|AVG(SALARY)| +----------+--------+-----------+ |jOOQ |Lukas | 225000| |SmartSoft |Lukas | 92500| |SmartSoft |Tom | 93000| |SoftSkills|Lukas | 80000| |SoftSkills|Tom | 90000| +----------+--------+-----------+
with data as (...)
select company, employee, avg(salary)
from data
group by rollup(company), employee
+----------+--------+-----------+
|COMPANY |EMPLOYEE|AVG(SALARY)|
+----------+--------+-----------+
|SmartSoft |Tom | 93000|
|SoftSkills|Tom | 90000|
|{null} |Tom | 91500|
|jOOQ |Lukas | 225000|
|SmartSoft |Lukas | 92500|
|SoftSkills|Lukas | 80000|
|{null} |Lukas | 132500|
+----------+--------+-----------+
Note how these rows hold the same information as the ones from the first query, where we were only grouping by employee… This becomes even more interesting, when we put more grouping fields into the rollup function:
with data as (...)
select company, employee, avg(salary)
from data
group by rollup(employee, company)
+----------+--------+-----------+
|COMPANY |EMPLOYEE|AVG(SALARY)|
+----------+--------+-----------+
|SmartSoft |Tom | 93000|
|SoftSkills|Tom | 90000|
|{null} |Tom | 91500|
|jOOQ |Lukas | 225000|
|SmartSoft |Lukas | 92500|
|SoftSkills|Lukas | 80000|
|{null} |Lukas | 132500|
|{null} |{null} | 112000|
+----------+--------+-----------+
In order to identify the totals rows for reporting, you can use the GROUPING() function in DB2, Oracle, SQL Server and Sybase SQL Anywhere. In Oracle and SQL Server, there’s the even more useful GROUPING_ID() function:
with data as (...)
select grouping_id(employee, company) id, company, employee, avg(salary)
from data
group by rollup(employee, company)
+----+----------+--------+-----------+
| ID|COMPANY |EMPLOYEE|AVG(SALARY)|
+----+----------+--------+-----------+
| 0|SmartSoft |Tom | 93000|
| 0|SoftSkills|Tom | 90000|
| 1|{null} |Tom | 91500|
| 0|jOOQ |Lukas | 225000|
| 0|SmartSoft |Lukas | 92500|
| 0|SoftSkills|Lukas | 80000|
| 1|{null} |Lukas | 132500|
| 3|{null} |{null} | 112000|
+----+----------+--------+-----------+
with data as (...)
select grouping_id(employee, company) id, company, employee, avg(salary)
from data
group by cube(employee, company)
+----+----------+--------+-----------+
| ID|COMPANY |EMPLOYEE|AVG(SALARY)|
+----+----------+--------+-----------+
| 3|{null} |{null} | 112000|
| 2|jOOQ |{null} | 225000|
| 2|SmartSoft |{null} | 92800|
| 2|SoftSkills|{null} | 86000|
| 1|{null} |Tom | 91500|
| 0|SmartSoft |Tom | 93000|
| 0|SoftSkills|Tom | 90000|
| 1|{null} |Lukas | 132500|
| 0|jOOQ |Lukas | 225000|
| 0|SmartSoft |Lukas | 92500|
| 0|SoftSkills|Lukas | 80000|
+----+----------+--------+-----------+
In other words, using the CUBE() function, you will get grouping results for every possible combination of the grouping fields supplied to the CUBE() function, which results in 2^n GROUPING_ID()’s for n “cubed” grouping fields
// assuming that DATA is an actual table...
create.select(
groupingId(DATA.EMPLOYEE, DATA.COMPANY).as("id"),
DATA.COMPANY, DATA.EMPLOYEE, avg(SALARY))
.from(DATA)
.groupBy(cube(DATA.EMPLOYEE, DATA.COMPANY));
select B.* from B
join A on A.ID = B.A_ID
import org.apache.openjpa.persistence.*;
@Entity
@FetchGroups({
@FetchGroup(name="detail", attributes={
@FetchAttribute(name="publisher"),
@FetchAttribute(name="articles")
}),
...
})
public class Magazine {
@ManyToOne(fetch=FetchType.LAZY)
@LoadFetchGroup("detail")
private Publisher publisher;
...
}
Fetch groups are sets of fields that load together. They can be used to to pool together associated fields in order to provide performance improvements over standard data fetching. Specifying fetch groups allows for tuning of lazy loading and eager fetching behavior.I see. I can hear your DBA screaming at the indecipherable and out of control SQL that must result from all of that. Or can anyone show me an example where this does NOT go out of control? Here’s Fetch Groups, as documented by Oracle Fusion Middleware, a tool to simplify your life when working with JPA / JDO: http://docs.oracle.com/cd/E16764_01/apirefs.1111/e13946/ref_guide_fetch.html </rant>
jOOQ is now also associated with the “dark side” and “SciFi”:
http://blog.pdark.de/2011/11/23/peace-between-java-and-sql/
So, join the dark side now :-)
This is a topic that has been discussed many times before. Java’s lack of unsigned byte/short/int/long types. The main reasons why the JLS designers omitted those types were:
Nevertheless, these types are sometimes useful for cryptography, image processing, binary protocols, everything related to binary data (why is byte unsigned after all??), and the list of rants in the following ticket at Sun/Oracle is long:
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4504839
For jOOQ, unsigned number types would be useful, as some databases support them (e.g. MySQL, Postgres). And they’re not necessarily trivial to map to Java. So I was looking for a good solution. The best one being to use wrapper classes extending java.lang.Number. So, I have raised the question on Stack Overflow to find such a library:
http://stackoverflow.com/questions/8193031/is-there-a-java-library-for-unsigned-number-type-wrappers
Incredibly, no one seems to have done this – except for some partial implementations in some large libraries. So I’m launching a new OSS project called jOOU – U is for Unsigned. Check out a small library for Java Unsigned Number wrappers:
// Method declarations with or without varargs
public static String concat1(int[] values);
public static String concat2(int... values);
// The above methods are actually the same.
String s1 = concat1(new int[] { 1, 2, 3 });
String s2 = concat2(new int[] { 1, 2, 3 });
// Only, concat2 can also be called like this, conveniently
String s3 = concat2(1, 2, 3);
// You can now have a generic type in your varargs parameter:
public static <T> T[] array(T... values);
// The above can be called "type-safely" (with auto-boxing):
Integer[] ints = array(1, 2, 3);
String[] strings = array("1", "2", "3");
// Since Object could also be inferred for T, you can even do this:
Object[] applesAndOranges = array(1, "2", 3.0);
// Overloaded for "convenience". Let's ignore the compiler warning
// caused when calling the second method
public static <T> Field<T> myFunction(T... params);
public static <T> Field<T> myFunction(Field<T>... params);
// The outer function can infer Integer for <T> from the inner
// functions, which can infer Integer for <T> from T...
Field<Integer> f1 = myFunction(myFunction(1), myFunction(2, 3));
// But beware, this will compile too!
Field<?> f2 = myFunction(myFunction(1), myFunction(2.0, 3.0));
// This one, you can always do:
Field<?> f2 = myFunction(myFunction(1), myFunction(2.0, 3.0));
// But these ones show what you're actually about to do
Field<? extends Field<?>> f3 = // ...
Field<? extends Field<? extends Number>> f4 = // ...
Field<? extends Field<? extends Comparable<?>>> f5 = // ...
Field<? extends Field<? extends Serializable>> f6 = // ...
Test.java:58: incompatible types
found : Test.Field<Test.Field<
? extends java.lang.Number&java.lang.Comparable<
? extends java.lang.Number&java.lang.Comparable<?>>>>
required: Test.Field<java.lang.Integer>
Field<Integer> f2 = myFunction(myFunction(1),
myFunction(2.0, 3.0));
create.selectFrom(AUTHOR).where(LAST_NAME.equal("Cohen"));
Routines r1 = ROUTINES.as("r1");
Routines r2 = ROUTINES.as("r2");
for (Record record : create().select(
r1.ROUTINE_NAME,
r1.SPECIFIC_NAME,
// 1. Ignore the data type when there is at least one out parameter
decode()
.when(exists(create()
.selectOne()
.from(PARAMETERS)
.where(PARAMETERS.SPECIFIC_SCHEMA.equal(r1.SPECIFIC_SCHEMA))
.and(PARAMETERS.SPECIFIC_NAME.equal(r1.SPECIFIC_NAME))
.and(upper(PARAMETERS.PARAMETER_MODE).notEqual("IN"))),
val("void"))
.otherwise(r1.DATA_TYPE).as("data_type"),
r1.NUMERIC_PRECISION,
r1.NUMERIC_SCALE,
r1.TYPE_UDT_NAME,
// 2. Calculate overload index if applicable
decode().when(
exists(
create().selectOne()
.from(r2)
.where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
.and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
.and(r2.SPECIFIC_NAME.notEqual(r1.SPECIFIC_NAME))),
create().select(count())
.from(r2)
.where(r2.ROUTINE_SCHEMA.equal(getSchemaName()))
.and(r2.ROUTINE_NAME.equal(r1.ROUTINE_NAME))
.and(r2.SPECIFIC_NAME.lessOrEqual(r1.SPECIFIC_NAME)).asField())
.as("overload"))
.from(r1)
.where(r1.ROUTINE_SCHEMA.equal(getSchemaName()))
.orderBy(r1.ROUTINE_NAME.asc())
.fetch()) {
// [...] do the loop
select
r1.routine_name,
r1.specific_name,
case when exists (
select 1 from information_schema.parameters
where (information_schema.parameters.specific_schema
= r1.specific_schema
and information_schema.parameters.specific_name
= r1.specific_name
and upper(information_schema.parameters.parameter_mode)
<> 'IN'))
then 'void'
else r1.data_type
end as data_type,
r1.numeric_precision,
r1.numeric_scale,
r1.type_udt_name,
case when exists (
select 1 from information_schema.routines as r2
where (r2.routine_schema = 'public'
and r2.routine_name = r1.routine_name
and r2.specific_name <> r1.specific_name))
then (select count(*)
from information_schema.routines as r2
where (r2.routine_schema = 'public'
and r2.routine_name = r1.routine_name
and r2.specific_name <= r1.specific_name))
end as overload
from information_schema.routines as r1
where r1.routine_schema = 'public'
order by r1.routine_name asc
package org.jooq;
public interface Condition {
// Various overloaded forms of the "AND" operation:
Condition and(Condition other);
Condition and(String sql);
Condition and(String sql, Object... bindings);
// [...]
}
package org.jooq.impl;
abstract class AbstractCondition implements Condition {
// The single point of failure
@Override
public final Condition and(Condition other) {
return new CombinedCondition(
Operator.AND, Arrays.asList(this, other));
}
// "Convenience methods" delegating to the other one
@Override
public final Condition and(String sql) {
return and(condition(sql));
}
@Override
public final Condition and(String sql, Object... bindings) {
return and(condition(sql, bindings));
}
}
// This is a convenience method:
public static <T> Field<T> myFunction(Field<T> field, T value) {
return myFunction(field, val(value));
}
// It's equivalent to this one.
public static <T> Field<T> myFunction(Field<T> field, Field<T> value) {
return MyFunction<T>(field, value);
}
Field<Integer> field1 = //...
Field<String> field2 = //...
Field<Integer> result1 = myFunction(field1, 1);
Field<String> result2 = myFunction(field2, "abc");
// While this works...
Field<Object> field3 = //...
Field<Object> result3 = myFunction(field3, new Object());
// ... this doesn't!
Field<Object> field4 = //...
Field<Object> result4 = myFunction(field4, field4);
Field<Object> result4 = myFunction(field4, (Field) field4);
Field<Object> result4 = myFunction(field4, (Field<Object>) field4);