MyBatis calling the MySQL stored procedure

1. Introduction to Stored Procedures

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed.

2. Stored Procedure Benefits

1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.

2. Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.

3. Stored procedures can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.

4. Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.

5. Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

3. Stored Procedure Disadvantages

1. Difficult to maintain, once the logic has changed to change the trouble
2. If the person writing this stored procedure leaves, the person who took over her code is a disaster, because someone else has to read your program logic and read your storage logic. Not conducive to expansion.
3. The biggest drawback! Although stored procedures can reduce the amount of code, improve development efficiency. But one thing that is very deadly is that it consumes too much performance.

4. Syntax for stored procedures 4.1 creating stored procedures

createprocedure sp_name()begin.........end

4.2 Calling a stored procedure

call sp_name()

Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

4.3 Deleting a stored procedure

procedure sp_name//

Note: You cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

4.4 Other common commands

procedure status

Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.

createprocedure sp_name

Show details of a MySQL stored procedure

5.MyBatis example of a MySQL stored procedure implementation 5.1 Simple description of the case

The case is mainly implemented by a simple statistic of the total number of devices of a certain name.

5.2 Creation of database tables

 DROP TABLE IF EXISTS ' Cus_device ';CREATE TABLE ' Cus_device '(' DEVICE_SN ' varchar( -) not NULLCOMMENT' Device number ',' device_cat_id ' int(1)DEFAULT NULLCOMMENT' Device type ',' Device_name ' varchar( -)DEFAULT NULLCOMMENT' Device name ',' Device_type ' varchar( -)DEFAULT NULLCOMMENT' device model ',PRIMARY KEY(' DEVICE_SN ')) Engine=innodbDEFAULTCharset=utf8;

5.3 Creating a stored procedure

Use the device name as input parameter to count the total number of devices as output parameters

DROP PROCEDURE IF EXISTS 'countdevicesname';DELIMITER;;CREATE   PROCEDURE 'countdevicesname'( in Dname VARCHAR), out Devicecount INT)BEGINSELECT COUNT(*)  into Devicecountfrom cus_device WHERE device_name = dname;  END;;D Elimiter;

5.4 MyBatis calls the MySQL stored procedure 1. Mybatis-config.xml

<?xml version= "1.0" encoding= "UTF-8"?>  <! DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" >  <configuration>      <settings>    <!--print query statements --        <setting name="Logimpl" value="stdout_logging" />    </Settings>    <!--configuration aliases --      <typealiases>          <typealias type="Com.lidong.axis2demo.DevicePOJO" alias=" Devicepojo " />       </typealiases>      <!--Configuring environment variables --      <environments Default="Development">          <environment ID="Development">              <transactionmanager type="JDBC" />              <dataSource type="Pooled">                  < property name="Driver" value="Com.mysql.jdbc.Driver" />                   < property name="url" value="jdbc:mysql://127.0.0.1:3306 /BMS?CHARACTERENCODING=GBK " />                  < property name="username" value="root" />                  < property name="password" value="123456" />              </dataSource>          </Environment>      </environments>      <!--configuration Mappers --      <mappers>          <mapper Resource="Com/lidong/axis2demo/devicemapper.xml" />      </mappers>  </configuration>

2. Cusdevice.java

 Public classdevicepojo{PrivateString Devocename;//Device name     PrivateString Devicecount;//Total number of devices     PublicStringGetdevocename() {returnDevocename; } Public void Setdevocename(String devocename) { This. devocename = Devocename; } PublicStringGetdevicecount() {returnDevicecount; } Public void Setdevicecount(String Devicecount) { This. Devicecount = Devicecount; }}

3. Implementation of Devicedao

package com.lidong.axis2demo;publicinterface DeviceDAO {    /**     * 调用存储过程 获取设备的总数     * @param devicePOJO     */    publicvoidcount(DevicePOJO devicePOJO);}

Implementation of the 4.Mapper

<?xml version= "1.0" encoding= "UTF-8"?>  <! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="Com.lidong.axis2demo.DeviceDAO">    <resultmap id="Baseresultmap" type="Cusdevicepojo">        <result column= "device_sn" property ="Device_sn" Jdbctype="VARCHAR" />    </resultmap>    <SQL id="Base_column_list">DEVICE_SN, Device_name,device_mac</SQL>    <select id="Count" parametertype="Devicepojo"  UseCache="false"statementtype="callable">                  <! [cdata[Call Countdevicesname (#{devocename,mode=in,jdbctype=varchar}, #{devicecount,mode=out,jdbcty        Pe=integer}); ]]>    </Select></mapper>  

Note: statementtype= "callable" must be callable, tell MyBatis to execute the stored procedure, otherwise it will error
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException

The Mode=in input parameter mode=out the output parameter jdbctype the field type defined for the database.
This writing mybatis will help us automatically backfill the value of the output devicecount.

5. Testing

 PackageCom.lidong.axis2demo;ImportJava.io.IOException;ImportJava.io.Reader;ImportOrg.apache.ibatis.io.Resources;ImportOrg.apache.ibatis.session.SqlSession;ImportOrg.apache.ibatis.session.SqlSessionFactory;ImportOrg.apache.ibatis.session.SqlSessionFactoryBuilder;/** * MyBatis Execute stored procedure * @author Administrator * */ Public  class testproduce {    Private StaticSqlsessionfactorybuilder Sqlsessionfactorybuilder;Private StaticSqlsessionfactory sqlsessionfactory;Private Static void Init()throwsIOException {String resource ="Mybatis-config.xml";          Reader reader = Resources.getresourceasreader (Resource); Sqlsessionfactorybuilder =NewSqlsessionfactorybuilder ();      Sqlsessionfactory = Sqlsessionfactorybuilder.build (reader); } Public Static void Main(string[] args)throwsException {testcallproduce (); }/** * @throws ioexception * *    Private Static void Testcallproduce()throwsIOException {init ();          Sqlsession session= sqlsessionfactory.opensession ();          Devicedao Devicedao = Session.getmapper (Devicedao.class); Devicepojo device =NewDevicepojo (); Device.setdevocename ("Device Name");        Devicedao.count (device); System.out.println ("Get"+device.getdevocename () +"total number of devices ="+device.getdevicecount ()); }}

Results

MyBatis calling the MySQL stored procedure