concat table fields and arbitrary strings

[TheDude]

Hello,

I've started playing with jOOQ for a week now, using it as a sql
querybuilder to use with spring's jdbcTemplate, and until now the
framework is fullfilling my needs !

I'm now facing the following concern, which I don't know how to
achieve, even since I've read the doc and browsed the api javadoc :

Let's say I have a countries table :
ccode char(2) PK, cname varchar(255)

I'd like to produce the following resultset :
'fr', 'fr - France'

The generated select statement (mysql syntax) should be like this :
<pre>
select `t`.`ccode`, concat(cast(`t`.`ccode` as char), ' - ',
cast(`t`.`cname` as char)) as `value_` from `countries` as `t` order
by `t`.`country` asc
</pre>

I would like to kow how to programmatically achieve it using the
Factory.concat method, or maybe do have I
missed something ?

Thanks in advance and greetings from France!

TL.

[Lukas Eder]

Hello,

> I'm now facing the following concern, which I don't know how to
> achieve, even since I've read the doc and browsed the api javadoc :

Where did you run into problems? Converting your " - " string to an
org.jooq.Field?
Something along these lines should work:

------------------------------------------------------------
import static org.jooq.impl.Factory.*;

Factory create = new Factory(...);

// Alias the countries table:
Countries t = Countries.COUNTRIES.as("t");

// Concat several org.jooq.Field objects.
// Use Factory.val() to create a bind value
create.select(t.ccode, concat(t.ccode, val(" - "), t.cname).as("value_"))
.from(t)
.orderBy(t.country.asc());
------------------------------------------------------------

Although, I don't know what t.country should refer to... If you do
need the casting to char, use the Field.cast() methods...

Cheers
Lukas

[Lukas Eder]

... Note, the relevant section explaining about creating bind values
in the manual is here:
http://www.jooq.org/manual/JOOQ/BindValues/

2012/3/18 Lukas Eder <lukas...@gmail.com>:

[er.gsma...@gmail.com]

 Hi,

  Thanks to reply, here i can't able to define DSL.groupConcat(). will u give the function definition. 

   

 b'ços the site become very complex to understand.

 Final request is there any site to refer advance jooq functionality on easy way with example..

Regards,

Manikandan

[er.gsma...@gmail.com]

Hi,

Am working new in jooq. will you explain how do i use group_concat. Here i have to group_concat one of the selection field.

Mysql query:

SELECT group_concat(AUF.AcLineId )FROM AcHeader as AH INNER JOIN AcDetails AS AD ON  AD.AcHeaderId = AH.AcHeaderId INNER JOIN AcUserField AS AUF ON AUF.AcLineId = AD.AcLineId AND AUF.FieldNo = 1 WHERE  AH.Company = 1 AND AH.AccountNo = 190000 AND AH.Status IN ('C', 'D')  

Jooq Query:

dslContext.select(AUF.AcLineId).from(AcHeader).Join(AcDetails).on(AcDetails.AcHeaderId.equal(AcHeader.AcHeaderId)).Join(AcUserField ).on(AcUserField.AcLineId.equal(AcDetails.AcLineId))  .and(AcUserField.FieldNo.equal()).Where(AcHeader.Company=1)   .and(AcHeader=AccountNo=190000).and(Status.in("'C','D'")).fetch()

http://stackoverflow.com/questions/32138417/how-do-i-write-jooq-select-query-for-the-following-mssql-query

Kindly share your idea..

Regard,

Manikandan

[Lukas Eder]

Hello Manikandan,

Thanks for reaching out on the user group as well. For completeness's sake, I'll cross-post my answer that I've given on Stack Overflow here, too. 

Cheers,

Lukas

jOOQ supports MySQL's GROUP_CONCAT() function natively as DSL.groupConcat(). Here's your corrected query with comments:

// Don't forget, of course:
import org.jooq.impl.DSL;

And then

// Use DSL.groupConcat() here
dslContext.select(DSL.groupConcat(AcUserField.AcLineId))
          .from(AcHeader)

          // Lower-case join()
          .join(AcDetails)
            .on(AcDetails.AcHeaderId.equal(AcHeader.AcHeaderId))

          // Lower-case join()
          .join(AcUserField)
            .on(AcUserField.AcLineId.equal(AcDetails.AcLineId))

          // Forgot to pass the parameter "1"
            .and(AcUserField.FieldNo.equal(1))

          // Lower-case where() and use .equal() or .eq() rather than a Java assignment "="
          .where(AcHeader.Company.equal(1))
          .and(AcHeader.AccountNo.equal(190000))

          // List values in in(...) predicate individually, don't put them all
          // in a single string.
          .and(AcHeader.Status.in("C", "D")).fetch()

Optionally, if you prefer to use aliased versions of your tables, you can do so:

AcHeader AH = AcHeader.as("AH");
AcDetails AD = AcDetails.as("AD");
AcUserField AUF = AcUserField.as("AUF");

dslContext.select(DSL.groupConcat(AUF.AcLineId))
          .from(AH)
          .join(AD)
            .on(AD.AcHeaderId.equal(AcHeader.AcHeaderId))
          .join(AUF)
            .on(AUF.AcLineId.equal(AD.AcLineId))
            .and(AUF.FieldNo.equal(1))
          .where(AH.Company.equal(1))
          .and(AH.AccountNo.equal(190000))
          .and(AH.Status.in("C", "D")).fetch()

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.