JDBC 之ResultSetMetaData获取列名字

虾米姐 阅读:604 2021-03-31 12:44:41 评论: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: 获取查询字段名称,字段拥有别名也能正常取值

声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号