JDBC 之ResultSetMetaData获取列名字

港控/mmm° 2023-10-10 12:31 29阅读 0赞

## ResultSetMetaData 功能结束： ##

整个数据库的信息：表名、表的索引、数据库产品的名称和版本、数据库支持的操作(增、删、改、查等等)

## 获取列名 ##

获取列名字有两种方式

### （1）getColumnName(int); ###

### （2）getColumnLabel(int); ###

这两种方式有什么区别？

项目实例:

String sql = "select tb.sid as sid, tb.arch_sid as archSid, tb.unit_proj_sid as unitProjSid, tb.individual_proj_sid as individualProjSid,\n"
      					+"tb.eng_proj_sid as engProjSid, tb.file_id as fileId, tb.file_no as fileNo, tb.arch_file_no as archFileNo, tb.file_title as fileTitle, \n"
      					+"tb.responsibility as responsibility, tb.file_image_no as fileImageNo, tb.start_date as startDate, tb.end_date as endDate,\n"
      					+"(select s1.value_	from sys_data_dict s1 where s1.key_ = tb.manuscript_code and s1.category_code ='MANUSCRIPT_CODE') as manuscriptCode, \n"
      					+"(select s1.value_	from sys_data_dict s1 where s1.key_ = tb.media_type_code and s1.category_code ='media_type_code') as mediaTypeCode,\n"
      					+"(select s1.value_	from sys_data_dict s1 where s1.key_ = tb.security_level_code and s1.category_code ='SECURITY_LEVEL_CODE') as securityLevelCode,\n"
      					+"(select s1.value_	from sys_data_dict s1 where s1.key_ = tb.storage_type_code and s1.category_code ='STORAGE_TYPE_CODE') as storageTypeCode,\n"
      					+"tb.page_no as pageNo, tb.text_nums as textNums, tb.draw_nums as drawNums, tb.base_nums as baseNums, tb.photo_nums as photoNums,\n"
      					+"tb.negative_nums as negativeNums, tb.open_status as openStatus \n"
      					+"from  ucas_file_info tb where 1 = 1 and (tb.unit_proj_sid is not null or exists(select 1 from ucas_unit_proj proj where proj.sid = tb.unit_proj_sid)) limit %d, %d";

这是一条很简单的查询语句，但是呢，他有很多字段进行了重命名，比如:arch\_sid -> archSid、unit\_proj\_sid -》unitProjSid 等等。

通过ResultSet获取到的ResultSetMetaData对象:

通过第一种方式getColumnName(int)获取到的字段还是为arch\_sid \\ unit\_proj\_sid  
通 过第二种方式getColumnLabel(int)获取到的字段才是为archSid \\ unitProjSid

ResultSet rs = null;
      		PreparedStatement ps = null;
      		try{
      			ps = conn.prepareStatement(String.format(sql, pageNo, pageSize), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      			ps.setFetchSize(Integer.MIN_VALUE);
      			rs = ps.executeQuery();
      			
      			ResultSetMetaData colData = rs.getMetaData();
      			ArrayList<HashMap<String, Object>> dataList = new ArrayList<HashMap<String, Object>>();
      			HashMap<String, Object> map = null;
      			
      			String c = null;
      			Object v = null;
      			while (rs.next()) {
      				map = new HashMap<String, Object>(100);
      				for (int i = 1; i <= colData.getColumnCount(); i++) {
      					//c = colData.getColumnName(i);
      					c = colData.getColumnLabel(i);
      					v = rs.getObject(c);
      					if(v instanceof java.sql.Timestamp){
      						// 处理java.sql.Timestamp 与es 日期个数转换
      						if(v != null){
      							 DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      							 String str = dateFormat.format(v);
      							 map.put(c, str);
      						}
      					} else {
      						map.put(c, v);
      					}
      					
      				}
      				dataList.add(map);
      				
      			}
    
      			for (HashMap<String, Object> hashMap2 : dataList) {
      				bulkProcessor.add(new IndexRequest("fileinfo").source(hashMap2));
      			}
    
      			logger.info("-------------------------- Finally insert number total");
                  // 将数据刷新到es, 注意这一步执行后并不会立即生效，取决于bulkProcessor设置的刷新时间
      			bulkProcessor.flush();
      		}catch(Exception e){
      			e.printStackTrace();
      			logger.error(e.getMessage());
      		}finally {
      			try {
      				rs.close();
      				ps.close();
      			} catch (Exception e) {
      				e.printStackTrace();
      				logger.error(e.getMessage());
      			}
      		}

## 总结: ##

getColumnName：获取表名称，一旦字段拥有别名无法取值。

getColumnLabel: 获取查询字段名称，字段拥有别名也能正常取值