JDBC备忘录7_ParameterMetaData和ResultSetMetaData的使用

 

通过ParameterMetaDate可以很好的进行动态sql的拼接

 

	public static void main(String[] args) {
		String sql = "select * from user u where u.money >= ? and u.name like ?";
		Object[] paramsValue = new Object[]{new Float(10.0), new String("%zhao%")};
		queryObjects(sql, paramsValue);
	}
	
	public static void queryObjects(String sql, Object[] paramsValue) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ParameterMetaData pmd = null;
		ResultSet rs = null;
		conn = JdbcUtils.getConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			pmd = pstmt.getParameterMetaData();
			for (int i = 0; i < pmd.getParameterCount(); i++) {
				pstmt.setObject(i+1, paramsValue[i]);
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				System.out.println("id = " + rs.getInt("id") + ", name = " 
						+ rs.getString("name") + ", birthday = " + rs.getDate("birthday")
						+ ", money = " + rs.getFloat("money"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

 

 

利用ResultSetMetaData可以将查询数据封装成Map对象返回

将对象包装成List并返回

public class GenerateMapByResultSetMetaData {
	public static void main(String[] args) {
		String sql = "select * from user u where u.money > ?";
		Object[] paramsValue = new Object[]{new Float(10.0)};
		List<Map<String, Object>> listMaps = null;
		try {
			listMaps = getListMap(sql, paramsValue);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("listMaps = " + listMaps.toString());
	}
	
	public static List<Map<String, Object>> getListMap(String sql, Object[] paramsValue) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ParameterMetaData pmd = null;
		ResultSetMetaData rsmd = null;
		Integer count = 0;
		
		Map<String, Object> data = new HashMap<String, Object>();
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pmd = pstmt.getParameterMetaData();
			rsmd = pstmt.getMetaData();
			count = rsmd.getColumnCount();
			String[] columNames = new String[count];
			System.out.println("columns count = " + count);
			
			//Set params value into sql
			for (int i = 0; i < pmd.getParameterCount(); i++) {
				pstmt.setObject(i+1, paramsValue[i]);
			}
			
			//Set column name into array
			for (int i = 0; i < columNames.length; i++) {
				columNames[i] = rsmd.getColumnName(i+1);
			}
			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				for (int i = 0; i < columNames.length; i++) {
					data.put(columNames[i], rs.getObject(i+1));
				}
				dataList.add(data);
			}
			
			return dataList;
		} finally {
			JdbcUtils.free(conn, pstmt, rs);
		}
		
	}
}