CODEWITHSUNDEEP
quarkusquarkus-panache
marceloplis
marceloplis

Reputation: 11

How I create a native query with multi tables on Quarkus

I'd liked migrate a native query in springboot to Quarkus, bellow is the repository method:

public MyObject findObject(Long idObject) {
    String query = "SELECT document \n" + 
            "         FROM table_a           cusco,\n" + 
            "              table_b           cust,\n" + 
            "              table_c           accnt,\n" + 
            "              table_d           prdacc,\n" + 
            "              table_e           ctprd_plan,\n" + 
            "              table_f           ctsth_plan,\n" + 
            "              table_g           cntst_plan,\n" + 
            "              table_h           prdct_plan\n" +                
            "        WHERE 1 = 1\n" + 
            "          AND cust.id = cusco.fk_cust_id\n" + 
            ...              
            "          AND cusco.id= ? ";
    
    List<MyObject> list = jdbcTemplate.query(query, new Object[]{idObject}, new MyObjectRowMapper());
    
    if(list != null && !list.isEmpty()){
        return list.get(0);
    }

    return null;
}

Is possible implements this method on Quarkus?

tks!

Upvotes: 1

Views: 6695

Answers (3)

Reshma Kachhadiya
Reshma Kachhadiya

Reputation: 59

List<?> data = entityManager.createNativeQuery(
                "SELECT j.dpi_name,j.type,j.display_name from (select p.*,d.provider_id from dpi_provider_api_master d left join dpi_api_response_mapping b on d.id=b.api_id left join dpi_properties_master p on p.id=b.dpi_property_id where d.dataset_id=:dataset and d.record_status='A' and b.record_status='A' and p.record_status='A' and p.is_ignored='N' GROUP BY p.id HAVING COUNT(p.dpi_name) = 1) j where j.provider_id<>:provider")
                .setParameter("dataset", dataset).setParameter("provider", provider).getResultList();

        for (Object resultObj : data) {
            Object[] result = (Object[]) resultObj;
            propertiesList.add(new NotSupportedPropertiesData(result[0].toString(), result[1].toString(),
                    result[2].toString()));
        }

here get data fro multiple table with set of parameters.

Upvotes: 0

RED-ONE
RED-ONE

Reputation: 435

The answer from @nbpRK is correct, I just put a detalled example and added the class of the resulting instance

 @ApplicationScoped
 public class MyRepository implements PanacheRepository<MyObject> {

 private final EntityManager entityManager;

 public MyRepository(EntityManager entityManager) {
    this.entityManager = entityManager;
 }

  public List<MyObject> nativeQuery(String value) {
    return entityManager.createNativeQuery("SELECT * FROM MyTable WHERE name = 
  :param", MyObject.class).setParameter("param", value).getResultList();
   }
  }

you can call

@Inject
MyRepository myRepository;

....
List<MyObject> data = myRepository.nativeQuery("test");

Upvotes: 2

nbpRK
nbpRK

Reputation: 21

import javax.persistence.Query;

Query query = em.createNativeQuery("SELECT * FROM table WHERE id = :num");
query.setParameter("num", 1);
List data = query.getResultList();

You can try this

Upvotes: 2

Related Questions