MyBatis如何調(diào)用存儲過程

更新時間：2024年05月28日 09:05:54 作者：云淡風輕58

這篇文章主要介紹了MyBatis如何調(diào)用存儲過程問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

項目結構

數(shù)據(jù)表t_user

創(chuàng)建User

package com.po;
public class User {
	private Integer id;
	private String name;
	private String sex;
	private Integer 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 + "]";
	}
}

創(chuàng)建UserMapper

package com.mapper;
import com.po.User;
public interface UserMapper {
	//增加用戶
	void addUser(User user);	
	//刪除用戶
	void deleteUser(Integer id);
	//根據(jù)id查詢用戶
	User getUserById(Integer id);
	//更新用戶
	void updateUser(User user);
}

創(chuàng)建insert_user存儲過程

CREATE PROCEDURE insert_user(OUT u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
INSERT INTO t_user (name,sex,age) VALUES (u_name,u_sex,u_age);
SET u_id=LAST_INSERT_ID();
END

在UserMapper.xml中調(diào)用insert_user存儲過程

<!-- 添加用戶 -->
	<insert id="addUser" parameterType="com.po.User" statementType="CALLABLE">
		{call insert_user(
		#{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}
	</insert>

創(chuàng)建deleteUser存儲過程

CREATE PROCEDURE deleteUser(IN u_id INTEGER)
BEGIN
DELETE FROM t_user WHERE id=u_id;
END

在UserMapper.xml中調(diào)用deleteUser存儲過程

<!-- 刪除用戶 -->
	<delete id="deleteUser" parameterType="Integer" statementType="CALLABLE">
		{call deleteUser(#{id,mode=IN})}
	</delete>

創(chuàng)建updateUser存儲過程

CREATE PROCEDURE updateUser(IN u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
UPDATE t_user SET name=u_name,sex=u_sex,age=u_age WHERE id=u_id;
END

在UserMapper.xml中調(diào)用updateUser存儲過程

<!-- 更新用戶 -->
	<update id="updateUser" parameterType="user" statementType="CALLABLE">
		{call updateUser(#{id,mode=IN},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}
	</update>

創(chuàng)建getUserById存儲過程

CREATE PROCEDURE getUserById(IN u_id INTEGER)
BEGIN
SELECT id,name,sex,age FROM t_user WHERE id=u_id;
END

在UserMapper.xml中調(diào)用getUserById存儲過程

<!-- 根據(jù)id查詢用戶 -->
	<select id="getUserById" parameterType="Integer" resultType="user" statementType="CALLABLE">
		{call getUserById(#{id,mode=IN})}
	</select>

創(chuàng)建UserDao

package com.dao;
import java.io.IOException;
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;
import org.junit.Test;
import com.mapper.UserMapper;
import com.po.User;
public class UserDao {
	private SqlSession	session=null;
	//獲取SqlSession對象
	public SqlSession getSqlSession() throws IOException {
		InputStream	is = Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is);
		return sessionFactory.openSession();
	}
	@Test
	public void addUser() throws IOException {
		session=getSqlSession();
		UserMapper um=session.getMapper(UserMapper.class);
		User user=new User();	
		user.setName("jack");
		user.setSex("男");
		user.setAge(20);
		um.addUser(user);
		System.out.println("添加成功，增加的id="+user.getId());
		session.commit();
		session.close();
	}
	@Test
	public void getUserById() throws IOException {
		session=getSqlSession();
		UserMapper um=session.getMapper(UserMapper.class);
		User user=um.getUserById(2);
		session.close();
		System.out.println(user);
	}
	@Test
	public void updateUser() throws IOException {
		session=getSqlSession();
		UserMapper um=session.getMapper(UserMapper.class);
		User user=um.getUserById(2);
		user.setName("john");
		user.setSex("男");
		user.setAge(30);
		um.updateUser(user);
		session.commit();
		session.close();
	}
	@Test
	public void deleteUser() throws IOException {
		session=getSqlSession();
		UserMapper um=session.getMapper(UserMapper.class);
		um.deleteUser(3);
		session.commit();
		session.close();
	}
}

執(zhí)行UserDao的addUser()方法：