IBatis (MyBatis): Working with Stored Procedures
Contents
1. Hi Chi Not yet Thanks September 14 2011 at 1 12 PM 4 Chi I meant to post this earlier but didn t get a chance I was able to get the results from a stored procedure call For reference here 1s the code public interface XYZMapper Select value call xyz_storedproc cld jdbcType NUMERIC mode IN Results t Result property severity column Severity javaType Strng class jdbcType JdbcType VARCHAR Result property descr column Descr javaType String class jdbcType JdbcType VARCHAR j Options statementT ype StatementType CALLABLE public List execute X YZParams xY ZParams j January 25 2012 at 4 36 PM Batis MyBatis Working with Dynamic Queries SOL JAXB Custom Binding Java util Date Spring 3 Serialization My Books loianeg roner con 201 1 03 ibatis mybatis working with stored procedures 10 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner Mastering Ext JS RAW Loiane Grone Loiane Groner Connect Connect with us on the following social media platforms DONDE We re on Follow RIGO loianegroner com 2011 03 ibatis mybatis working with stored procedures 11 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner Buy me a coffee Do you like this blog Feel free to donate dollar or any amount you want Categories Select Categ2. IMPORTANT Oracle ref cursors are usually returned as parameters NOT directly from the stored proc So with ref cursors resultMap and or resultType is usually not used First Example We want to call the procedure getTota City and this procedure only have one OUT parameter and no IN INOUT parameter How to do tt We are going to ser inline parameters in this first example To use inline parameters create a POJO class to represent your parameters set the parameterType to the class you created and you are going to use this notation to represent each parameter parameterName mode OUT jdbcType INTEGER e mode canbe IN OUT INOUT e and specify the jdbcType of your parameter To create the Mybatis XML configuration you can use the select ou update tag Do not forget to set the statementType to CALLABLE Here is how our MyBatis statement 1s going to look like 1 lt select id callGetTotalCity parameterType Param statementType C 2 CALL getTotalCity total mode OUT jdbcType INTEGER 3 lt select gt ED ooo E And this is the POJO class which represents the parameter for getTotalCity procedure 1 package com loiane model 2 3 public class Param 4 5 private int total 6 7 public int getTotal 8 return total 9 j 10 11 public void setTotal int total 12 this total total 13 14 Second Example Now we are going to try to call the same stored procedure we demonstrated on th
3. WHERE state _id stateld END DELIMITER USE blog ibatis DROP procedure IF EXISTS getStates DELIMITER USE blog ibatis CREATE PROCEDURE blog ibatis getStates BEGIN SELECT state_id state code state name FROM state END DELIMITER 1 SPMapper XML IN stateId SMAI I did not find anything on the user manual about how to call stored procedures so I decided to search on the mailing list And I found some tips of how to call stores procedures On the previous version iBatis has a special XML tag for stored procedures But there is no XML tag for it on current MyBatis version version 3 To calla stored procedure usgin MyBatis iBatis 3 you will have to follow some tips Se o Must set the statement type to CALLABLE Must use the JDBC standard escape sequence for stored procedures call xxx parml parm2 Must set the MODE ofall parameters IN OUT INOUT All IN OUT and INOUT parameters must be a part of the parameterType or parameterMap discouraged The only exception is if you are usmg a Map as a parameter object In that case you do not need to add OUT parameters to the map before calling MyBatis will add them for you loianeg roner con 201 1 03 ibatis mybatis working with stored procedures 3 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner automatically 5 resultType or resultMap more typically is only used if the procedure returns a result set 6
4. com loiane test Hl H TestStoredProcedureDAQ java a c lib gm junit 4 8 2 jar i mybats 3 0 3 jar a mysql connector java 5 1 12 bin jar 5 cr sgl oe AR procedures sql _ he FA script sql B readme Sample Database Please run the script into your database before getting started with the project implementation You will find the script with dummy data inside the sql folder M state_id SMALLINT 9 country id SMALLINT city_id INT n de CHAR 2 country id SMALLINT i 2 country code Ri 2 H state id SMALLINT D lt gt state_code CHAR 2 7 amp country nam e VARCHAR 50 l Eee daty name VARCHAR 50 lt gt state_name VARCHAR 50 gt Ls C kooo j As we are going to work with stored procedures you will also have to execute a script with procedures Here are the procedures USE blog ibatis DROP procedure IF EXISTS getTotalCity DELIMITER USE blog_ibatis CREATE PROCEDURE blog ibatis getTotalCity OUT total INTEGER BEGIN SELECT count into total NOU BWDN BP loianeg roner com 201 1 03 ibatis mybatis working with stored procedures 2 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner FROM city END DELIMITER USE blog ibatis DROP procedure IF EXISTS getTotalCityStatelId DELIMITER USE blog ibatis CREATE PROCEDURE blog ibatis getTotalCityStateId BEGIN SELECT count into total FROM city
5. 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner Lojane Groner My development notes e Home e Contact e Blog e About Me e ExtJS Plugins o PagingToolbarResizer Enter Search Terms search Welcome to Loiane Groner IBatis MyBatis Working with Stored Procedures March 29 2011 By Loiane This tutorial will walk you through how to setup iBatis MyBatis ina simple Java project and will present how to work with stored procedures using MySQL The goal os this tutorial is to demonstrate how to execute call stored procedures using iBatis MyBatis W mybatis SOIL Mapping Framework for Java IBATIS Pre Requisites For this tutorial I am using IDE Eclipse you can use your favorite one DataBase MySOL Libs jars Mybatis MySOL conector and JUnit for testing This is how your project should look like loianeg roner com 201 1 03 ibatis mybatis working with stored procedures 1 12 04 09 13 IBatis MyBatis Working with Stored Procedures Loiane Groner a i ibatis stored procedures ibatis stored procedures master 5 i src a i com loiane dao E 4 MyBatisConnectionFactory java 3 l 44 StoreProcedureDAQ java a iA com loiane data H E SPMapper java ue DA SPMapper xml on com loiane model al Fy Param java 44 Param2 java He E State java a A SqilMapConfig xml JRE System Library JavaSE 1 6 BA Referenced Libraries E test 5 H
6. 3 ibatis mybatis working with stored procedures 6 12 04 09 13 IBatis MyBatis Working with Stored Procedures Loiane Groner Annotation for First Example XML 1 Select value CALL getTotalCity total mode OUT jdbcType INT 2 Options statementType StatementType CALLABLE 3 Object callGetTotalCityAnnotations Param param 2 It is very similiar to a simple select statement but we have to set the statement type to CALLABLE To do it we can use the annotation Options With annotations we can only use inline parameters so we will not be able to represent the second exemple using annotations Annotation for Third Example XML The explanation is the same as first example I am just going to list the code 1 Select value CALL getTotalCityStateId stateId mode IN jdbc 2 Options statementType StatementType CALLABLE 3 Object callGetTotalCityStateIdAnnotations Param2 param2 E H Annotation for Fourth Example XML I tried to set the fourth example with annotation but the only thing I ve got is this 1 TODO set resultMap with annotations 2 Select value CALL getTotalCityStateId 3 Options statementType StatementType CALLABLE 4 Results value 5 Result property id column state_id 6 Result property name column state_name 7 Result property code column state_code 8 3 9 9 List lt State gt callGetStatesAnnotations An
7. Total int total 12 this total total 13 14 public int getStateId 15 return stateld 16 17 public void setStateId int statelId loianeg roner con 201 1 03 ibatis mybatis working with stored procedures 5 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner 18 this stateId statelId 19 20 Fourth Example Now let s try to retrieve a resultSet from the stored procedure For this we are going to use a resultMap 1 lt resultMap type State id resultState gt 2 lt result property id column state_id gt 3 lt result property name column state_ name gt 4 lt result property code column state_ code gt 5 lt resultMap gt 6 7 lt select id callGetStates resultMap resultState statementType CALI 8 CALL getStates 9 lt select gt E gt State POJO class 1 package com loiane model 2 3 public class State 4 5 private int id 6 private String code 7 private String name 8 9 public int getId 10 return id 11 12 public void setId int id 13 this id id 14 15 public String getCode 16 return code 17 18 public void setCode String code 19 this code code 20 21 public String getName 22 return name 23 24 public void setName String name 25 this name name 26 27 2 SPMapper Annotations Now let s try to do the same thing we did usmg XML config loianeg roner com 201 1 0
8. d it does not work I tried to search on the mailing list no luck I could not find a way to represent a resultMap with annotation and stored procedures I don t know if it is a limitation If you have any clue how to do it please leave a comment I will appreciate tt m Download If you want to download the complete sample project you can get it from my GitHub account https github com7 loiane ibatis stored procedures If you want to download the zip file of the project just click on download loianeg roner com 201 1 03 ibatis mybatis working with stored procedures 7 12 04 09 13 Downloads for loiane ibatis helloworld Batis MyBatis Working with Stored Procedures Loiane Groner There are more articles about iBatis to come Stay tooned Happy Coding E e Post to Delicious 5 You may also like HE D eee ed feeb rerhly a Imp orting an Excel Spreadsheet into an ExtJS DataGrid usi mybatis SOL Mapping Framewor Getting Started with iBatis M y Batis Annotations hemp Mocaihort Eiee port ssoced B g mazar ERTELE IMEL iHi ero mbn LTH ha iit ted Bete iota ExtJS How to Export DataGrid to Excel ExtJS 4 File Upload Spring M VC 3 Examp le mybatis SOL Mapping Framewor Introduction to iBatis My Batis An alternative to Hiber extjs4 grid header tooltip extjsd gay exta b gt resources gt D ext all_js PSS ee ExtJS 4 How to add Tooltip to G
9. e first example but we are going to use a parameterMap like you used to do in version 2 x loianeg roner con 201 1 03 ibatis mybatis working with stored procedures 4 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner A very important note this is discouraged please use mline parameters Let s declare the Param POJO class as a parameterMap lt parameterMap type Param id testParameterMap gt lt parameter property total jdbcType INTEGER mode OUT gt 1 2 3 lt parameterMap gt And the stored procedure statment lt update id callGetTotalCity2 parameterMap testParameterMap statei CALL getTotalCity lt update gt 1 2 3 gt Note that now we use question mark to represent each parameter Third Example Now we are going to call a stored procedure with IN and OUT parameters Let s follow the same rules as the fisrt example We are going to use inline parameters and we are going to create a POJO class to represent our parameter MyBatis code 1 lt select id callGetTotalCityStateId parameterType Param2 statemen 2 CALL getTotalCityStateId 3 stateId mode IN jdbcType INTEGER 4 total mode OUT jdbcType INTEGER 5 lt select gt eee 2 Param POJO 1 package com loiane model 2 3 public class Param2 4 5 private int total 6 private int stateId 7 8 public int getTotal 9 return total 10 11 public void set
10. ory Loiane Groner e Contact e Blog e About Me e ExtJS Plugins 2013 Loiane Groner All rights reserved loianeg roner com 201 1 03 ibatis mybatis working with stored procedures 12 12
11. rid Header Filed in iBatis MyBatis Tags iBatis Java MyBatis mySQL stored procedure Comments 6 loianeg roner con 201 1 03 ibatis mybatis working with stored procedures 8 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner Sunil Vashisth Thanks for this Nice Article Very clear and concise May 23 2011 at 9 01 AM Santosh Hi I checked your code https github cony loiane ibatis stored procedures But 1 want call getTotalCity procedure without using annotaion code SqlMapper java Using Session object I am trying to do this Session session sqlSessionFactory openSession ExecutorType SIMPLE true Param pobj new Param Param p session selectList getTotalCity poby System out prinin Result p getTotalQ DEBUG main gt Executing CALL getTotalCity DEBUG main gt Parameters Null pointer exception Can you please help me out June 3 2011 at 9 22 AM Loiane Hi Santosh Please check the class StoreProcedureDAO java that is within the source code You will find how to call it in this class July 25 2011 at 10 15 AM loianeg roner com 201 1 03 ibatis mybatis working with stored procedures 9 12 04 09 13 Batis MyBatis Working with Stored Procedures Loiane Groner 3 Chi Any luck with using the annotations to deal with results ofa stored procedure call Thanks August 29 201 1 at 7 15 PM Loiane
Download Pdf Manuals
Related Search