FAQ Database Discussion Community


QueryDSL Left Join with additional conditions in ON

java,left-join,querydsl
Is it possible to do the following query in QueryDSL? SELECT p.* FROM parts_table p LEFT JOIN inventory_balance_table i ON (p.part_no = i.part_no AND i.month = MONTH(CURRENT_DATE) AND i.year = YEAR(CURRENT_DATE)); Inventory balance stores inventory data for every part number/month/year; I need the only the data for the current year...

QueryDSL DateTime field formatted projection

java,sql-server,querydsl
I trying to do a query that returns the date and time part of a Datetime field using QueryDSL. The query in plain SQL looks like this: select convert(varchar, FELectura, 103) as sDate, convert(varchar, FELectura, 108) as sTime from dbo.T2_LecturaEstacionClima; Where FELectura is a DateTime field and the Database is...

QueryDSL Spring JPA Hibernate IllegalArgumentException Parameter value not matching type

hibernate,maven,querydsl,spring-jpa
I'm trying to configure QueryDSL + Spring JPA + Hibernate. The QueryDSL 'Q' query classes for my domain objects get generated using Maven POM configurations. I have a 'Person' object which has a String attribute 'username'. I want a person returned with username = "jon". When I run a simple...

Floating point types returned in ORM / DSL

java,oracle,hibernate,floating-point,querydsl
The Java™ Tutorials state that "this data type [double] should never be used for precise values, such as currency." Is the fact that an ORM / DSL is returning floating point numbers for database columns storing values to be used to calculate monetary amounts a problem? I'm using QueryDSL and...

Calling Postgres function using QueryDSL

postgresql,querydsl
I have a package with similarity functions installed on my Postgres DB. I was calling them using pure SQL on a JDBC, and it was working as expected. Now I'm trying to refactor my code to use QueryDSL. The first step of the similarity function is set a limit, between...

Elastic query DSL: Wildcards in terms filter?

elasticsearch,querydsl,elastic
I am trying to filter the documents using terms filter. I am not sure how to introduce wildcards in filter. I tried something like this: "filter":{ "bool":{ "must":{ "terms":{ "wildcard" : { "aircraft":[ "a380*" ] } } } } } But I get SearchParseException with this. Is there no way...

Spring data: order by result of condition for each row

spring-data,spring-data-jpa,criteria-api,querydsl
In plain SQL is possible to calculate runtime variables, like IF/SUM for each row, and possible to use it for ordering. My question about possibility of doing things like that in QueryDSL/Criteria API Is it possible or not? I have entity called OrderSummary, with following fields: @Entity @Table(name = "orders")...

nonfatal user error in JPAQuery (QueryDSL)

jpa,querydsl
I have to add a search function using QueryDSL / JPAQuery. The search should use a table consisting of parent ids: ID LABEL P_ID 1 A null 2 AB 1 3 ABC 2 A simple SQL statement gives the right result: SELECT * FROM ACT JOIN ORG DEPT ON ACT.ORGID...

Mocking db queries with querydsl - issue with Optional

java,junit,mocking,guava,querydsl
I've some difficulties to write tests on DB queries for my application, which uses querydsl on top of a mongo. I've found several examples of people who just unit-test the query object itself, but I'd like to go a step further and test how the query performs (like an integration...

Join in unidirectional association

java,jpa,querydsl
Association between School and Student is unidirectional. How can I create query from Student with a join to School using JPA criteria or Querydsl? class School{ @OneToMany private List<Student> students; } class Student{ private String name } ...

@Convert doesn't work with Hibernate and QueryDSL

java,hibernate,querydsl
I have a table Incidencia with a CHAR (1 Byte) column visiblemovil. This column has 2 possible values: "S" or "N" (yes/no, si/no in Spanish) that I want convert to boolean using @Convert annotation. This is my code: Converter: @Converter public class SiNoToBooleanConverter implements AttributeConverter<Boolean, String> { @Override public String...

Contains Search on Calendar Attribute in Spring-Data and QueryDSL

date,filter,spring-data,querydsl
I would like to use my search field as well on my date attributes using QueryDSL and Spring-Data. For Example: When there are records with 04.01.2015 and I come in with 201, it should be match, con 2015 contains 201. Something like this should work: // DOES NOT COMPILE!!! private...

Spring data repository QueryDslPredicateExecutor without pageable count

spring,spring-data,spring-data-jpa,querydsl
I'm trying to create a spring data repository with a method to list a page of entities and a QueryDSL predicate using this: public interface EntityRespository extends JpaRepository<Entity, Integer>, QueryDslPredicateExecutor<Entity> { List<Entity> findAllBy(Predicate predicate, Pageable pageable); } As mentioned here Way to disable count query from PageRequest for getting total...

Order By calculated value on 1:n relation in QueryDsl with Spring Data

spring-data,querydsl
i am using Spring Data with QueryDSL. I have a JPQLQuery and want to order it by a calculated value. This calculated value is located in a 1:n relation. Let's say i have the following entities: Bill - with a list of pizzas Pizza - with an attribute prize and...

What is the difference in use static import and create new q-type instance using QueryDSL?

java,querydsl
I saw in production code line like this: public Info findById(final long id){ QInfo info = QInfo.info; JPAQuery query = newJpaQuery().from(info); ..... } and on the other: import static com.example.system.app.entities.QApplication.application; public Application findById(final long id){ return newJpaQuery().from(application).where(application.applicationId.eq(id)).uniqueResult(application); } what is the difference in use of using static import and create...

Filter with Spring-Data and QueryDSL on nullable reference attribute

filter,datatable,spring-data,querydsl
I have following issue. I am using Jquery Datatable serverside and I am now implementing the search box. But I have an issue there in special case, when a dataset has an attribute, what is "null". So the dataset will not be found although it should found cos it matches...

QueryDSL - auto trimming of DB2 varchar fields

querydsl
I have a Mysema QueryDSL project to maintain... Columns in DB2 and also SQL Server are varchars, the result is that the fields have the length of the columns and not the data. My Java code is: QTransferTableLeveranciers leveranciers = QTransferTableLeveranciers.transferTableLeveranciers; JPAQuery query = new JPAQuery(entityManager).from(leverancier); return query.list(leveranciers); How to...

Querydsl: a field is not properly initialised on the child class

java,querydsl
Good day, We are using qdsl to access our inherited objects. We have a parent class (Parent) which has a complicated object (BasicProduct) as a field. Then we have several children (one of them is Child). In the query we are trying to access the BasicProduct from the QChild autogenerated...

Reactive Mongo Extensions: Pass List Of Values In $in Query using `Query DSL`

mongodb,scala,playframework-2.3,querydsl,reactivemongo
I am trying to pass multiples value in $in query using Query DSL with Reactive Mongo Extensions. But the result is empty list. Follwoing is my Code: def findUsersByRolesIds(rolesIds: List[BSONObjectID], page: Int, pageSize: Int): Future[List[User]] = { logger.info("findUsersByRolesIds Reactive Repository Method"); userGenericRepo.find($doc("userRoles._id" $in (rolesIds)), $doc("createdOn" -> -1 ), page, pageSize);...

JPA QueryDSL: count() leads to ORA-01722: invalid number

oracle,jpa,querydsl
I have the following Query, which tries to find the count of all RUNNING Trackings: new JPAQuery(getEntityManager()).from(myTracking) .where(myTracking.trackingStatus.isNotNull(), myTracking.trackingStatus.eq(TrackingStatus.RUNNING)) .count() This leads to ORA-01722: invalid number. Why? EDIT: Logging of the SQL statement: /* select count(myTracking) from MyTracking myTracking where myTracking.trackingStatus is not null and myTracking.trackingStatus = ?1 */ select...

How to write search queries in kibana using Query DSL for Elasticsearch aggregation

elasticsearch,querydsl,kibana-4
I am working on ELK stack to process Apache access logs. Spent a lot of time understanding Query DSL format so that more complex queries can be written. Currently am facing issues with running the queries in kibana interface but the same queries work just fine when posted using curl...

QueryDSL difference between explicit join and where clause?

java,sql,jpa,jpql,querydsl
Say you have a QueryDSL query as follows: JPQLQuery query = new JPAQuery(em); QUser user = QUser.user; QLocation location= QLocation.location; query.from(User).innerJoin(user.location, location).where(user.name.eq("Giuseppe").and(location.name.eq("Vatican City"))); How is that different (functionally) from the following? JPQLQuery query = new JPAQuery(em); QUser user = QUser.user; query.from(User).where(user.name.eq("Giuseppe").and(user.location.name.eq("Vatican City"))); It seems to me that both are able...

QueryDSL Unexpected Token , with any() and in clause

java,hibernate,querydsl
I have two models, Location and LocationAttribute, with a Many to Many relationship. I have a list of LocationAttribute IDs, and would like to find all Locations that have at least one of those attributes. Location.java: @Entity public class Location { @Id @GeneratedValue @Column(name="LOCATION_ID") protected int id; @ManyToMany(targetEntity = LocationAttribute.class)...

Parse SQL to sql builder

java,sql,postgresql,querydsl,jooq
Is there easy way to convert SQL string like: SELECT u.name, u.role FROM users WHERE user_id = ? to any of SQL builders like JOOQ, QueryDsl just to get ability to modify query — add joins, additional "where" clauses, LIMIT-OFFSET?...

QueryDsl - filtering Map based on key

java,sql,hibernate,dictionary,querydsl
I have folowing property in my hibenrate entity class: @MapKeyJoinColumn(name = "language_code") @LazyCollection(LazyCollectionOption.EXTRA) @ElementCollection(fetch = FetchType.LAZY) @CollectionTable(name = "text_translations", joinColumns = @JoinColumn(name = "text_id")) private Map<Language, String> translations = new HashMap<>(); Now I want to query this entity and filter content of map by user's language (i.e. by map's key)....

Elastic DSL: Aggregate and order by keys as numeric

elasticsearch,querydsl,elastic
In my dataset, where duration field is string, I am trying to find aggregation count on duration in the way below: "aggs": { "duration": { "terms": { "script": "Math.ceil(doc[\"duration\"].value as double)", "order" : { "_term" : "asc"} } } } This returns result as : "buckets" : [ { "key"...

Dynamic spring data jpa repository query with arbitrary AND clauses

java,spring,spring-data,spring-data-jpa,querydsl
I'm using Spring data jpa repositories, Got a requirement to give search feature with different fields. Entering fields before search is optional.I have 5 fields say EmployeeNumber, Name, Married,Profession and DateOfBirth. Here i need to query only with the given values by user and other fields should be ignored.Ex, Input...

Elasticsearch DSL : Multiple aggregations

elasticsearch,querydsl,elastic
Shown below is the structure of type of my data. ArrivalAirport | DepartureAirport | ArrivalDelay | DepartureDelay A | B | 2 | 5 B | C | 3 | 6 C | A | 4 | 7 A | B | 5 | 8 I want to perform together...

Trying to setup Querydsl 3.6.0 with MongoDB, but MorphiaAnnotationProcessor is not found

java,mongodb,maven,querydsl
I have a small example project for mongodb and can't get the annotation processor to work. I am using the examples from: http://www.querydsl.com/static/querydsl/latest/reference/html/ch02s07.html Running mvn clean install produces: Annotation processor 'com.querydsl.mongodb.morphia.MorphiaAnnotationProcessor' not found I have been all over the place looking for that processor. Just can't find it. Here is...

Querydsl: how to make left join by column

jquery,sql,querydsl
Im trying to match this SQL query in querydsl SELECT tr.* FROM test.TRIP_REQ tr left outer join test.ADDR_BOOK ab on tr.REQ_USERID=ab.USER_ID I know how to make left join query if you join into identity column but struggle to make it work with joining on 2 alternative columns. tr.REQ_USERID and ab.USER_ID...

OrderBy + sum() + BooleanExpression

java,querydsl
I've got two entities: Category and Product with one-to-many relation. How can I order categories by number of their products, with price greater than 100? Something like (this doesn't work): query.from(category).leftJoin(category.products, product).orderBy(product.price.gt(100).count().desc()).list(category) ...