Why I so recommend 'SQL First'?

2024-12-25 by Frank Cheung

Looking at backend development as a whole, most of the work revolves around database development (commonly referred to as "CRUD Boy"). How we interact with databases is closely related to our development experience. The industry frequently discusses the "object-relational impedance mismatch," which lies at the heart of the issue: given that we have two different "programming worldviews"—a relational model database (SQL) and a programming language (Java, C#, Go)—each with its own methodologies, use cases, and ways of thinking, how can they coexist harmoniously and exchange information? Clearly, the "impedance mismatch" is not an easy problem to solve.

Many backend developers try to avoid writing SQL by struggling with various ORM (Object-Relational Mapping) solutions. To be frank, this isn't necessary. ORMs may seem to offer high development efficiency on the surface, but in reality, they are just layers of abstraction—ultimately leading back to the SQL layer. ORMs, in simple terms, transform one problem into another for resolution, completely bypassing SQL. However, issues specific to databases, such as join queries, nested subqueries, and complex SQL logic, can hardly be perfectly addressed within ORMs—or rather, they are not as straightforward or comfortable to write. Why go through the trouble of using cumbersome ORMs when direct SQL is more flexible and efficient? ORMs might find themselves stretched beyond their capabilities. These problems fall precisely into the domain where relational databases excel. Transferring tasks best suited for relational databases to ORMs, which are less adept at handling them, seems rather misguided. The object-oriented paradigm (of which ORMs are a derivative) should perhaps temper its ambitions and focus on what it does best. Moreover, SQL itself is sufficiently elegant and worthy of deep study.

The replacement of Hibernate by iBatis/MyBatis is a case in point, proving that writing SQL directly can be more flexible and efficient (SQL First/SQL Centric). Later developments like MyBatis Plus's lambda syntax somewhat revert to earlier approaches—similar to Microsoft's LINQ, which was widely adopted early on.

Of course, this doesn’t mean completely discarding the beneficial aspects of ORMs, such as:

• The "object" part of ORMs, which automatically converts a row of data (result set) into an object, facilitating business code processing.
• The readability concerns of SQL compared to programming languages, which depend on developer habits; familiarity with SQL naturally enhances readability.
• Single SQL statements cannot cover everything. Clearly, complex business logic requires collaboration between SQL and Java.

Refer to: The Disaster of ORM (Object-Relational Mapping).

Considering these pros and cons, I boldly propose a clear, simple, and reusable JDBC solution aimed at addressing CRUD (Create, Read, Update, Delete) issues within the realm of Java and JDBC. Defining the interface between the two domains is crucial. In Java, developers often use the DAO (Data Access Object) layer to solve this problem, which is exactly what we are discussing here.