Mybatis General crud-Design Ideas

One about the advantages of Mybatis1.1 MyBatis

1 Lightweight ORM.

2 provides a perfect caching mechanism.

3 mapper.xml Acoustic SQL is clearer and more flexible, and SQL facilitates SQL tuning.

4 Resulttype Resultmap Processing Returns a result set, decoupled from Pojo.

1.2 MyBatis Use Experience

This is the only place where the mybatis is not easy to use.

1 need to write a DAO interface and mapper.xml for each table, which is not very friendly to the developer, assuming that the system has 30 business tables, hehe.

2 Although there are generator tools, you can automatically generate the DAO interface and Mapper.xml, you can not write the code yourself, but there are 2 disadvantages:

(1) Generator only provides the basis for the addition and deletion of the function, complex SQL or to add their own.

(2) or the above problem, each table should have DAO interface and Mapper.xml.

1.3 About physical paging

For the function of paging, the benevolent see bar, in my personal view, I do not think it is necessary.

One of the advantages of MyBatis is that the original SQL in Mapper.xml, especially for complex SQL statements, is attractive.

If you paged the SQL package into the following format,

<!----Select COUNT (1) as ' Totalnum ', t.* from  (select  user_id, user_name from user) T limit startrowno,rownum<!----Select COUNT (1) as ' Totalnum ', USER_ID, user_name from user limit Startrowno,rownum

I think, or forget it, as the original SQL to the reality. SELECT * I won't tell you, select * FROM (SQL) is not necessary at all.

If you want to implement a flexible query method, there are paging parameters by paged query, no paging parameters to query all. So my point is, MyBatis provides a <if></if> tag with the following code:

 <!--Mapper.xml -   <SelectID= "Selectbypage"ParameterType= "Map"Resulttype= "HashMap">Select COUNT (1) as ' Totalnum ', user_id,user_name from user<ifTest= "Pageparam! = null" >Limit #{pageparam.startrowno},#{pageparam.pagesize}</if>        </Select>

If you want the paging method to be more flexible, and you want to implement a method that can be paginated for any table, the code can write

 <SelectID= "Selectbypage"ParameterType= "Map"Resulttype= "HashMap">Select ${querycolumn} from ${tablename}<ifTest= "Page! = null" >Limit #{page.startrowno},#{page.pagesize}</if>      </Select>

About paging, that's all, just a personal point of view, and welcome criticism, advice or discussion.

Two MyBatis General CRUD design 2.1 requirements

Based on 1.2, you want to provide a load, insert, delete, Save method similar to hibernate, for the underlying data access layer operation, the system only needs to exist one place, easy to unify management, reduce unnecessary code.

Based on 1.1, avoid using the Java wrapper string sql,mapper.xml, @select, @selectProvider three choices.

2.2 Design 2.2.1 Generic CRUD interface

Functional method interfaces that you want to implement

/*** Generic CRUD data Access Layer interface * *@authorSvili * @date November 11, 2016 **/ Public InterfaceCrudserviceinter {/*** Search by PRIMARY KEY * *@param<T> * Pojo class *@paramClazz * Pojo class-class Object *@paramPrimaryvalue * Primary KEY value *@returnPojo Object *@throwsException*/<T> T Selectbyprimarykey (class<t> clazz, Integer primaryvalue)throwsException; /*** Insert Data * *@param<T> * Pojo class *@paramT * Pojo Object *@returnnumber of data bars *@throwsException*/<T>intInsert (T t)throwsException; /*** Insert Data * *@param<T> * Pojo class *@paramT * Pojo Object *@returnnumber of data bars *@throwsException*/<T>intInsertselective (T t)throwsException; /*** Delete * <p> * Delete * based on PRIMARY key * </p> * *@param<T> * Pojo class *@paramClazz * Pojo class-class Object *@paramPrimaryvalue * Primary KEY value *@returnnumber of data bars*/<T>intDeletebyprimarykey (class<t>Clazz, String primaryvalue); /*** UPDATE * <p> * update all fields of Pojo, including null (null value) fields, according to the primary key * </p> * <p> * </P&G     T *      * @param<T> * Pojo class *@paramT * Pojo Object *@returnnumber of data bars *@throwsException*/<T>intUpdatebyprimarykey (T t)throwsException; /*** UPDATE * <p> * Update pojo non-empty fields based on PRIMARY key * </p> * <p> * UPDATE * </p> * * @param<T> * Pojo class *@paramT * Pojo Object *@returnnumber of data bars *@throwsException*/<T>intUpdatebyprimarykeyselective (T t)throwsException; /*** Search by Criteria *@paramClazz Pojo Class-class Object *@paramconditionexp WHERE Condition expression *@paramConditionparam where conditional expression dynamic parameters *@return     *@throwsException*/<T> list<t> selectbycondition (class<t> clazz,string conditionexp,map<string,object> Conditionparam)throwsException;}

2.2.2 Based on primary key query 1 first, clear interface

throws Exception;

Smart you should have noticed that the primary key value is shaping.

To illustrate this, I will set a primary key of type int for each table because the database I use is MySQL.

If you are using Oracle, the primary key is a GUID, or if you want the primary key value to be a string type, or if you want to be compatible with string and integer, then you can set the parameter type of the interface to string.

For the primary key type, there is a special description later in the article.

2 analysis of SQL in Mapper.xml

<SelectID= "Selectbyprimarykey"ParameterType= "Map"Resulttype= "HashMap">Select<foreachItem= "ColumnName"Index= "Index"Collection= "Querycolumn"Separator="," >#{columnname}</foreach>From ${tablename} where ${primarykey} = #{primaryvalue}</Select>

Note: Do not use <! [cdata[]]> attempts to escape this SQL will cause the <foreach></foreach> tag not to be parsed.

About the <! in XML [cdata[]]>, self Baidu get Oh.

3 Defining the parameters in SQL

There are 4 parameters to pass in SQL, namely: tableName (table name), Querycolumn (query field set), PrimaryKey (primary key column name), Primaryvalue (primary key value).

4 interface parameters and SQL parameter conversions

(1) TableName

From the Java class to the database table name, we can get two information, one is the class name, the second is a JPA @table annotation.

Note that two points, 1. Annotations take precedence, 2.Java naming rules are humps, and database naming rules are underlined. If you don't play the cards, think hard about how to convert.

//get Pojo table name Public StaticString Gettablename (class<?>clazz) {        //Hump turn down underlineString TableName =Stringutil.cameltounderline (Clazz.getname ()); //determine if there is a table annotation        if(Clazz.isannotationpresent (Table.class)) {            //Get Annotation ObjectTable table = clazz.getannotation (table.class); //set the Name property            if(!table.name (). Trim (). Equals ("")) {                returnTable.name (); }        }        returnTableName;}

(2) Querycolumn

Get the field name directly, and notice that the hump turns to underline.

// Get all field names  Public Static List<string> getallcolumns (class<?> clazz) {        Listnew arraylist<string>  ();         = clazz.getdeclaredfields ();          for (Field field:fields) {            Columns.Add (Stringutil.cameltounderline (Field.getname ()));        }         return columns;}

(3) PrimaryKey

This only depends on the @id of JPA.

Of course, you can customize a set of standards, such as each table is fixed a primary key field named TABLE_ID. But I don't think it's necessary, since there are JPA standards, why go to your own mind? If you use Oracle's GUID, no temper, hehe.

//Get primary key field Public StaticField Getprimaryfieldnotcarenull (class<?>clazz) {Field field= Fieldreflectutil.findfield (Clazz, Id.class); if(Field! =NULL) {            returnfield; } Else {            return NULL; }}//Gets the field of the specified annotation type Public StaticField FindField (class<?> clazz, class<?extendsAnnotation>Annotationtype) {Class<?> SearchType =Clazz;  while(! Object.class. Equals (SearchType) && searchtype! =NULL) {field[] fields=Searchtype.getdeclaredfields ();  for(Field field:fields) {if(Field.isannotationpresent (Annotationtype)) {returnfield; }} searchtype=Searchtype.getsuperclass (); }        return NULL;}

2.2.3 Other Interfaces

about other interfaces, in fact, the same thing, here do not explain, just follow the 2.2.2 in the idea to see the source is OK.

Three source

Source Address: Https://github.com/LittleNewbie/portal

How to read the source code:

Main 4 Files Generaldao,generalmapper.xml,generaldaoprovider,crudserviceimpl

Generaldao declares the data Access layer interface;

Generalmapper.xml and Generaldaoprovider cooperate with Generaldao.

The Crudserviceimpl implements the conversion of the interface parameter to the Generaldao parameter, and the enclosing return type.

Mybatis General crud-Design Ideas