先看看存储过程是怎么写的
DROP PROCEDURE IF EXISTS `helloPage`;
Create PROCEDURE helloPage(tableName text,in pageIndex INT,in pageSize INT)
BEGIN
DECLARE page_index INT DEFAULT 1;
DECLARE tbl_name nvarchar(100);
SET page_index = ( pageIndex-1 ) * pageSize;
SET tbl_name = CONCAT(tableName);
SET @STMT := CONCAT("select o.* from (","SELECT * FROM ", tbl_name,") o limit ",page_index,",",pageSize,";");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
END;
调用:CALL helloPage("Notice",2,10);
然后就是JDBC的调用
//普通JDBC调用存储过程
public void jdbcTest(){
String url = "jdbc:mysql://172.18.81.206/openfire";
String name = "com.mysql.jdbc.Driver";
String user = "root";
String password = "cvtecici";
Connection conn = null;
CallableStatement callStmt = null;
try {
Class.forName(name);//指定连接类型
conn = DriverManager.getConnection(url, user, password);//获取连接
callStmt=conn.prepareCall("CALL helloPage('Notice',?,?)");
callStmt.setInt(1,3);
callStmt.setInt(2,10);
ResultSet rs=callStmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("noticeId"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
Mybatis调用 测试类
<span style="white-space:pre"> </span>//Mybatis 测试调用存储过程
public void mybatisTest(){
SqlSession sqlSession=sqlSessionFactory.openSession();
NoticeDao noticeDao=sqlSession.getMapper(NoticeDao.class);
System.out.println(noticeDao.getNotices_test().size());
}
Mybatis 的配置文件
<select id="getNotices_test" resultMap="noticeResult" statementType="CALLABLE">
CALL helloPage("Notice",2,10);
</select>
挺容易的............