Questions tagged [jooq]

1

votes
1

answer
226

Views

ORM Who can generate script dynamically

So basically I was checking working of hibernate, as well evaluated ORMs JOOQ. JPA is main de-facto standard for all java based ORM implementations more or less. I am not saying they are not perfect or good but my problem is like I have found many issues and hick-ups while using various options prov...
1

votes
1

answer
348

Views

IF Condition Within Sum in JOOQ

MySQL allows me to run the following query: SELECT SUM(IF(COLUMN1 > COLUMN2, COLUMN2, COLUMN1)), SUM(IF(COLUMN3 = 0,4 ,10)) FROM TABLE; How can I do this in JOOQ?
Anurag Sahu
1

votes
0

answer
77

Views

Injecting JOOQ Context using CDI and JNDI

I try to use jooq on an PostgreSQL Database. The Web-App is built on the JSF Framework using CDI for dependency injection. The server is WildFly 11. I use the jooq codegen to generate pojos and daos. Following the instructions on http://awolski.com/integrating-jooq-easy/ I should be able to inject...
1

votes
0

answer
295

Views

jOOQ requires Timestamp type to access datetime type in MySQL but this doesn't work

I have a MySQL database with data like this: These dates have data types datetime: We are using jOOQ in our project for querying MySQL. Now I need to exclude all entries older than a particular date from a result set. jOOQ demands to set a Timestamp value, and when I do it doesn't impact the result...
Václav
1

votes
1

answer
136

Views

How to use jooq codegen to automatically add custom fields to build pojos?

I use jooq codegen to generate entity classes like this : public class TCoactivitiesPinan extends BaseEntity implements Serializable { private static final long serialVersionUID = 2007524284; private Integer id; private String openid; private String tel; private Timestamp createdtime; ...} b...
hjk9317
1

votes
1

answer
31

Views

Query by joining a particular partition of a table with another table returns partial fields

On execution of the below statement, the result record only has fields for table B, but if I replace select() with select(A.fields()).select(B.fields()) , I get fields for both A and B, is this expected behavior? I would assume to get all fields just like when doing a regular join. DSL.using(configu...
anuj
1

votes
1

answer
281

Views

Batch insert onDuplcateKeyIgnore JOOQ

I have a class that looks like public class ABLink { private Long aId; private Long bId; } this class has a corresponding entity in the database(Postgresql) that looks like: CREATE TABLE a_b_link( a_id BIGINT NOT NULL, b_id BIGINT NOT NULL, CONSTRAINT a_b_link_pk PRIMARY KEY (a_id, b_id) ); And I am...
1

votes
2

answer
32

Views

JOOQ with SQL DataWarehouse?

Does JOOQ support dialect for 'SQL DataWarehouse'? Any pointers .
Sid
1

votes
1

answer
74

Views

Java, tomcat8: MYSQL query start to become very low after some hours that the deploy of a new .war has been done

I have a java website (a forum, to be more precise) which is hosted from a debian VPS, thanks to tomcat8. The website was not written by me, it was transfered to my ownership some days ago and I'm starting to write my code. The DAO (repository) of the application is based on JOOQ, but I don't like i...
Errore Fatale
1

votes
1

answer
70

Views

jOOQ: returning list of three objects from joins

This question is an extension of jOOQ: returning list with join,groupby and count in single object, but now with three objects instead of two. Idea select A.*, B.*, C.* from A left join B on B.aId = A.aId left join C on C.bId = B.bId Concrete example SelectWhereStep query = using(configuration()) .s...
Rico
1

votes
0

answer
618

Views

Using @Transactional annotation in Service Layer for REST GET APIs

I am building ReSTful APIs using Spring-boot 1.5.10.RELEASE, Java 8 and MySQL 5.6 and jOOQ(https://www.jooq.org/doc/3.9/manual/). In Service layer, I am using @Transactional annotation offered by Spring framework. Here is how my UserService class looks like: @Service @Slf4j public class UserService...
realPK
1

votes
1

answer
112

Views

Jooq not returning primary key after store, on sqlite

I'm using Gradle and https://github.com/etiennestuder/gradle-jooq-plugin, configured like that jooq { version = '3.10.6' foo(sourceSets.main) { jdbc { url = 'jdbc:sqlite:${projectDir.absolutePath}/foo.sqlite' } } } I have the following dependencies of interest jooqRuntime 'org.xerial:sqlite-jdbc:3.2...
facewindu
1

votes
1

answer
70

Views

get Postgres UDT value with JOOQ

In Postgres, i created a UDT as follows CREATE TYPE MY_TYPE AS ENUM ('FAILED', 'SUCCESS'); and in postgres cli, i can retrieve the UDT value back like \dT+ MY_TYPE which lists the details of the UDT, including its enum values under the 'Elements' column EDIT: added result of '\dT+' command postgres=...
abisheksampath
1

votes
2

answer
149

Views

jOOQ join Select<?> object

I want to perform something like this in SQL: SELECT * FROM tbl1 t1 INNER JOIN (SELECT MAX(col1) as maxCol, col2 FROM tbl1 t1 GROUP BY t1.col2) subQ ON t1.col2 = subQ.col2 AND t1.col1 = subQ.maxCol In jOOQ, I store the subQ into Select object: Select subQ = myDSL.select(DSL.max(TBL1.COL1).as('...
Newbie
1

votes
0

answer
77

Views

Query Has The Wrong Type

jOOQ 3.10.7 chooses the wrong type (bigint instead of boolean) in a multi-row upsert if the boolean is null in the first row. Below is the general code to build and execute the query. try (Connection writeConn = DatabaseManager.getConnection(true); final DSLContext writeContext = DatabaseManager.get...
DanO
1

votes
1

answer
43

Views

I can't get the complete list of tables using jooq 3.9.1 from Postgres databse

I am creating the table as follows: CREATE TABLE plist1 (c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (c1) When I tried to read the complete list of tables from the Postgres database including the master tables that we have used for partitioning. Interestingly when I have used the stand alone progr...
Patan
1

votes
1

answer
71

Views

I want to use jooq on a server where the DB environment is dynamic

I want to use jooq on a server where the DB environment is dynamic. I want to use jooq in spring boot 2 gradle environment. But there is a problem. The build.gradle file requires hard-coded DB information but is available. Can I create only JClass like QClass in QueryDSL? I am in the server's extern...
Shinssy
1

votes
1

answer
40

Views

Writing Data from RDS to Disk in JOOQ

My use case is that I have to run a query on RDS instance and it returns 2 millions records. Now,I want to copy the result directly to disk instead of bringing it in memory then copying it to disk. Following statement will bring all the records in memory, I want to transfer the results directly to f...
Anurag Sahu
1

votes
0

answer
51

Views

How to use Scala enums for JOOQ converters

I found in documentation how to force type from value to Java enum by EnumConverter. I tried to make my own converter for scalas enum. Enum: package com.enums object BlockSize extends Enumeration { type BlockSize = Value val Small, Medium, Large, Wide = Value } Converter: package com.converters impo...
Anton Feoktistov
1

votes
0

answer
20

Views

How to create DSL.rowField() from RowN?

I am currently playing around with JOOQs DSL.arrayAgg(..) and want to query all PROJECTS with each queried PROJECT row having a nested row of its PROJECTMEMBERUSERS. I have to join a USER for each PROJECTMEMBERUSER. The USER should be part of the same nested row as the PROJECTMEMBERUSER. Below is t...
Tobias Marschall
1

votes
1

answer
32

Views

JOOQ: Logically group columns from different tables in common interface

We have a table design where a lot of tables share some columns, e.g. in one case some of our tables have the column markedForDeletion. In another case, multiple of our tables have the columns approvedAt and approvedBy. These tables don't share anything in terms of to be JOINED data and thus, I don'...
Theo
1

votes
1

answer
100

Views

How to select single row from resultset for one field?

My select query is: sql.select(COMPANY.NAME, COMPANY_CONTACT.NAME.as('CONTACT_INTERACTION')) .from(COMPANY) .join(COMPANY_CONTACT) .on(COMPANY_CONTACT.COMPANY_ID.equal(COMPANY.ID)) .where(COMPANY.DELETED.equal(false)); ' sql ' is of type DSLContext. I'm trying to select just single COMPANY_CONTACT....
Ventsy Mihalov
1

votes
1

answer
72

Views

JOOQ generate with composite key with Spring Boot

I'm trying JOOQ to generate the database schema to use it with spring-boot. I'm using the following maven configuration: org.jooq.codegen.JavaGenerator org.jooq.meta.postgres.PostgresDatabase .* public org.jooq.codegen.maven.engletter target/generated-sources/jooq true true true true true org.jooq.c...
Reto Buchli
1

votes
1

answer
22

Views

Forcetype Expressions doesn't work for jooq version 3.10

I am using a custom converter and using the following expression in the forceType. But it looks the the expression doesnt work and the converter doesnt get executed. UPOrigin up_entries\.origin|up_methods\.origin Any help would be great.
chand
1

votes
1

answer
50

Views

jooq code generation mysql remote db maven

When I try to generate code against my remote DB, I keep getting some variation on the below: [ERROR] Failed to execute goal org.jooq:jooq-codegen-maven:3.11.5:generate (default) on project vaadinwebsite: Error running jOOQ code generation tool: Communications link failure [ERROR] [ERROR] The last...
user3329922
1

votes
1

answer
42

Views

jooq specify database runtime

I have the exact same database definition for multiple databases ( and database servers ). How do I tell Jooq to use the same database as the 'Connection' I created to connect to the DB? Example ( for MySQL ): jdbc:mysql://localhost:3306/tsm - my development DB ( tsm ), used to generate code jdbc:m...
user3329922
1

votes
1

answer
35

Views

Fetching records into mutable POJO Jooq

I went through the documentation here on fetching records into mutable pojo in Jooq. It says that columns are mapped to the best matching constructor, attributes or setters. Can anyone share more information what does best matching attributes or setter means? How does it map the column when I am not...
Anurag Sahu
1

votes
0

answer
39

Views

jOOQ plain SQL with bind

I want to use the plain sql for fetching a query, say: SELECT * FROM users WHERE fname = ... AND lName = ... Now I know I can do this.dsl.fetch(query, 'myFirstName', 'myLastName'); But I want to use binds so I can do final String query = 'SELECT * FROM users WHERE fName = :fname AND lName = :lname...
Kousha
1

votes
1

answer
46

Views

How to build a condition based on a composite data type with jOOQ?

In my view I have a column that refers to another table as its data type: CREATE VIEW my_view AS SELECT c.*, CAST(p.* AS parent) AS entity_parent FROM entity_child c LEFT JOIN parent p ON c.parent_id = p.id GROUP BY c.id, p.id; I need to query the view based on a value of the entity/parent value. Wi...
stewe
1

votes
1

answer
28

Views

JOOQ update set not populating values

In a Spring Boot application, I am using JOOQ to build SQL which is then being executed through JdbcTemplate. I am not using codegen or executing queries with JOOQ. The problem I have run into is with doing an Update. I'm using table() and field() as static imports from org.jooq.impl.DSL. The table...
Johnny Hamerstix
1

votes
1

answer
81

Views

Replacement for deprecated PostgresDataType.JSON?

I'm using JOOQ with PostgreSQL, and trying to implement a query like this: INSERT INTO dest_table (id,name,custom_data) SELECT key as id, nameproperty as name, CONCAT('{'propertyA': '',property_a,'', 'propertyB': '',property_b,'','propertyC': '',property_c,''}')::json as custom_data FROM source_tabl...
Steven Frew
1

votes
2

answer
33

Views

How to bind variables to conditional statement when using jOOQ to build SQL?

I'm using jOOQ-3.11.9 to build SQL. The following is my code: String sql = DSL.using(SQLDialect.MYSQL) .select(DSL.asterisk()) .from(table('service')) .where('name = ?', 'service1') .getSQL(); What I expect is select * from service where (name = 'service1') But the result is select * from service w...
ayaya
1

votes
0

answer
66

Views

Is there a way to decrease the memory needed for the Kotlin compiler?

I wrote a custom JOOQ generator to generate kotlin classes intead of Java classes. The problem is, the database that I am generating code for has more than 500 tables. I need to make some changes to this generator, so I will be compiling a lot of the code it produces for testing. Problem is: I keep...
LouizFC
1

votes
2

answer
35

Views

Is there a way to get dynamically get all table field names using jOOQ?

The codegen generated TableImpls contain methods like getIndexes, getPrimaryKey, getKeys. There is no method to get all table fields (TableField) like getFields. Is there another way to check programmatically if e.g. name is an actual column of a table?
Marcel Overdijk
1

votes
1

answer
89

Views

JOOQ How to convert JSON based on other column value?

Let's say I have a table customer(int id, type varchar, preferences jsonb). The type can be REGULAR, PREMIUM etc. Based on the column type value the preferences JSON structure will be different. While loading a customer record from database if type=REGULAR I want to convert it into RegularCustomerPr...
K. Siva Prasad Reddy
1

votes
0

answer
32

Views

Lambda jooq wrong date from mysql

In AWS Lambda I am using jOOQ to read data from a MySql database. In a simple select query as follow I am reading a column whose type is 'date'. I tried searching for the solution, but no luck. context.select(Transaction.TRANSACTION.TRANSACTION_DATE) .from(Transaction.TRANSACTION) .where(Transactio...
Pradipkumar Ahire
1

votes
0

answer
38

Views

Bulk/Batch update using PostgreSQL java

I am doing insertion/Updation into table using below command . insertResult = ((InsertReturningStep) ctx.insertInto(jOOQEntity, insertFields) .values(insertValue).onDuplicateKeyUpdate() .set(tableFieldMapping.duplicateInsertMap)).returning().fetch(); But using above command I am able to i...
user3040299
1

votes
1

answer
22

Views

How to write proper boolean comparison for PostgreSQL in JOOQ

I have a table t with boolean column b. db.select(T.B).from(T).where(T.B.isTrue()).fetch(); db.select(T.B).from(T).where(T.B.eq(Boolean.TRUE)).fetch(); both these statements are translated to same sql query: select 'public'.'T'.'B' from 'public'.'T' where 'public'.'T'.'B' = true; and this is not wor...
Edgars Jasmans
1

votes
1

answer
19

Views

jOOQ: best way to get aliased fields (from #as(alias, aliasFunction))

I have to access the same table for multiple references from a 'root' table. In order to do so, I'm creating aliases for these tables: protected final Table foo = Tables.XYZ.as('foo', ); protected final Table bar = Tables.XYZ.as('bar', ); bar-alias-function would be declared as follows_ protected fi...
Chris
1

votes
1

answer
24

Views

jOOQ: reusing / copying queries

In order to avoid re-creating the same part of a dynamic query over and over again, I was going to build the main part once and then reuse this part in different parts of the application. Since building the query is somewhat involved (see question jOOQ: best way to get aliased fields (from #as(alias...
Chris

View additional questions