EzDevInfo.com

How to get all the result columns from database with other custom(concat, sum, count) columns in Jooq

I have a table Table1 with 6 columns.

Here is the sql statement that i need to map.

Select *,count(ID) as IdCount from Table1;

Now, the sql query result will be 7 columns ( 6 Table1 columns and 1 IdCount column). But when i implement the same in Jooq with this query, it only gets a single column "IDCount".

SelectQuery q = factory.selectQuery();
        q.addSelect(Table1.ID.count().as("IdCount"));
        q.addFrom(Table1.TABLE1);

Now, the resultant recordset have only a single column "IdCount" while what i need is all the columns and one additional column "IdCount". I want 7 columns in Jooq too.

How to use a custom strategy with the jOOQ code-generator and Maven?

With jOOQ, I may want to combine using the jOOQ code generator with Maven and a custom generator strategy. It looks as though this can be done as such (leaving out irrelevant parts):

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>2.2.2</version>

  <!-- The plugin should hook into the generate goal -->
  <executions>
    <execution>
      <goals>
        <goal>generate</goal>
      </goals>
    </execution>
  </executions>

  <configuration>
    <generator>
      <name>org.jooq.util.DefaultGenerator</name>
      <!-- But the custom strategy is not yet compiled -->
      <strategy>
        <name>com.example.MyStrategy</name>
      </strategy>
    </generator>
  </configuration>
</plugin>

The above configuration depicts the problem. jOOQ's code generator hooks into the generate goal of the Maven lifecycle, which takes place before the compile goal of the lifecycle. For code generation, however, it needs a pre-compiled custom strategy class, or I will get a ClassNotFoundException. How can this be resolved with Maven? Can I compile a single class before executing the generate goal?

My open source library is stabilising. Now how to get attention? [closed]

I had recently stabilised developments of a major open source library written in Java. I have then published an article on the server side, which has brought me a lot of positive (but also critic, constructive) feedback. And a first contributor, which is great!

• See the article here: http://www.theserverside.com/discussions/thread.tss?thread_id=61162
• And the library: http://jooq.sourceforge.net

So with all of that good feedback, I have a great feeling about my project, in a way that I am creating something useful and original. Some key ideas behind my project, and why I think it is original:

• It's an or-mapper. OK, now that really isn't original... ;-)
• It has code generation in it. OK, still not original. But that's always nice.
• It allows for creating type-safe queries in Java using its own domain specific language. That's better. No string concatenation. JPA has only just recently copied criteria queries from Hibernate.
• It allows for doing so with all SQL features, including complex joins, nested selects, unions, aliasing, etc. Now that seems original to me. OR-mappers tend to try to ignore the relational data model behind RDBMS.
• It supports all kinds of native non-standard functionality like UDTs, stored procedures, native functions, etc. I don't know any or-mapper that does that.

I think that these key ideas are useful for a very specific type of developer. That specific developer

• interfaces Java with huge legacy databases.
• knows SQL well and wants to use it extensively.
• doesn't want to learn any new language (HQL, JPQL, etc)
• doesn't want to spend one minute fine-tuning some sophisticated XML-configuration.
• wants little abstraction over SQL, because his software is tightly coupled with his database. Something that I think the guys at Hibernate or JPA seem to have ignored.
• EDIT: needs a strong but light-weight library for database access. For instance when they develop for mobile devices (see comment by SRM).

Now is the beginning of the hard work. How to get attention? How can I attain a bigger crowd? How can my project become relevant? How to reach to that "specific type of developer"?

Mapping between Oracle Packages and Java Packages

In my database interfacing library jOOQ, I would like to add support for Oracle (or DB2, etc) packages. I have already implemented stored procedure/function support where every stored object is modelled as a generated Java class. For example, this stored function

CREATE FUNCTION f_author_exists (author_name VARCHAR2) RETURNS NUMBER;

will generate a class that can be used like this (note, there are also lots of convenience methods, this example just shows the general design):

// A new "function call instance". The function needs to be instanciated
// once per call
FAuthorExists f = new FAuthorExists();

// Set the function parameters on the call instance and call it
f.setAuthorName("Paulo");
f.execute(connection);

// Fetch the result from the function call instance
BigDecimal result = f.getReturnValue();

The reason I chose a mapping SQL function -> Java Class is because stored procedures allow complex return values (several OUT, or IN OUT parameters) that I want to be able to fetch one by one after calling the procedure:

p.getOutParam1();
p.getOutParam2();

Now this design works fine with stored functions / procedures, where overloading is not possible. Within Oracle's (or DB2's) packages, however, I can have several functions with the same name, like

CREATE PACKAGE my_package IS
  FUNCTION f_author_exists (name VARCHAR2) RETURNS NUMBER;
  FUNCTION f_author_exists (name VARCHAR2, country VARCHAR2) RETURNS NUMBER;
END my_package;

When I generate a class per function (or procedure), I will have naming clashes with several FAuthorExists Java classes. A lame solution is to add an index to the class name, such as FAuthorExists2, FAuthorExists3. Another lame solution is to generate some sort of hash value (or the value itself) from the parameter names/types directly into the classname, such as FAuthorExistsVARCHAR2, FAuthorExistsVARCHAR2VARCHAR2. Neither solution is desirable for obvious reasons.

Does anyone have a simple solution to this problem? Or maybe an Idea of a better overall design which would not produce such function name overloading issues?

Any feedback appreciated!

Creating JOOQ query dynamically

I need to create a JOOQ SELECT query dynamically based on the set of parameters. I dont know how to append it dynamically. Please help

Thanks in advance.

Retrieving the value of selectCount in jooq

I have some code that looks like this:

Record record = jooq
    .selectCount()
    .from(USERS)
    .fetchOne();

Currently I'm doing the following to get the count:

Integer count = (Integer) record.getValue(0);

But it seems like there must be a better solution (that's type-safe...since that's the whole point of using jooq). Any suggestions?

What are best practices for maintaining a Mysql database schema in a cross platform way?

We have two software stacks, Ruby on Rails and Java, that share a single Mysql database. We're using the Jooq Java database abstraction layer, which works by reading an existing database schema and generating code.

We have been relying on Rails migrations to keep track of schema changes, but our Java developers are unfamiliar with this, and our Rails devs have had to dig us out of problems more than once. It's also a rather cumbersome process, making a schema change can take 5-10 minutes, whereas it would take seconds if modifying the schema directly through a tool like MySql Workbench.

It would be preferable to have a solution for modifying our DB schema that wasn't tied to Rails, can anyone recommend a suitable approach?

Cast smallint unsigned in Java

I'm using jOOQ to get id which in MySQL is smallint unsigned primary key auto_increment

public List<Integer> getID() {
   Factory sql = new Factory(Database.getInstance().connect(), SQLDialect.MYSQL);
   return (List<Integer>) sql.select().from("users").fetch().getValues("id_users");
}

And go error

org.jooq.tools.unsigned.UShort cannot be cast to java.lang.Integer

Here they wrote that smallint unsigned should be cast to int.

Edit Method should be

public List<UShort> getID() {
    Factory sql = new Factory(Database.getInstance().connect(), SQLDialect.MYSQL);
    return (List<UShort>) sql.select().from("users").fetch().getValues("id_users");
}

And in loop result should be cast to int.

How to get the underlying connection inside a transaction using jOOQ?

I'm using jOOQ inside an existing project which also uses some custom JDBC code. Inside a jOOQ transaction I need to call some other JDBC code and I need to pass through the active connection so everything gets inside the same transaction.

I don't know how to retrieve the underlying connection inside a jOOQ transaction.

create.transaction(configuration -> {
    DSLContext ctx = DSL.using(configuration);

    // standard jOOQ code
    ctx.insertInto(...);

    // now I need a Connection
    Connection c = ctx.activeConnection(); // not real, this is what I need
    someOtherCode(c, ...);
});

Reading the docs and peeking a bit on the source code my best bet is this:

configuration.connectionProvider().acquire()

But the name is a bit misleading in this particular use case. I don't want a new connection, just the current one. I think this is the way to go because the configuration is derived and I will always get the same connection, but I'm not sure and I can't find the answer in the documentation.

Create table with primary key using jOOQ

jOOQ has CREATE TABLE syntax as stated in the documentation:

create.createTable(AUTHOR)
      .column(AUTHOR.ID, SQLDataType.INTEGER)
      .column(AUTHOR.FIRST_NAME, SQLDataType.VARCHAR.length(50))
      .column(AUTHOR_LAST_NAME, SQLDataType.VARCHAR.length(50))
      .execute();

I'm wondering how to define which column belongs to the primary key? So is there a way in jOOQ to create a CREATE TABLE statement with PRIMARY KEY information?

I'm specifically interested in a solution for SQLite, which doesn't have syntax to add the primary key afterwards, so I think in worst case I have to go to a DB specific solution?

How to select from only one table in jOOQ using a query with a join?

I have the following query in jOOQ:

factory()
.select()
.from(PERSON)
.join(ENDUSER).on(ENDUSER.PERSON_FK.equal(PERSON.ID))
.where(ENDUSER.ID.equal(userId))
.fetchOne();

This query returns to me a Record with all columns from PERSON and ENDUSER, but I only want the columns from PERSON (that's why I put .from(PERSON) and not .from(PERSON, ENDUSER)). I know it doesn't matter that much but I don't want unnecessary fields to be returned.

jooq Converter: from java.sql.Date to java.time.LocalDate

I have tried to write a Converter<java.sql.Date, java.time.LocalDate> but I can't get it to work with all time zone settings.

The idea:

• if the client code has a LocalDate, say 20-Aug-2014, and saves it to the DB, it should appear as 20-Aug-2014 in the DB, no matter what the client time zone is.
• if the DB contains a date of 20-Aug-2014, the client should receive a LocalDate of 20-Aug-2014, no matter what the client time zone is.

My test:

@Test public void dateConverter() {
  for (int offset = -12; offset <= 12; offset++) {
    TimeZone localTz = TimeZone.getTimeZone(ZoneOffset.ofHours(offset));
    TimeZone.setDefault(localTz);
    LocalDate ld = LocalDate.now();

    sql.insertInto(DATE_TEST).set(new DateTestRecord(ld)).execute();
    LocalDate savedLd = sql.selectFrom(DATE_TEST).fetchOne(DATE_TEST.DATE_);
    assertEquals(savedLd, ld, "offset=" + offset);
    sql.delete(DATE_TEST).execute();
  }
}

My converter:

public class DateConverter implements Converter<Date, LocalDate>{
  @Override public LocalDate from(Date date) { return date.toLocalDate(); }
  @Override public Date to(LocalDate ld) { return Date.valueOf(ld); }
  @Override public Class<Date> fromType() { return Date.class; }
  @Override public Class<LocalDate> toType() { return LocalDate.class; }
}

I have tried various variations but none worked...

JOOQ ignoring database columns with default values

It seems that JOOQ is completely ignoring the default values of database columns. Neither gets the ActiveRecord object updated nor does it skip this column on INSERT. Instead it tries to set it to NULL which fails on NOT NULL columns.

Example:

CREATE TABLE bug (
  foo int,
  bar int not null default 42
);

  BugRecord b = jooq.newRecord(BUG);
  b.setFoo(3);
  b.store();          

  assertNotNull(b.getBar()); // fails

  Record r = jooq.select().from(BUG).fetchOne();
  assertEquals(new Integer(-1), r.getValue(BUG.BAR)); // fails

  // DataMapper pattern
  Bug b = new Bug();
  b.setFoo(3);
  bugDao.insert(b); // Fails because it tries to set "bar" to NULL

The behaviour I would expect is that either the newRecord() initializes all default variables with the korrekt values (although I understand that this could be difficult if the result is the outcome of a custom function :-)).or that the INSERT INTO does not insert all unmodified columns with default values and then that the INSERT INTO is followed by a SELECT that fetches the now existing values from the database (similar to a RETURNING).

Is this really a bug/limitation or am I missing some config option etc which makes it possible to use "not null default" columns?

Can JPA/Hibernate be combined with other persistence framework like jOOQ

We have domain that where 90% of the classes are very straightforward and can be easily mapped 1:1 in the DB. I am very happy how Hibernate combined with spring-data-jpa removes tons of chores for these classes.

The rest of the domain however is challenging and for number of reasons I don't want to directly map it to DB tables.

I did experiment to introduce intermediate beans that are managed by Hibernate and map from these beans to my domain and this worked well when all relations are from the challenging to the easy part. This approach fails when I have "easy" class managed by Hibernate that references "challenging" class that is mapped in custom Java code and not directly hibernate managed.

This is when I realized I cannot find no way to customize Hibernate and plug in some sort of ObjectFactory that would allow me to do such transformations on the fly.

--edit--

What is my question: What is the easiest way to have DDD-style domain layer that has zero DB concerns in the entities, while using JPA? In DDD all DB concerns are handled by the Repository which typically collaborates with DAOs.

Zero DB concerns in the entities means there are no JPA annotations or mapping configurations in the Domain classes. One way to do it is to have JPA (or other persistence technology) manage TOs that are mapped to the Domain Entities. If I take this route however I have to have all of my entities, even the simplest one (think Address) go via the mapping layer.

I would like to use something "lazy" like JPA for the trivial entities and be able to mix them with the other entities that are "manually" managed. Currently I do not know clever solution that allows me to have link from JPA managed entity to non-JPA managed entity. I can always retrieve the JPA entity and then retrieve the non-JPA entity with second call but would like to avoid this if possible.

jOOQ - support for JSR310

Does jOOQ provide support for JSR310 in combination with PostgreSQL? In particular, I am trying to use the following classes:

• java.time.Instant
• java.time.LocalDate
• java.time.LocalTime
• java.time.LocalDateTime

I am storing in the following data types (as per http://www.postgresql.org/docs/9.1/static/datatype-datetime.html):

• java.time.Instant: timestamp with timezone
• java.time.LocalDate: date
• java.time.LocalTime: time without timezone
• java.time.LocalDateTime: timestamp without timezone

Are these data types correct?

Does jOOQ support translation between java.sql.Timestamp, java.sql.Date and java.sql.Time and the four classes above (in both directions)?