springspring-datamicronautmicronaut-data

How to get db information using Spring JdbcTemplate and plain SQL (Micronaut)


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"));
    });
  }

Solution

  • 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