0

I have this huge denormalized "product" table in Oracle with hundreds of columns. There are several products. Not all the columns apply for every product. Those columns that does not apply to a particular row/product have value as NULL in the table. I need to create a mapping between the product and column names. I am using Jdbc template and spring boot. I am building a microservice. If the user gives a specific product as a query parameter, the columns that pertains to the specific product only should be returned.

Any ideas on how I should go about doing this?

Thanks for your time.

Namagiri Sridhar
  • 177
  • 1
  • 6
  • 18

1 Answers1

1

The short answer is not possible with jdbctemplate unless you manually create a query appending the column names.

If you are using Hibernate/JPA then you can use the criteria query assuming you have multiple separate POJOS one for each specific product type and a POJO for the denormalized type.

CriteriaQuery<T> cq = builder.createQuery(SpecificProduct.class);
// write the Root, Path elements as usual
Root<EntityClazz> root = cq.from(EntityClazz.class);
cq.multiselect(root.get(EntityClazz_.COL1), root.get(EntityClazz_.COL2));  //using metamodel
List<T> result = em.createQuery(cq).getResultList()

See a similar answer here

asolanki
  • 1,333
  • 11
  • 18