generated wrong sql [DATAJDBC-585]

[spring-projects-issues]

jiahua-lan opened DATAJDBC-585 and commented

The main code is as follows:

import lombok.Data;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;
import java.time.LocalDateTime;
@Data
@Table("people")
public class Person {
    @Id
    private final Long id;
    private String name;
    @Column("birth_day")
    private LocalDateTime birthday;
    @Column("created_date")
    @CreatedDate
    private LocalDateTime createdDate;
}

public interface PersonRepository extends PagingAndSortingRepository<Person, Long> {}

@SpringBootTest
class PersonRepositoryTest {

    @Autowired
    PersonRepository personRepository;

    @Test
    void findAll() {
        Iterable<Person> people = personRepository.findAll();
        assertTrue(people.iterator().hasNext());
    }

}

I used schema.sql to create the database table, and used the data.sql file to initialize the data. I confirmed through h2-console that the people table exists and that the table contains a piece of data.

When running the test case, the generated sql is as follows:

SELECT "people"."ID" AS "ID", "people"."NAME" AS "NAME", "people"."birth_day" AS "birth_day", "people"."created_date" AS "created_date" FROM "people"

The exception information is as follows:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "people" not found; SQL statement

I think the sql generated by spring data jdbc is wrong, because generally speaking, the generated sql should look like this:

SELECT p.ID AS ID, p.NAME AS NAME, p.birth_day AS birth_day, p.created_date AS created_date FROM people p

 

---

Affects: 2.0.2 (Neumann SR2)

Issue Links:

• DATAJDBC-593 Generated sql for Postgres is problematic
  ("duplicates")

[spring-projects-issues]

Jens Schauder commented

The SQL is generated as intended.

You just have to use lower/upper case names as expected by your database if you explicitly name things using @Table or @Column to specify names explicitly.

Or name the table and columns in the create statements as you did in the annotations using quoted names.

The decision for this approach was made in order to allow for names which might not be acceptable with out quoting, i.e. names that are keywords as for example "ORDER" or names that contain not allowed characters (spaces, punctuation and similar)

[spring-projects-issues]

jiahua-lan commented

Thank you for your help