I am currently working on a Spring Boot based micro service application which uses JPA for DB persistence.The Database used is PostgreSQL.In order to get the last value of the sequence hotel_id_seq the below method was used.
The problem rose with a new change request which required the service to be deployed in different environments in which case that portal customer name would make up the schemaname and would have to be appended (in place of "schemaName" on each instance. As I am using a native query is there a way to accomodate the dynamic customername in to the query? Could this query be rewritten in JPQL to circumvent this situation?
These were some of the related questions that I found on Stackoverflow but they did not offer a specific answer to my question.
Getting next value from sequence with jpa repository in postgreSQL
How to retrieve the current value of an oracle sequence without increment it?
Model class:
package travel.travelos.hotel.setup.entity.local;
import java.util.HashSet;
import java.util.Set;
@Entity
@Table(name = "hotel")
public class Hotel implements java.io.Serializable {
private long id;
//Ommited for Clarity
public Hotel() {
}
public Hotel(Long hotelId) {
this.id = hotelId;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false)
public long getId() {
return this.id;
}
public void setId(long id) {
this.id = id;
}
//Ommited for Clarity
}
Jpa Repository:
package travel.travelos.hotel.setup.main.repository;
import java.util.List;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import rezg.rezos.hotel.setup.entity.local.Hotel;
public interface HotelRepository extends PagingAndSortingRepository<Hotel, Long> {
//Note:SchemaName would vary accordnig to portal customers name
@Query(nativeQuery = true, value = "SELECT last_value FROM schemaName_hotel_setup.hotel_id_seq")
public Long getLastSequence();
}
application.yml
//Ommited for Clarity
spring.jpa.database-platform: org.hibernate.dialect.PostgreSQLDialect
spring.jpa.show-sql: true
spring.jpa.hibernate.ddl-auto: none
spring.jpa.properties.hibernate.default_schema: schemaName_hotel_setup #SchemaName name appended
#spring.jpa.properties.hibernate.globally_quoted_identifiers: true
portal:
name: schemaname #new portals name would represent schema name
data_consistancy_service_url: http://172.16.16.207:8080/data-consistancy-service/
data_notify_max_flag_count: 10
data_notify_limit_count: 100
data_notify_schedule_pattern: 0 0/5 * * * *
Update:Alternate fix
I managed to make some amendments to the the way the query is executed and dynamically appended the portal name thereby handling this scenario in the HotelServiceImpl level rather than in the HotelRepository as before.Though this work around method works fine, the implementation effects the separation of concerns. How would this solution be improved further? I would appreciate any help.
package rezg.rezos.hotel.setup.service;
@Component
@Transactional
@PropertySource("classpath:application.yml")
class HotelServiceImpl implements HotelStandardInfoService{
private HotelRepository hotelRepository;
@Autowired
private Messages messages;
@Autowired
private HttpSession session;
@Value("${portal.name}")
private String portalName;
@Autowired
private EntityManager entityManager;
//Ommited for Clarity
@Override
public String generateHotelCode() throws Exception {
System.out.println("portalName : "+ portalName);
Query q = entityManager.createNativeQuery("SELECT last_value FROM "+portalName +"_"+"hotel_setup.hotel_id_seq");
Long nextHotelId=((BigInteger)q.getSingleResult()).longValue();
System.out.println("nextHotelId : "+ nextHotelId);
/*Long nextHotelId = hotelRepository.getLastSequence();*/
;
Useful reference
