MyBatis Example Stored procedure (i)

This example is an example of the first stored procedure in MyBatis from getting started to mastering, and the underlying environment for this example can be obtained from http://mybatis.tk or Https://github.com/mybatis-book/book, and the sample source code The Simple-all.

Stored procedures are common in databases, although most stored procedures are complex, but are used the same way when using MyBatis calls, so we use a simple stored procedure in this section to understand how stored procedures are used in MyBatis. Basic Preparation

The stored procedure involves the table sys_user, and the table statements are as follows.

DROP TABLE IF EXISTS ' sys_user ';
CREATE TABLE ' sys_user ' (
  ' id ' bigint () not NULL auto_increment COMMENT ' user ID ',
  ' user_name ' varchar (DEFAULT) Null COMMENT ' username ',
  ' user_password ' varchar ($) ' Default ' NULL COMMENT ' password ',
  ' user_email ' varchar (default ' tes ') t@mybatis.tk ' COMMENT ' email ',
  ' user_info ' text COMMENT ' profile ',
  ' head_img ' blob COMMENT ' Avatar ',
  ' create_time ' DateTime default NULL COMMENT ' Create Time ',
  PRIMARY KEY (' id ')
) engine=innodb auto_increment=1035 DEFAULT charset= UTF8 comment= ' user table ';

Prepare the test data as follows.

INSERT into ' sys_user ' VALUES (' 1 ', ' admin ', ' 123456 ', ' admin@mybatis.tk ', ' Admin user ', 0x1231231230, ' 2016-06-07 01:11:12 ');
INSERT into ' sys_user ' VALUES (' 1001 ', ' Test ', ' 123456 ', ' test@mybatis.tk ', ' Test user ', 0x1231231230, ' 2016-06-07 00:00: 00 ');

The corresponding entity classes Sysuser as follows:

/**
 * User table */public
class Sysuser implements Serializable {
    private static final long Serialversionuid = 1 L;
    /**
     * User ID */
    private Long ID;
    /**
     * User name */
    private String userName;
    /**
     * Password * */
    private String userpassword;
    /**
     * * Mailbox */
    private String useremail;
    /**
     * Introduction * */
    private String userInfo;
    /**
     * Avatar */
    private byte[] headimg;
    /**
     * Create time */
    private Date createtime;
    Omit getter and Setter
}

Build Stored Procedures

Let's first create the following stored procedure.

# The first stored procedure
# Querying user's other information based on user ID
# method looks very wonderful, but shows a number of output parameters
DROP PROCEDURE IF EXISTS ' select_user_by_id ';
DELIMITER;;
CREATE PROCEDURE ' select_user_by_id ' (
in UserId BIGINT, out
userName varchar (), out
userpassword varchar (a), out
useremail VARCHAR (+), out
userInfo TEXT, out
headimg BLOB, out
createtime DATETIME)
BEGIN
# Querying other data based on user ID
select User_name,user_password,user_email,user_info,head_img,create_time
Into Username,userpassword,useremail,userinfo,headimg,createtime from 
sys_user
WHERE id = userId;
END
;;
DELIMITER;

To create an XML method

<select id= "Selectuserbyid" statementtype= "callable" usecache= "false" >
    {call select_user_by_id (
        #{id , mode=in}, 
        #{username, Mode=out, Jdbctype=varchar},
        #{userpassword, Mode=out, Jdbctype=varchar},
        #{ UserEmail, Mode=out, Jdbctype=varchar},
        #{userinfo, Mode=out, Jdbctype=varchar},
        #{headimg, Mode=OUT, Jdbctype=blob, javatype=_byte[]},
        #{createtime, Mode=out, Jdbctype=timestamp}
    )
</select>

In the method that calls the stored procedure, we need to set StatementType to callable, when calling the stored procedure in the Select element, because the stored procedure does not support the MyBatis level two cache (described later in this section), in order to avoid the cache configuration causing an error, We directly set the UseCache property of the Select element to False.

When using parameters in a stored procedure, in addition to writing the necessary property names, you must specify mode (mode) of the parameter, the optional value is in, out, INOUT three, the input parameter uses in, the out parameter uses out, and the output parameter uses INOUT. From the above code can be easily seen in and out of the difference between the two modes, that is the parameters of out mode, you must specify Jdbctype. This is because in the in mode, MyBatis provides the default Jdbctype, which is not provided in the Out mode, so jdbctype must be specified, and Jdbctype must also be specified when the Oracle database is used if there is null in the import parameter.

In addition to these points mentioned above, Headimg also set a special javatype. In MyBatis-mapped Java classes we do not recommend the use of basic types, but the Java type corresponding to the database BLOB type is usually written as a byte[] byte array, because the byte[] array does not have a default value, so it does not affect our general use. However, if you do not specify Javatype, MyBatis uses the Byte type by default. Since we use byte is the basic type, so when setting Javatype, the basic type to use the underlined type, here is the byte[],_byte corresponds to the basic type, byte corresponds to the byte type, you must pay attention when using Javatype. Creating Interfaces

/**
 * Use stored procedures to query user information
 * 
 * @param user
 * @return *
/void Selectuserbyid (Sysuser user);

Since we do not have a return value for this stored procedure (not to be confused with the out parameter), we return the value type using void, and if you set the return value to Sysuser or list<sysuser> it will not be an error, but any time the return value is null. Writing tests

@Test public
void Testselectuserbyid () {
    sqlsession sqlsession =//Get Sqlsession method
    try {
        // The XML and interface of this example are defined in Usermapper
        usermapper usermapper = Sqlsession.getmapper (usermapper.class);
        Sysuser user = new Sysuser ();
        User.setid (1L);
        Usermapper.selectuserbyid (user);
        Assert.assertnotnull (User.getusername ());
        System.out.println ("User name:" + user.getusername ());
    } finally {
        sqlsession.close ();
    }
}

Execute the test and output the following log:

DEBUG [main]-==>  Preparing: {call select_user_by_id (?,?,?,?,?,?,?)} 
DEBUG [main]-==> parameters:1 (Long)
user name: admin

When using the out-of-parameter method, we typically use the properties in the object to receive the value of the parameter, or use the Map type method to receive the return value. There is a big difference between the two cases. When we use the POJO object to receive the parameter, we must ensure that all the arguments in the POJO have corresponding attributes exist, otherwise it will throw a similar "Could not set property ' xxx '" error, because the POJO object does not exist in the corresponding setter method guide of the cause. You do not need to have this property when using the map type, and when map receives a parameter for the stored procedure, you can obtain the value of the parameter through the Get ("Property name") method of the Map object. Error Hints

In addition to the errors mentioned above, when you execute a stored procedure, you may also encounter the following error:

Parameter number x is a out Parameter

The possible cause of this error is because the stored procedure you called does not exist, or the out parameter written in the MyBatis does not correspond to the parameters of the database stored procedure.