我在使用UNION problem时遇到问题:我有两个表TOPIKSUBTOPIK分别执行左连接和右连接,然后使用以下语句对它们进行UNION:

mySqlStmt = "SELECT *
FROM TOPIK
LEFT OUTER JOIN SUBTOPIK
ON TOPIK.IDTOPIK=SUBTOPIK.IDTOPIK
WHERE (TOPIK.TARGETNPMHS LIKE '%0000312100006%'
OR ((TOPIK.TARGETOBJECTNICKNAME LIKE '%OPERATOR KEPALA BAA%'
OR TOPIK.TARGETOBJECTNICKNAME LIKE '%OPERATOR BAA%' )
AND TOPIK.TARGETNPMHS IS NULL))
UNION
SELECT *
FROM TOPIK
RIGHT OUTER JOIN SUBTOPIK
ON TOPIK.IDTOPIK=SUBTOPIK.IDTOPIK
WHERE (SUBTOPIK.NPMHSRECEIVER LIKE '%0000312100006%'
OR ((SUBTOPIK.OBJNICKNAMERECEIVER LIKE '%OPERATOR KEPALA BAA%'
OR SUBTOPIK.OBJNICKNAMERECEIVER LIKE '%OPERATOR BAA%' )
AND SUBTOPIK.NPMHSRECEIVER IS NULL))"

The code above works on mysql workbench, but when i use it in java,

get connection...bla..bla;
stmt =  con.prepareStatement("mySqlStmt");
rs = stmt.executeQuery();
while(rs.next()) {
  String topik_idTopik = ""+rs.getLong("TOPIK.IDTOPIK");
}

我得到了
java.sql.SQLException: Column 'TOPIK.IDTOPIK' not found.

但是TOPIKSUBTOPIK都有这个列。

最佳答案

列的别名是IDTOPIK,不应使用完全限定名:

stmt =  con.prepareStatement(mySqlStmt);
rs = stmt.executeQuery();
while(rs.next()) {
  String topik_idTopik = ""+rs.getLong("IDTOPIK");
}

08-26 10:32