分页显示数据库数据

来源：互联网发布：园林绿化施工网络编辑：程序博客网时间：2024/06/05 06:35

数据分页显示作为网站显示数据的必备需求之一，每个程序猿都应该懂得如何将获取到的数据进行分页后展示。我今天要分享的不是简单的分页显示数据，而是将获取到的数据转换成JSON格式。如果想要的数据复杂的话，就需要HashMap嵌套ArrayList，ArrayList嵌套HashMap等。具体情况具体分析，我今天用到的就是HashMap嵌套ArrayList，ArrayList再嵌套HashMap。

获取的初始化数据：

{total=35, data=[{school=南京邮电学院, married=0, email=null, age=25, name=张鹏楠},{school=南京邮电学院, married=0,email=xucc198712@qq.com, age=27, name=徐承承},{school=宁波工程大学, married=0,email=xzz@163.com, age=25, name=谢忠哲}],avgAge=27.2857}

格式化以后的数据：

{"total":35, "data":[{"school":"南京邮电学院","married":0,"email":null,"age":25,"name":"张鹏楠},{"school":"南京邮电学院","married":0,"email":"xucc198712@qq.com","age":27,"name":"徐承承},{"school":"宁波工程大学","married":0,"email":"xzz@163.com","age":25,"name":"谢忠哲}],"avgAge":27.2857}

分页展示的效果也就显示了，只给出具体的代码实现吧，还望参考：

<span style="font-size:14px;">import java.sql.Clob;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.sql.Timestamp;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.Map;public class TestDB {public static String driver = "com.mysql.jdbc.Driver";public static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";public static String user = "root";public static String pwd = "root";/** *  * @param index页码 * @param size每页的数据量 * @return返回当前页的数据 * @throws Exception */public HashMap SearchEmployees(int index, int size, String sortField, String sortOrder) throws Exception    {    String sql = "SQL语句";    //存储总的数据        ArrayList dataAll = DBSelect(sql);                //存储当前页要展示的数据        ArrayList data = new ArrayList();                int start = index * size, end = start + size;        for (int i = 0, l = dataAll.size(); i < l; i++)        {            HashMap record = (HashMap)dataAll.get(i);            if (record == null) continue;            if (start <= i && i < end)            {                data.add(record);            }        }        HashMap result = new HashMap();        result.put("data", data);        result.put("total", dataAll.size());        return result;    }/** *  * @param sqlsql语句 * @returnsql结果集 * @throws Exception */public ArrayList DBSelect(String sql) throws Exception{    Connection conn = getConn();Statement stmt = conn.createStatement();            ResultSet rst = stmt.executeQuery(sql);        ArrayList list = ResultSetToList(rst);rst.close();stmt.close();conn.close();        return list;}private Connection getConn() throws Exception{Class.forName(driver).newInstance();Connection conn = null;if(user == null || user.equals("")){conn = java.sql.DriverManager.getConnection(url);}else{conn = java.sql.DriverManager.getConnection(url, user, pwd);}return conn;}/** * 将ResultSet查询到的结果集以“列名”：“数据”的形式保存到ArrayList数组中 *  * @param rssql查询结果集 * @returnArrayList * @throws Exception */private static ArrayList ResultSetToList(ResultSet   rs) throws Exception{        ResultSetMetaData md = rs.getMetaData();    int columnCount = md.getColumnCount();    ArrayList list = new ArrayList();    Map rowData;    while(rs.next()){    rowData = new HashMap(columnCount);    for(int i = 1; i <= columnCount; i++)   {         Object v = rs.getObject(i);            if(v != null && (v.getClass() == Date.class || v.getClass() == java.sql.Date.class)){    Timestamp ts= rs.getTimestamp(i);    v = new java.util.Date(ts.getTime());    //v = ts;    }    rowData.put(md.getColumnName(i),   v);    }    list.add(rowData);        }    return list;} }</span><span style="font-size:18px;"></span>

得到的Result即可以转化为JSON格式，具体的转化过程忽略……如有需要再补充说明，谢谢。

1 0