Associate values of a dropdown with a specific column in the DB for query

0

I have a filter on my site that is in the image below:

Therealpurposeofthisisto"search" travel destinations (eg Paris, Barcelona, Bahia etc ...) and display the same.

I have the following table structure represented by bean's below (with comments in both classes):

Destination.java

@Entity
@Table(name="destination")
public class Destination implements Serializable {
    private static final long serialVersionUID = 1L;

    //Resto dos atributos omitidos para facilitar o etendimento

    //Cada atributo abaixo equivale a uma tabela, na qual possui os atributos no qual será baseado o filtro
    @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name="fk_economic")
    private EconomicProfile economicProfiles;

    @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name="fk_general")
    private GeneralProfile generalProfiles;

    @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name="fk_social")
    private SocialProfile socialProfiles;

    @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name="fk_trip")
    private TripProfile tripProfiles;

    @OneToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name="fk_weather")
    private WeatherProfile weatherprofile;
    //fim dos filtros

    //Getter and Setter
}

Below is an example of a target-related table:

@Entity
@Table(name="economic_profile")
public class EconomicProfile implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_economic")
    private Long id;


    //Os atributos abaixo correspondem ao filtro de "Procuro por..." na imagem em anexo.
    @Column(name="economic_travel")
    private Boolean economic;

    @Column(name="intermediate_travel")
    private Boolean intermediate;

    @Column(name="luxury_travel")
    private Boolean luxury;

    //Getter and setter

}

Well, I chose to do this, because in each filter at the time of registering the destination the user can choose more than one to register.

Okay, now that the time comes that hit me doubts. How to correctly pass parameters to my action? So how can I bind by matching the selected value in each dropdown with the corresponding column in my profile?

To be clearer, I'll give you an example below:

<f:form id="form-filter-perfect-travel" modelAttribute="destination" action="${pageContext.request.contextPath}/perfect-travel-filter" method="get" class="form-filtro">

    <div class="box-select">
        <label>PROCURO POR</label>
        <f:select class="select01" path="economicProfiles">
            <option value="">Selecionar...</option>
            <option value="economic">Viagem Económica</option>
            <option value="intermediate">Viagem Intermediária</option>
            <option value="luxury">Viagem de Luxo</option>
        </f:select>
    </div>
</f:form>

GET request:

... / viatge / perfect-travel-filter? economicProfiles = intermediate

I think it's clearer now. The question is how do I get the value selected to pass as a parameter in a query query that would return my targets), like query below example:

select 
    d.id_destination
    d.tenant_id,
    d.appear_website,
    d...
from
    destination d
        inner join
    economic_profile ON id_destination = id_economic
where
    (luxury_travel = ? || economic_travel = ? || intermediate_travel = ?);
    
asked by anonymous 10.06.2015 / 02:48

1 answer

0

I ended up finding a solution (not very elegant), but it ended up serving exactly for my purpose.

In my tag's select HTML I passed the name of each attribute equal to the bean in the value , as I'll show below:

       <select id="social-select"
            class="select01" name="social">
            <option value="">Selecionar...</option>
            <option value="accompanying">Acompanhante</option>
            <option value="alone">Sozinho</option>
            <option value="children">Crianças</option>
            <option value="friends">Amigos</option>
            <option value="elderly">Idosos</option>
            <option value="familyChildren">Sem Crianças</option>
            <option value="teenager">Jovem</option>
        </select>

Then it makes a call via AJAX (this was adopted because we want to display only one page fragment, without "updating" it whole). The controller responsible for displaying the result makes a call to my service layer where it does a Query via JPQL

@Autowired
private CurrentTenantResolver<Long> tenantResolver;

@Autowired
private DestinationRepository destinationRepository;

@PersistenceContext
private EntityManager entityManager;

@SuppressWarnings("unchecked")
public List<Destination> filterDestinations(String economic, String general, String social, String weather, String trip){
    Long currentTenantId = tenantResolver.getCurrentTenantId();     
    String query = "SELECT d FROM Destination d INNER JOIN EconomicProfile e ON d.idDestination = e.id INNER JOIN GeneralProfile g ON d.idDestination = g.id INNER JOIN SocialProfile s ON d.idDestination = s.id INNER JOIN WeatherProfile w ON d.idDestination = w.id INNER JOIN TripProfile t ON d.idDestination = t.id WHERE (e."+economic+" = ?1 and g."+general+" = ?2 and s."+social+" = ?3 and w."+weather+" = ?4 and t."+trip+" = ?5)";        
    entityManager.setProperty(PersistenceUnitProperties.MULTITENANT_PROPERTY_DEFAULT, currentTenantId);
    return entityManager.createQuery(query).setParameter(1, true).setParameter(2, true).setParameter(3, true).setParameter(4, true).setParameter(5, true).getResultList();
}

I use Spring Data , but I currently adopt the SaaS template in my application, so for technical reasons I would have to set my tenant current after starting the transaction I had to choose to perform the query via entityManager . That is, each option of my tag select matches the column where it should be searched for by the parameter "true" (1).

That way I was successful.

    
16.06.2015 / 20:38