Spring Data JPA原生查询无法返回结果，请求技术排查

Hey there, let's figure out why your native query isn't returning any results. Here are the most likely issues to check step by step:

• Parameter binding mismatch
  You're using @Param annotations for named parameters (df, programId, limit), but your query uses positional placeholders (?1, ?2, ?3). For native queries in Spring Data JPA, named parameters require using the :paramName syntax instead of positional ones. Mixing these two can cause Spring to pass incorrect (or no) values to your query. Update your query to match the parameter names:

  @Query( value = "select q.* " +
          "from question q " +
          "left join question_program qp " +
          "on q.question_id = qp.question_id " +
          "where " +
          "q.difficulty_level_id = :df " +
          "and " +
          "q.paid = false " +
          "and " +
          "qp.program_id = :programId " +
          "order by random() " +
          "limit :limit", nativeQuery = true )
  List<Question> getFreeRandomQuestions( @Param("df") Integer df, @Param("programId") Integer programId, @Param("limit") Integer limit);
  

• LEFT JOIN being treated as INNER JOIN
  Putting qp.program_id = ?2 in the WHERE clause negates the LEFT JOIN behavior. If a question has no matching question_program records, qp.program_id will be NULL, and NULL = [your programId] evaluates to false—so those questions get filtered out entirely.

  • If you want to keep questions that might not have a linked program (but filter those that do to match programId), move that condition to the ON clause:

    @Query( value = "select q.* " +
            "from question q " +
            "left join question_program qp " +
            "on q.question_id = qp.question_id and qp.program_id = :programId " +
            "where " +
            "q.difficulty_level_id = :df " +
            "and " +
            "q.paid = false " +
            "order by random() " +
            "limit :limit", nativeQuery = true )
    

  • If you only want questions that do have a matching programId, switch to an INNER JOIN instead—it's clearer and avoids this confusion.

• Verify your data exists
  Before blaming the query, run the raw SQL directly in your database client (like MySQL Workbench, pgAdmin) with hardcoded values for df, programId, and limit. For example:

  select q.* 
  from question q 
  left join question_program qp 
  on q.question_id = qp.question_id 
  where q.difficulty_level_id = 1 
    and q.paid = false 
    and qp.program_id = 5 
  order by random() 
  limit 10;
  

  If this returns nothing, the issue is with your data, not the query. Double-check that there are rows in question with paid = false, matching difficulty_level_id, and linked to the specified programId in question_program.

• Database-specific random() function
  Note that random() is database-dependent: PostgreSQL uses random(), but MySQL uses rand(), SQL Server uses NEWID() or RAND(). If your database doesn't recognize random(), the query might fail silently. Confirm the correct random sorting function for your database and update the query accordingly.

• Entity mapping correctness
  Make sure your Question entity is properly mapped to the question table. Check that:

  • The question_id field is annotated with @Id and matches the database column type (e.g., Integer for INT).
  • All other fields in Question align with the question table's columns (no typos, correct data types).
  • Any custom @Column annotations point to the right column names.

内容的提问来源于stack exchange，提问作者ArslanAnjum