Questions tagged [jooq]
951 questions
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?
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...
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...
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...
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
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...
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...
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...
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...
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=...
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('...
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...
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...
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...
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...
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...
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...
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'...
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....
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...
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.
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...
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...
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...
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...
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...
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...
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...
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...
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...
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?
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...
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...
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...
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...
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...
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...