WHERE conditional queries without using string-CodePudding

I have to return the State corresponding to its City in the address_table sent through a POST request. I used a WHERE clause in the query, but it requires a string. What should I do if I want it to contain the city returned from a method in AddressManager?

This code is working if the City sent as a post request is what I put in WHERE a.city = ''

Repository

@Repository
public interface AddressRepository extends JpaRepository<Address, Long>
{       
    @Query("SELECT a.state FROM address_table a WHERE a.city = '' ") 
    String getState();
    // the blank city should be the city from user.setCity (ua.getCity())
}

Manager

@Component 
public class AddressManager
{
    @Autowired
    private AddressRepository aRepo;

    @Autowired
    private UserRepository uRepo    

    public void save(UserAddressDto ua)
    {               
        User user = new User();
            
        user.setCity (ua.getCity());
        user.setState(aRepo.getState());
                    
        uRepo.save(user);
    }
}

CodePudding user response：

Using JPQL query with a named parameter would look like this taking into account that several states may have namesake cities:

@Repository
public interface AddressRepository extends JpaRepository<Address, Long>
{       
    @Query("SELECT a.state FROM Address a WHERE a.city = :city ")
    List<String> getState(@Param("city") String city);
}

As for the condition, this may be implemented using CASE ... END a similar way:

    @Query("SELECT a.state FROM Address a WHERE a.city = (CASE :city = '' THEN :userCity ELSE :city END) ") 
    List<String> getState(
        @Param("city") String city,
        @Param("userCity") String userCity
    );

CodePudding user response：

If city is a String then:

AddressRepository

@Query("SELECT a.state FROM address_table a WHERE a.city = :city ", nativeQuery = true) 
String getState(@Param("city") String city);

AddressManager

user.setCity (ua.getCity());
user.setState(aRepo.getState(ua.getCity()));