Use tuple queries for projections using QueryDSL and Query by Example

[kekhuai]

I'm implementing a dynamic query with following code.

userRepository.findBy(QUser.user.id.isNotNull(), q -> q.project("username").as(UserWithUsernameOnly.class).all());

But the generated query still select all of the columns.

select user0_.id as id1_0_, user0_.create_date as create_d2_0_, user0_.created_by as created_3_0_, user0_.last_modified_by as last_mod4_0_, user0_.last_modified_date as last_mod5_0_, user0_.password as password6_0_, user0_.username as username7_0_, user0_.version as version8_0_ from users user0_ where user0_.id is not null

I expect the generated query should only select the specified column.

Here is the reproducible repo.
https://github.com/kekhuay/special-palm-tree

[gregturn]

I have certainly reproduced this issue. To be clear, the query works. It simply doesn't slim things down to the subset of columns expected.

I tested against both H2 as well as Postgres with the following test case:

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = FetchableFluentQueryByPredicateIntegrationTests.Config.class)
@Transactional
public class FetchableFluentQueryByPredicateIntegrationTests {

	@Autowired FetchableFluentRepository repository;

	@BeforeEach
	void setUp() {
		repository.saveAndFlush(new User("Bilbo", "Baggins", "riddles@tolkien.com"));
	}

	@Test
	void projectionsOnDtoClassesShouldHaveAReducedProjectionInTheQuery() {

		List<UserDto> users = repository.findBy(QUser.user.firstname.eq("Bilbo"), p -> p //
				.project("firstname") //
				.as(UserDto.class) //
				.all());

		assertThat(users).extracting(UserDto::getFirstname).containsExactly("Bilbo");
	}

	@Test
	void projectionsOnEntitiesShouldHaveAReducedProjectionInTheQuery() {

		List<User> users = repository.findBy(QUser.user.firstname.eq("Bilbo"), p -> p //
				.project("firstname") //
				.all());

		assertThat(users).extracting(User::getFirstname).containsExactly("Bilbo");
	}

	public interface FetchableFluentRepository extends JpaRepository<User, Long>, QuerydslPredicateExecutor<User> {

	}

	public interface UserDto {

		String getFirstname();
	}

	@EnableJpaRepositories(considerNestedRepositories = true, basePackageClasses = FetchableFluentRepository.class, //
			includeFilters = @ComponentScan.Filter(value = { FetchableFluentRepository.class },
					type = FilterType.ASSIGNABLE_TYPE))
	@EnableTransactionManagement
	static class Config {

		@Bean(initMethod = "start", destroyMethod = "stop")
		public PostgreSQLContainer<?> container() {

			return new PostgreSQLContainer<>("postgres:9.6.12") //
					.withUsername("postgres");
		}

		@Bean
		public DataSource dataSource(PostgreSQLContainer<?> container) {

			PGSimpleDataSource dataSource = new PGSimpleDataSource();
			dataSource.setUrl(container.getJdbcUrl());
			dataSource.setUser(container.getUsername());
			dataSource.setPassword(container.getPassword());
			return dataSource;
		}

		@Bean
		public AbstractEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {

			LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
			factoryBean.setDataSource(dataSource);
			factoryBean.setPersistenceUnitRootLocation("simple-persistence");
			factoryBean.setPersistenceUnitName("spring-data-jpa");
			factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

			Properties properties = new Properties();
			properties.setProperty("hibernate.hbm2ddl.auto", "create");
			properties.setProperty("hibernate.dialect", PostgreSQL91Dialect.class.getCanonicalName());
			factoryBean.setJpaProperties(properties);

			return factoryBean;
		}

		@Bean
		PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
			return new JpaTransactionManager(entityManagerFactory);
		}
	}
}

In both scenarios, first on H2 (before adding the Testcontainers chunk now shown) as well as on Postgres (see above), I saw this query:

2023-03-28 09:05:55,297 DEBUG                        org.hibernate.SQL: 128 - select u1_0.id,u1_0.DTYPE,u1_0.active,u1_0.city,u1_0.country,u1_0.streetName,u1_0.streetNo,u1_0.age,u1_0.binaryData,u1_0.createdAt,u1_0.dateOfBirth,u1_0.emailAddress,u1_0.firstname,u1_0.lastname,u1_0.manager_id from SD_User u1_0 where u1_0.firstname=?
2023-03-28 09:05:55,297 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [1] as [VARCHAR] - [Bilbo]

This while seeing the hint applied (by design) of:

All that being said, this is a JPA hint, which means it's not a hard requirement that the persistence provider support this hint. I'm trying to dig in and see if we're doing the hint wrong, or if in reality, these persistence providers simply don't honor it.

[gregturn]

I also flipped to MySQLContainer, and got the same results.

2023-03-28 09:19:12,231 DEBUG                        org.hibernate.SQL: 128 - select u1_0.id,u1_0.DTYPE,u1_0.active,u1_0.city,u1_0.country,u1_0.streetName,u1_0.streetNo,u1_0.age,u1_0.binaryData,u1_0.createdAt,u1_0.dateOfBirth,u1_0.emailAddress,u1_0.firstname,u1_0.lastname,u1_0.manager_id from SD_User u1_0 where u1_0.firstname=?
2023-03-28 09:19:12,232 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [1] as [VARCHAR] - [Bilbo]

[mp911de]

Query by Example uses specifications that select the Root. For projections we need to resort to tuple queries, including a proper selection:

CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery()

query.select(criteriaBuilder.tuple(employee.get(Employee_.name), employee.get(Employee_.salary)));

Related: #487

[mp911de]

Tuple-queries are now in place via #2327. Closing this ticket as superseded. Anyone interested in that feature is welcome to test Spring Data JPA 3.5 snapshots and report their experience here or in a new ticket.