JPA nativeQuery 变量为null，存在bytea不存在：或者upper(bytea)不存在问题

最新推荐文章于 2022-12-07 19:13:20 发布

原创最新推荐文章于 2022-12-07 19:13:20 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jpa

本文讨论了在使用JPA进行数据库查询时遇到的空值问题，指出当参数为null时会导致查询异常。提出了解决方案，包括在Service层增强空值处理或采用Querydsl进行更灵活的查询构造，以确保程序的健壮性。通过示例展示了如何利用Querydsl避免因null值引发的错误，从而提高代码质量。

@Query(
		value = "select * from products where  "
			+ " case when :XXXXRegion is not null and :xxxxRegion!='' then upper(xxxx_region) =upper(:xxxxRegion) else 1=1 end "
			+ " and "
			+ " case when :xxxxModel is not null and :xxxxModel !='' then upper(xxxx_model) =upper(:xxxxModel) else 1=1 end "
			+ " and "
			+ " case when :systemPlatform is not null and :systemPlatform!=''  then upper(system_platform) =upper(:systemPlatform) else 1=1 end "
			+ " ORDER BY id ASC, effective_time desc", nativeQuery = true
	)
	fun findByRegionAndFastoneModelAndSystemPlatform(
		@Param("fastoneRegion")
		fastoneRegion: String?,
		@Param("fastoneModel") xxxxModel: String?,
		@Param("systemPlatform") systemPlatform: SystemPlatform?
	): List<Product>

如果代码是这样写的话，如果存在xxxxModel 传递进来的是null 就会直接报错，jpa的nativeQuery对于null值处理不了

可以service加强对null值的处理

或者

使用QUERYDSL 来做查询

	fun findVmByFastoneRegionAndSystemPlatformAndFastoneModelAndVendor(
		region: Region?,
		platform: SystemPlatform?,
		fastoneModel: String?,
		vendor: Vendor?
	): List<Product> {
		val product = QProduct.product
		val productTypeExpression = product.productType.eq(ProductType.VM)
		val regionExpression = region?.let { product.fastoneRegion.eq(region) }
		val fastoneModelExpression = fastoneModel?.let { product.fastoneModel.eq(fastoneModel) }
		val platformExpression = platform?.let { product.systemPlatform.eq(platform) }
		val vendorExpression = vendor?.let { product.vendor.eq(Vendor.AWS) }
		val buildExpression =
			productTypeExpression.and(regionExpression).and(fastoneModelExpression).and(platformExpression)
				.and(vendorExpression)
		return repository.findAll(buildExpression, product.id.asc(), product.effectiveTime.desc()).toList()
	}