MyBatis调用存储过程实战

最新推荐文章于 2025-09-25 02:45:00 发布

原创最新推荐文章于 2025-09-25 02:45:00 发布 · 326 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

MyBatis

本文详细介绍了一个基于MyBatis的存储过程应用案例，包括数据库设计、领域模型、会话工厂、Mapper接口及XML配置、配置文件设定以及测试流程。通过具体代码演示了如何使用存储过程进行数据的增删改查操作。

一数据库设计

1 新建数据表

CREATE TABLE `tb_user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(18) DEFAULT NULL,
  `sex` CHAR(2) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
);

2 插入数据的存储过程

CREATE PROCEDURE insert_user
           (OUT v_id INTEGER,IN v_name VARCHAR(18),IN v_sex  VARCHAR(19),IN v_age INTEGER)
           BEGIN
           # 执行insert操作，values中的值是传入的参数
           INSERT INTO tb_user(NAME,sex,age)  VALUES(v_name,v_sex,v_age);
           # 将insert成功自动生成的主键id值设置到变量v_id中返回
           SET v_id = LAST_INSERT_ID();
           END

3 查询数据的存储过程

    CREATE PROCEDURE select_user()
    BEGIN
    SELECT id,NAME,sex,age FROM tb_user;
    END

4 根据id查询数据的存储过程

     CREATE PROCEDURE select_user_by_id(IN v_id INTEGER)
     BEGIN
     SELECT id,NAME,sex,age FROM tb_user WHERE id = v_id;
     END

5 修改数据的存储过程

     CREATE PROCEDURE update_user
     (IN v_id INTEGER,IN v_name VARCHAR(18),IN v_sex  VARCHAR(19),IN v_age INTEGER)
     BEGIN
     UPDATE tb_user SET NAME = v_name,sex = v_sex,age = v_age
     WHERE id = v_id;
     END

6 删除数据的存储过程

    CREATE PROCEDURE delete_user_by_id(IN v_id INTEGER)
    BEGIN
    DELETE FROM tb_user WHERE id = v_id;
    END

二领域模型

package org.fkit.domain;
import java.io.Serializable;

public class User implements Serializable{
     
     private static final long serialVersionUID = 1L;
     
     private Integer id;
     private String name;
     private String sex;
     private Integer age;
     
     public User() {
           super();
           // TODO Auto-generated constructor stub
     }
     
     public User( String name, String sex, Integer age) {
           super();
           this.name = name;
           this.sex = sex;
           this.age = age;
     }
     public Integer getId() {
           return id;
     }
     public void setId(Integer id) {
           this.id = id;
     }
     public String getName() {
           return name;
     }
     public void setName(String name) {
           this.name = name;
     }
     public String getSex() {
           return sex;
     }
     public void setSex(String sex) {
           this.sex = sex;
     }
     public Integer getAge() {
           return age;
     }
     public void setAge(Integer age) {
           this.age = age;
     }
     @Override
     public String toString() {
           return "User [id=" + id + ", name=" + name + ", sex="  + sex + ", age="
                     + age + "]";
     }
     
     
}

三会话工厂

package org.fkit.factory;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class FKSqlSessionFactory {
    
    private static SqlSessionFactory sqlSessionFactory = null;
    
    // 初始化创建SqlSessionFactory对象
    static{
        try (// 读取mybatis-config.xml文件
                InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            ){
            sqlSessionFactory = new SqlSessionFactoryBuilder()
                    .build(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // 获取SqlSession对象的静态方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }

    // 获取SqlSessionFactory的静态方法
    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

}

四 mapper

1 UserMapper

package org.fkit.mapper;

import java.util.List;
import org.fkit.domain.User;

public interface UserMapper {

    /**
     * 插入数据
     * @param user 包含要查询的User信息
     */
    void saveUser(User user);
    
    /**
     * 查询所有数据
     * @return List<User> 包含所有User数据的List集合
     */
    List<User> selectUser();
    
    /**
     * 根据id查询对应的数据
     * @param id 用户的id
     * @return User 包含用户信息的User对象
     */
    User selectUserById(Integer id);
    
    /**
     * 修改数据
     * @param user 包含要修改的User信息
     */
    void modifyUser(User user);
    
    /**
     * 删除数据
     * @param user 包含要修改的User信息
     */
    void removeUser(User user);
    
}

2 UserMapper.xml

<?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">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.UserMapper">

  <insert id="saveUser" parameterType="org.fkit.domain.User"  statementType="CALLABLE">
      {call insert_user(
      #{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN}
      )}
  </insert>
  
   <select id="selectUser" resultType="org.fkit.domain.User"  statementType="CALLABLE">
      {call select_user()}
  </select>
  
    <select id="selectUserById" parameterType="int"  resultType="org.fkit.domain.User" statementType="CALLABLE">
      {call select_user_by_id(#{id,mode=IN})}
  </select>
  
   <update id="modifyUser" parameterType="org.fkit.domain.User"  statementType="CALLABLE">
      {call update_user(
      #{id,mode=IN},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN}
      )}
  </update>
  
   <delete id="removeUser" parameterType="org.fkit.domain.User"  statementType="CALLABLE">
      {call delete_user_by_id(#{id,mode=IN})}
  </delete>
  
</mapper>

五配置文件

1 db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis
username=root
password=123456

2 log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">  
<log4j:configuration  xmlns:log4j="http://jakarta.apache.org/log4j/">  
    <appender name="STDOUT"  class="org.apache.log4j.ConsoleAppender">  
        <layout class="org.apache.log4j.PatternLayout">  
            <param name="ConversionPattern" value="%5p [%t] %m%n"  />  
        </layout>  
    </appender>   
     <logger name="org.fkit.mapper">  
        <level value="DEBUG" />  
    </logger>
    <root>  
        <level value="ERROR" />  
        <appender-ref ref="STDOUT" />  
    </root>  
</log4j:configuration>

3 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">
  <!--  XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
     <properties resource="db.properties"/>
     <!-- 指定 MyBatis 所用日志的具体实现 -->
     <settings>
           <setting name="logImpl" value="LOG4J"/>
           <!-- 要使延迟加载生效必须配置下面两个属性 -->
           <setting name="lazyLoadingEnabled" value="true"/>
           <setting name="aggressiveLazyLoading" value="false"/>
     </settings>
     <environments default="mysql">
     <!-- 环境配置，即连接的数据库。 -->
    <environment id="mysql">
    <!--  指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
      <transactionManager type="JDBC"/>
      <!--  dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
       <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <!-- mappers告诉了MyBatis去哪里找持久化类的映射文件 -->
  <mappers>
      <mapper resource="org/fkit/mapper/UserMapper.xml"/>
  </mappers>
</configuration>

六测试

package org.fkit.test;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;
import org.fkit.domain.User;
import org.fkit.factory.FKSqlSessionFactory;
import org.fkit.mapper.UserMapper;


public class ProcedureTest {

    public static void main(String[] args)  {
        // 定义SqlSession变量
        SqlSession sqlSession = null;
        try {
            // 创建SqlSession实例
            sqlSession = FKSqlSessionFactory.getSqlSession();
            ProcedureTest t = new ProcedureTest();
            // 测试插入数据
//            t.testSaveUser(sqlSession);
            // 测试查询所有数据
//            t.testSelectUser(sqlSession);
            // 测试根据id查询对应的数据
//            t.testSelectUserById(sqlSession);
            // 测试修改数据
//            t.testModifyUser(sqlSession);
            // 测试删除数据
            t.testRemoveUser(sqlSession);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        }finally {
            // 关闭SqlSession
            if(sqlSession != null)
                sqlSession.close();
        }
    }
    
    // 测试插入数据
    public void testSaveUser(SqlSession sqlSession){
        // 获得UserMapper接口的代理对象
        UserMapper um = sqlSession.getMapper(UserMapper.class);
        User user = new User("test","男",23);
//        User user = new User("jack","男",20);
//        User user = new User("rose","女",18);
//        User user = new User("tom","男",21);
//        User user = new User("mary","女",19);
//        User user = new User("alice","女",18);
//        User user = new User("scott","男",25);
        // 调用保存User的方法
        um.saveUser(user);
        System.out.println("插入成功，返回的id = " + user.getId());
    }
    
    // 测试查询所有数据
    public void testSelectUser(SqlSession sqlSession){
        // 获得UserMapper接口的代理对象
        UserMapper um = sqlSession.getMapper(UserMapper.class);
        // 调用查询所有User数据的方法
        List<User> users = um.selectUser();
        // 遍历打印每一个User对象
        users.forEach(user -> System.out.println(user));
    }
    
    // 测试根据id查询对应的数据
    public void testSelectUserById(SqlSession sqlSession){
        // 获得UserMapper接口的代理对象
        UserMapper um = sqlSession.getMapper(UserMapper.class);
        // 调用根据id查询User数据的方法
        User user = um.selectUserById(1);
        System.out.println(user);
    }
    
    // 测试修改数据
    public void testModifyUser(SqlSession sqlSession){
        // 获得UserMapper接口的代理对象
        UserMapper um = sqlSession.getMapper(UserMapper.class);
        // 查询出id为1的User对象
        User user = um.selectUserById(1);
        // 修改对象的属性
        user.setName("fkit");
        user.setSex("男");
        user.setAge(19);
        // 调用修改数据的方法
        um.modifyUser(user);
    }
    
    // 测试删除数据
    public void testRemoveUser(SqlSession sqlSession){
        // 获得UserMapper接口的代理对象
        UserMapper um = sqlSession.getMapper(UserMapper.class);
        // 查询出id为1的User对象
        User user = um.selectUserById(1);
        // 调用删除数据的方法
        um.removeUser(user);
    }
}

七测试结果

1 testSaveUser

DEBUG [main] ==>  Preparing: {call insert_user( ?,?,?,? )}
DEBUG [main] ==> Parameters: test(String), 男(String),  23(Integer)
DEBUG [main] <==    Updates: 1
插入成功，返回的id = 1

2 testSelectUser

DEBUG [main] ==>  Preparing: {call select_user()}
DEBUG [main] ==> Parameters:
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
User [id=1, name=test, sex=男, age=23]

3 testSelectUserById

Sun Sep 01 08:32:22 CST 2019 WARN: Establishing SSL connection  without server's identity verification is not recommended.  According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL  connection must be established by default if explicit option  isn't set. For compliance with existing applications not using  SSL the verifyServerCertificate property is set to 'false'. You  need either to explicitly disable SSL by setting useSSL=false,  or set useSSL=true and provide truststore for server certificate  verification.
DEBUG [main] ==>  Preparing: {call select_user_by_id(?)}
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
User [id=1, name=test, sex=男, age=23]

4 testModifyUser

DEBUG [main] ==>  Preparing: {call select_user_by_id(?)}
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
DEBUG [main] ==>  Preparing: {call update_user( ?,?,?,? )}
DEBUG [main] ==> Parameters: 1(Integer), fkit(String), 男(String), 19(Integer)
DEBUG [main] <==    Updates: 1

5 testRemoveUser

DEBUG [main] ==>  Preparing: {call select_user_by_id(?)}
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
DEBUG [main] ==>  Preparing: {call delete_user_by_id(?)}
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==    Updates: 1

标签

#MyBatis