java mysql 别名无效,JDBC SQL别名不起作用

I am trying to run the following query in my java web application:

SELECT platform AS "Platform" FROM edb.cases

The web-app is working fine and is able to execute all queries however whenever I use an alias (through 'AS'), the resultant data-set gives me a null value for the alias. In fact, despite using an alias for the column 'platform' in the above query, the resultant data-set has a null value for the key 'Platform' but gives me the correct value for the key 'platform' (which is the original name of the column).

Now the actual sql statement which I need to execute is a bit more complex with select statements and left joins on the same table twice using aliases, like so:

SELECT numOne.platform , numTwo.platform AS 'PlatformTwo' FROM edb.cases LEFT JOIN

edb.platform as numOne ON (numOne.rank = cases.platform) LEFT JOIN edb.platform as numTwo ON

(numTwo.rank = cases.highestPlatform) WHERE cases.index = 1000

The problem is that the resultant data-set contains the correct value for the key 'platform' (for numOne table) but the keys 'PlatformOne' and 'PlatformTwo' DO NOT EXIST. The aliases are not working!

I have tried both the statements in MySql workbench and they work fine.

Please do not hesitate to ask for more information.

EDIT:

The code that prepares the query and sends it to the database:

public static List> executeQuery(final String query,

Map data) {

List> result = null;

try {

Connection conn = createConnection();

PreparedStatement pstmt = null;

pstmt = conn.prepareStatement(query);

if(data != null) {

pstmt = createPreparedStatement(pstmt, data);

}

System.out.println(pstmt.toString());

//The GET_CASE_FOR_INDEX query uses the executequery function in the else block:

if((pstmt.toString().indexOf("INSERT") >= 0) || (pstmt.toString().indexOf("UPDATE") >= 0)) {

pstmt.executeUpdate();

} else {

ResultSet rs = pstmt.executeQuery();

ResultSetMetaData md = rs.getMetaData();

int columns = md.getColumnCount();

result = new ArrayList>();

/*

* Get the next row of the ResultSet 'rs' and insert a Map of the Column/Value pair

* into the ArrayList of Maps 'result'

*/

while(rs.next()) {

Map row = new HashMap(columns);

for(int i=1; i <= columns; i++) {

try {

row.put(md.getColumnName(i), rs.getObject(i));

} catch(Exception e) {

System.out.println(md.getColumnName(i));

System.out.println(row);

e.printStackTrace();

}

}

result.add(row);

}

}

destroyConnection(conn);

pstmt.close();

} catch(SQLException e) {

//TODO

e.printStackTrace();

}

return result;

}

The function creating the prepared statement:

//creates a prepared statement by checking the type of the value that needs to be set.

private static PreparedStatement createPreparedStatement(

PreparedStatement pstmt, Map data) {

try {

for(Integer key : data.keySet()) {

Object value = data.get(key);

System.out.println(key);

if(data.get(key).equals(Types.NULL)) {

pstmt.setNull(key, Types.INTEGER);

} else if(value.getClass().equals(Integer.class)) {

pstmt.setInt(key, (Integer) value);

} else if(value.getClass().equals(String.class)) {

pstmt.setString(key, (String) value);

} else if(value.getClass().equals(Date.class)) {

pstmt.setDate(key, (Date) value);

} else if(value.getClass().equals(Timestamp.class)) {

pstmt.setTimestamp(key, (Timestamp) value);

}

}

}catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return pstmt;

}

And the code snippet which uses the executeQuery function to execute the query and sends it to the web template:

Map data_details = new HashMap();

data_details.put(1, parameter_ID);

List> details = DBUtility.executeQuery(DBQuery.GET_CASE_FOR_INDEX, data_details);

webContext.setVariable("details", details);//This is where the template variable is being set

System.out.println(details);

The GET_CASE_FOR_INDEX query is :

SELECT numOne.platform , numTwo.platform AS 'PlatformTwo' FROM edb.cases LEFT JOIN

edb.platform as numOne ON (numOne.rank = cases.platform) LEFT JOIN edb.platform as numTwo ON

(numTwo.rank = cases.highestPlatform) WHERE cases.index = ?

When I print the details hash map (which is the result data-set) the key PlatformTwo is entirely absent!

解决方案

You are using the .getColumnName method of ResultSetMetaData, which returns the name of the underlying column (if available). .getColumnLabel will return the column alias as defined by SELECT ... AS ....

To illustrate, the following Java code

PreparedStatement ps = conn.prepareStatement(

"SELECT platform AS Platypus FROM cases");

ResultSet rs = ps.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

System.out.println(String.format(

".getColumnName returns \"%s\"",

rsmd.getColumnName(1)));

System.out.println(String.format(

".getColumnLabel returns \"%s\"",

rsmd.getColumnLabel(1)));

returns

.getColumnName returns "platform"

.getColumnLabel returns "Platypus"