JDBC与Mybatis调用存储过程实例(分页存储过程)

最新推荐文章于 2024-07-18 18:15:00 发布

原创最新推荐文章于 2024-07-18 18:15:00 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

J2EE 同时被 2 个专栏收录

143 篇文章

订阅专栏

数据库

16 篇文章

订阅专栏

本文详细介绍了如何编写SQL存储过程，并通过JDBC进行调用，包括使用Mybatis简化调用流程。

先看看存储过程是怎么写的

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>

挺容易的............