Issue
I have a Spring Boot connected with Oracle Database application. The same version of the application works fine with MySQL, but when I try to do some requests to Oracle Database it throws error:
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
"status":500,"error":"Internal Server Error","trace":"org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)\r\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)\r\n\tat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)\r\n\tat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154) at com.example.pawel.expense.controller.ExpenseController.createExpense(ExpenseController.java:67) ~[classes/:na] ECT...
So here is my controller method for this function:
@PostMapping("/expense")
ResponseEntity<Expense> createExpense(@Valid @RequestBody Expense expense) throws URISyntaxException {
Expense result = expenseRepository.save(expense);
return ResponseEntity.created(new URI("/api/expense/" + result.getId())).body(result);
}
And my Expense model
@NoArgsConstructor
@Data
@Table(name="expense")
@Entity
public class Expense {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
private Long id;
private Instant date;
private double amount;
private String description;
@ManyToOne
private Category category;
@ManyToOne
private User user;
// Getters and setters
Here is my application.properties
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.jpa.properties.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.show-sql=true
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.username=system
spring.datasource.password=root
spring.datasource.initialization-mode=always
spring.datasource.validationQuery=SELECT 1
logging.level.org.hibernate.SQL=DEBUG
spring.server.compression.enabled=true
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.jpa.properties.hibernate.format_sql=true
Solution
You should add Hibernate Dialect
to your application.properties like so
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
Or create a Bean for Oracle datasource. Follow this guide for further information Configuring Spring Boot for Oracle
Answered By - Kirill Grosu
Answer Checked By - Candace Johnson (JavaFixing Volunteer)