Why get java.sql.SQLException

Question

I am developing a REST API Application for the first time and i was excited. Unfortunately, after the successful build, and when i run my application, i was facing error as below..

    java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:10774) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatement.setFormOfUseInternal(OraclePreparedStatement.java:10755) ~[ojdbc8-21.1.0.0.jar:21.1.0.0.0]

Could you please help me fixing it. Thanks a lot in advance.

Here is the pieces of my code:

RestapiApplication.java

@SpringBootApplication
@ComponentScan(basePackages = "com.gfit.restapi")
@EnableSwagger2
public class RestapiApplication {        
   public static void main(String[] args) {
        SpringApplication.run(RestapiApplication.class, args);
   }        
}

ModelEntity.java

/*@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "TEST_EMP")*/
public class ModelEntity {

   /*@Id*/
   private BigInteger track_id;
   private BigInteger seq_num;
   private String request_type;
   private String certification_id;
   private String student_id;

   // constructor, getter, setter and tostring

}

GfitRESTRepositoryImpl.java

    @Repository
    public class GfitRESTRepositoryImpl implements GfitRESTRepository{
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @Override
        public ModelEntity findById(String certification_id) {
            try {
                ModelEntity cert_detail = jdbcTemplate.queryForObject("SELECT track_id, seq_num, request_type, certification_id, STUDENT_ID FROM tbl_ep_stg_caa_ext WHERE certification_id = '' ",
                        BeanPropertyRowMapper.newInstance(ModelEntity.class), certification_id);
                System.out.println(cert_detail);
                return cert_detail;
            } catch (IncorrectResultSizeDataAccessException e) {
                return null;
            }
        }
    }

GfitRESTController.java

    @CrossOrigin(origins = "http://localhost:3000")
    @RestController
    @RequestMapping("/api")
    public class GfitRESTController {
    
        @Autowired
        GfitRESTRepository gfitRESTRepository;
    
        //@GetMapping("/certification/detail/{certification_id}")
        @RequestMapping(value = "/certification/detail/{certification_id}", method = RequestMethod.GET)
        public ResponseEntity<ModelEntity> getCertificateByID(@PathVariable("certification_id") String certification_id) {
            ModelEntity cert_detail = gfitRESTRepository.findById(certification_id);
    
            if (cert_detail != null) {
                return new ResponseEntity<>(cert_detail, HttpStatus.OK);
            } else {
                return new ResponseEntity<>(HttpStatus.NOT_FOUND);
            }
        }
    
    }

My Actual DB query gives the below result, and which i was trying to execute in my code above :

Postman screenshot :

Screenshots From Swagger-UI Endpoint :

Answer 1

Can you try downgrading the OJDBC JAR to version 12.2.0.1? I'm facing a similar issue with an update statement in my own application. We had upgraded to 21.1 a little while back from 12.2 and we never faced this issue with the 12.2 driver. I spent hours trying to figure it out and ended up reverting the driver version just to test it out and the error went away.

https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8/12.2.0.1