I am trying to create a web application using Micronaut and need to get information from travelLog table using Spring JdbcTemplate and plain SQL. I was using this tutorial https://www.greggbolinger.com/posts/using-springs-jdbctemplate-with-micronaut/ to solve this, but I faced the following problem:
30.869 [default-nioEventLoopGroup-1-2] ERROR i.m.h.s.netty.RoutingInBoundHandler - Unexpected error occurred: StatementCallback; bad SQL grammar [SELECT * FROM travelLog]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "travellog" does not exist
Position: 15
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM travelLog]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "travellog" does not exist
Position: 15
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
at
Here is travelLog table Schema
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<changeSet id="01" author="julia">
<createTable tableName="travelLog"
remarks="A table to contain all travel logs">
<column name="id" type="int">
<constraints nullable="false" unique="true" primaryKey="true"/>
</column>
<column name="date" type="timestamp">
<constraints nullable="false"/>
</column>
<column name="regNumber" type="varchar">
<constraints nullable="false"/>
</column>
<column name="ownersName" type="varchar(50)">
<constraints nullable="false"/>
</column>
<column name="odometerValueBeg" type="int">
<constraints nullable="false"/>
</column>
<column name="odometerValueEnd" type="int">
<constraints nullable="false"/>
</column>
<column name="departurePlace" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="destinationPlace" type="varchar(255)">
<constraints nullable="false"/>
</column>
<column name="description" type="varchar">
</column>
</createTable>
</changeSet>
</databaseChangeLog>
Here is JdbcTemplateFactory.java
@Factory
public class JdbcTemplateFactory {
@Inject
DataSource dataSource;
@Bean
@Singleton
JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource);
}
}
Here is TravelLogService.java
@Singleton
@Requires(beans = JdbcTemplate.class)
public class TravelLogService {
private final JdbcTemplate jdbcTemplate;
public TravelLogService(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Transactional
public void printUsernames() {
jdbcTemplate.query("SELECT * FROM travelLog", (rs) -> {
System.out.println(rs.getString("ownersName"));
});
}
To create and query case sensitive table, columns etc, names must be quoted like this:
SELECT * FROM "travelLog"
For liquibase settings check this answer https://stackoverflow.com/a/60654633/1854103
Also please consider change your naming conventions