java中ResultSet遍历数据操作

Z时代
2024-01-10
分类：综合

1.查找数据库中表的列名


<pre name="code" class="html">String sql = "select *from tblmetadatainfo";
 ResultSet rs = MySqlHelper.executeQuery(sql, null);
 String str="";
 try {
  ResultSetMetaData rsmd = rs.getMetaData();
  for (int i = 1; i < rsmd.getColumnCount(); i++) {
  str+=rsmd.getColumnName(i)+",";
  }
  str=str.substring(0, str.length()-1);
 } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }

2.查找数据库中表中每条记录的列值


for(int i=1;i<rs.getMetaData().getColumnCount();i++){
   str+=rs.getString(i)+",";
  }

补充知识：Java：使用ResultSet.next()执行含有rownum的SQL语句速度缓慢

在使用Oracle数据库进行分页查询时，经常会用到如下SQL：

select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?

使用的java代码如下：


int startIdx = 0;
int endIdx = 10000; 
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
 ps.setInt(1, startIdx);
 ps.setInt(2, endIdx);
 try (ResultSet rs = ps.executeQuery();) {
 while (rs.next()) {
  String id = rs.getString(2);
  System.out.println("id="+id);
 }
 }
}

当使用以上代码时，会发现当取完最后一条记录后，再执行rs.next()时，本来希望返回false后跳出循环，但rs.next()会执行非常长的时间。解决的方法是不让rs.next()来判断下一条记录不存在，而在代码通过计数来实现：


int startIdx = 0;
int endIdx = 10000;
int i = 0;
int count = endIdx - startIdx;
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
 ps.setInt(1, startIdx);
 ps.setInt(2, endIdx);
 try (ResultSet rs = ps.executeQuery();) {
  while (rs.next()) {
  i++;
  String id = rs.getString(2); 
  System.out.println("id="+id);
   if(i == count) {
   break;
  }
 }
 }
}

如果代码中设置了fetchSize，并且fetchSize不能被count整除时，在取最后一片数据时，rs.next()也会执行很长时间：


int startIdx = 0;
int endIdx = 10000; 
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
 ps.setFetchSize(300);
 ps.setInt(1, startIdx);
 ps.setInt(2, endIdx);
 try (ResultSet rs = ps.executeQuery();) {
 while (rs.next()) {
  String id = rs.getString(2);
  System.out.println("id="+id);
 }
 }
}