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

Original address: HTTP://WWW.CNBLOGS.COM/SHANHEYONGMU/P/7121556.HTML1. 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.

<select 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:

<select 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%list<user> list = usermapper.selectuserbyname ("% Administrator%") to the parameter.

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

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--     <select 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 public    void Finduserbyusertest () throws 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 * @author Joanna.yan * */public class Userqueryvo {    //packaging the required query conditions//        user query conditions    private usercustom use Rcustom;    Public Usercustom Getusercustom () {        return usercustom;    }    public void Setusercustom (Usercustom usercustom) {        this.usercustom = Usercustom;    }        Other query conditions can be packaged, 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 property    #{usercustom.sex}: Remove the Pojo wrapper object in the gender value    ${usercustom.username }: Remove Pojo wrapper object in User name--    <select id= "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 list<usercustom> finduserlist (Userqueryvo userqueryvo) throws Exception;

1.4.5 Test Code

  @Test public    void Finduserlisttest () throws exception{        sqlsession sqlsession=sqlsessionfactory.opensession ( );        Usermapper Usermapper=sqlsession.getmapper (usermapper.class);        Create wrapper object, set query condition        userqueryvo userqueryvo=new userqueryvo ();        Usercustom usercustom=new Usercustom ();        Usercustom.setsex ("1");        Usercustom.setusername ("Zhang San Fung");        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--    <select id= "Finduserbyhashmap" parametertype= "HashMap" resulttype= " User ">        select * from user where Id=#{id} and username like '%${username}% '    </select>

The red callout in SQL is the HashMap key.

Mapper.java:

Pass HASHMAP Comprehensive query user information public list<user> finduserbyhashmap (hashmap<string, object> map) throws Exception;

Test:

   @Test public    void Finduserbyhashmaptest () throws exception{        sqlsession sqlsession= Sqlsessionfactory.opensession ();        Usermapper Usermapper=sqlsession.getmapper (usermapper.class);        Constructs the query condition HashMap object        hashmap<string, object> map=new hashmap<> ();        Map.put ("id", ten);        Map.put ("username", "Zhang San");        Pass HashMap object query user list        list<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-    <select id= "Findusercount" parametertype= "user" resulttype= "int" >        Select Count (*) from user    </select>

Mapper Interface:

Public interface Usermapper {    //Get the total number of user lists public    int findusercount (user user) throws Exception;}

Test:

@Test public    void Findusercounttest () throws exception{        sqlsession sqlsession=sqlsessionfactory.opensession ();        Create Usermapper object, MyBatis automatically generate Mapper proxy object        usermapper usermapper=sqlsession.getmapper (usermapper.class);        User User=new User ();        User.setusername ("Yan");        Method of calling Usermapper        int count=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

   <!--definition Resultmap        A mapping between the select ID id_,username username_ from the user and the attributes in the user class.          Type:resultmap The type of Java object that is finally mapped, you can use the alias          ID: The unique identifier for RESULTMAP-    <resultmap type= "user" id= " Userresultmap ">        <!--ID represents a unique identifier in the query result set            : Column name            property:type the name                    of the property in the specified Pojo type Finally resultmap a mapping relationship between column and property (correspondence)--        <id column= "id_" property= "id"/>        <!-- Result: Define column for normal name Mapping            : Query out the name of the            property                    in the specified Pojo type Property:type Finally resultmap a mapping relationship between column and property (correspondence)--        <result column= "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 other mapper files, the front needs to be namespace--    <select id= "Finduserbyidresultmap" parametertype= "int" resultmap= "Userresultmap" >        SELECT ID id_,username username_ from USER WHERE id=#{value}    </select>

2.5.2.3 Mapper.java

Query user information according to ID, use RESULTMAP output public user    finduserbyidresultmap (int id) throws Exception;

2.5.2.4 Test

   @Test public    void Finduserbyidresultmaptest () throws 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.

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