jdbc,hibernate,mybatis调用存储过程

一，调用存储过程

　　1 jdbc

//jdbc 调用 有参的存储过程
    public static void doProcess(int id){
        System.out.println(conn);
        try {
            CallableStatement callableStatement= conn.prepareCall("{call querybyid(?)}");
            callableStatement.setInt(1, id);
            ResultSet rs=callableStatement.executeQuery();
            Book book=new Book();
            List<Book> lists=new ArrayList<Book>();
            while(rs.next()){
                book.setId(rs.getInt("id"));
                book.setImage(rs.getBlob("image"));
                book.setName(rs.getString("name"));
                book.setPage(rs.getInt("page"));
                book.setTests(rs.getString("tests"));
                lists.add(book);
            }
            System.out.println(lists.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

　　2 hibernate

 <sql-query name="testProcess" callable="true">
        <return class="com.m01.bean.News">
            <return-property name="id" column="id"></return-property>
            <return-property name="title" column="title"></return-property>
            <return-property name="author" column="author"></return-property>
            <return-property name="date" column="date"></return-property>
            <return-property name="desc" column="desc"></return-property>
            <return-property name="price" column="price"></return-property>
        </return>
        {call queryNews()}
    </sql-query>

    @Test//测试存储过程
    public void doProcess(){
        Query query=session.getNamedQuery("testProcess");
        System.out.println(query.list());
    }

　　3 mybatis

　　　在映射文件中编写 select语句 调用存储过程

<!--  编写存储过程-->
     <select id="queryById" parameterMap="myMap" statementType="CALLABLE" resultType="com.m01.bean.User">
         call queryById(?)
     </select>
     <parameterMap type="java.util.Map" id="myMap">
         <parameter property="id_in" mode="IN" jdbcType="INTEGER"/>
     </parameterMap>

    @Test//测试存储过程
    public void testProcess(){
        Map map=new HashMap();
        map.put("id_in", 1);
        User user=session.selectOne("com.m01.bean.userMapper.queryById", map);
        System.out.println(user);
    }

---

 

二，调用函数

　　1 jdbc

//jdbc 调用 有参的函数
        public static void doFunction(int id){
            System.out.println(conn);
            try {
                CallableStatement callableStatement= conn.prepareCall("{?=call eById(?)}");
                callableStatement.registerOutParameter(1, Types.VARCHAR);
                callableStatement.setInt(2, id);
                callableStatement.execute();
                String name=callableStatement.getString(1);
                System.out.println(name);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

　　2 hibernate

    @Test//测试函数
    public void doFunction(){
    
        SQLQuery query= session.createSQLQuery("SELECT eById(?)");
        query.setParameter(0, 111);
        List list=query.list();
        System.out.println(list.toString());
    }

 　　3 mybatis

<!-- 编写函数 -->
     <select id="queryPricebyId" statementType="CALLABLE"
     parameterType="java.util.Map">
         {#{var1,mode=OUT,javaType="double"}=call queryPricebyId(#{varid,mode=IN,javaType="java.lang.Integer"})}
     </select>

@Test//测试自定义函数
    public void testFunction(){
        Map map=new HashMap();
        map.put("var1", null);
        map.put("varid", 1);
        session.selectOne("com.m01.bean.userMapper.queryPricebyId", map);
        System.out.println(map.get("var1"));
    }