[FIXED] Java Spring JDBC

Issue

I'm new to java and spring, I know that jdbc is outdated, but I would like to master this technology, why when I generate a request using a ResultSet, I get a problem and I cannot understand what is happening at all

Photo

although if you repeat the same request through the console, then everything works fine

Photo

Inquiries:

private final static String CREATE_CAR_QUERY = "insert into cars values(default,?,?,?,?,?,?,?,?,?)";

private final static String CREATE_DRIVER_CAR_QUERY = "insert into driver_car values(?,?)";

private final static String FIND_CAR_ID_BY_CAR_NUMBER_QUERY = "select id from cars where car_number = ";

function:

> public void create(Car entity, Long driverId) {
>         Long carId = 0L;
>         try (PreparedStatement preparedStatement = jpaConfig.getConnection().prepareStatement(CREATE_CAR_QUERY)) {
>             preparedStatement.setTimestamp(1, new Timestamp(entity.getCreated().getTime()));
>             preparedStatement.setTimestamp(2, new Timestamp(entity.getUpdated().getTime()));
>             preparedStatement.setBoolean(3, entity.getVisible());
>             preparedStatement.setString(4, entity.getCarName());
>             preparedStatement.setString(5, entity.getImageUrl());
>             preparedStatement.setString(6, entity.getColor());
>             preparedStatement.setInt(7, entity.getYearsOfIssue());
>             preparedStatement.setDouble(8, entity.getEngineCapacity());
>             preparedStatement.setString(9, entity.getCarNumber());
>             preparedStatement.execute();
>         } catch (SQLException e) {
>             e.printStackTrace();
>         }
> 
> 
>         try (ResultSet resultSet = jpaConfig.getStatement().executeQuery(FIND_CAR_ID_BY_CAR_NUMBER_QUERY
> + entity.getCarNumber())){
>             carId = resultSet.getLong("id");
>         } catch (SQLException e) {
>             System.out.println("problem: = " + e.getMessage());
>         }
> 
> 
>         try (PreparedStatement preparedStatement = jpaConfig.getConnection().prepareStatement(CREATE_DRIVER_CAR_QUERY)) {
>             preparedStatement.setLong(1, driverId);
>             preparedStatement.setLong(2, carId);
>             preparedStatement.execute();
>         } catch (SQLException e) {
>             e.printStackTrace();
>         }
>     }

The entity that arrives:

Photo

Although almost the same function, it works with the same query, but in one search by car_number, and in the other by driver_id:

Inquiries: private final static String FIND_ALL_SIMPLE_CARS_BY_DRIVER_ID_QUERY = "select id, cars_name, color, years_of_issue, engine_of_capacity, car_number from cars left join driver_car ab on cars.id = ab.car_id where ab.driver_id = ";

    public Map<Long, String> findByDriverId(Long driverId) {
        Map<Long, String> map = new HashMap<>();
        try (ResultSet resultSet = jpaConfig.getStatement().executeQuery(FIND_ALL_SIMPLE_CARS_BY_DRIVER_ID_QUERY
+ driverId)) {
            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String carName = resultSet.getString("cars_name");
                map.put(id, carName);
            }
        } catch (SQLException e) {
            System.out.println("problem: = " + e.getMessage());
        }
        return map;
    }

Solution

As already commented by @DaveNewton you should use a PreparedStatement for the FIND_CAR_ID_BY_CAR_NUMBER_QUERY similarly to the way you’re already doing it for the insert statements. This ensures proper quoting and escaping of query values and thus makes your life a lot easier and your code less affected by sql injection attacks.

private final static String FIND_CAR_ID_BY_CAR_NUMBER_QUERY = "select id from cars where car_number = ?";

try (PreparedStatement preparedStatement = jpaConfig.getConnection().prepareStatement(FIND_CAR_ID_BY_CAR_NUMBER_QUERY)) {
  preparedStatement.setString(entity.getCarNumber());
  try (ResultSet resultSet = preparedStatement.executeQuery()) {
    carId = resultSet.getLong("id");
  }
} catch (SQLException e) {
  System.out.println("problem: = " + e.getMessage());
}

Answered By - dpr