Mybatis series tutorial (5) -- handle the store procedure)

Other tools or technologies used:

Project management tool: Maven

Test Run tool: JUnit

Database: Derby

This section describes how to create a stored procedure in Derby and how to call the stored procedure in mybatis.

1. Create a stored procedure in Derby

1. Create a new normal Java project in eclipse named test_store_procedure

2. Create a class named storeprocedureoperationclass. class under the com. Freud. Practice package.

Package COM. freud. practice; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. preparedstatement; import Java. SQL. sqlexception; /***** Stored Procedure class ** @ author Freud **/public class storeprocedureoperationclass {/***** execute the inserted Stored Procedure ** @ Param ID * @ Param username * @ Param password * @ Param nickname * @ throws sqlexception */public static void insertdata (int id, string username, string pas Sword, string nickname) throws sqlexception {connection = drivermanager. getconnection ("JDBC: Default: Connection"); preparedstatement P = connection. preparestatement ("insert into user_test_tb (ID, username, password, nickname) values (?,?,?,?) "); P. setint (1, ID); p. setstring (2, username); p. setstring (3, password); p. setstring (4, nickname); system. out. println ("insert values (ID =" + ID + ", username =" + username + ", password =" + password + ", nickname =" + nickname + ") "commandid p.exe cuteupdate (); p. close (); connection. close ();}}

3. export data to c: \ Freud \ test_store_procedure.jar using the jar command or eclipse tool.

4. Declare the stored procedure in the IJ command line

CREATE PROCEDURE FREUD.INSERT_USER(IN THE_ID INTEGER,                   IN THE_USERNAME VARCHAR(20), IN THE_PASSWORD VARCHAR(20), IN THE_NICKNAME VARCHAR(20)) PARAMETER STYLE JAVA MODIFIES SQL DATA LANGUAGE JAVA EXTERNAL NAME 'com.freud.practice.StoreProcedureOperationClass.insertData';

5. call sqlj. install_jar ('C: \ Freud \ test_store_procedure.jar ', 'freud. test_sprocedure ', 0); import the jar package to Freud mode and name it test_sprocedure.

6. Call call syscs_util.syscs_set_database_property ('derby. database. classpath', 'freud. test_sprocedure ') on the IJ console, and add the jar package to the database classpath search path.

In this way, the Derby stored procedure is created. (For more information about the Derby stored procedure, see apache Derby)

2. Call the stored procedure in mybatis

Maven dependencies:

<dependencies><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.2.7</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.9</version><scope>test</scope></dependency><dependency><groupId>org.apache.derby</groupId><artifactId>derby</artifactId><version>10.10.2.0</version></dependency><dependency><groupId>org.apache.derby</groupId><artifactId>derbyclient</artifactId><version>10.10.2.0</version></dependency></dependencies>

Mybatis configuration file src/main/resource under the source directory

Test-mybatis-configuration.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><properties><property name="driver" value="org.apache.derby.jdbc.ClientDriver" /><property name="url"value="jdbc:derby://localhost:1527/freud;create=true" /></properties><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="${driver}" /><property name="url" value="${url}" /></dataSource></environment></environments><mappers><mapper resource="com/freud/practice/UserMapper.xml" /></mappers></configuration>  

User. Java object class (src/main/Java/COM/Freud/practice directory)

package com.freud.practice;public class User{private Integer id;private String username;private String password;private String nickname;public Integer getId(){return id;}public void setId(Integer id){this.id = id;}public String getUsername(){return username;}public void setUsername(String username){this.username = username;}public String getPassword(){return password;}public void setPassword(String password){this.password = password;}public String getNickname(){return nickname;}public void setNickname(String nickname){this.nickname = nickname;}}

Usermapper. xml mapper file (src/main/Java/COM. Freud. Practice directory)

<? XML version = "1.0" encoding = "UTF-8"?> <! Doctype mapper public "-// mybatis.org//dtd mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <mapper namespace =" com. Freud. Practice. usermapper "> <! -- Query --> <select id = "getuser" resulttype = "com. Freud. Practice. User"> select * From user_test_tb </SELECT> <! -- Call the stored procedure of insertion --> <insert id = "insertuser" statementtype = "callable"> call Freud. insert_user (# {ID}, # {username}, # {password}, # {nickname}) </Insert> </mapper>

Usermapper. Java mapper class (src/main/Java/COM. Freud. Practice directory)

package com.freud.practice;import java.util.List;public interface UserMapper{public List<User> getUser();public void insertUser(User user);}

Test class testmybatis. Java (src/test/Java/COM. Freud. Practice directory)

Package COM. freud. practice; import Java. io. inputstream; import Java. text. messageformat; import Java. util. list; import Org. apache. ibatis. session. sqlsession; import Org. apache. ibatis. session. sqlsessionfactory; import Org. apache. ibatis. session. sqlsessionfactorybuilder; import Org. JUnit. before; import Org. JUnit. test; public class testmybatis {private string source; private inputstream; private sqlsessionfactory; @ beforepublic void setup () {/*** prepare the mybatis Runtime Environment */source = "test-mybatis-configuration.xml"; inputstream = testmybatis. class. getclassloader (). getresourceasstream (source); sqlsessionfactory = new sqlsessionfactorybuilder (). build (inputstream) ;}@ testpublic void testinsert () {try {// get session connection sqlsession session = sqlsessionfactory. opensession (); // obtain mapperusermapper usermapper = session. getmapper (usermapper. class); system. out. println ("test insert start... "); // display the user information system before insertion. out. println ("before Insert"); this. printusers (usermapper. getuser (); // insert usermapper. insertuser (this. mockuser ("freu_ins_user", "freud_ins_pass", "freud_ins_nick"); // submit the transaction session. commit (); // displays the inserted user information system. out. println ("\ r \ nafter Insert"); this. printusers (usermapper. getuser (); system. out. println ("test insert finished... ");} catch (exception e) {e. printstacktrace () ;}}/*** assemble a user object ** @ return */public user mockuser (string username, string password, string nickname) {user = new user (); User. setid (50); User. setusername (username); User. setpassword (password); User. setnickname (nickname); Return user;}/***** print user information to the console ** @ Param users */private void printusers (final list <user> Users) {int COUNT = 0; For (User: Users) {system. out. println (messageformat. format ("= user [{0}] ======================", ++ count); system. out. println ("User ID:" + User. GETID (); system. out. println ("User Username:" + User. getUserName (); system. out. println ("User Password:" + User. getPassword (); system. out. println ("user nickname:" + User. getnickname ());}}}