Resolving the SQL Error: 42122, SQLState: 42S22 in Spring Boot Using H2 Database

Discover how to troubleshoot and fix the `Column not found` error in your Spring Boot application using the H2 database. Learn best practices for writing SQL queries in H2 and ensure smooth database operations.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: H2Database/SpringBoot SQL Error: 42122, SQLState: 42S22 (Column not found)

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding and Fixing the SQL Error in Spring Boot with H2 Database

If you're working with a Spring Boot application and using H2 database, you might encounter the frustrating error, SQL Error: 42122, SQLState: 42S22 with the message "Column not found". This issue often arises during CRUD operations, particularly when the SQL query cannot locate the necessary columns in your database schema.

The Problem

In our case, the issue stemmed from an H2 database query that functioned correctly when executed against a MySQL database, but failed when using H2. The pivotal error was related to the id column of the AtmCashPickup entity.

Here’s a short snippet of the error message encountered:

[[See Video to Reveal this Text or Code Snippet]]

This hints that the SQL query expects an id column that is not included in the SELECT statement.

The Solution

To resolve this error, you need to ensure that the id field is part of your SQL query since it is a mandatory field for the AtmCashPickup entity. Here’s a step-by-step breakdown of the fix:

1. Modify Your SQL Query

Add the id field to your SQL query within the repository interface. This ensures that the expected structure of the returned result set aligns with the properties of your entity.

Original Query:

[[See Video to Reveal this Text or Code Snippet]]

Revised Query:

[[See Video to Reveal this Text or Code Snippet]]

2. Update Your Repository

Update your repository code to reflect this change. Here is the updated code for the repository interface:

[[See Video to Reveal this Text or Code Snippet]]

3. Test Your Application

After making the changes, run your application again and execute the method that calls findAtmCashPickupNonNullRanges(). You should no longer encounter the "Column id not found" error, and your expected values should return successfully.

Conclusion

Errors like SQL Error: 42122 can disrupt development, but by ensuring that your SQL queries match the entity definitions, you can resolve such issues efficiently. This method not only applies to H2 database situations but can also be beneficial when working with other SQL databases.

For any Spring Boot application involving SQL queries, always double-check:

All required columns are included in the select clause,

The SQL syntax is compatible with the specific database being used.

Happy coding! If you encounter any further issues, feel free to reach out for assistance.