MyBatis Learning--sql Statement Builder

Study notes, selected from MyBatis official Chinese document: http://www.mybatis.org/mybatis-3/zh/statement-builders.html

problem

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

1 //Anonymous Inner class2  PublicString Deletepersonsql () {3   return NewSQL () {{4Delete_from ("Person");5WHERE ("ID = #{id}");6 }}.tostring ();7 }8 9 //builder/fluent StyleTen  PublicString Insertpersonsql () { OneString sql =NewSQL () A. Insert_into ("Person") -. VALUES ("ID, first_name", "#{id}, #{firstname}") -. VALUES ("Last_Name", "#{lastname}") the . 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 () {{ ASelect ("P.id, P.username, P.password, P.first_name, P.last_name"); atFrom (' Person P '); -     if(id! =NULL) { -WHERE ("P.id like #{id}"); -     } -     if(FirstName! =NULL) { -WHERE ("P.first_name like #{firstname}"); in     } -     if(LastName! =NULL) { toWHERE ("P.last_name like #{lastname}"); +     } -Order_by ("P.last_name"); the }}.tostring (); * } $ Panax Notoginseng  PublicString Deletepersonsql () { -   return NewSQL () {{ theDelete_from ("Person"); +WHERE ("ID = #{id}"); A }}.tostring (); the } +  -  PublicString Insertpersonsql () { $   return NewSQL () {{ $Insert_into ("Person"); -VALUES ("ID, first_name", "#{id}, #{firstname}"); -VALUES ("Last_Name", "#{lastname}"); the }}.tostring (); - }Wuyi  the  PublicString Updatepersonsql () { -   return NewSQL () {{ WuUPDATE ("Person"); -SET ("first_name = #{firstname}"); AboutWHERE ("ID = #{id}"); $ }}.tostring (); -}

Method	Description
SELECT (String) 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 a comma-delimited list of column names and aliases, but can also be any type accepted by the database driver.
Select_distinct (String) 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 more than once, 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) From (String ...)	Starts or inserts into the FROM clause. Can be called more than once, and parameters are added to the FROM clause. Parameters are usually table names or aliases, or any type accepted by the database driver.
JOIN (String) JOIN (String ...) Inner_join (String) Inner_join (String ...) Left_outer_join (String) Left_outer_join (String ...) Right_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) 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 automatically link and , this is a very rare method that is used only for completeness.
Group_by (String) 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) 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) 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 () or Into_columns () and Into_values ().
SET (String) 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.
Into_columns (String ...)	Appends columns phrase to an INSERT statement. This should is call Into_values () with together.
Into_values (String ...)	Appends values phrase to an INSERT statement. This should is call Into_columns () with together.

Since version 3.4.2, you can use variable-length arguments as follows:

1  PublicString Selectpersonsql () {2   return NewSQL ()3. Select ("P.id", "A.username", "A.password", "P.full_name", "D.department_name", "C.company_name")4. From (' Person P ', ' account A ')5. Inner_join ("DEPARTMENT D on d.id = p.department_id", "company C on d.company_id = C.id")6. WHERE ("p.id = a.ID", "P.full_name like #{name}")7. Order_by ("P.id", "P.full_name")8 . toString ();9 }Ten  One  PublicString Insertpersonsql () { A   return NewSQL () -. Insert_into ("Person") -. Into_columns ("ID", "Full_name") the. Into_values ("#{id}", "#{fullname}") - . toString (); - } -  +  PublicString Updatepersonsql () { -   return NewSQL () +. UPDATE ("Person") A. SET ("full_name = #{fullname}", "Date_of_birth = #{dateofbirth}") at. WHERE ("ID = #{id}") - . toString (); -}

MyBatis Learning--sql Statement Builder