Java数据库连接与分页处理-CSDN博客

本文链接：https://blog.csdn.net/zhangshuling/article/details/2886383

//---------------------------------------连接数据库的类
/*
* Created on 2008-7-28
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package others;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
* @author zhangshuling
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class Conn {
private String _driver;
private String _url;
private String _username;
private String _password;
private String _jndi;
public Conn(){
  set(1);
}

/**
  *
  * @param __driver
  * @param _url
  * @param _username
  * @param _password
  * @param _jndi
  */
public Conn(String _driver,String _url,String _username,String _password,String _jndi){
  this._driver = _driver;
  this._url = _url;
  this._username = _username;
  this._password = _password;
  this._jndi  = _jndi;
}

public void set(int _connnection_type){
  if(_connnection_type==1){
   this._driver = "org.gjt.mm.mysql.Driver";
   this._url = "jdbc:mysql://localhost/english";
   this._username = "root";
   this._password = "Auscn_ShineCode";
  }else{
   this._jndi  = "_jndi_english";
  }
}

/**
  *
  * @param _username
  * @param password
  * @param host
  * @param database
  * @param conn
  * @return
  */
public  Connection getConnection(Connection _conn){
  try{

   Class.forName(this._driver);
   _conn = DriverManager.getConnection(this._url,this._username,this._password);
  }catch(SQLException _sqlEx){
   _sqlEx.printStackTrace();
  }catch(Exception _ex){
   _ex.printStackTrace();
  }
  return _conn;
}

public Connection getConnection(Connection _conn,int _connectionType){
  if(_connectionType==1){
   return getConnection(_conn);
  }else{
   return getConnectionByJNDI(_conn);
  }
}


/**
  *
  * @param conn
  * @param jndiName
  * @return
  */
public  Connection getConnectionByJNDI(Connection _conn){
  try{
   Context _initContext = new InitialContext();
   DataSource _ds = (DataSource)_initContext.lookup(this._jndi);
   _conn = _ds.getConnection();
    }catch(NamingException _nEx){
   _nEx.printStackTrace();
  }catch(Exception _ex){
   _ex.printStackTrace();
  }
  return _conn;

}

public void closeConnection(Connection conn){
  if(conn!=null){
   try{
    conn.close();
    conn = null;
   }catch(Exception ex){

   }
  }
}
public void closePreparedStatement(PreparedStatement ps){
  if(ps!=null){
   try{
    ps.close();
    ps = null;
   }catch(Exception ex){

   }
  }
}
public void closeResultSet(ResultSet rs){
  if(rs!=null){
   try{
    rs.close();
    rs = null;
   }catch(Exception ex){

   }
  }
}
public static void main(String args[]){

}

}
//-------------------------分页处理类----------------------------
/*
* Created on 2008-9-5
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package others;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import others.Conn;
/**
* @author zhangshuling
*
* TODO To change the template for this generated type comment go to Window -
* Preferences - Java - Code Style - Code Templates
*/
public class JavaPage {
/**
  *
  */
public static final String _KEY_STR_COLLECTION = "COLLECTION";
public static final String _KEY_STR_PAGELINK = "PAGELINK";
private String _pageLink = null; //分页链接
private int _pageSize = 10; //分页单位
private int _pageTotal = 0; //总页数
private String _next_image = "../images/next.gif";
private String _back_image = "../images/backing.gif";
private String _next_image2 = "../images/next2.gif";
private String _back_image2 = "../images/back2.gif";
private Hashtable _hashTablePage = null;
public JavaPage() {
  super();
  _hashTablePage = new Hashtable();
}
public JavaPage(String _countSql, String _sql, int _pageSize, int _curPage,
   String _url, String _next_image, String _next_image2,
   String _back_image, String _back_image2) {
  super();
  _hashTablePage = new Hashtable();
  this._pageSize = _pageSize;
  this._next_image = _next_image;
  this._next_image2 = _next_image2;
  this._back_image = _back_image;
  this._back_image2 = _back_image2;
}
/**
  * @return Returns the _pageCount.
  */
public int getPageTotal() {
  return _pageTotal;
}
/**
  * @return Returns the _pageLink.
  */
public String getPageLink() {
  return _pageLink;
}
/**
  * @return Returns the _hashTablePage.
  */
public Hashtable getHashTablePage() {
  return _hashTablePage;
}
public Hashtable page(String _countSql, String _sql, int _curPage,
   String _url, boolean _method, String _methodName) {
  Connection _conn = null;
  PreparedStatement _ps = null;
  ResultSet _rs = null;
  Conn _con = new Conn();
  List _queryList = new ArrayList();
  Object[] _obj = null;
  StringBuffer _sbPageLink = new StringBuffer(
    "<table width="100%" align="center" cellspacing="0" cellpadding="0">");
  try {
   _conn = _con.getConnection(_conn);
   _ps = _conn.prepareStatement(_countSql);
   _rs = _ps.executeQuery();
   _rs.next();
   this._pageTotal = _rs.getInt(1);
   if (this._pageTotal > 0) {
    int _pageCount = (int) Math.ceil((double) this._pageTotal
      / this._pageSize); //总共该分多少页
    int _start = _curPage - 4;
    int _end = _curPage + 4;
    if (_start <= 1) {
     _start = 1;
     _end = _start + 9;
    }
    if (_end >= _pageCount) {
     _end = _pageCount;
     _start = _end - 9;
     if (_start <= 0) {
      _start = 1;
     }
    }
    int _before = (_curPage - 1 > 0 ? (_curPage - 1) : 1);
    int _next = ((_curPage + 1) < _pageCount ? (_curPage + 1)
      : _pageCount);
    _sbPageLink
      .append("<tr><td align="center" height="30" width="100%">");
    _sbPageLink.append("<img boder="0" src=""
      + _back_image2
      + "" style="cursor:hand" οnclick=""
      + getURL(_url, 1, _method, _methodName)
      + "" title="转到第1页"/>");
    _sbPageLink.append("&nbsp;&nbsp;");
    _sbPageLink.append("<img boder="0" src=""
      + _back_image
      + "" style="cursor:hand" οnclick=""
      + getURL(_url, _before, _method, _methodName)
      + "" title="转到第" + _before + "页"/>");
    _sbPageLink.append("&nbsp;&nbsp;");
    for (int i = _start; i <= _end; i++) {
     if (i != _curPage) {
      _sbPageLink.append("<a href=""
        + getURL(_url, i, _method, _methodName)
        + "" title="转到第" + i + "页">"
        + i + "</a>");
     } else {
      _sbPageLink
        .append("<span title="当前第"
          + i
          + "页"><font color="red">"
          + i + "</font></span>");
     }
     if (i < _end) {
      _sbPageLink.append("&nbsp;|&nbsp;");
     }
    }
    _sbPageLink.append("&nbsp;&nbsp;");
    _sbPageLink.append("<img boder="0" src=""
      + _next_image
      + "" style="cursor:hand" οnclick=""
      + getURL(_url, _next, _method, _methodName)
      + "" title="转到第" + _next + "页"/>");
    _sbPageLink.append("&nbsp;&nbsp;");
    _sbPageLink.append("<img boder="0" src=""
      + _next_image2
      + "" style="cursor:hand" οnclick=""
      + getURL(_url, _pageCount, _method, _methodName)
      + "" title="转到第" + _pageCount
      + "页"/>");
    _sbPageLink.append("</td></tr>");
    _ps = _conn.prepareStatement(_sql);
    _rs = _ps.executeQuery();
    int _focus = ((_curPage-1) * this._pageSize) + 1;
    _queryList = parseResultSet(_queryList, _rs, this._pageSize,
      _focus);
   } else { //总记录为0 无记录
    _sbPageLink
      .append("<tr><td align="center" width="100%">");
    _sbPageLink.append("没有符合条件的数据！");
    _sbPageLink.append("</td></tr>");
   }
  } catch (Exception ex) {
   ex.printStackTrace();
  } finally {
   _con.closeResultSet(_rs);
   _con.closePreparedStatement(_ps);
   _con.closeConnection(_conn);
  }
  _sbPageLink.append("</table>");
  this._hashTablePage.put(JavaPage._KEY_STR_COLLECTION, _queryList);
  this._hashTablePage.put(JavaPage._KEY_STR_PAGELINK, _sbPageLink
    .toString());
  return this._hashTablePage;
}
/**
  *
  * @param _url
  * @param _method
  * @param _methodName
  * @return
  */
public String getURL(String _url, int page, boolean _method,
   String _methodName) {
  if (_method) {
   _url = "javascript:" + _methodName + "('" + _url + "'," + page
     + ")";
  } else {
   if (_url.indexOf("?") >= 0) {
    _url += "&page=" + page;
   } else {
    _url += "?page=" + page;
   }
  }
  return _url;
}
public static void main(String[] args) {
  String _countSql = "select count(prob_id) from problem";
  String _sql = "select prob_id,prob_pic from problem";
  int _curPage = 1;
  JavaPage page = new JavaPage();
  Hashtable table = page.page(_countSql, _sql, _curPage, "../examine.do?method=111", true,
    "goPage");
  List list = (List) table.get(JavaPage._KEY_STR_COLLECTION);
  String link = (String) table.get(JavaPage._KEY_STR_PAGELINK);
  if (list != null) {
   Object[] obj;
   for (int i = 0; i < list.size(); i++) {
    obj = (Object[]) list.get(i);
    System.out.println(obj[0] + "---------" + obj[1]);
   }
  }
}
/**
  * 解析记录集(ResultSet)
  *
  * @param list
  * @param rs
  * @return
  * @throws SQLException
  */
public List parseResultSet(List _list, ResultSet _rs, int _size, int _focus) {
  try {
   _rs.absolute(_focus);  //定位到第多少格
   _rs.previous(); //前移一格
   int _col = _rs.getMetaData().getColumnCount();
   Object[] _obj;
   int _index = 1;
   while (_rs.next()) {
    if(_index>_size)
     break;
    _obj = new Object[_col];
    for (int i = 1; i <= _col; i++) {
     _obj[(i - 1)] = _rs.getString(i);
    }
    _list.add(_obj);
    _index++;
   }

  } catch (SQLException _sqlEx) {
   _sqlEx.printStackTrace();
  } catch (Exception _ex) {
   _ex.printStackTrace();
  }
  return _list;
}
}
//-----------------------------------测试jsp
<%@ page language="java" import="java.util.*" contentType="text/html;charset=gbk"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>分页代码</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
  </head>
  <script language="javascript">
  function goPage(url,page){
   if(url.indexOf("?")>=0){
    url=url+"&page="+page;
   }else{
    url=url+"?page="+page;
   }
     window.location.href = url;
  }
  </script>
  <body>
<%
      int _curPage = 1;
      String _page = request.getParameter("page");
      if(_page!=null){
       try{
          _curPage = Integer.parseInt(_page);
         }catch(Exception ex){
           _curPage = 1;
         }
      }

      String _countSql = "select count(prob_id) from problem";
   String _sql = "select prob_id,prob_pic from problem";
      others.JavaPage _javaPage = new others.JavaPage();
      //java.util.Hashtable _table = _javaPage.page(_countSql,_sql,_curPage,"page.jsp",false,null);    //直接链接
      java.util.Hashtable _table = _javaPage.page(_countSql,_sql,_curPage,"page.jsp",true,"goPage");  //调用js方法
      java.util.List _collection =(java.util.List)_table.get(others.JavaPage._KEY_STR_COLLECTION);
      String _pagelink =(String)_table.get(others.JavaPage._KEY_STR_PAGELINK);

      %>
        <table align="center" width="300">
        <%
        if(_collection!=null){
          int _size = _collection.size();
          Object _obj[];
          for(int _i=0;_i<_size;_i++){
     _obj = (Object[])_collection.get(_i);
              out.println("<tr>");
              out.println("<td align=/"center/">");
              out.println(_obj[0]);
              out.println("</td>");
              out.println("<td align=/"center/">");
              out.println(_obj[1]);
              out.println("</td>");
             out.println("</tr>");

          }
        }
        %>


        <tr>
          <td align="center" colspan="2" width="100%">
          <span id="pagelink"></span>
          <textarea id="content" style="display:none"></textarea>
          <script language="javascript">
           document.getElementById("content").innerHTML = "<%=_pagelink%>";
           document.getElementById("pagelink").innerHTML = document.getElementById("content").value;
          </script>
          </td>
        </tr>
        </table>
  </body>
</html>

jdbc分页