Make SQL/hql/jdoql easier to write and reuse (below)

Let the Framework do something to make SQL/hql/jdoql easier to write (on)

This article discusses how to write the SQL processing layer that exists between common SQL/hql statements and JDBC/hibernate and contains hundreds of lines of code.

In the open-source project, ibatis, an SQL-based ORM solution, can be referenced. In addition, it also has its new competitor orbroker, which is awesome and difficult to use.

1. line feed and alignment for SQL is nothing more than writing it into XML
In this way, the newline cannot be used in Java strings.
The problem is that the logic-rich SQL code is separated from the business class, and I don't like it very much.
Another future solution is to use groovy to write business classes and compile them into Java classes. Cute groovy support
String SQL = "select * From Foo
Where ....."

2. for n-plus? Parameter.
// Compile the SQL statement into preparedstatement and assign a value to the attribute
String SQL = "select * from Dandy where name =: Name ";
Query. setparam ("name", "gigix ");

When doing well, you should also support embedded attributes to solve the problem of attributes with the same name.
String SQL = "select * from Dandy, pet where dandy. Name =: dandy. Name and pet. Name =: Pet. Name"
Query. setparam ("Dandy", gigix );
Query. setparam ("Pet", PET );

Finally, automatic binding of all attributes in the object should be supported to simplify writing.
// Automatically bind the age and name attributes in the gigix object
String SQL = "Update dandy set age =: age where name =: Name"
Query. setobjectasparams (gigix );

3. Solve the concatenated SQL statement (1) -- prioritize the use of replace instead of the concatenated SQL statement to reflect the SQL Overview
String SQL = "select * From Foo order by {sortcolumn }}";
... // Condition judgment statement
Query. setreplace ("sortcolumn", "Code ");
 
The overall control is much better than the following maze formations

String SQL = "select * From Foo ";
... // Condition judgment
SQL + = "order by code ";

4. Solve splicing SQL (2) -- manage reusable SQL in a unified manner
The idea of better overall control than partial stitching
For SQL statements that can be partially reused, the current developers do not need to reuse them and completely copy the paste. Once modified, the changes will be split.
The other is to combine the reusable and non-reusable parts of the complex collage and create a new maze.

The o/R broker practices are worth referring:

<sql-statement id="getEmployees" result-object="Employee">
SELECT 
*
FROM 
Employee

<append-statement id-suffix="ById">
WHERE
EmployeeId = :id
</append-statement>

<append-statement id-suffix="BySalaryRange">
WHERE
Salary BETWEEN :lowSalary AND :highSalary
</append-statement>

</sql-statement>

Then, getemployeesbyid and getemployeesbysalaryrange can return different SQL statements.

5. join SQL (3) -- velocity/freemarker is always a good tool for text generation.

Web page generation, code generation, and template engine all prove to be much better than pure Java code. Otherwise, how can we not write servelet? generation of SQL is no exception. O/R broker has this function. The clever thing is that you can directly call the velocity engine without spending a line of code.

<sql-statement id="getEmployee">SELECT EmployeeId,EmployeeName,Salary,FROM Employee#if ($employee.id)WHERE EmployeeId = :employee.id#end</sql-statement>