mybaties调用存储过程

Mybaties调用存储过程 1、创建实体类

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 + "]"; } } 创建UserMapper package com.mapper; import com.po.User; public interface UserMapper { //增加用户 void addUser(User user); //删除用户 void deleteUser(Integer id); //根据id查询用户 User getUserById(Integer id); //更新用户 void updateUser(User user); } 2、创建插入存储过程 创建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

调用插入存储过程

<!-- 添加用户 --> <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>

3、创建deleteUser存储过程

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

调用删除存储过程

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

4、创建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

调用更新存储过程

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

5、创建getUserById存储过程

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

在UserMapper.xml中调用getUserById存储过程

<!-- 根据id查询用户 --> <select id="getUserById" parameterType="Integer" resultType="user" statementType="CALLABLE"> {call getUserById(#{id,mode=IN})} </select>

6、代码调用

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(); } }

7、总结 在mybaties的XML文件中调用存储过程时注意： 1）每个方法上要增加statementType=“CALLABLE”； 2）方法体上要用{}包裹； 3）sql所需要的参数在注明类型的时候，要指明mode的类型（in或者out）指的是存储过程的入参或者出参数。