JDBC SQL别名不起作用 [英] JDBC SQL aliasing not working
问题描述
我正在尝试在我的java Web应用程序中运行以下查询：
I am trying to run the following query in my java web application:
SELECT platform AS "Platform" FROM edb.cases
web-app工作正常，但是每当我使用别名时都可以执行所有查询（通过'AS'），结果数据集为别名提供了一个空值。实际上，尽管在上面的查询中使用了列'platform'的别名，但结果数据集对于'Platform'键有一个空值，但是给了我关键'platform'的正确值（这是原始的）列的名称。）
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).
现在，我需要执行的实际sql语句对于select语句有点复杂，并且使用别名在同一个表上左联接两次，像这样：
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
问题是结果数据集包含键'platform'（对于numOne表）的正确值，但键'PlatformOne'并且'PlatformTwo'不存在。别名不起作用！
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!
我已经尝试过MySql工作台中的两个语句，它们运行正常。
I have tried both the statements in MySql workbench and they work fine.
请不要犹豫，询问更多信息。
Please do not hesitate to ask for more information.
编辑：
准备查询并将其发送到数据库的代码：
The code that prepares the query and sends it to the database:
public static List<Map<String, Object>> executeQuery(final String query,
Map<Integer, Object> data) {
List<Map<String, Object>> 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<Map<String, Object>>();
/*
* 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<String, Object> row = new HashMap<String, Object>(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<Integer, Object> 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;
}
使用executeQuery函数执行查询并发送它的代码片段到网络模板：
And the code snippet which uses the executeQuery function to execute the query and sends it to the web template:
Map<Integer, Object> data_details = new HashMap<Integer, Object>();
data_details.put(1, parameter_ID);
List<Map<String, Object>> 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);
GET_CASE_FOR_INDEX查询是：
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 = ?
当我打印详细信息哈希映射（结果数据集）时，关键PlatformTwo完全是缺席！
When I print the details hash map (which is the result data-set) the key PlatformTwo is entirely absent!
推荐答案
您正在使用 .getColumnName 方法 ResultSetMetaData ，它返回基础列的名称（如果可用）。 .getColumnLabel 将返回由 SELECT ... AS ... 定义的列别名。
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 ....
为了说明，以下Java代码
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)));
返回
.getColumnName returns "platform"
.getColumnLabel returns "Platypus"
这篇关于JDBC SQL别名不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
