JDBC操作结果集

之前大学的时候写过几次后就没有碰JDBC了，后来工作中用到一次，就百度查了一下，谁知道以后会不会再遇到。

代码

public JSONObject getEnterpriseInfo(String year) {
		JSONObject result = new JSONObject();
		JSONArray dataList  = new JSONArray();
		result.put("result", "200");
		result.put("des", "failure");
		// 数据统计
		Calendar cal = Calendar.getInstance();
		String thisYear = cal.get(Calendar.YEAR) + "";
		String sql = null;
		// 根据sql不同查不同的表
		if (year.equals(thisYear) && year != "" && year != null) {
			sql = "SELECT TOWN_NAME, COUNT(*)AS COUNT FROM `b_enterprise` WHERE DATA_TYPE = 1 GROUP BY TOWN_NAME";

		} else if (!year.equals(thisYear) && year != "" && year != null) {
			sql = "SELECT TOWN_NAME, COUNT(*)AS COUNT FROM `b_enterprise_his` WHERE YEAR= " + year
					+ " GROUP BY TOWN_NAME;";
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn = DbUtils.getConnection();
		pstmt = DbUtils.createPreparedStmt(conn, sql);
		//查询区镇name
		QueryCondition qc = new QueryCondition(TownEntity.ID, QueryCondition.gt, 0);
		List<Object> list = dbManager.queryByConditionNoTransaction(TownEntity.class, qc);
		TownEntity town = new TownEntity();
		Map<String, Object> tempMap = new HashMap<String, Object>();
		try {
			//操作结果集主要的部分
			rs = pstmt.executeQuery();
			while (rs.next()) {
				tempMap.put("townName", rs.getObject(1));
				tempMap.put("count", rs.getObject(2));
				dataList.add(tempMap);
			}
			if (list != null && list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {
					town = (TownEntity) list.get(i);
					if(dataList.toString().indexOf(town.getName()) == -1){
						String townNameString = town.getName() ;
						tempMap.put("townName", townNameString);
						tempMap.put("count", 0);
						dataList.add(tempMap);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.closePreparedStmt(pstmt);
		}
		result.put("result", "100");
		result.put("des", "success");
		result.put("list", dataList);
		return result;
	}
}

tempMap.put(“townName”, rs.getObject(1));中的1代表列数 别的没什么需要注意的了。(下面demo这个可能更为简洁直观)

		Connection conn = null;
		Statement sta = null;
		ResultSet rs = null;
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/easyui", "root", "root");
		sta = conn.createStatement();
		rs = sta.executeQuery("select * from e_user");
		ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
		int columnCount = md.getColumnCount();   //获得列数 
		while (rs.next()) {
			Map<String,Object> rowData = new HashMap<String,Object>();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}