CODEWITHSUNDEEP
javaspringspring-data-jpamariadbnativequery
treedust
treedust

Reputation: 142

java.sql.SQLSyntaxErrorException: No such column: id

java.sql.SQLSyntaxErrorException: No such column: id

When table already contains the column id

I'm trying to have the following native SQL query that runs with expected results from within MariaDB SQL from a Repository class: 

SELECT name
FROM table1 t1
WHERE t1.table2_id IN (
  SELECT id
  FROM table2 t2
  WHERE t2.column1_number = 1 AND
        t2.id IS NOT NULL
  )
ORDER BY t1.name

The entity classes are as follows: 


@Entity
@Table(name = "table1")
public class Table1 {

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Integer number;

    @ManyToOne
    @JoinColumn(name = "table2_id")
    private Table2 table2;

   // Getters and Setters
}


@Entity
@Table(name="table2")
public class Table2 {
    private  Integer id;
    private  Integer column1_number;

    @Override
    public Integer getId() {
        return id;
    }

    @Override
    public void setId(Integer id) {
        this.id = id;
    }

   // Getters and Setters
}

The Repository is: 


@Repository
public interface Table1Repository extends JpaRepository<Table1, Integer> {

    @Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);
}

Any ideas as to why I am getting this error at run time? This error does not occur at compile time.

Upvotes: 0

Views: 2507

Answers (3)

treedust
treedust

Reputation: 142

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);

should have been List<String> and not List<Table1>

and so the solution was:

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<String> findByNumberOrderByName(Integer number);

Upvotes: 1

m4c_4rthur
m4c_4rthur

Reputation: 53

Try to add Id element to table1 and annotated both tables Id fields with @Id annotation like below.

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private Long id;

Upvotes: 0

Mohamed Sweelam
Mohamed Sweelam

Reputation: 1229

You are trying to search in table2 using id, but you didn't choose the correct mapping column from table1 which should be table2_id.id , so the query should be something like this 

SELECT name FROM table1 t1 WHERE t1.table2_id.id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = 1 AND t2.id IS NOT NULL) ORDER BY t1.name

Also note you're using native query, that means querying the db directly, so make sure you're using the same correct name in your database tables.

Upvotes: 0

Related Questions