/** * SQL数据查询,按ResultSet的列定义转换并返回Table,支持分页。应从前端传入完整列定义(Baas.getDataColumns( data)), * 以解决oracle等数据库不区分date、time、datetime,导致的数据格式转换问题;兼容了以前只传入列名字符串(data. getColumnIDs())的写法,但是已不再推荐。 * * @param conn * @param sql * @param params SQL中问号对应的参数值,按顺序匹配 * @param columns 列定义 * @param offset 偏移行,null则不分页 * @param limit 行数,null则不分页 * @return * @throws SQLException */ public static Table queryData( Connection conn, String sql, List<Object> params, Object columns, Integer offset, Integer limit) throws SQLException { if (limit != null && offset != null) { if (isMysql(conn)) { sql += " LIMIT " + offset + "," + limit; } else if (isOracle(conn)) { sql = String.format( "SELECT * FROM (SELECT rownum no___, A___.* FROM (%s) A___ WHERE rownum <= %d) WHERE no___ > %d", sql, offset + limit, offset); } } // System.out.println(sql); PreparedStatement pstat = conn.prepareStatement(sql); try { if (params != null) { for (int i = 0, len = params.size(); i < len; i++) { pstat.setObject(i + 1, params.get(i)); } } ResultSet rs = pstat.executeQuery(); if (limit != null && offset != null && !isMysql(conn) && !isOracle(conn)) { for (int i = 0; i < offset; i++) { rs.next(); } } Table table = null; if (columns instanceof JSONObject) { table = Transform.createTableByColumnsDefine((JSONObject) columns); } else { table = Transform.createTableByResultSet(rs, (String) columns); } Transform.loadRowsFromResultSet(table, rs, limit); return table; } finally { pstat.close(); } }