分页实现（一） Mysql实现

来源：互联网发布：天天特价淘宝网官网编辑：程序博客网时间：2024/06/05 00:56

由于我的Mysql的版本低，写的时候出现了好多问题，幸好有老师搞定了嘿嘿，分享一下。。。。。。

BEGIN

DECLARE ZongJiLu INT DEFAULT 0;

DECLARE ZongYeShu INT DEFAULT 0;

DECLARE kaishi INT DEFAULT 0;

DECLARE jieshu INT DEFAULT 0;

DECLARE limit_sql VARCHAR(200);

SELECT COUNT(*) INTO ZongJiLu FROM user;

IF paginal=0 THEN

SET paginal=paginal+1;

ELSE

SET paginal=paginal;

END IF;

SET ZongYeShu=ZongJiLu/paginal;

SET @kaishi=(nowPage-1)*paginal;

SET @jieshu=paginal;

SET @limit_sql = 'SELECT * FROM user LIMIT ?,?';

PREPARE count_stmt from @limit_sql;

EXECUTE count_stmt USING @kaishi,@jieshu;

END

java相应的代码

public List<News> findNowPageInfo2(Integer nowpage) {
  List<News> entities = new ArrayList<News>();
  try {
   st = conn.prepareCall("{call FenYe(?,?)}");
   st.setInt(10,2);
   st.registerOutParameter(2, Types.VARCHAR);

   rs = st.executeQuery();
   while (rs.next()) {
    News entity = new News();
    entity.setId(rs.getInt("id"));
    entity.setTitle(rs.getString("title"));
    entity.setContent(rs.getString("content"));


    entities.add(entity);
   }
   release(rs,pstmt);

  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return entities;
}