When i try to execute native query in my Spring JPA i give an error in my code ?
import javax.management.Query;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
import java.sql.SQLException;
import java.time.Instant;
@Slf4j
@Repository
@AllArgsConstructor
public class CarDAOImpl {
private DataSource dataSource;
private JdbcTemplate postgresTemplate;
private EntityManager em;
public List<CalculatedCar> getCalculatedCarsBy(String minutes) throws SQLException {
String sql = "SELECT tmi.p_id AS id,"
+ "tmi.p_park_id AS parkId, "
+ "tmi.p_gosnumber AS gosnumber,"
+ "tmi.p_park_name AS parkName,"
+ "taotr.p_lon AS lon,"
+ "taotr.p_lat AS lat,"
+ "p_azimuth AS vector,"
+ "p_speed AS speed,"
+ "tmi.p_mark_name AS markName,"
+ "tmi.p_mark_id AS markId,"
+ "tmi.p_transport_type AS TT_Title,"
+ "mt.p_group_id AS groupTransportType,"
+ "mt.p_title AS titleTransportType,"
+ "org.p_title AS organization,"
+ "taotr.p_time AS time,"
+ "p_kilometers AS kilometers,"
+ "coalesce(substring(tum.p_name from 0 for position(' км' in tum.p_name)), '') AS road_name,"
+ "coalesce(tmi.p_navigator, '-') AS stationNum,"
+ "tmi.p_machine_type "
+ "FROM t_machine_info AS tmi "
+ "LEFT JOIN t_machine_type AS mt on tmi.p_machine_type = mt.p_id "
+ "LEFT JOIN t_organization AS org on tmi.p_organization = org.p_id, "
+ "(SELECT * FROM t_auto_on_the_roads "
+ "WHERE p_id in (SELECT max(p_id) FROM t_auto_on_the_roads "
+ "WHERE p_time >= (current_timestamp AT TIME ZONE 'UTC') - (?1 * '1 minutes'::interval) "
+ "GROUP BY p_id_auto)) AS taotr "
+ "LEFT JOIN t_unit_members AS tum ON tum.p_id = taotr.p_name_road "
+ "WHERE taotr.p_id_auto = tmi.p_id;";
List<Object[]> resultList = em.createNativeQuery(sql)
.setParameter(1, minutes)
.getResultList();
if(!resultList.isEmpty()) {
System.out.println("[CarDAOIMPL getCalculatedCarsBy] RESULT PARAMETERS ");
Object[] rs = resultList.get(0);
for(int i = 0; i < rs.length; i++) {
System.out.println(rs[i]);
}
} else {
System.out.println("[CarDAOIMPL getCalculatedCarsBy] RESULT PARAMETERS EMPTY");
}
List<CalculatedCar> calculatedCars = new ArrayList<>(resultList.size());
resultList.forEach(result -> {
Integer parkId = (Integer)result[0];
String gosNumber = (String) result[1];
String parkName = (String) result[2];
Double lon = (Double) result[3];
Double lat = (Double) result[4];
Integer vector = (Integer) result[5];
Integer speed = (Integer) result[6];
String markName = (String) result[7];
Integer markId = (Integer) result[8];
String title = (String) result[9];
String groupTransportType = (String) result[10];
String titleTransportType = (String) result[11];
String organization = (String) result[12];
Instant time = (Instant) result[13];
Integer kilometers = (Integer) result[14];
String roadName = (String) result[15];
Integer stationNum = (Integer) result[16];
CalculatedCar car = CalculatedCar
.builder()
.parkId(parkId)
.gosnumber(gosNumber)
.parkName(parkName)
.lon(lon)
.lat(lat)
.vector(vector)
.speed(speed)
.markName(markName)
.markId(markId)
.TTTitle(title)
.groupTransportType(groupTransportType)
.titleTransportType(titleTransportType)
.organization(organization)
.time(time)
.kilometers(kilometers)
.roadName(roadName)
.stationNum(stationNum)
.build();
calculatedCars.add(car);
});
return calculatedCars;
}
}
I try to use escape characters like '' or use round brackets in SQL, i think java String and SQL query is interpreted differently For example
`WHERE p_time >= ((current_timestamp AT TIME ZONE 'UTC') - (?1 * '1 minutes'::interval))`
or like this
`WHERE p_time >= (current_timestamp AT TIME ZONE 'UTC') - (?1 * ''1 minutes''::interval)`
because i have an error in this place Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ":" Position: 1936
Use CAST:
CAST('1 minutes' AS interval)
instead of
'1 minutes'::interval