Mybatis--sql Statement Builder Class

SQL Statement Builder class issues

One of the most painful things that Java programmers face is embedding SQL statements in Java code. This is usually done because SQL statements need to be generated dynamically-otherwise they can be placed in an external file or stored procedure. As you've seen, MyBatis has a powerful dynamic SQL generation scheme in its XML mapping feature. However, it is sometimes necessary to create SQL statements inside Java code. At this point, MyBatis has another feature that can help you in reducing the typical plus, quotation marks, new lines, formatting problems, and embedding conditions to handle extra commas or and join words before. In fact, it's a nightmare to generate SQL code dynamically in Java code. For example:

String sql = "Select P.id, P.username, P.password, P.full_name," "p.last_name,p.created_on, p.updated_on" + "from person P , account A "+" INNER joins DEPARTMENT D on d.id = p.department_id "+" INNER joins company C on d.company_id = c.id "+" wher E (p.id = a.ID and p.first_name like?) "+" OR (P.last_name like?) "+" GROUP by p.id "+" have (P.last_name like?) "+" OR (P.first_name like?) "+" ORDER by p.id, P.full_name ";

The solution

MyBatis 3 provides a handy tool class to help resolve the problem. Using the SQL class, simply create an instance to invoke the method to generate the SQL statement. The problem in the example above is like rewriting the SQL class:

PrivateString Selectpersonsql () {return NewSQL () {{SELECT} ("P.id, P.username, P.password, P.full_name"); SELECT ("P.last_name, p.created_on, p.updated_on"); From ("Person P"); From ("Account A"); Inner_join ("DEPARTMENT D on d.id = p.department_id"); Inner_join ("Company C on d.company_id = C.id"); WHERE ("P.id = a.id"); WHERE ("P.first_name like?");    or (); WHERE ("P.last_name like?"); Group_by ("P.id"); Having ("P.last_name like?");    or (); Having ("P.first_name like?"); Order_by ("P.id"); Order_by ("P.full_name"); }}.tostring ();}

What is special in this example? When you look closely, you don't have to worry about the "and" keyword that recurs occasionally, or the choice between "where" and "and", or nothing. The SQL class is very mindful of where the where should be, where it should be used, and all the string links.

SQL class

Here are some examples:

//Anonymous Inner class PublicString Deletepersonsql () {return NewSQL () {{Delete_from ("Person"); WHERE ("ID = ${id}"); }}.tostring ();}//builder/fluent Style Publicstring Insertpersonsql () {String SQL=NewSQL (). Insert_into ("Person")    . VALUES ("ID, first_name", "${id}, ${firstname}")    . VALUES ("Last_Name", "${lastname}"). toString (); returnSQL;}//with conditionals (note the final parameters, required for the anonymous inner class to access them) PublicString Selectpersonlike (FinalString ID,FinalString FirstName,FinalString LastName) {  return NewSQL () {{SELECT} ("P.id, P.username, P.password, P.first_name, P.last_name"); From ("Person P"); if(id! =NULL) {WHERE ("P.id like ${id}"); }    if(FirstName! =NULL) {WHERE ("P.first_name like ${firstname}"); }    if(LastName! =NULL) {WHERE ("P.last_name like ${lastname}"); } order_by ("P.last_name"); }}.tostring ();} PublicString Deletepersonsql () {return NewSQL () {{Delete_from ("Person"); WHERE ("ID = ${id}"); }}.tostring ();} PublicString Insertpersonsql () {return NewSQL () {{Insert_into ("Person"); VALUES ("ID, first_name", "${id}, ${firstname}"); VALUES ("Last_Name", "${lastname}"); }}.tostring ();} PublicString Updatepersonsql () {return NewSQL () {{UPDATE] ("Person"); SET ("first_name = ${firstname}"); WHERE ("ID = ${id}"); }}.tostring ();}

Method	Description
SELECT (String)	Start or insert to the SELECT clause. Can be called more than once, and parameters are added to the SELECT clause. Parameters typically use comma-separated column names and list, but it can also be any type accepted by the database driver.
Select_distinct (String)	You can also insert the DISTINCT keyword into the generated query statement by starting or inserting to the SELECT clause. Can be called multiple times, and parameters are added to the SELECT clause. Parameters typically use a comma-delimited list of column names and aliases, but can also be any type accepted by the database driver.
From (String)	Starts or inserts into the FROM clause. Can be called more than once, and parameters are added to the FROM clause. parameter is usually a table name or alias, or it can be a database Any type accepted by the driver.
JOIN (String) Inner_join (String) Left_outer_join (String) Right_outer_join (String)	Add a new JOIN clause of the appropriate type, based on the calling method. Parameters can contain joins that are combined by the column and join on conditions into a standard.
WHERE (String)	Inserts a new where clause condition, which is linked by and. Can be called multiple times, and each time the new condition is linked by and. Use or () to separate or.
OR ()	Use OR to separate the current where clause condition. Can be called multiple times, but unstable SQLis called or generated more than once in a row.
and ()	Use and to separate the current where clause condition. Can be called multiple times, but unstable SQLis called or generated more than once in a row. Because WHERE and having both will automatically link and, which is a very rare method that is used only for completeness.
Group_by (String)	Inserts a new GROUP by clause element, connected by a comma. Can be called multiple times, each time a new condition is concatenated by commas.
Having (String)	Inserts a new HAVING clause condition. Connected by and. Can be called multiple times, each time by and to connect the new condition. Use or () to separate or.
Order_by (String)	Inserts a new ORDER by clause element, which is concatenated by commas. Can be called multiple times, each time a new condition is concatenated by commas.
Delete_from (String)	Start a DELETE statement and specify the table name from which table you want to delete. Usually it follows the WHERE statement!
Insert_into (String)	Start an INSERT statement and specify the name of the table where you want to insert the data. followed by one or more values ().
SET (String)	Insert into the set list for the UPDATE statement
UPDATE (String)	Begins an UPDATE statement and specifies an indication that the update needs to be updated. followed by one or more set (), usually with a where ().
VALUES (String, String)	Inserted into the INSERT statement. The first parameter is the name of the column to be inserted, and the second parameter is the value of the column.

Sqlbuilder and Selectbuilder (already obsolete)

Before the 3.2 release, we used a bit of a different approach by implementing the threadlocal variable to mask some of the language limitations that led to the trouble with the Java DSL. But this approach has been abandoned, and the modern framework

People are welcome to use the builder type and the idea of anonymous inner classes. As a result, Selectbuilder and Sqlbuilder classes have been discarded.

The following methods are only applicable to obsolete Sqlbuilder and Selectbuilder classes.

Method	Description
BEGIN () /RESET ()	These methods empty the threadlocal state of the Selectbuilder class and prepare a new build statement. Begin () reads best when starting a new statement. For some reason (under certain conditions, perhaps Is logic requires a completely different statement), the cleanup statement in the Execute RESET () reads the best.
SQL ()	Returns the generated SQL () and resets the Selectbuilder state (as if BEGIN () or reset () is called). Therefore, the method can only be called once!

The Selectbuilder and Sqlbuilder classes are not magical, but it is important to know how they work. Selectbuilder uses a combination of static import and threadlocal variables to open the neat syntax using Sqlbuilder, which can be easily interleaved with conditions. Using them, you can statically import the class's methods just like this (one or the other, not both):

Import static org.apache.ibatis.jdbc.selectbuilder.*; Import static org.apache.ibatis.jdbc.sqlbuilder.*;

This allows you to create a method like this:

/*DEPRECATED*/ PublicString Selectblogssql () {BEGIN ();//clears ThreadLocal variableSelect ("*"); From ("BLOG"); returnSQL ();}/*DEPRECATED*/PrivateString Selectpersonsql () {BEGIN ();//clears ThreadLocal variableSelect ("P.id, P.username, P.password, P.full_name"); SELECT ("P.last_name, p.created_on, p.updated_on"); From ("Person P"); From ("Account A"); Inner_join ("DEPARTMENT D on d.id = p.department_id"); Inner_join ("Company C on d.company_id = C.id"); WHERE ("P.id = a.id"); WHERE ("P.first_name like?");  or (); WHERE ("P.last_name like?"); Group_by ("P.id"); Having ("P.last_name like?");  or (); Having ("P.first_name like?"); Order_by ("P.id"); Order_by ("P.full_name"); returnSQL ();}

Mybatis--sql Statement Builder Class