jooq / jooq Goto Github PK
View Code? Open in Web Editor NEWjOOQ is the best way to write SQL in Java
Home Page: https://www.jooq.org
License: Other
jOOQ is the best way to write SQL in Java
Home Page: https://www.jooq.org
License: Other
The count function renders bad SQL in dialects that do not render parentheses on function argument lists, if those lists are empty.
f is a function without arguments. this works correctly:
select f from dual
count is a function with arguments. this works correctly:
select count(id) from table
count can work without arguments. this doesn't work:
select count* from table
the parentheses must be rendered in this case
org.jooq.ResultProviderQuery is a model for a union (or other type of set combination) of queries. Such a combination of queries can be ordered globally like this:
SELECT A, B FROM T1
UNION
SELECT A, B FROM T2
ORDER BY A
This should be reflected in jOOQ by pulling up order by clauses from SelectQuery to ResultProviderQuery
Please add support for http://h2database.com/html/grammar.html#merge
Stacktrace:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2228)
at org.jooq.impl.SelectQueryImpl.execute(SelectQueryImpl.java:320)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:61)
at org.jooq.test.jOOQAbstractTest.testCombinedSelectQuery(jOOQAbstractTest.java:176)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Please add this to the parent POM:
<plugin>
<!-- If this plugin produces an error, make sure you are running Maven with Java 6 -->
<artifactId>maven-enforcer-plugin</artifactId>
<executions>
<execution>
<id>enforce-java</id>
<goals>
<goal>enforce</goal>
</goals>
<configuration>
<rules>
<requireJavaVersion>
<version>[1.6.0,1.7.0)</version>
</requireJavaVersion>
</rules>
</configuration>
</execution>
</executions>
</plugin>
Currently, the factory class "Create" knows only static methods.
Add a state to that factory (Create create = new Create(...)), such that these elements can be contained in the factory:
jOOQ/jOOQ/src/main/java/org/jooq/Configuration.java contains
import org.hamcrest.Factory;
which breaks the build with Maven.
The implementation of the execute() methods has not yet been done
None
We have an application which runs on many different databases (currently, six are supported).
From the tutorial, it's not clear whether this kind of app is supported by jOOQ. At first glance, it looks like the code generator will generate DB specific code (which won't run on other databases anymore).
Please either improve the documentation or allow to configure the generated code at runtime to work with any database supported by jOOQ.
Using Java 6 and 7 on Ubuntu 12.04 (amd64) gives:
jOOQ $ mvn clean install
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building jOOQ 2.3.1
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-clean-plugin:2.4.1:clean (default-clean) @ jooq ---
[INFO]
[INFO] --- maven-enforcer-plugin:1.0:enforce (enforce-maven) @ jooq ---
[INFO]
[INFO] --- maven-jaxb2-plugin:0.8.1:generate (default) @ jooq ---
[INFO]
[INFO] --- maven-resources-plugin:2.5:resources (default-resources) @ jooq ---
[debug] execute contextualize
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 7 resources
[INFO] Copying 1 resource
[INFO]
[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ jooq ---
[INFO] Compiling 443 source files to /home/stier/tmp/jOOQ/jOOQ/target/classes
[INFO] -------------------------------------------------------------
[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] /home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/ConnectionProxy.java:[67,0] error: ConnectionProxy is not abstract and does not override abstract method getNetworkTimeout() in Connection
[ERROR] /home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/CursorImpl.java:[234,18] error: CursorImpl.CursorResultSet is not abstract and does not override abstract method getObject(String,Class) in ResultSet
[ERROR]
T extends Object declared in method getObject(String,Class)
/home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/PreparedStatementProxy.java:[68,0] error: PreparedStatementProxy is not abstract and does not override abstract method isCloseOnCompletion() in Statement
[INFO] 3 errors
[INFO] -------------------------------------------------------------
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 18.183s
[INFO] Finished at: Tue Jun 26 13:33:42 CEST 2012
[INFO] Final Memory: 14M/168M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:2.3.2:compile (default-compile) on project jooq: Compilation failure: Compilation failure:
[ERROR] /home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/ConnectionProxy.java:[67,0] error: ConnectionProxy is not abstract and does not override abstract method getNetworkTimeout() in Connection
[ERROR] /home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/CursorImpl.java:[234,18] error: CursorImpl.CursorResultSet is not abstract and does not override abstract method getObject(String,Class) in ResultSet
[ERROR]
[ERROR] T extends Object declared in method getObject(String,Class)
[ERROR] /home/stier/tmp/jOOQ/jOOQ/src/main/java/org/jooq/impl/PreparedStatementProxy.java:[68,0] error: PreparedStatementProxy is not abstract and does not override abstract method isCloseOnCompletion() in Statement
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException
Some fields should be clearly marked as the primary key. If a table contains primary key fields, then its generated records can implement an UpdatableRecord interface, which allows for storing changes made to the record back to the database.
I have a set of tables that share a common group of columns, and would like to perform generic operations on them. It would feel natural to capture the commonality in an interface, and subclass each table with something that implements this interface. Unfortunately, the private constructors in tables make them effectively final, so this isn't possible.
Instead I'm creating a class for each table, embedding the table, and adding proxy accessors for the common columns. This makes composing queries a little clunky, but seems like it will work for most cases.
Makes you wish Java had duck typing like Scala.
We have a lot of unsigned bigint columns in our database, and use String to store their values in Java, because there is no "primitive" numeric type large enough to hold them. I noticed that the jOOQ code generator treats unsigned bigint as Long, but this will probably cause value overflows and other unexpected behavior at extreme values.
None
I have a custom function in the database that I incorporate into queries. I use my Routine class to create instances of it as a Field. When I run my queries, however, the routine doesn't respect my schema mapping, and instead always accesses the routine at its codegen-time schema.
The Javadoc comment of the exists condition on line 976 of the Factory class should read
{code}
/**
EXISTS ([query])
as oppose to duplicating the Javadoc for the nontExists condition. If required I can fork and submit the trivial patch.
Sometimes it is more convenient to express a field directly in SQL, much like the PlainSQLConditio
Our (legacy) database has enum columns with values "true" and "false" -- silly I know. jOOQ dutifully reads them, but of course we end up with the reserved words "true" and "false" as attempted enum field names, breaking the compiler.
When relations are generated on a schema subset (using the includes/excludes mechanisms of code generation), a NullPointerException occurs.
The solution is not to generate relations if one side of the primary-key / foreign-key relationship is excluded
When tables are aliased, they cannot be used in joins anymore. This also means, that a table cannot be joined on itself
Currently, date, time timestamp fields cannot be stored to the database, only selected. This is because of a lack of implementation in FieldTypeHelper. It should not be too difficult to complete this rather important implementation
None
None
None
Updatable records may be stale (i.e. database record has changed in the mean time). A refresh method could re-read all values in the record
Binding is only tested in simple ways. There could be quite a few latent bugs.
Add more unit tests focussing on binding variables
None
I used the jooq-codegen-maven plugin version 1.6.9 in an exploratory project.
The code that is generated has a @javax.annotation.Generated
annotation with a value {"http://www.jooq.org", "1.6.8"}
.
It seems that the plugin was build with a wrong generator.properties file.
Attempting to use nullif from a subtype of MySQLFactory causes an exception like the one below:
org.jooq.exception.SQLDialectNotSupportedException: Type class org.jooq.impl.CombinedCondition is not supported in dialect null
at org.jooq.impl.AbstractDataType.getDataType(AbstractDataType.java:428)
at org.jooq.impl.FieldTypeHelper.getDataType(FieldTypeHelper.java:781)
at org.jooq.impl.Factory.getDataType(Factory.java:4470)
at org.jooq.impl.Factory.val(Factory.java:4003)
at org.jooq.impl.Factory.nullif(Factory.java:1578)
MySQL supports NULLIF as documented here:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_nullif
Some tolerance of null values in Plugin.java might be nice. For example, in other plugin configurations I often leave , which apparently parses as null. Currently Plugin.java will throw an NPE instead of translating to empty string.
Mapping a generated schema to one in the database at runtime is great, but it still leaves a constraint on the build system to use a particular schema in order for the class names to be reliable. More of a nice-to-have. =)
In order to know whether the Record has been changed since its load, a Value class should be added to wrap values in Records
None
None
Table relations are not generated by default. When this happens, Query.returning() will always return null because it will be unable to find the primary key(s) being returned.
I propose adding a flag in the generated classes which indicates that relations were not generated, then using this flag in returning() to throw UnsupportedOperationException() indicating that this feature is unsupported on tables without relations.
That code reuse just doesn't make sense
This has become urgent as it prevents efficient developings in jooq-meta
Some database support a HAVING clause. This could be added to jOOQ
JOOQ doesn't support insert.onDuplicateKeyIgnore() for H2, nor does it allow INSERT ... WHERE NOT EXISTS as mentioned here: http://stackoverflow.com/questions/6736518/h2-java-insert-ignore-allow-exception
We need a way to insert a new row, unless it already exists (this is the counterpart to MERGE support).
Given the following valid PostgreSQL DDL, jOOQ 2.0.0 will generate Java metamodel code that doesn't compile.
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
baz_id INTEGER NOT NULL,
a INTEGER
);
CREATE TABLE bar (
id SERIAL PRIMARY KEY,
baz_id INTEGER NOT NULL,
b INTEGER
);
CREATE TABLE baz (
id SERIAL PRIMARY KEY,
c INTEGER
);
ALTER TABLE foo ADD CONSTRAINT baz_fk FOREIGN KEY ( baz_id )
REFERENCES baz ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE bar ADD CONSTRAINT baz_fk FOREIGN KEY ( baz_id )
REFERENCES baz ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
None
If Query methods returned "this" instead of void, something like this would be thinkable:
SelectQuery query;
query.select(A, B, C)
.from(T1)
.join(T2, T1_ID, T2_ID)
.where(A.isEqualTo(B))
.groupBy(A, B, C)
.orderBy(A);
Obviously, this would mean a major API change
FieldMapsForInsert.isExecutable() returns false if
INSERT INTO foo VALUES ();
is invoked, but this is completely legal for tables containing a single AUTO_INCREMENT field.
Currently, general !RuntimeExceptions (such as !IllegalStateException) are used to indicate misuse of the framework.
Add more explicit exceptions and document them
Even if HSQLDB is not very wide-spread, support implementation is easy, as the metadata model is similar to the one of MySQL
While in SQL, a ResultProviderQuery (e.g. union of queries) cannot be further grouped, selected etc, in jOOQ it could be if ResultProviderQuery.combine() methods would return a wrapper for an inner select.
This means:
q1 = SELECT * FROM dual
q2 = SELECT * FROM dual
c1 = q1.combine(q2) = (SELECT * FROM dual) union (SELECT * FROM dual)
c2 = c1.groupBy(x) = select x from (c1) group by x
Some SQL operations only work for single-field selects, e.g.
SELECT * FROM t1 WHERE f IN (SELECT f FROM t2)
In this example, the inner select MUST return exactly one row. Other examples are
SELECT * FROM t1 WHERE f = (SELECT f FROM t2)
In this example, not only one row must be returned, but also one column. Of course, this cannot be checked at compile-time, but the single-field select would come in handy again
Implement single-field select interfaces and adapt the API to only accept these kind of selects where applicable
This needs to be resolved using the newly introduced org.jooq.TupleX
types
There are likely to be some errors with the built-in functions, which aren't all tested
Once primary / foreign key relationships are available, OR-Mapping can be done, so a foreign key can be resolved and a new record fetched
Code generation could also be done from Hibernate mappings. In fact, HQL could be completely rendered by jOOQ
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.