1.結構
2.準備資料
建表和插入資料
1 CREATE TABLE p_user(
2 id INT PRIMARY KEY AUTO_INCREMENT,
3 name VARCHAR(10),
4 sex CHAR(2)
5 );
6 INSERT INTO p_user(name,sex) VALUES(\'A\',"男");
7 INSERT INTO p_user(name,sex) VALUES(\'B\',"女");
8 INSERT INTO p_user(name,sex) VALUES(\'C\',"男");
建立存儲過程
1 DELIMITER $
2 CREATE PROCEDURE mybatis.get_user_count(IN sex_id INT,OUT user_count INT)
3 BEGIN
4 IF sex_id=0 THEN
5 SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex=\'女\' INTO user_count;
6 ELSE
7 SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex=\'男\' INTO user_count;
8 END IF;
9 END
10 $
3.調用存儲過程
SET @user_count=0;
CALL mybatis.get_user_count(1, @user_count);
SELECT @user_count; 在mysql中運作得：
4.建立實體類
1 package com.cao.bean;
2
3 public class PUser {
4 private String id;
5 private String name;
6 private String sex;
7 public PUser() {}
8 public PUser(String id,String name,String sex) {
9 this.id=id;
10 this.name=name;
11 this.sex=sex;
12 }
13 public String getId() {
14 return id;
15 }
16 public void setId(String id) {
17 this.id = id;
18 }
19 public String getName() {
20 return name;
21 }
22 public void setName(String name) {
23 this.name = name;
24 }
25 public String getSex() {
26 return sex;
27 }
28 public void setSex(String sex) {
29 this.sex = sex;
30 }
31 @Override
32 public String toString() {
33 return "User [id=" + id + ", name=" + name + ", sex=" + sex + "]";
34 }
35
36
37 } 4.映射檔案
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="procedure">
6 <!-- 查詢男性或者女性的數量，如果輸入的是0，則是女性 -->
7 <select id="getCount" parameterMap="getUserCount" statementType="CALLABLE">
8 CALL mybatis.get_user_count(?,?)
9 </select>
10 <parameterMap type="java.util.Map" id="getUserCount">
11 <parameter property="SexId" mode="IN" jdbcType="INTEGER"/>
12 <parameter property="UserCount" mode="OUT" jdbcType="INTEGER"/>
13 </parameterMap>
14 </mapper> 5.配置檔案
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
3
4 <configuration>
5 <environments default="development">
6 <environment id="development">
7 <transactionManager type="JDBC">
8 <property name="" value=""/>
9 </transactionManager>
10 <dataSource type="UNPOOLED">
11 <property name="driver" value="com.mysql.jdbc.Driver"/>
12 <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
13 <property name="username" value="root"/>
14 <property name="password" value="123456"/>
15 </dataSource>
16 </environment>
17 </environments>
18
19 <mappers>
20 <mapper resource="com/jun/sql/config/user.xml"/>
21 </mappers>
22
23 </configuration> 6.測試類
1 package com.jun.main;
2
3 import java.io.IOException;
4 import java.io.Reader;
5 import java.util.HashMap;
6 import java.util.Map;
7
8 import org.apache.ibatis.io.Resources;
9 import org.apache.ibatis.session.SqlSession;
10 import org.apache.ibatis.session.SqlSessionFactory;
11 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
12 import org.junit.Test;
13 public class MainTest {
14 /**
15 * 方式一
16 * @throws Exception
17 */
18 @Test
19 public void test1() throws Exception {
20 Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
21 SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
22 SqlSession sqlSession=sqlSessionFactory.openSession(true); //true後是自動送出
23
24 String statement="procedure.getCount";
25
26 Map<String,Integer> parameterMap=new HashMap<>();
27 parameterMap.put("SexId", 1);
28 parameterMap.put("UserCount", 0);
29 sqlSession.selectOne(statement, parameterMap);
30 Integer result=parameterMap.get("UserCount");
31 System.out.println("result="+result);
32 sqlSession.close();
33 }
34
35
36 } 7.效果