ibatis-调用存储过程

原创

素颜猪 2014-09-27 10:39:26 博主文章分类：ibatis ©著作权

©著作权归作者所有：来自51CTO博客作者素颜猪的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、创建工程和数据库
   1.工程名：ibatisdemo1
   2.数据库名：ibatis
       1)创建表
       表：student
       CREATE TABLE `student` (
          `sid` int(11) NOT NULL,
          `sname` varchar(30) DEFAULT NULL,
          `major` varchar(30) DEFAULT NULL,
          `birth` date DEFAULT NULL,
          `score` decimal(10,0) DEFAULT NULL,
          PRIMARY KEY (`sid`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       添加测试数据
       insert into
           `student`(`sid`,`sname`,`major`,`birth`,`score`)
       values (1,'ss','ff','2014-03-06','22');
       insert into
           `student`(`sid`,`sname`,`major`,`birth`,`score`)
       values (2,'vv','ee','2014-03-05','33');
       2)创建存储过程
       无参存储过程
       DELIMITER $
       DROP PROCEDURE IF EXISTS `mybatis`.`showdata` $
       CREATE PROCEDURE `mybatis`.`showdata`()
       BEGIN
           SELECT
               *
           FROM
               student;
       END $
       DELIMITER ;
       调用：CALL showdata();

       带输入参数存储过程
       DROP PROCEDURE IF EXISTS `mybatis`.`showdatabyid` $
       CREATE PROCEDURE `mybatis`.`showdatabyid`(IN sid INT)
       BEGIN
           SELECT
               *
           FROM
               student
           WHERE
               student.sid = sid;
       END $
       DELIMITER ;
       调用：CALL showdatabyid(2)

       带输出参数存储过程
       DELIMITER $
       DROP PROCEDURE IF EXISTS `mybatis`.`showdatacount` $
       CREATE PROCEDURE `mybatis`.`showdatacount`(OUT counts INT)
       BEGIN
           SELECT
               COUNT(1) INTO counts
           FROM
               student;
       END $
       DELIMITER ;
       调用：
       CALL showdatacount(@counts);
       SELECT @counts;
二、添加相关jar
   1.在项目中创建lib目录
       /lib
   2.在lib目录下添加jar包
       mysql-connector-java.jar
       ibatis-2.3.3.720.jar
       junit-4.4.jar
三、添加配置文件
   1.在项目中创建conf目录
       /conf
   2.在conf目录添加属性文件
       属性文件名称：SqlMap.properties
       内容：
       driver=com.mysql.jdbc.Driver
       url=jdbc:mysql://localhost:3306/ibatis
       username=root
       password=root
   3.在conf目录添加配置文件
       配置文件名称：SqlMapConfig.xml
       内容：
       <?xml version="1.0" encoding="UTF-8" ?>
       <!DOCTYPE sqlMapConfig
            PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
            "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

       <sqlMapConfig>
           
           <properties resource="SqlMap.properties"/>
           
           <transactionManager type="JDBC" commitRequired="false">
           
               <dataSource type="SIMPLE">
                  <property name="JDBC.Driver" value="${driver}"/>
                  <property name="JDBC.ConnectionURL" value="${url}"/>
                  <property name="JDBC.Username" value="${username}"/>
                  <property name="JDBC.Password" value="${password}"/>
                </dataSource>
           </transactionManager>
       </sqlMapConfig>
四、创建与数据库表中相关的javabean和映射文件
   1.在src下创建包
       cn.jbit.domain
   2.在包下创建类
       类名：Student.java
       内容：
       public class Student {
           private Integer sid;
           private String sname;
           private String major;//主修专业
           private Date birth;
           private float socre;
           // get and set 省略
       }
   3.在包下创建映射文件
       映射文件名：Student.xml
       内容：
           <?xml version="1.0" encoding="UTF-8" ?>
           <!DOCTYPE sqlMap
                PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
                "http://ibatis.apache.org/dtd/sql-map-2.dtd">
           <sqlMap>
              <typeAlias alias="Student" type="cn.jbit.domain.Student"/>
              
              <procedure id="studentproc" resultClass="Student">
               {CALL showdata()}
              </procedure>
              
              <procedure id="studentprocbyid" resultClass="Student" parameterClass="int">
               {CALL showdatabyid(#id#)}
              </procedure>
              
              <parameterMap class="map" id="outprocmap">
               <parameter property="counts" javaType="int" jdbcType="int" mode="OUT"/>
              </parameterMap>
              <procedure id="outproc" parameterMap="outprocmap">
               {CALL showdatacount(?)}
              </procedure>
           </sqlMap>
    4.在核心配置文件中添加引用映射文件
       
       <sqlMap resource="cn/jbit/domain/Student.xml"/>
五、设计DAO层
   接口：IStudentDao.java
       public interface IStudentDao {
           /**
           * 调用存储过程
           * @return
           */
           public List<Student> callProc();

           /**
           * 调用带参存储过程(输入)
           * @return
           */
           public List<Student> callProc(int id);

           /**
           * 调用带参存储过程(输出)
           * @return
           */
           public Integer callProcout();
       }
   实现类：StudentDaoImpl.java
       public class StudentDaoImpl implements IStudentDao{
           private static SqlMapClient sqlMapClient;
           static{
               try {
                   //加载配置文件
                   Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
                   //实例化SqlMapClient
                   sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
                   //关闭
                   reader.close();
               } catch (IOException e) {
                   e.printStackTrace();
               }
           }

           @Override
           public List<Student> callProc() {
               List<Student> students=null;
               try {
                   students = sqlMapClient.queryForList("studentproc");
               } catch (SQLException e) {
                   e.printStackTrace();
               }
               return students;
           }

           @Override
           public List<Student> callProc(int id) {
               List<Student> students=null;
               try {
                   students = sqlMapClient.queryForList("studentprocbyid",id);
               } catch (SQLException e) {
                   e.printStackTrace();
               }
               return students;
           }

           @Override
           public Integer callProcout() {
               HashMap hm = new HashMap();
               hm.put("counts",0);
               try {
                   sqlMapClient.insert("outproc", hm);
               } catch (SQLException e) {
                   e.printStackTrace();
               }
               return (Integer) hm.get("counts");
           }
       }
六、设计SERVICE层
   接口：IStudentService.java
       public interface IStudentService {
           /**
           * 调用带参存储过程(输出)
           * @return
           */
           public int callProcout();

           /**
           * 调用存储过程
           * @return
           */
           public List<Student> callProc();

           /**
           * 调用带参存储过程(输入)
           * @return
           */
           public List<Student> callProc(int id);
       }
   实现类：StudentServiceImpl.java
       public class StudentServiceImpl implements IStudentService {

           private IStudentDao studentDao = new StudentDaoImpl();
           @Override
           public List<Student> callProc() {
               return studentDao.callProc();
           }

           @Override
           public List<Student> callProc(int id) {
               return studentDao.callProc(id);
           }

           @Override
           public int callProcout() {
               return studentDao.callProcout();
           }
       }
七、测试
   1.在项目中创建test目录
       /test
   2.在test目录下创建包
       cn.jbit.junit
   3.在包下创建测试类
       类名：StudentTest.java
       public class StudentTest {
           IStudentService studentService = new StudentServiceImpl();
           /**
           * 测试调用带参存储过程(输出)
           */
           @Test
           public void testCallProcOut(){
               int result =studentService.callProcout();
               System.out.println(result);
           }


           /**
           * 测试调用带参存储过程(输入)
           */
           @Test
           public void testCallProcbyid(){
               List<Student> students =studentService.callProc(2);
               for (Student student : students) {
                   System.out.println(student.getSid());
               }
           }

           /**
           * 测试调用存储过程
           */
           @Test
           public void testCallProc(){
               List<Student> students =studentService.callProc();
               for (Student student : students) {
                   System.out.println(student.getSid());
               }
           }
       }