skip to Main Content

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

2

Answers


  1. Use CAST:

    CAST('1 minutes' AS interval)
    

    instead of

    '1 minutes'::interval
    
    Login or Signup to reply.
  2. remove :: . also postgres uses $n placeholders for parameters, not ?n like some other SQL databases

    try this:

    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 * interval '1 minute')
                            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;
            """;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search