Spring+springmvc+mybatis Deep learning and building (d)--mybatis input mapping and output mapping

Reprint Please specify source: http://www.cnblogs.com/Joanna-Yan/p/6878529.html

We talked about spring+springmvc+mybatis. Deep Learning and building (c)--mybatis Global profile parsing

1. Input mappings

Specifies the type of input parameter through ParameterType, which can be a wrapper type of simple Type, HashMap, Pojo.

1.1 #{} and ${}

#{} is implemented to set parameter values to pre-processing statements in Preparestatement, and #{} in SQL statements represents a placeholder.

<id= "Finduserbyid"  parametertype= "int"  resulttype = "User" >    SELECT * from user where id=#{id}</Select>

Using the placeholder #{} can effectively prevent SQL injection, which does not require a type of relational parameter value, and MyBatis automatically converts Java types and JDBC types. #{} can receive either a simple type value or a Pojo property value, and if ParameterType transmits a single type value, the #{} bracket can be either value or another name.

Unlike ${} and #{}, the ParameterType incoming content can be stitched into SQL without JDBC type conversion through ${}, and ${} can receive either a simple type value or a Pojo property value, if ParameterType transmits a single simple type value, ${} Only value can be in parentheses. Using ${} does not prevent SQL injection, but sometimes it is very convenient to use ${}, as in the following example:

    <id= "Finduserbyname"  parametertype= "java.lang.String"  Resulttype= "Joanna.yan.mybatis.entity.User">        select * from User where username Like '%${value}% '    </Select>

If this example uses #{} then the incoming string must have%, and the% is artificial stitching in the parameters, it is obviously a bit cumbersome, if the use of ${} in SQL splicing as% in the way of calling the mapper interface to pass parameters is much easier.

// If you use a placeholder symbol, you must add% to the pass-through parameter list<user> list = usermapper.selectuserbyname ("% Administrator%");

// If you use the ${} primitive symbol, you do not have to add% to the parameter List<user>list = usermapper.selectuserbyname ("Administrator");

For example, order by ordering, if you pass the column name through the parameter to SQL, according to the column name to sort, it should be written as: ORDER by ${columnname}

If you use #{} you will not be able to implement this feature.

1.2 Passing simple types

Refer to the example above.

1.3 Passing Pojo objects

MyBatis uses the OGNL expression to parse the value of an object field, as in the following example:

     <!-- Pass Pojo Object Comprehensive query user information    -     <  ID= "Finduserbyuser"  parametertype= "user"  resulttype = "User" >          SELECT * from user where id=#{ID} and username like '%${username}% '       </Select>

The red callout in SQL is the name of the field in the user object.

Test:

    @Test    publicvoidthrows  exception{        sqlsession sqlsession =sqlsessionfactory.opensession ();        Usermapper usermapper=sqlsession.getmapper (usermapper.  Class);        User User=new  User ();        User.setid (ten);        User.setusername ("Zhang San");        List<User> list=usermapper.finduserbyuser (User);        SYSTEM.OUT.PRINTLN (list);        Sqlsession.close ();    }

1.4 Passing Pojo Wrapper objects

In the development of the query conditions through Pojo, query conditions are comprehensive query conditions, not only including the user query criteria, but also include other query conditions (such as the user to purchase the product information as a query condition), you can use the wrapper object to pass input parameters.

1.4.1 Demand

Complete the user information comprehensive query, need to pass the query condition is very complex (may include user information, other information, such as goods, orders, etc.)

1.4.2 defining the wrapper type Pojo

For the above requirements, it is recommended to use the Pojo of the custom wrapper type to wrap the complex query conditions in the Pojo of the wrapper type.

/*** Package Type *@authorJoanna.yan **/ Public classUserqueryvo {//The required query conditions for packing here//User Query Criteria    PrivateUsercustom Usercustom;  PublicUsercustom Getusercustom () {returnUsercustom; }     Public voidSetusercustom (Usercustom usercustom) { This. Usercustom =Usercustom; }        //can package other inquiry conditions, orders, goods//......}

1.4.3 Mapper.xml

Define user information comprehensive query in Usermapper.xml (complicated query condition, complex association query through advanced query)

    <!--User Information Comprehensive query Userqueryvo defines the Usercustom attribute #{usercustom.sex}: Remove the Pojo wrapper object in the gender value ${usercustom.username}: Remove the user name from the Pojo wrapper object  -    <SelectID= "Finduserlist"ParameterType= "Joanna.yan.mybatis.entity.UserQueryVo"Resulttype= "Joanna.yan.mybatis.entity.UserCustom">SELECT * from USER WHERE user.sex=#{usercustom.sex} and User.username like '%${usercustom.username}% '</Select>

1.4.4 Mapper.java

// User Information Comprehensive query  Public throws Exception;

1.4.5 Test Code

@Test Public voidFinduserlisttest ()throwsexception{sqlsession sqlsession=sqlsessionfactory.opensession (); Usermapper Usermapper=sqlsession.getmapper (Usermapper.class); //Create wrapper objects, set query criteriaUserqueryvo userqueryvo=NewUserqueryvo (); Usercustom Usercustom=NewUsercustom (); Usercustom.setsex ("1"); Usercustom.setusername ("Zhang San Feng");        Userqueryvo.setusercustom (Usercustom); List<UserCustom> list=usermapper.finduserlist (USERQUERYVO);    SYSTEM.OUT.PRINTLN (list); }

1.5 Delivery HashMap

The SQL mapping file is defined as follows:

    <!-- Transfer hashmap Comprehensive query user information    -    <  ID= "Finduserbyhashmap"  parametertype= "HashMap"  Resulttype  = "user">        select * from user where id=#{ID} and username like '%${< C18>username}% '    </Select>

The red callout in SQL is the HashMap key.

Mapper.java:

// Transfer hashmap Comprehensive query user information
 Public throws Exception;

Test:

@Test Public voidFinduserbyhashmaptest ()throwsexception{sqlsession sqlsession=sqlsessionfactory.opensession (); Usermapper Usermapper=sqlsession.getmapper (Usermapper.class); //constructing a query condition HashMap objectHashmap<string, object> map=NewHashmap<>(); Map.put ("id", 10); Map.put ("Username", "Zhang San"); //Pass HashMap object query user listList<user> list=usermapper.finduserbyhashmap (map);        SYSTEM.OUT.PRINTLN (list);    Sqlsession.close (); }

When the key in the passed map is inconsistent with the resolved key in SQL, no error is encountered, only the value obtained by key is empty.

2. Output Map 2.1 Output Simple Type

Look at the example below, output int, mapper.xml file:

    <!-- get the total number  of user lists  -    <  ID= "Findusercount"  parametertype= "user"  resulttype = "int" >         Select COUNT (*) from user    </Select>

Mapper Interface:

 Public Interface usermapper {    // Get the total number of user lists public    intthrows  Exception; }

Test:

@Test Public voidFindusercounttest ()throwsexception{sqlsession sqlsession=sqlsessionfactory.opensession (); //Create Usermapper object, MyBatis automatically generate Mapper proxy objectUsermapper Usermapper=sqlsession.getmapper (Usermapper.class); User User=NewUser (); User.setusername ("Yan"); //methods for calling Usermapper        intCount=usermapper.findusercount (user);        System.out.println (count);    Sqlsession.close (); }

Summarize:

The result set that you query is a row and a column, and you can use a simple type for output mapping.

2.2 Output Pojo objects and Pojo list

Regardless of whether the output Pojo a single object or a list (including Pojo), the specified type is the same in Mapper.xml Resulttype.

The method return value type specified in Mapper.java is not the same:

(1) Outputs a single Pojo object, and the method return value is a single object type.

(2) Output POJO Object list, method return value is list<pojo>

The generated dynamic proxy object is based on the return value type of the Mapper method to determine whether to call Session.selectone (return a single object call) or Session.selectlist (return a collection object call).

2.3 Resulttype Summary

Using Resulttype for output mapping, the column can be mapped successfully only if the queried column name matches the attribute name in the Pojo.

If the queried column names and the property names in the Pojo are all inconsistent, the Pojo object is not created.

The Pojo object is created as long as the queried column name and the attributes in the Pojo are consistent.

2.4 Output HashMap

The output Pojo object can use the HashMap output type instead, and the output field name as the key,value of the map is the field value.

2.5 Resultmap

Resulttype can specify that the query results be mapped to Pojo, but the property name of the Pojo and the column name of the SQL query must be consistent to be mapped successfully.

If the SQL query field name and Pojo property names do not match, you can resultmap the field name and the property name by a corresponding relationship, Resultmap essentially also need to map the query results to the Pojo object.

Resultmap can implement Pojo that map query results to complex types, such as Pojo and list implementations in query result mapping objects to implement one-to-one queries and one-to-many queries.

2.5.1 Resultmap How to use

If the queried column name is inconsistent with the Pojo property name, a mapping is made between the column name and the Pojo property name by defining a resultmap.

(1) Definition Resultmap

(2) Use Resultmap as the output mapping type of the statement.

2.5.2 the SQL below uses the user to complete the mapping

SELECT ID id_,username username_ from USER WHERE Id=#{value}

SQL has renamed the column names of the query results:

The property name and the top query column names in the user class are inconsistent.

2.5.2.1 definition Resultmap

    <!--define RESULTMAP to make a mapping between the select ID id_,username username_ from the user and the attributes in the user class. Type:resultmap the final mapped Java object type, you can use the alias ID: Unique identifier for the Resultmap -    <Resultmaptype= "User"ID= "Userresultmap">        <!--The ID represents a unique identifier in the query result set: Column name Property:type the name of the attribute in the specified Pojo type, and finally resultmap to column and property as a mapping relationship (correspondence relationship) -        <IDcolumn= "Id_" Property= "id"/>        <!--Result: Define column for normal name Mapping: query out the name of the Property:type in the Pojo type specified by the name of the property in the final resultmap to column Make a mapping relationship with the property (corresponding relationship) -        <resultcolumn= "Username_" Property= "username"/>    </Resultmap>

2.5.2.2 using Resultmap as the output mapping type for statement

    <!-- use Resultmap for output mapping        Resultmap: Specify the ID of the defined resultmap, if this resultmap in the other mapper file, the front needs      to add namespace-- >    <ID= "Finduserbyidresultmap"  parametertype = "int" Resultmap = "Userresultmap" >         Select id id_,username username_ from USER WHERE id=#{value}    </Select >

2.5.2.3 Mapper.java

    // querying user information by ID, using RESULTMAP output     Public User finduserbyidresultmap (intthrows Exception;

2.5.2.4 Test

    @Test    publicvoidthrows  exception{        sqlsession Sqlsession=sqlsessionfactory.opensession ();        Usermapper usermapper=sqlsession.getmapper (usermapper.  Class);        User User=usermapper.finduserbyidresultmap (1);        SYSTEM.OUT.PRINTLN (user);        Sqlsession.close ();    }

2.6 Summary

Using Resulttype for output mapping, the column can be mapped successfully only if the queried column name matches the attribute name in the Pojo.

If the queried column name is inconsistent with the Pojo property name, a mapping is made between the column name and the Pojo property name by defining a resultmap.

If this article is helpful to you, please give me a reward!

Spring+springmvc+mybatis Deep learning and building (d)--mybatis input mapping and output mapping